OOW OCT 4: SQL Tuning Made Easier with SQLTXPLAIN (SQLT)
Carlos Sierra will be presenting SQLT at OOW on October 4 at 12:45 at the Moscone West – 3022. If you are interested to attend this session please register soon. As of September 5 the assigned room is at 50% capacity. Also, once at OOW please verify room name since we may have had to move this SQLT session to a larger room. Please find below the abstract for this session.
SQL tuning is a daunting task. Too many things affect the Cost-Based Optimizer (CBO) when you’re deciding on an execution plan. CBO statistics, parameters, bind variables, their peeked values, histograms, and a few more are common contributors. The list of areas to analyze keeps growing. Over the past few years, Oracle has been using SQLTXPLAIN (SQLT) as part of a systematic way to collect all the information pertinent to a poorly performing SQL statement and its environment. With a consistent view of this environment, an expert on SQL tuning can perform more diligently, focusing more on the analysis and less on the information-gathering. This tool can also be used by experienced DBAs to make their life easier, at least when it comes to SQL tuning. Learn more in this session.
Carlos Sierra's Tools and Tips 
Hi Sir,
Any presentation to understand SQLT reports to fix best plan according report for poorly performing query. I need to understand how to interpret SQLT report.
Please shear me recorded presentation or any document for this.
Thanks
Amit Tripathi
Amit Tripathi
November 5, 2012 at 2:31 am
PPT used is available on same MOS note 215187.1.
There will be a new book published early on 2013 about SQLTXPLAIN. The author is Stelios Charalambides and the publisher Apress. Look for it early next year.
Carlos Sierra
November 5, 2012 at 7:07 am
Hi Sir,
In customer sid, we have the following need:
1) cursor_sharing=force in database side
2) The statement is not use bind variable (So Oracle do it for us)
3) In this context, the SQL executed with “alter session set cursor_sharing=exact” works very fine
So i decided to create sql profile with sqlt (with force_match=TRUE) to fix the plan
Assume the good plan plan_hash_value and sql_id (with cursor_sharing=exact) equals:
XXXXXXXX/abcd
After sql profile created, i execute the original SQLs without setting cursor_sharing (So it’s equal to force in my cas)
The problem is: when i check the sql_profile,plan_hash_value for the SQLs it show me that it use the sql_profile already created, but plan_hash_value is not the right. And the excution take lot of time (KO)
My question is:
when i check with select plan_hash_value, sql_profile from v$sql where sql_id=’my_sql_id’
the plan_hash_value should to be equal to plan_hash_value used when i create the sql_profile with coe script?
Thanks in advance for your attention.
Ymezroui
November 6, 2012 at 2:38 pm
Yahya,
Keep in mind that a SQL Profile is nothing but a set of CBO Hints under a Signature. And a Signature is a hash on SQL Text. So with the correct use of force_match as you did, the CBO can find the SQL Profile you created and all the CBO Hints are applied. Now, this is important:
1. CBO Hints are suggestions to the CBO. It is possible that one or more may be ignored. In a drastic case imagine a Hint references an Index and this Index no longer exists.
2. SQL Profiles, when a Hint is ignored, it still applies all other Hints on the Profile. This can have an adverse effect on your execution time. Think case 1 above.
Even if all CBO Hints are applied there is no guarantee you will get same plan. The reason is that Hints syntax does not allow to fully describe the execution plan. It gets close, but never to fully describe it. Most probably your plan is similar to the one you wanted to generate, but there must be an important difference.
If you are on 11g use SQL Plan Management (SPM) instead of SQL Profiles.
Carlos Sierra
November 7, 2012 at 8:16 am
Thank you very much for your reply,
unfortunately we are in 10.2.0.3 for this particular database.
I will try to use outline or perhaps (better approch) check what is the root cause of cbo do not finding the right plan 🙂
With Regards
Y.M
Ymezroui
November 7, 2012 at 3:59 pm