![]() Low rowcount…this section tends to leave the Subquery alone. This is also where the CBO looks at the sub query to get an idea of approx row count returned. This is simple but look at all the other items considered. Review the original SQL…b.status = OPEN and c.status= open and a.status = b.status…therefore…a.status = OPEN !!! This process added this where clause predicate…because…there MIGHT be an index on this column…transitive math. This particular SQL…check above…does not have the ‘A=’STATUS’…Ĭheck out the verbiage! Considering simple filter push at line 928…transitive predicates are generated at line 933…and the SQL that is being submitted for processing at line 943! Next comes the query transformation! This trace tells you what it is considering…the legend is useful here too. ![]() ALL of the settings appear…even the undocumented ones (these start with an under bar…). I’ve actually used these lists to diagnose a particular performance issue (once upon a time…). There are also the current settings at the database level as well as a list of what the default settings are. The first time are the settings used to process this SQL. I’m not showing all of this because of space limitations. Init.ora parameters appear several times. There is a handy legend of what the various terms mean…look at the very first one…CBQT…query transformation…I’m not making this stuff up! This information appears several times throughout this trace file. Next is the actual submitted SQL statement and its SQL id. These query blocks are used to hold the rowsets for Explain Plan processing. The Query Block Signature is where the SQL is examined for sub queries and table accesses and the query blocks assigned. ![]() The trace starts out with useful information about the Oracle and session. Watch for my tool on running this and displaying the useful information in various panels…hope to have it working soon…with this tool you will not have to ask for the trace file. bat file to turn on/off the trace and tell you the name of the trace file and its location! The trace files are all created in the ‘BACKGROUND_DUMP_DEST’ location (init.ora setting).Īlter session set events '10053 trace name context forever' Īlter session set events '10053 trace name context off' This is the syntax to start and stop this trace. Watch for an announcement on this new tool. I am going to write a 10053_trace tool that will not only run the below-mentioned script syntax but display the output without having to ask the DBA for your trace file (will use a database directory). ![]() This is the ONLY place to see the actual SQL statement Oracle submitted on your behalf. This trace is the only way I know of to see if the CBO considered my hints, and if not, why.Īlso, I’ve discussed query transformation…where Oracle fixes data type mis-matches and ‘opens access paths’…transitive math…adding additional where clause items…I’ll discuss as one appears in my example trace. I like to show this trace in my SQL tuning class after I discuss the hard-parse process because everything I say about hard parsing appears in this trace file…the permutations, the query transformations, the statistics and how they are used, and how the cost numbers are used…its all here…Īs far as a tuning resource, I’ve not been that interested in it…except when it comes to hints. Look up the blog on hard parsing and this will reflect here with the actual details. This trace really tells you ‘why did the CBO process this explain plan’…it goes into considerable detail on the hard parsing process that I’ve discussed in various blogs in 2015. I’m not sure when it first showed up but easily back to Oracle8i. This trace has been around for quite a while.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |