Why Rebuild Indexes? |
Vložit
- čas přidán 21. 07. 2024
- In this episode of daily DBA, I pick up 5 important #DBA related questions and give my answers! Do not forget to checkout BONUS QUESTION at the end of the video!
00:00 Performance Tuning
01:21 How to open database when archive log is missing?
07:06 Could you also explain about high water mark please?
17:33 #dba Challenge!
19:48 Why we need to rebuild the indexes?
24:39 #dba Challenge!
25:07 When you issue SELECT * FROM EMP; does it use Indexes on the table?
25:39 What is the difference between data file header and data block header? What it contains?
26:41 #dba Challenge!
Bonus Question
27:15 Why can't we give DBA access to developer team?
Website: www.dbagenesis.com/
Facebook: / dbagenesis
Instagram: / dbagenesis
Twitter: / dbagenesis - Věda a technologie
If you’re selecting all the records then index usage and rebuild are not needed because it will be an overhead on the database. This is because reading the index and scanning the entire table at the end will be expensive as compared to just the full table scan. If the query output is more than 50% full table scan is better than index scan. #dbaChallenge
I neva seen someone explaining HWM in such a conceptual way... Kudos to ur simplicity...
Most welcome !
You are Awesome Arun 👏🏻👏🏻👏🏻 I guess no one will explain wt is meant by high water mark better than this . I have been following ur videos for 1month . I will send a personal email for queries and other details . Again Kudos to your explanation 🙌🏻🙌🏻🙌🏻Thanks for Sharing the Knowledge.😊😊😊
Hi Arun,
A) Rebuild index on the reporting table (Only Selects) -> It depends on the data distribution.If data is fragmented,we can reorg the table and rebuild index once .
If no further DML's are happening on the table,then no need to rebuild the indexes.
B) select * from emp; -> It will be a FTS as we are querying all the data whitout any predicate.
Even if predicate is there,it depends how much data needs to be retrieved . If data with predicate is more than 80% of the rows,optimizer will prefer FTS.
Thanks
Exactly what you explained in this video about open database forcefully happened to me in my Job. We had to go through the same route to fix the issue! Very good analysis
Thanks for sharing
Amazing 👌, thanks very much
Hi Arun really amazing videos and good stuff to learn and refresh topics.. pls explain SPM and sql plan baselines
Hi Arun Sir,
Thanks for all the knowledge you are sharing with us .
so mine answer for #DBAChallenge A2:- We have to rebuild index on reporting server also , because even on reporting server data stored in it changes by time as the changes made to database.
@DBA Genesis - Is this answer correct? Please revert.
Outstanding way of teaching 🎉🎉
As per my knowledge and understanding am answering these .
Yes , since reporting database is for selecting the contents mainly . Indexes will improve the performance. These are useful .
For select * from table , index won’t be used bz it has to be under go full table scan .
DB file header Contains metà info about the file and dB block header contains space mgmt info .
Hlo can you explain me difference of rebuild index and rename index...
Do by using rebuild index add extra index from the previous one in a table? And in rename index it change the position of previous index
Hi Arun,
I'm soo glad I've been a part of this channel... I don't think anyone would explain things like u did, no channel is dedicated to just DBA stuff like yours and these series of dailyDBA videos took it to next level. Keep doing wat Ur doing :) thanks a ton.
My question: after we do some kind of performance tuning on the database like index rebuild or tablespace reorg, how do I measure the performance before and after?? How can I be sure that my tuning has made any difference to the performance or not?
PT is a reactive process. So you need to know that your application team is complaining about queries running slow. This gives you some data like xyz query is taking 30 min to execute.
You start PT on this one query first and later speak with application team as to how much time it takes to execute the same query. Thats how you gather details that your tuning tasks are in positive direction.
All in all, you must have a PT problem OR some stats to check your actions agains!
Where i can purchase this book
All DBA Genesis books can be found here: support.dbagenesis.com/dba-books/
Thanks a ton Arun for explaining in such tiny detail. Watching your videos surely giving me lot of knowledge. Take a bow for this great work 🙇♂️ . Looking forward for some more excited content like this.
Most welcome! Kindly help me in my vision to reach all the DBAs on the planet. Let's build the biggest DBA community and spread the word as much as possible.
Lot of knowledge gathered for interview
Indexes on reporting table need not be rebuild as its only required when there is deletes inside table.Also For select * from table , index won’t be used as it doesn't has where clause rather it will go for full table scan Correct me Arun if I am wrong here .
Well explained. Very easy to understand.
Hlo can you explain me difference of rebuild index and rename index...
Do by using rebuild index add extra index from the previous one in a table? And in rename index it change the position of previous index
Thanks for the video. I would recommend in the notes putting links/time stamps of the video where the question is asked and answered, so someone can get to the question they want to know about quickly. Thanks!
Good idea J.M. will ask my team to do it!
i second to this :)
Informative. I have a question for you. Suppose my source DB LOGGING is set to false, and when you try to perform a clone using the RMAN from source backup to destination DB. When I try to perform recovery after the clone, it fails due to out of sync SCN. My questions are
1. What effect l would NOLOGGIN have in this scenario?
2. Will setting the parameter you mentioned to ignore SCN help in my case
Thanks
Very nicely and deeply explained..one question when the high water mark is at lower level and the actual data in data block is at higher level, what happens..dies our resource falls short...impact?
Very good job in explaining the high water mark, however regarding why rebuilding indexes explanation I don’t think it is correct, but overall very helpful video thank you
#dbaChallenge
Data block header consists of information, such as the block address and the type of segment.and also information like pct used % and pct free%.
first time I am understanding high water mark such a nice and easy way , well explained Arun you are genius and your lectures are very interesting.
It's my pleasure
Hlo can you explain me difference of rebuild index and rename index...
Do by using rebuild index add extra index from the previous one in a table? And in rename index it change the position of previous index?
Awesome hwm explanation Why doesn't oracle reset hwm on rows deleting directly?
Hey could you please tell more about space shrinking and block defragment, actually I have answered in an interview 4years before but now it's not in my brain
Hi Arun, How frequently we should shrink the datafiles ? By looking at which parameter/metrics, we should consider to shrinking the datafile ? We have db in TB size and its not possible to shrink those no of datafiles ? pls advise
Hi Arun,Doesn't oracle does not shrink the high water mark,because it thinks there will insert again in the block ,so it can avoid this costly operation.Could you please comment if my thought is correct ?
DB block contain : header, table directory, Row directory, row data, free space
Can you please explain 2 term PCT FREE & PCT USED?
on Reporting table index rebuild is not required because there is no DML apply on table. so no shuffling of record as explained in video with using book
Good video arun
Awesome
Hi Arun,
1. I think with a select * from table an normally index will not be used. Only when selecting with a where clause. As databases are cost based (rule based days are over) these days Oracle itself will calculate whether it will need an index. I think if you still want the database using an index even though oracle itself calculates differently then you can use a hint in your query.
2. I think rebuilding an index is useful as it is making queries faster but may I suggest it is good to also sometimes rearrange the data in the table as not only rebuilding the index will speed up a query but also the index clustering (which is one of the criteria for using an index) will be lower which then again lowers the amount of datablocks needed in a query. Maybe Arun can explain about index clustering to those who are not familiar with that.
I agree on both the points mentioned by you!
Can you explain about oracle top wait events with real time examples?
Sometime in upcoming episodes !!
You can simply do an explain plan for the select statement and see if its a full table scan or index scan(depends on what column the index is created on)
I'll try to answer. Hope someone will correct me if I'm wrong.
1)I guess rebuilding index is not necessary if the table is used only for select(which is unlikely);
2)There is data block address and row chaining information stored in data block headers also HWM info;
3)When you issue select * it doesn’t use indexes. You have to use WHERE clause with indexed column to make it happen.
Index are used when indexed column are used in where Clause ....
Hi sir,i am a beginner and have one question,there was 2node rac test database and i took down the 1st node then By mistake i took down the crs with VIP and the both nodes goes down,and then i was not able to get session of VIP,which node i took down was successfully up but the 2nd node is still down and there is no any d.bin services are running, how can i take the both nodes up?
How about a quick reboot? Oracle restart service will auto bring up both the nodes DBs.
Index will only be used if the where cause reference the index column in certain orders too
datablock header contains the transaction info like locking information on every row that particular data block contains
Sir. Respect.
Yes, respect for everyone who is part of our DBA community!
very useful
Keep watching
on select * from emp indexes are not use because it is full table scan.
Nice explanation sir.. Make some more difficult levels for performance tunning in oracle.
Sure, will be publishing soon
Where i can purchase this book
I have a question.regularly I am getting one question from application is that,one of the query is ran fine upto yesterday.but today it is taking more time to fetch records why?.for this what exactly need to check.
Check the data growth in the table. If there is genuinely more data inside the table, it will take time.
Run stats on that table for indexes to be updated, and the data growth in the table plays big part too. Make indexes invisible if necessary to test performance on query
i created Indexes on table but it created in system tablespace so would it make impact on performance.
Not really
Yes
indexes are used when you are accessing a small amount of data , in the exampl we will have a full table scan
Hi Arun,
First of all let me tell u , you are doing an amazing job.
I have one doubt regarding SQL query tuning,
how and when we should modify a query by "with clause"
and use /*+materialize*/ hint.
please answer this.
in what conditions index is crashed in reporting tables and needs to be rebuilt even no delete is performed
HWM is at segment level, not at individual block level.. at Data Block level it is PCTFREE and PCTUSED only, not HWM.
Hi Arun,
What is split brain and symptom of split brain in RAC database???
Picked it up for upcoming episodes
Thanks Arun 🙏
No need to rebuild indexes for report table which is only use for select query purpose..
how do i reset high water mark?
kindly explain b-tree vs bit map indexes
Picked up for upcoming episodes!
How to check waste space in table and how much space it is?
When u issue select * from emp:
Ans: I think no , as there is no condition clause.
What data block header contains ?
Scn, data file number,
Where and how can I learn performance tuning??
Very soon, we are about to launch PT training in the second quarter of 2020
Does gathering stale index stats and rebuilding indexes are same?
What do you think?
Both are same.
Rebuilding indexes is for benefit of index scanning
Data block header contains high water mark level
2 million count(*) - 0.015 s, deleted 1.5 million - count (*) - 0.15 s , truncated table count(*) - 0.008 s
A DBA is a sole administrator that should be held responsible and to oversee any other activities may he be a developer or whatsoever.
When a query is executed .. in order to optimize the performance of that query indexes are used... in other words it is used to locate the data quickly
Are you sure? for a select * from emp; oracle will use indexes?
@@dbagenesis hi Arun , for select * from emp ... it wont use index.. rather it performs full tablescan..index will be used if any column has a index specified
DBA Genesis Link for purchase book
How to handle the environment where you're only one DBA in that environment?
So whats your specific question? Is your job demanding you to stay more hours? Or your team does not have proper KT? Or you don't know what to do?
Any of the above, speak to your manager or boss whom you report to. If you still the only one, the speak to the client whose db you managing.
Hi Arun.
My answer for select * from emp;
No, it will not take an index scam to fetch a data...
Index are used when you include the indexed column in where clause.
Select * from tables not using indexes
Regarding your question on "rebuilding indexes on a table which is dedicated for SELECT cmds."
--- Yes, we can because index rebuild fasts up SELECT related query performance. There will be a significant improvement in performance wise.
Nope, indexes on reporting table need not be rebuild. Remember, index rebuild must be done when there are deletes inside the table. If a table is just reporting table they you are wasting system resource in rebuilding the index!
No becasue there is No where Clause