Read Oracle SQL Execution Plan | DBMS XPLAN

so before I jump into showing you how the execution plan is how to read the execution plan hmm I think we can talk about indexes but it will also make sense if we talk about indexes once we see the execution plan hmm so I'll log in to my server we'll get into sqlplus / sure since DBA and I think as we had already created a test table yesterday for the lochs activity right so select it was described test underscore one right this is the table we had created so what I'll do is I'll quickly show you the execution plan on this but very straightforward guys the execution plan that you're going to see is this very silly and a small one and trust me in real time the execution plans will be very big and you need kind of like different I mean you need a separate time to read through all those explained plan so let me quickly select star from test underscore one so we have these different values inside this table and you can see like ID one is primary key because we see it does not know and I remember we created it as a primary key so now that we know about this table what you can do is you can quickly specify set auto trace on so when you issue this command what happens whenever you execute a query what Oracle will do is it will it will show you the output can you see you got all your records and also it is giving you a or it will show you the execution plan right on the screen now what exactly do you see over here so guys I want to show you guys something so what I'll do is I'll shut down this database and start the database there is something I want to show you very important and you will understand who I am doing it once we have the database up okay so the problem with Auto trace is you need to commit otherwise it won't allow you to mediate or commit or rollback whatever works best you will understand it in some time why we shut down the database and then we'll generate the plan again all right Alex it and here we'll connect to the database again start up or what I would do is guys just for simplicity purpose I'll also take a duplicate session ok database is up and running right sqlplus / of system e so over here what I will do is clear screen and I'll put this side-by-side as much as possible your screen I want to show you something important all right so set auto trace on and select star from test under school one all right don't worry about this plan for now of course we'll come back to this one and in the second session now this is something I'm going see set auto trace on right and then select star from test underscore one now compare these statistics and tell me what different did you see over here it's open for all anyone can speak the consistent reads and then the recursive calls hmm then the physical reads therefore the first one the fiscal reads are more consistent reads are more excessive calls are more compared to the second one right any any other point guys I actually I'm looking for an answer from what we have learnt as an Oracle database architecture the sod memories hmm for the first one the Sun memories ooh and the sod members would otherwise for no that's not the answer I'm looking read only in section one or data discovery has been read by using recursive call ok these these are what you're saying but what I'm saying is as how you have learned the Oracle database architecture it has to do something with the instance and the database now I give you a hint the second for the first one is meaning is reading it's actually using more memory rather than physical this perfect perfect that's the exact answer I was looking at guys see remember I mentioned in the Oracle architecture first time read if the SQL is not or the buffers or the records are not available inside the instance then Oracle will travel to the disk can you see we have physical reads six physical reads happen on the disk and that's how Oracle was able to pull up these records and you can see 18 recursive calls who remain and and guys see right now again we can't get into all this to understand what each one of them are but the point that I want to prove over here is you can simply look at the physical reads and over here it's zero that means if the SQL buffer sorry if the records are available in the database instance because the session one the user queried the table and now these records are already available in the memory then next time when you are selecting from session - what Oracle will do is it will avoid the physical reads and it would get the records output right from the memory like the RAM all right so that's one point I wanted to prove so what I'll do is I'll quickly close the second one and we'll jump into the first session to understand the SQL execution plan so let me clear screen and set lines triple line and select star from test underscore one okay then something we see as execution plan alright we see the records we have a plan hash value this is the hash value that has been assigned so it's hash value is again a unique IP given to the plan so this plan unique ID is this value all right and these are the exact access path like what steps Oracle optimizer things it is good in order to get the records out of your table the way you read the execution plan is from outside in that means you will have to read from the the most indented operation all right if you see out of these two this one is the like most indented one so if you have a big query you will have to read the text that is more like indented towards the right side and then you move to the other text like in this case this is something you have to start reading and then move up the branch so what you have to do is when you are reading an execution plan has things will not be as simple as you see in real time so let me quickly pull up or give you a sample how things will look in real time or how bad a plan might be hmm so we will see something like this and I'm just putting I'm using the same statements all right so this might be like this and this might be like this and this might be on this level then this might be on this level like this this and this so how do you start reading this execution plan is like rightmost indented lines you start with these two because these two are indented and we write most and then you read on these two that means once you are done with these two then you start understanding these three because these three are joints they are joined that because Oracle considers these three must run in parallel exactly in the parallel the same way these two are running alright and then you read about these two that means these might be again joined on tables and then you read the outermost statement again there is reading the execution plan itself is another science so we'll come back to the simple execution plan so in this execution plan as I mentioned you have to read from the rightmost indented operation in this case you can see what Oracle did was Oracle try to access the full table and it will give the name of the object it was trying to access that is that is test underscore one and then the rows that got impacted due to this read and then you have the bytes like 25 bytes of data that was impacted over there or it was read from the disk and this is something is important like cost and as I mentioned one of your performance tuning goals is of course to reduce the Spahn Stein but you you're one of your goal is also to work on the throw port that that means to reduce the cpu right so in this case can you see Oracle calculates its own cost it's not something like predefined but what you can do is like when you have lot of steps where is our example okay I delete it so when you have lot of steps in large lot of operations then you see which step is costing you more there might be one step that is costing you a lot and then you see can we reduce the cost by creating an index or modifying the query itself that is coming inside the database or maybe the defragmentation of the data inside the database lot of other steps stuff are there and then time is what defines exactly as to how much time each operation takes in to execute the query now by looking at this we can say that it's taking almost like two seconds to get the output so this is the way to generate the execution plan and and guys like execution plans this is the simplest one execution plan that you can ever see I'll show you one other example just to tell you how smart optimizer is to change the plan based on the requirements we are accessing the same table select star from test one all right now I am adding your where clause where ID number will clear the screen nice that was easy so you'll understand so select star from test underscore one where ID number is equal to one all right and I am hitting Enter can you see we got this output we got the record where ID number is one amazing and now if you start reading the execution plan this is your execution plan you can see index unique scan this is where Oracle is accessing the index that is created on the table that means the primary key that we have created so why Oracle is using the primary key this time because in the where clause we are introducing a column that has a index created on it all right so that's the ID number one and Oracle is smart enough to quickly use the index and this is the index name don't worry because when you created the table or when we created the table we did not assign the name to the primary key so Oracle gives a name to that index so this is Oracle given name you don't have to worry about it so index are now Oracle what it is doing rather than the full table scan because in the where clause we have a condition where like the ID 1 oh sorry ID column has a index created so Oracle is able to quickly use the index rather than reading all the records and rejecting all the records where ID is not equal to 1 so Oracle definitely will have lot of plans but it thinks this one is the better plan now I'll give you another example where you will understand when Oracle will not use the index okay so I'll clear the screen sorry yes why is the physical I can see one physical read this is because this time Oracle referred to the index this index was not in memory right so okay it it approach to the index and this time Oracle was looking at the index so select star from test underscore one where now see the magic value equal to Val one okay before that let me show you the table data so that you will understand select star from test underscore one alright at this time forget about the execution plan just look at this one and this one okay for the Val grid that we are looking at the data now we'll try to get this record but in the where condition I will use the value column okay so guys be careful don't get confused remember earlier I have used the query in the pair condition I have used the column that has a primary key index but this time I'm using a column in the query select star from test underscore one where value this is the column I'm using where no index has been created and I'll give valve 3 and I'll hit enter what do you think what has happened over here come on guys sorry can you repeat [Music] memory so it dark oh no no no no no we are not talking about statistics right now we are looking at the execution plan right so that's more important over there can you see why oracle did a full table scan over here because the column that no I did yeah the column that we are using in the SQL query there is no index on the value column right so that's the reason Oracle has to go for full table scan now the problem with the full table scan and guys there is always pros and cons for full table scan and I will talk about it in some time once we are done with this example now if you see it it is telling predicate information identified by the operation ID so for the operation ID number one for this table access full oracle has applied a filter technically Oracle didn't apply the filter we told Oracle to give us records where value equal to l3 so this is a good example like the execution plan right on the screen you can see the filtration of the data so that way many a times even sometimes it's good to look at the filter data and like if I mention value not equal to value three like male female example I gave right so if I'm saying the user is using a stupid query where filter says gender is not equal to female then it might become a problem or gender is not equal to male and then what I would do is I would quickly analyze the values inside the table and see what percentage of the gender is male what percentage of the ureter Records inside the table are female and then I can suggest a good option to the application team so again guys our goal for right now is was to how to generate an execution plan and how to quickly read it but there are lot of other things inside the explained plan which is out of scope of our session but I want to show you something that will help the experience DBS so if it's a just type explained plan over there generate execution plan in Oracle so guys we have two options to generate the execution plan the auto trace option is the easy one the one that we have used just now but there is a big problem with this one the issue is once you hit enter after giving the command it will first read the data it will actually execute the statement and then show you the execution plan but in real-time when a query is taking lot of time you cannot wait for the query to complete and then look at the explained plan or the execution plan right see if a user is saying hey this query is taking 55 minutes then if you use the auto trace option that we are using right now you will have to wait for 55 minutes to look at the execution plan now that's wastage of your time so what we can do is first of all we'll stop the auto trace set auto trace off all right now if you run the query select star from test underscore one it will not show you the execution plan right so this auto trace option is good easy option in case you just want to play around or look at queries that are causing problem but if a query is really poor then this is not the good way then you need a different method in order to not to run the query but just look at the plan so this is where what you do is you just type explain plan for and then type the SQL like I'll show you this one so explain plan for you type this and then type whatever your SQL command is let's take select star from test underscore one where value is equal to we'll go for valve for all right and hit enter now what happened explain plan is generated but this query is not executed against the database so you don't have to wait inside the database for this query to finish the execution and then only you will see the explained plan not like that the beauty about this method is you got the execution plan now you can happily look at the execution plan but the question is how do you look at the execution plan so you need to run this query you can simply copy paste it so select plan table output whenever you say explain plan it will create a plan table and in the plan table it will store your execution plan so whatever the query where you mention explained plan for the explained plan is stored into the plan table so when you run this query DBMS X plan is the package that will display the plan output you can literally copy paste it you don't need to modify the command and if you hit enter can you see you have the plan hash value you have the execution plan over there and you also have the filter but the only problem with this one is of course there is there are ways to display these statistics again there are other packages you can display these statistics separately but we are right now dealing only with the execution plan so this method you should use only when a query is very poor and it's taking a lot of time maybe twenty minutes thirty minutes to execute and you want to generate the plan so that point of time use this method so you can quickly look at the plan without executing the MM a man okay it's lot of new things inside the execution plan and one of the important thing was indexes so what are indexes and I think a lot of times three there has spoken about indexes and row IDs and then index values so we will look at what are indexes and all in our I mean that's our next topics I hope there is no time limit right now so guys any doubts for now before I move quickly into explaining you what are indexes how indexes are related to that's done through hints and as I mentioned I'm not a fan of friends so I would request you to read about it and sorry yeah that's done through hints what you can do is you can use SQL hints to force a plan on a query and as I mentioned earlier I am literally not a fan of hints guys and even if you have any queries related hints even if I know the answer I'll try to avoid it because I have seen a lot of problems with hints in my career and I actually hate it technically

Channel: DBA Genesis

Views: 16,367

Rating: 4.8476191 out of 5

Keywords: SQL Execution Plan, Xplan, DBMS Xplan, sql tuning, oracle optimizer, oracle performance, sql show plan

Id: aUxrs8x_ieQ

Length: 24min 53sec (1493 seconds)

Published: Sat Sep 14 2019