Generate correct estimation details in an execution plan using GATHER PLAN STATISTICS hint in Oracle

0:00 next is um for example if you want to get another some more statistics for example if you want to have uh details on uh cardinality as uh estimated rows and uh and actual rows by the way someone would like to help me like what exactly is cardinality anyone yeah go ahead guys someone has to answer this yes please else i need to answer them cardinality is the number of rows out of all the rows like which

0:46 number of rows you are selecting okay that's a good attempt actually yes that's absolutely correct it's basically the number of rows that the optimizer guesses will be processed for the uh for a plan step right so yes that's right so what do you mean by high cardinality and low cardinality high cardinality means a probability of peaking the index is high and if it is low then probability is very low of picking value probability of picking an index is high yeah no that's not i'm sorry but that's not the correct answer okay talking about the high cardinality basically refers to columns with values that are very common or unique

1:32 right high cardinality column values are what typically what are they like id numbers right email addresses or in fact sometimes user names so high cardinality will be for example if you have any table called as test table right with the column test id so test id this column would contain a unique values right so this is a high cardinality column right if you talk about the low cardinality so uh values like boolean values right or gender countries or maybe flags those are very common uh you know um common low cardinality column examples where we have very few unique values right okay okay i i will probably add one line here

2:18 so what prashant said that high cardinality means high number of unique values and if it's a low cardinality then it's a less number of unique values so typically gender is a very good because there can be two or at most three genders so that's a that's a low cardinality but employee id will be a high cardinality because every single employee id will be unique now somnath said that high cardinality picks up the index so uh probably that there is yeah i'm making a mistake sorry that i currently will pick up the index

3:04 which in a roundabout way is correct also although i mean to say i need to say that when the cardinality is high the selectivity will also get high then in that case the select the probability of picking the index will be high and yes yes i agree well stalking when you have you know touch this topic of selectivity well selectivity what is selectivity is basically um what denotes a fraction or a percentage of the data or rows that is returned or accessed by any operation like select or update right so selectivity is what it basically number of total number of rows returned when you divide it with the total number of rows that gives you selectivity right of course that's a very important aspect or

3:50 a metric that is considered by the optimizer while creating the execution plan right so cardinality and selectivity yes those these are the core factors which influences cbo to prepare a good or a better plan right so if it's a low cardinality table then you have a you know a totally different execution plan and if you have a high cardinality value on a column then of course it you will get a you know a good good plan okay coming back uh to this slide so for example if you want to check the cardinality estimation information within your execution plan so what you can do you have to use i o stats and an additional argument with dbms underscore xplan.display function right so you have

4:36 to pass iosat and it will provide you information or like uh buffers and reads so iostats will give you information on buffers and read like um how many buffer scans it actually you know it it user or during this specific operation and how many reads or disk io has happened during this specific step now if you do this this entire thing this will also bring e-rows and arrows as well that is the estimated rows and actual rows estimated i mean for this full table scan table name is t right it estimated 1000 rows but actually this table has got 2000 rows right so this is an incorrect estimation right so there is some problem right so

5:21 maybe what happened the statistics are not updated right so that's why it's still considering like the total rows for this table t within this table t is thousand right but in real or in actual it has 2 000 rows are you getting me so same instead of generating this euro and aero specific information you can also use gather underscore plan underscore statistics that's how gather underscore plan underscore statistics is one of the hint that you can use with your sql where you want to generate the execution plan and want to get this 0 and arrow or cardinality specific information for example if you see here on id or this id step 3 table access full right so it has got it has estimated rows five

6:08 three six two nine but in actual or in real it has got only 27 rows because so uh why is that by the way are in the many idea why in what particular scenario we have actual row and ero value difference or a gap in these two numbers are in them you know this is a piece uh pruning is called pruning no no no no no no no no no no no someone has to answer this question just give everybody a chance to answer this good question right that's why it is different is coming right

6:53 no no statistics are there i'm not talking about the state their condition if they are still outdated missing or locked whatever i'm not talking about the state but yeah statistics are there yeah the status is the old statistic right that's why it is happening right okay yes you are close you are near yes that's right so in what particular scenario you think like this difference between eros and arrows can happen in in actual in real we have only 27 rows but what it estimated for this full table scan on t1 table is 53629 so what what have happened it could be case like someone actually uh deleted uh you know uh a huge amount of data from the table now only 27 rows are there but in real after

