Oracle Performance Tuning - Read and interpret Explain Plan

hello friends this is Durga again as part of Oracle performance tuning in this video I will be covering how to interpret the explained plan you if you see my videos you will understand how to generate explained plan needle by using set auto trace or by using planned table method and here I will try to explain you how to interpret the explained plan in detail so for that let me write a little bit of complex query okay so for that I will be joining three tables employees jobs and Department and I will try to see how many employees are there in each department with each with a given job title for the salary greater than $5,000 see if you if you look at employees table here if you go to the data there are one not seven records okay it has the employee ID employee name email and the phone number hydrate job ID and also department ID so we need to join two departments table to get the department name and the job job ID jobs table to get the job title and also we want to see for a given Department for a given job title how many employees are there whose salary is greater than five thousand so it covers filtering it covers in group by it covers joints so it is a little bit complex query and which I will be first writing and then we will generate the x-plane plan and we will interpret the explained plan so select d dot department name j dot job title comma count of one from employee c.join departments d on a dot department ID so this is how the query looks like so let me first copy this query okay and hit enter so there is a okay actually the queries wrong so join here jobs are okay now it will run and I didn't use Elias so now it would run and hit enter man a dot job ID J dot job ID now it should run so you can see that let me do the order way also so order by D dot Department name J dot job title so that all the departments are grouped together and hit enter you can see that there are there is one employee in accounting department with accounting manager job title whose salary greater than five thousand similarly one from public accountant and and then there are five employees in finance department with the job title accountant who's selling more than five thousand dollars so this is how the results look like and sales in the most highly paid out narration compared to anything else not Department compared to anything else so now we will see the explained plan for this query what we have to do is we can actually do set auto trace trace only and then run this query again and hit enter it gives you the it gives the explained plan as well as the statistics to run this query let me zoom out a little bit and also let me run a command called set lines 200 so that the output is well formatted okay so this is the explained plan now I will I will explain you how to interpret this explained plan so now I will be whiteboarding so now I will be whiteboarding here so the query starts executing here so this will be the first step or called as road okay so from route you there is a first level of Blanche so sort group by and then there is another Blanche for nested loops and then you can see that these two nested loop and the table access all at at one level so here from here it blanched out to two so this is let's say a and this is B so there is a branch and there is B branch and from here again it has a has two more branches one for table access full and other for table access by index ID so this nested loop actually have two more branches so this is one branch under this nested loop and and the branch is this one so again we will there are two branches here let us say this is a one and a two and this is nested loop and this index UNIX can and again the CA one so this is a and this is a 1 and this is a 2 and again this a 1 is branded into 2 let us say it is a 1 1 a 1 2 okay and again this have deceived index eunuchs can here similarly be does not have any branch here only this year have a one a two between nested loops and index Phoenix can and then this a one have two branches a1 and a2 a1 is stable access full and a2 is stable access by index row ID and then a1 one does not have any anything under the under it but a want to have this index eunuchs can okay so this index eunuchs can is related to a one two so all these the ones which does not have some sub things or caller Leafs and all others like this one let's say this is the yes s 1 and s 1 1 which is banded into a and B so this these are all this is root all the intermediary one server called and branches and branches can be at different level and all the hanging ones are the Leafs so this is how you should do first visualize this explained plan in a tree structure and then what you have to do is the way you have to interpret is until you go to the leaf so in this case and you have to go go towards the left first so in this case you start with this s 1 s 1 1 and go to the left through the branches in this case a a 1 a 1 1 so this is the first thing which you need to read so what is the a 1 1 this is a 1 1 table access full is a 1 1 ok so the first thing what it will read employees table and once it gets the employees table then you have to go to the branch and read the second a it spear so before reading a 1/2 you should understand what's happening at its branch level so for table access full the band is nested loops and you need to understand what the nested loops is I will be covering later all the different ways and that will be datasets other all the different ways that will be used to join the datasets in this case it is trying to join employees and departments table so first it gets the data for the employees and for each employee ID what nested loop means it will go to the next its peer which is departments using the department ID of for each employee so we have one or seven employees in our data set and it will go through each and every employee get the department and then it will go to the then it will go to the department's table but again these departments have another leaf so once you go to the national loop and come back to this table Excel by index row ID you need to understand that it's not the leaf node so there is the leaves which is called an index UNIX can so for each employee it will go to the department department ID underscore PK index and but the department ID underscore PK does not have department name in that and to get the department name it has to go to the department's table and once it gets the department table the nested loop the join will be done and for each record again it has to go through the nested loop and for this nested loop which is that if the parent branch of this national loop it has to go to the index UNIX scan to go to the job ID and once the join is done it has to go to the next lower branch which is national look and it's really stable access by row ID it has to go to the job table to get the job title and then it has to apply this sort and grew by because we have the group by condition in our table too to get the results so this is how you should read the explained plan and you should interpret the data in that spend plan I will be covering all these things again in detail for now just understand how to read that explained plan more than anything else you have to start with the root which is s in this case go through the left side branches until it goes to the leaf node which is a 1-1 which is table axis full of employees once it once it starts reading the data depending upon how the data how it is performing join with the other table if the query involves join it will use the algorithm of nested loops and there are the sort may join hash hash join etcetera in this case it is using nested loops so using so from here it will go to this one and go to the P of table accessible which is table access by index row ID and then it will loop through it until the join is done once it is done once the employees and departments table is joined then it will go to the next level branch which is this one and again this is combination of two branches one is already performed and the next one is to do the index UNIX can to join the data set on the jobs table and once that nested loop is done it has to go to this list node which is to get the job title from the drop table and then it will go through the branches bottom-up so this is how you should read the x-plane plan and it should interpret Dex plane plan but you need to understand each and every step what is it nested loop what is table accessible what is table access by index lloyd e what is index UNIX scan and there are many other such operations that will be used when running the query and you have to interpret all these things to make sure what this explain what these explained plants actually mean so it's not I cannot cover everything as part of one video we will be talking about we will be running different types of queries and as and when we come across a new thing we will try to understand what the new stuff literally mean for example in this case I will explain what stable access full what is table access by index flow ID what is index unit scan and what is the nested loop what is odd group by for this query in future when we write different query using different set of tables or same set of tables with different filter conditions etcetera in the query changes and if the operations change I will try to explain as many as possible through this course for now just to understand how to read the x-plane plan that is more than enough so once you understand the explain plan it when you do set auto trace it also run the statistics okay and as part of the statistics there are certain things called like this recursive calls maybe blog gets consistent there's physical reads redo size these are the may actually even redo size is not that important especially for the performance tuning content which kids are targeted for almost everyone who want to use Oracle database so these four are the most important statistics and then sorts are also important and these things are not that important they are important but not that important and this is also not very important but these are the things which will actually and which will actually and means these are the statistics data that are the truth from IO which is the most time taking activity when you run the query so you need to understand these things in detail to have to make sure how what is the warhead or when we run the query so I will be calling those things also as we proceed forward and also so far we have seen how to generate explain when using the SQL Developer and SQL plus but in real world scenario when we actually start using our applications in production especially for DBS there is a very handy tool called the Oracle Enterprise Manager and we need to first understand what it is in detail especially from the context of performance tuning and we are talking about performance tuning as part of this playlist and that will be taken care as part of the next video that being said I hope you are enjoying the content on my channel if you like this video please click on the like button if you want to provide feedback or ask any technical questions please use the comment section of the video and if you have not subscribed to my channel yet please so you will get to see lot more content like this thank you bye

Channel: itversity

Views: 72,644

Rating: 4.1797752 out of 5

Keywords: Performance Tuning, Oracle Database (Software)

Id: _HzmCVYBmac

Length: 17min 42sec (1062 seconds)

Published: Sat Nov 14 2015