The plan table described it is, in fact, a public synonym in earlier releases At water natural table it’s actually a public synonym and if we select star from DBA Tenant mins were synonym name equals plan to table it’s, in fact, oops kiss sensitivity plan table the plan tables actually a public setting them on to sister plan table dollar I forget exactly what release that came in I think it may have been released 10 so for all your databases the plan table isn’t actually a table it’s sending on something else if you want to create your own copy you can that the script provided to do that and some people do why because if you use the set statement ID clause, for example, explain plan for select whatever something rather just next are from Duel you can set this up you’ll see the syntax edge one you can set a statement ID and that means you can main the statement and store multiple plans on the table if you’re doing that you won’t be wanting to use the public cinnamon.
You’ll be wanting to help your own copy at the table so you can keep them indefinitely and note that if your statement involves buying variables it doesn’t do bind variable peeking and that’s very important indeed so for example if I were to um let’s see connectors schema shot tiger well select star form M my Empress my employees when departments 20 30 and 10 right explain plans for the select star from the camp where that snow equals 30 and then select star from table to plan dot display full table scan it expects to get six rows back and that is, of course, corrects so are indeed six employees in department thirty if however, I said explain plan for where department ID equals colon v1 that variable probably doesn’t even exist and what is going to do is just do some sort of guest on average three it has no idea absolutely no idea how many rows will be returned by that filter it doesn’t peak the bind variables.
So even if I’m very disconcerting yes it is even if I do this if I execute column v1 equals 30 even if I give it a value, oops parable doesn’t even exist to this well we were number and you’re right it is incredibly disconcerting and very inaccurate I can set it to thirsty and even now if I do my explain plan it has not peaked the value this is what you must remember to explain plan is bloody useless if you’ve got buying variables right so mm-hmm I’m jumping ahead here but of course all of my apex applications use user-entered values as bind values yes in my sequel statement so I’m assuming that at some point we’re going to learn a way to I just have to explain plan that does work well we’ll do what we can no pretty horrible, okay yeah yeah it is horrible so it doesn’t peak the variables dynamic sampling we’ll talk about later displaying plan may not tell the truth it’s really what I’m saying the predicted plan may not be the one that’s actually used.
And there could be several reasons for that scheming parameter changes sure maybe explain plan now with your current environments but then when you run the statement later you get a completely different plan because something’s changed bowling variables are the worst ones dynamic sampling is important that’s to do with adjusting statistics on the fly but buying variable to the biggie now explain plan really doesn’t understand binds at all they’re also what they call it baptism features which can mean you get completely different plans Jessica speed back means that it runs the statements with one plan realizes the plan was rubbish tries a different plan later explained plan doesn’t know that’s happening sis 6feedback that’s the cost-based optimizer consider reality adaptive cursor sharing buying variables can mean no so that I’m English half my employees are called Smith so select star from m Pierre ii name equal Smith.
I probably want to do a full table scan select star from MP name equals Zebedee I want to use an index because there’s only one somebody in the whole company what am I having a select star from amp where name equals Column whatever and that’s your problem Teresa right that that is my problem adaptive cursor sharing lets oracle feed in the different results into the parsing algorithm which allow topical to learn from its mistakes there’s also adaptive execution plans but will come but what do you have to bear in mind as the explained plan is very easy but it not necessarily the truth particularly if you’ve got buying variables involved particularly in that case okay now AWR stats pack reports are surely they show you the plan that was actually used, so these do not lie but as you saw on that one I showed you the information is very limited indeed by default the stats pack utility won’t even show you it at all but a WR.
It will show you the truth, but the plan is limited, and if you look at that thing again for this one here I’ve got the plan out I’ve got how long it took I’ve got how often it was executed I’ve got the IO Fights but I haven’t got the predicate if there were any it doesn’t show you the full story, so this really was the plan used but it doesn’t give you as much information explain plan you don’t get this a filter so different plans have different purposes now you can either get to get the plans out by the way to get this the way you do it is in the devious x plan you’ve got them but it will tell you the truth it’s just not the whole truth you’ve got display a WR you tell it the sequel you once by sequel ID which you’ve probably found by looking at the previous year the overall activity report and that will tell you all the plans of Scott for that sequel ID.
Now also trace I’ve used a lot also trace is dead easy to use it runs the statements then it runs explain plan against the statement so you may not be getting a complete truth because explain plan doesn’t always tell you the complete truth right but no it’s very quick and easy to use it runs a statement and by default will give you an explain plan and the execution stats so if I set auto trace on and then run a statement any segment at all will do let me take say that one may have before select star from my depth nor equals 30 right okay I run the statement to completion back comes to an execution plan expecting six rows back and then you get the execution starts right, so this is explained plan nothing more than us, but this is what actually happened always remember though that this is Essex takes a couple of runs to stabilize you’ve got to get rid of the recursive calls which the hard passing so if I run stand DB block gets that’s because we’re updating in memory as we do the pass.
That’s not updating data that’s updating that’s the passing work load coming through there if I run the statement again ah that is accessing data the parsing is pretty much dropped away completely it’s all got one recursive call, so this is what really did happen that’s only a prediction so for example if I lie to the optimizer execute DBMS stats dot set table stats for Scott dot amp sum rows a thousand to tell it there are thousand rows well I’ve got auto trace on run my query you’re expecting to get 393 rows back after applying the filter adapter no equals 30, so that is just explain plan where’s the truth is of course who is your side consistent yet and like all six rows that’s all you’ve got to hang on to the this of course is the truth because we’re and the statements this is the truth this is just explained plan right there are variations if I set also trace on statistics will suppress the explain plan so you get the query and then the starts if I run set auto trace on explain and run the query.
I get the execution plan but not the statistics very useful one is trace only so instead of saying auto trace on I say set auto tree this is the press is the screen output right but believe me, it runs the statement and then explain time this and shows this is invaluable for example if I want to run to see what actually happens I run some decent-size statement select some amount no not some as I say I want to select me a select amount sold no I would do that yes I will come outsold and say prod ID from sales join products using that’s not going to work I’ll select yeah yes that will work about sold prod ID from sales join customers using must ID so I’m joining sales to customers where costs City equals Los Angeles remember correctly that’s going to return quite a few rows which will be pretty disastrous really that’s hopeless isn’t it so what I can do however is h / SH sets auto tree only and now RAM it’s it ran It’s and Backcomb twelve thousand rows.
That the output was suppressed and there I see what would have involved 5,000 or 6,000 physical reads 7,000 consistent gets you notice for a larger query the recursive stuff becomes trivial John what is the note mean the noncaptive.