7:41 you the stats were collected a massive delete operation happened on this table t1 and now because statistics are still there but with the older information right okay yes so that's why we are seeing five three six two nine as the estimated rows but in real if you go and do the select count you will get 27 only and that's what and that's the reason you know you'll get an expensive planner you know and you'll get a sub optimal plan and the performance or the behavior of the sequel is not up to the mark because statistics are not there or are not fresh basically yeah yeah yeah so so i think sugita was close to the answer yes in the sense you said that if you get this type of mismatch between the estimated rows and actual rows you

8:26 can for sure think that the statistics has not been updated because the oracle optimizer now thinks estimates that you have 53 or 1000 rows while in reality you have only 27. so this is this is a classic case so where the latest stats has to be there because this is the kind of stupid plans that will come up if you do not have the latest stats especially after you have done a delete or modified the table in some way so this is one point i had to impress go ahead press on thank you good point but yeah one thing but uh i didn't get any kind of uh execution plan like heroes errors like that as i told you

9:12 you have to generate your execution plan in advance format right or otherwise you have to use hint called as gather underscore plan underscore statistics because the plan what you're seeing maybe in front of your screen right now i mean you know in your own system is a basic plan right basic plan looks like this right you only got name rules time information right yes but you have to generate the execution plan in an advanced format maybe like this or even if you go and pass ios that it will also provide information about the estimation or the cardinality estimations as well along with the buffers and read information right okay next slide one more question suppose that my table is at 23 terabyte okay i am not a generated immigrant statistics gather statistics okay so

9:59 in this case because it may be their performance issue that's why on that if i write this select comment with the gather plan it may impact our the performance also well that's a very good question actually so jeet i must say yes that's a very valid question because in order to use this specific hint you have to execute the query right and of course you're not going to use this hint in case of dmls right but even if you if you use this gather plan statistics with dql like select operation of course if the table size is huge and you're actually doing a full table scan on it ultimately this will execute and it will wait for some time before it presents the output at the of the execution plan

10:45 in front of you right so the best way you can use the advanced execution plan right ex with advanced execution plan you need not to run or execute the query right like this if you pass your uh i mean if you do an explain plan and then do a select and then you know execute this sql with advanced or maybe with the i o stats you will get this information as well right so this is a good option when you know the uh when it's not an expensive operation it hardly takes few ms but you want to reduce it to one or two ms maybe or into centi seconds right so maybe in that case you want to execute the select with this specific hint yeah prashanth one point maybe you want to explain this to sujit and others so

11:32 then this this thing is a two-step process the first part you have to first explain plan for the sql statement you will get a message explained and then do the select star from table so if you might want to explain that part a little uh which one didn't get you i didn't get you so you talked about explain plan right right so if you use explain plan for and then use the sql statement then you use select start from table dbms explained dot display so i don't see that part of the command ah okay yes yes that's right yes of course i'm so sorry i missed that part i ideally that that part should be here in this in this specific ppt or yes

12:17 that's right for example if you want to generate the execution plan for any specific sql statement you have to use explain plan for and then followed by the ah by that specific statement or sql statement select delete update whatever right so once you pass explain plan for and then your statement it will come out says explained means temporary it has created the information for you so next step should be you need to query the plan table using dbms underscore explain xplan.display function right and it's up to you if you want to get typical simple or advanced uh version of your execution plan okay so so just to sum up what prashant said

13:05 so this is a two-step process if you don't want the query to run because as you said if it's a 20-25 terabyte table you cannot even risk running the query so so gather underscore plan underscore statistics is out of the equation we will not do that so what can we do so we can do a two-step process first step is let us say that uh sql query is select star pro employee so what i will do is explain plan for select star from employee and the output will be explained only thing you will get is a message explained then you have to run the second part of the query select star from table dbms underscore explain dot display then my plan table and whatever so it fetches from the cursor the latest uh

13:52 plan and then displays it on the table so that two-step process you have to google and see a little bit so you have to and same you can get from auto trace as well right same information if you set it explain only so it will it will own it will not execute the query for you with auto trace it will only present the execution plan using i'm talking about the auto trace option but yeah all i mean i'm a big fan of this you know dbms underscore explain a display function so this is the easiest way how you can do it uh do explain plan for and then the sql it will come out quickly saying explained i mean it's done so

14:39 it's temporarily hold that information within the plan table and what next you can do you need to just query dbms underscore expand dot display i mean that's how