Why Rebuild Indexes? | #dailyDBA 20

in performance tuning this is what I love about performance tuning like people talk about aw our people talk about so many other stuff indexes rebuilding indexes stats gathering and all like it's not one thing that impacts the performance it's everything so like the way data is stored inside the database is one of the most critical parameters or concepts that you all have to master even before you try to become a performance building welcome back guys are on this side and today I have something special for all of you I guess I have this book and by the way I am NOT going to promote this book don't worry but there is something like one question out of all the daily DBA questions I would need this book to explain you guys something very important so meanwhile like until we reach that particular question let us first start over show with the first question of the day and when I am that particular question or when I am at that particular question I would be using this book alright that being said let us start our first question of the day how do open database when archive blog is missing hmm very typical and very critical situation and it happens in real time like I have personally faced these kind of issues where you are trying to recover a database and you don't have archived logs like what can you do and in these kind of situations so what generally people do is they contact the Oracle support and what Oracle support will do is I mean they have a hidden parameter inside Oracle if you set that hidden parameter inside the parameter file you would be able to open the database forcefully of course there are some positives and negatives or benefits and drawbacks of using these kind of hidden parameters one condition like what I am going to say you have to use it only after contacting Oracle support like don't use it without informing Oracle support they must know that something has gone wrong in your environment the archive block was missing and even they should actually they are the people who should tell you like okay go ahead and use this particular parameter so let me tell you what exactly that parameter is it is allow reset logs corruption now this parameter once you use this mitr allow recent logs corruption equal to true what will happen is Oracle will not be looking for new archive logs that are required to recover or open your database right now when you perform a normal database recovery like we all know whenever we perform a normal database recovery we need to apply those archive logs whichever Oracle is demanding in order to bring the database into a consistent State and once you have the database in a consistent state then only you can open the database ha all right so that being said like this particular parameter let's take you don't have an archive log all right Oracle is telling you to apply an archive log and you don't have the archive log maybe you misplaced it or the archive logs are deleted or you don't have our third log backups what will you do in these kind of situations if you set this parameter allow reset log corruption equal to true in the parameter file then it will allow you to open the database even though your data files SCN is not in sync like what do we mean by the data file SCM see when you try to openly database right so in the database data files header the Sen number must be same so only if the Sen number in all the data files is same then only you can open the database right now when you apply archive logs what happens the archive logs increase the Sen number while recovering the particular data file right while you are applying the archive locks on to the database what happens is it will increase the Sen number right so once all the data file header SC n number comes in sync that's when it allows you to open the database now the biggest problem is if you miss the archive log you cannot bring the same number in sync right that's when you have to use this hidden parameter and then you can open the database but there are some issues with this parameter like it's not as simple as you think one of the recommendations of the Oracle support is whenever you are using this parameter you need to rebuild the database now what is the meaning of rebuilding the database after you open the database using this parameter so the rebuilding part is once you open the database using this parameter forcefully all right so you have to export the data from the database like create a new database and import the data into the new database now understand for multi terabyte databases it's close to impossible in order to export import into a new database so what we do or rather I will share my experience 99.9% of the times I mean there is no data loss okay so it's safe and I mean I have never seen there is loss of data inside the database I mean Oracle says that when you open the database using this parameter forcefully it might corrupt the database now forget about the database but it does corrupt your undo tablespace and that is the reason what happens is when you open the database the undo tablespace will crash and that's when you need to rebuild the index or you need to bounce the database sorry you need to actually rebuild the undo tablespace once you rebuild the undo tablespace then you have to bounce the database once you bounce the database it's open all right so this parameter very careful and each one of you should be using this parameter at your own risk or at least inform the Oracle support when something like this happens and only if they allow you to use this parameter then only go ahead and use this parameter inside your database that being said I think that will help you so let's move on to the next question could you also explain about high watermark of course why not and this high watermark is very important for all the DPS and it is dead important when it comes to performance tuning so guys performance tuning is not only about the indexes it is also about how data blocks are stored inside these segments now when it comes to these particular table segments there is something called as high watermark and to understand this high watermark I think I need some water bottles to explain you all the example let me get some water bottles alright guys I'm back with two water bottles I think this one is empty and this one has some water alright so for me to explain you what exactly is high watermark I think this is such a coincident right like for explaining high watermark we are using water alright that being said let's come back to our example now understand this is a data block alright it's a new data block and Oracle considers our data block as a data block right so this data block is having no data or no records stored inside the data block alright so in this data block currently if I ask you what is the high watermark assume that my left hand is the high watermark right so currently the data is at level 0 so the high watermark is set to 0 alright now we'll try to store some record or some data into this data block and see how high watermark increases okay so currently this is the high watermark it is set to 0 because there is no data inside the data block right so let us insert some data I mean we'll insert some water now if you look back to this data block what do you think the high watermark has increased so what exactly is high water high watermark is the level till which data has ever been stored inside the data block be very careful to what I am saying high watermark is the level to which data has been stored in a data block ever okay I'll come back to this definition in some time so now the high watermark has been increased to this level right because we have this much data inside the data block now let us pour some more water and see what happens to the high watermark alright we are back so earlier the high watermark was this much now we have some more data inside the data block so high watermark has been increased to this level perfect everything is fine now let me delete some data out of this particular data block and see what happens to the high watermark now assume this this is the high watermark level okay and just look at what happens when I delete the data from this data block so eventually we will try to drink some water or maybe I'll code it in the other world model alright so now we have deleted some data out of this data block now what do you think what is the level of high watermark this one no the high watermark will still be the one that was earlier all right now you might be wondering would like why this is the speciality if you look back to the definition of high watermark which I mentioned earlier high watermark is the level like the max level that the data block has ever stored the data now if you look at this data block the level the max level or the last highest level that's why it is called as high watermark so the last highest level till where the data block stored the data can you see okay this part is deleted even we all know but high watermark shows or displays the maximum or the highest level that this data blocked ever stored any data right now even if I delete entire data out of this block even if I removed the entire water even if I delete each and every single record and even if I make this data block empty still the high watermark will be there like desperation because this is the max height highest level of data that was stored inside this data block now that being said you might have this wonderful question like so what's going on what is the impact of high watermark on the database performance there is a very big impact of this high watermark when you delete the records and when you truncate the records I'll come to the topic in a minute but let me first talk about what happens inside the database now assume this let's take your database is going for a full table scan okay so I'll try to have three data blocks and we are assuming okay we are just assuming all these three blocks were 100% full so like the high watermark on all these three bottles were like on to the top of the block or these water bottles I guess you all get me and get what I'm saying so high watermark levels on all the three bottles is to the top of the bottle right now when Oracle is trying to go for a full table scan Oracle assumes with the high watermark that all the three blocks are full and they contain records now when Oracle starts to read this block then it will get an O this much data is empty okay when Oracle tries to read this data block Oracle will try to assume oh okay this much is empty when Oracle tries to read this data block Oracle will think like oh okay now assume how much resources have been wasted in reading these blocks when your high-water mark shows the different data compared to the records that are stored inside the data block right so once again so if your high-water mark is set to whatever level inside the data block and if the data is not up to the high-water mark which might happen because the data has been deleted all right it happens inside the database so if the data is deleted the high-water mark displays the highest level of data that was ever stored inside the data block now this high watermark shows or tells Oracle that okay this data block had data or has data but until unless Oracle comes and breed this block till then it cannot say whether this block is empty or whether this block contains data right now that being said you all might have this very important question saying like so why does Oracle do not reset the high watermark Oracle will reset the high watermark only in conditions where you are using either the truncate or you are trying to shrink the space and that's why it is very important to shrink the space inside the table spaces or your Oracle database so that these kind of high-water marks can be reset to the exact value or exact levels inside the data block now assume this data block okay so we don't have any data in this second data block all right high watermark is still here but assume I tend to shrink this space inside the tablespace so what happens is this high watermark will be reset to zero all right can you see so for these two like high watermark has come down till here for this data block for this data block it has come to zero for this data block it has come to the level of the water right now when Oracle tries to read each and every data block what will happen Oracle will read this data block because high watermark shows that there is some data Oracle will skip this block because high watermark shows there is no data right and Oracle will read this block because the high watermark shows that there is some data all right so guys in performance tuning this is what I love about performance tuning like people talk about aw are people talk about so many other stuff indexes rebuilding indexes stats gathering and all like it's not one thing that impacts the performance it's everything so like the VAE data is stored inside the database is one of the most critical parameters or concepts that you all have to master even before you try to become a performance tuning expert that being said I think this example is one of the best examples or I tried my best to explain all of you what exactly is high watermark I think you all get it so let's move on to the next question of the day guys before I move on to the next question I would like to give this as a debate challenge to all of you all right now understand this what you can do is take this as an experiment like to test how fast or what is the impact of high watermark on your database queries okay so do this thing try to create a test table and on that test table try to insert 10 million records you can always create a pl/sql loop to try and insert the records commit the data all right now do one thing try to issue the command like select count star from the table and just record the time it takes to get the output right that being said next try to delete all the records using the delete command okay and you can commit the data also commit and then try to once again issue the same command select count star from that particular table and then you will see the magic this command even though the data has been deleted from the table still it will take the same amount of time compared to your previous query which you ran before deleting the records so why does it happen even though you have deleted all the records why the query is taking same time because the high-water mark has not been reset inside the data blocks alright now to test the experiment again what you do is this time try to insert 10 million records into the same table and try to truncate the table ok so before proceeding of course you issue select count star from the table and then record the time whatever time it takes to get the output of the query next truncate the table and then run the same query select count star from the table and then you will see the magic like this command this time will work way faster because truncate command resets the high-water mark that being said I guess I want all of you to post your experiment results in the comments below this video I would love to see like what all have you discovered that being said now it's time to move on to the next question of the day why we need to rebuild the indexes amazing question now this is the reason I mentioned this book I mean I've got this book but I never read it I mean it's still pending I need to read this one so to talk about rebuilding the indexes I would need an experiment to talk to all of you so let me show you this experiment first I mean I'm not going to damage the book though but let me explain you what I am trying to say guys indexes is as simple as you see the contents like table of contents right so whatever you are seeing over here like inside the database it's exactly the same now we have the chapter names and the page number okay now you tell me I am trying to go to manufacturing muscle which is at page number 19 okay so let me go to page number 19 where is it alright this is page number 19 I'm not sure if you all can see the page number but that's okay this is page number 19 and you can see manufacturing muscle this is the I think chapter 2 right assume this now what I will do I mean I'm not going to do I am going to delete or remove these three pages okay so assume that this is the database table and somebody is deleting the data from the table alright so if I remove these pages what happens okay so I'll try to fold them rather deleting okay that's not a good idea to remove these pages so I would rather fold these pages now the biggest issue is there is a problem in the counting of the pages because this page is page number 18 this is 17 the side it is 18 I think okay this is 17 this is 18 19 to 24 are deleted and this is 25 so there is a disconnect in the page numbers right so that means this 25th page number should be 19th page number according to the new page numbers because these pages are missing right so these missing pages we need to make sure the numbers are in series right in order for the index to work perfect so if this phase number is 17 this is 18 the next page should be 19 right so whenever we delete or remove any pages from a book we need to make sure the numbers are in serial right that's I mean that will help you in order to find the data faster okay now that being said I have deleted some data inside the table right now don't you think it is our responsibility to tell the index that hey I guess page number 19 is missing so what we have to do so either we have to update the index right because that page we have deleted we have removed that page right and if we are renaming the page numbers in the new order then we have to make sure that the index is referencing to the correct topic right because on the new nineteenth page it would not be manufacturing muscles it might be some other topic right I hope you all get the idea so if you try to delete data inside the table what happens is the index needs to be rebuild because index need to identify the new locations and rearrange the data right now the next important question that arises is when do you go for rebuilding the indexes see if your table is having 20 to 30% deletes like if that's the amount of deletes that happens inside a table that time you can blindly go for rebuilding the indexes so it's not only like 20 to 30% it's like anything about 20 to 30% if you have those range of deletes then you should go for rebuilding indexes now ways let me give you all another debate challenge okay I want all of you who's listening to this video tell me this you have a table and it's a reporting table like only selects are run against the table do you still go ahead and rebuild the indexes on that particular table such the DBA challenge all right put on your comments and I will respond to all of you inside the comments that being said let's move on to the next question when you issue select star from EMP does it use indexes on the table what do you think guys Yes No maybe I think let's take this also as one of the BB challenges and try to answer it inside the comments because indexes are used when I am NOT going to say the answer put your answers into the comments and I'll see you all over there that being said let's move on to our last question of the day what is the difference between data file header and data block header what it contains all right I think this is pretty straightforward the data file header would contain the details about the data file and the data block header will contain the details about the data block I think that the sweetest answer I can give you but let us drill down so the data file header will contain the details like size of the data file which tablespace it belongs to which is the or what was the last checkpoint number what was the latest Sen number what is the data file number like inside Oracle each data file has a unique number that is assigned to it like the SIS data file will be number one right and then you also have details about the database it belongs to the database ID that the data file belongs to it also contains the data file creation data also guys right so these are the details that the data file header contains now when it comes to the data block I guess once again guys I think this video will have the maximum number of DBA challenges and I would want all the debates to put on your comments below this video about what exactly the data block header contains alright so I guess we have three DBA challenges and I want all of you to comment below this video I'll see you all in the comments and meanwhile I guess it's time to jump on to the most exciting part of the day and that is the bonus pack and this is one of the bonus questions which I have chosen so 1db a was asking me this question like why can't we give access the DB access to the developer team because they're sending them lot of requests and he's getting pissed off I guess this is like I mean why don't you give like the DB access so the developer will do your job and developer will do his job and then you can sit at home and relax right so you are the DBA owner why do you want to give the DB access to someone else you take the responsibility why company hires a DBA there are some policies there are some security policies in place the developer needs to develop the code if they need help they will come to you now as a DBA let's take you have some work on the storage why don't you think the storage team give you the entire access why don't you think the network team gives you the entire access of the network why don't you do everything so what you do is you take the jobs of all the network admin storage admin system admin take over and include and give it as a package to the developer along with the DBA role like I mean there is a way that things work there is a way companies work and these policies are very important because that's the way it defines how well the companies work or the teams work now if there is a problem then who will take the responsibility let's take you give the DB access to the developer and the developer is like developer screws up with the database developer delete something from the database which is important to the application owner then who's responsible do you take the responsibility that if something wrong is done by the developer you are going to restore the database recover the database and do you have the capacity that you can get that downtime from the client no I think some of the questions are like I know iterate it's when you get lot of queries or lot of emails from the application team in order to change something inside the database or make some changes to the database as a VBA owner now understand the same thing what do you think like how many times the network team storage team or system admin team is getting irritated by you when you are sending them the request might happen right so when somebody is sending requests to you you are getting irritated so when you send the request don't you think the other people are getting irritated now if that could have been done then there would have been only one DBA in the company or one developer in the company who would own the database who would own the application who would own the network who would own the system who would own the storage like we don't need other employees don't do this it's not possible i 100% understand some of the silly requests that application team sent to the debate team it sometime irritates but that's how things work all right that's all for today guys I think I'll see you all in the next episode till then take care bye bye antique commenting below these videos I would want all of you to send me email to support a debate genesis comm in case if you have any queries doubt confusion till then take care bye bye [Music]

Channel: DBA Genesis

Views: 13,325

Rating: 4.9181285 out of 5

Keywords: oracle recovery, rman backup and recovery, oracle high water mark, rebuild index, oracle datafile, oracle datablock

Id: alvUF2V73Us

Length: 30min 50sec (1850 seconds)

Published: Tue Jan 28 2020