Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Posts Tagged ‘SQLTXPLAIN

Custom SQL Profile and Plan Stability on 10g

with one comment

SQLTXPLAIN includes into the sqlt/utl sub-directory a script named coe_xfr_sql_profile.sql. This script inputs a SQL_ID, then it asks to choose from a list of known Plan Hash Values (PHV), and after a few seconds it outputs another script that contains the Outline of the PHV together with the SQL Text for the given SQL_ID.

These are some of the things we can do with the script generated by coe_xfr_sql_profile.sql for your SQL_ID/PHV:

  1. If we execute the generated script with no modifications into the same system, then it creates a Custom SQL Profile with the Outline of the plan from given SQL_ID/PHV. This Plan is independent of CBO Statistics, meaning your Plan will be stable even if you gather new statistics (or delete them).
  2. If we execute this generated script into a similar system (one with same schema objects), then it creates the Custom SQL Profile from the source system into the target system. That means we basically migrated the PHV into another system. Think on this: “DEV instance exhibits a good-performing Plan. PROD only generates a poorly-performing Plan. You want to quickly migrate the good Plan from DEV into PROD”.
  3. You can only generate a good-performing Plan if you place a CBO Hint in your SQL, but you cannot modify the application. So, you execute a modified version of your SQL which includes the CBO Hint, then execute coe_xfr_sql_profile.sql for this modified SQL. When you open the generated script you remove the CBO Hint from the SQL Text section. Then this newly generated and modified script contains the original SQL without the Hint and the Outline of the Plan with the Hint. When executed either on the source system or a target system, it creates a custom SQL Profile on it. This Profile associates the original SQL with the good-performing Plan.
  4. Your original SQL contains a CBO Hint or set of hints and it produces a poorly-performing Plan. When you remove them you notice you get a good-performing Plan, so you execute  coe_xfr_sql_profile.sql for the modified SQL (the one with the hints removed). Then you open the generated script and replace on it the SQL Text with the original one (with the hints on it). This generated and modified script, when executed on the source or target system, will create a Custom SQL Profile for the original SQL with the good-performing Plan.

If after you create a Custom SQL Profile with coe_xfr_sql_profile.sql you change your mind and want to drop such Profile, you will find the drop command on the same script that was generated by coe_xfr_sql_profile.sql containing your Profile.

This coe_xfr_sql_profile.sql script uses API DBMS_SQLTUNE.IMPORT_SQL_PROFILE, which is not documented.

A word of caution:

SQL Profiles are basically a set of CBO Hints associated to a SQL Text through a “signature”. This signature is a hash function on the SQL Text. If a Hint from the Profile cannot be applied, it does not invalidate the entire Profile. In other words, the CBO will still try to apply as many Hints as possible. Imagine that one of the Hints refers to an Index, and the Index is dropped, then the Hint will get silently ignored. This may produce sub-optimal Plans.

Another consideration is the effect of a Virtual Private Database (VPD), which appends some dynamic predicates to your SQL. In this case, the Plan produced with the Profile my not be optimal.

If your database is 11g or higher, you’d rather want to use SQL Plan Management (SPM), which provides better Plan Control functionality.

Written by Carlos Sierra

April 9, 2012 at 8:13 am


with 53 comments

OK, I will start with the basics…

SQLTXPLAIN, also known as SQLT for short, is a tool I developed by myself and to myself. At least that was the idea when its name was coe_xplain.sql. That was long ago… like in the past millenia or so.

Today, SQLT is a tool that Oracle uses to diagnose SQL statements performing poorly. So, it went from a single-user tool into one that is used by hundreds of engineers at Oracle. Thanks to the valuable contribution of ideas from some of the brightest minds at Oracle Support in the area of query tuning, SQLT was reborn between the years 2008 and 2012. In particular I thank Abel Macias, Mauro Pagano and Mark Jefferys for their constant flow of ideas and by reporting to me my own flaws. I also thank the more than 650 participants of my two SQL Tuning Workshops, since it is in these sessions where I have learned about most of the enhancements I have implemented over the past 3 years or so.

Anyways, back to the question “What is SQLTXPLAIN?”. This is a tool developed in PL/SQL that inputs one SQL statement and output a zip file that contains many pieces. Some are for diagnostics, and some for further use as a Test Case (TC) is created. So, when someone at Oracle asks for a SQLTXPLAIN, basically the request is to install this tool and execute one of its main methods in order for the requestor to analyze your issue.

SQLT does not come pre-installed, nor it comes with the product. To get it to work, you need to download it first from My Oracle Support (MOS). You can find it under Doc ID 215187.1. After you download it, please unzip it and read included file sqlt_instructions.html. These instructions are going to basically ask you to copy this file to the database server, unzip it there again, and execute sqlt/install/sqcreate.sql connected as SYS. Provide a value to the inline parameters, and SQLT should be installed in a few minutes. If you notice the sqlplus session exists then something went wrong. Read the *.log files and look for errors. Fix them or at least try. If you get stuck, contact the tool author (me).

Once SQLT is installed then you are ready to use it. Start with method XTRACT. Refer to the instructions provided with the tool. Basically, connect into sqlplus as the application user and execute sqlt/run/sqltxtract.sql passing the SQL_ID of the SQL that you want to analyze. SQLT XTRACT will generate a zip file. Open it up and look for file sqlt_sNNNNN_main.html. Try to make sense out of it. Start with links on SQL Text, Plans Summary and Observations. Then take it from there.

What you will see in the main html report is basically the entire environment around your SQL. I mean in terms of performance. Everything that may affect your SQL performance: from CBO statistics, to parameters, to bind variables, etc. You will find details about cursor sharing, SQL Plan Management (SPM), SQL Profiles, Stored Outlines, etc. Tons of details. Not everything is used every time. What is important is that everything is there in case you need it. Even more, it is there in case someone with more expertise in SQL tuning may need it later on.

SQLT works on RAC. It works also on a distributed environment. It will soon work on data-guard  (stand-by read-only) databases. If you want to see a sample of its output, download it from same MOS 215187.1. Feedback on this tool is always welcomed.

Written by Carlos Sierra

April 3, 2012 at 4:24 pm


Tagged with ,

SQLT is now available

leave a comment »

SQLTXPLAIN (SQLT) was made available on April 2, 2012. Find this tool under MOS 215187.1. It contains 2 fixes and 37 enhancements:


  1. Peeked and Captured Binds in Execution Plan of MAIN was showing :B1 where predicate was “COL=:B10” (false positives). Fix display of bind peeking and bind capture when SQL contains binds like :b1 and :b10.
  2. Metadata script includes now creation of Statistics Extensions.


  1. New HC when derived stats are detected on a Table or Index (GLOBAL_STATS = ‘NO’ and PARTITIONED = ‘YES’).
  2. New HC when SQL Plan Baseline contains non-reproducible Plans.
  3. New HC indicating sizes of SQL Plan History and SQL Plan Baseline (enabled and accepted Plans).
  4. New HC when there is an enabled SQL Profile and there are VPD policies affecting your SQL. Plan may be unstable.
  5. New HC when there is more than 1 CBO environment in memory or AWR for given SQL.
  6. New HC when Indexes or their Partitions/Subpartitions have UNUSABLE segments.
  7. New HC when Indexes are INVISIBLE.
  8. New HC when an Index is referenced in a Plan and the index or its partitions/subpartitions are now UNUSABLE.
  9. New HC when an Index is referenced in a Plan and the index is now INVISIBLE.
  10. New HC when Table has locked statistics.
  11. New HC when INTERNAL_FUNCTION is present in a Filter Predicate since it may denote an undesired implicit data_type conversion.
  12. New HC when Plan Operations have a Cost of 0 and Cardinality of 1. Possible incorrect Selectivity.
  13. New HC when SREADTIM differs from actual db file sequential read for more then 10%.
  14. New HC when MREADTIM differs from actual db file scattered read for more then 10%.
  15. New HC when BLEVEL has changed for an Index, an Index Partition or an Index Subpartition according to statistics versions.
  16. New HC when NUM_ROWS has changed more than 10% for a Table, a Table Partition or a Table Subpartition according to statistics versions.
  17. New HC when Index is redundant because its leading columns are a subset of the leading columns of another Index on same Table.
  18. New HC when leaf blocks on a normal non-partitioned index are greater than estimated leaf blocks with a 70% efficiency.
  19. Active Session History sections on MAIN report include now up to 20 sessions and 20 snapshots (it was 10 and 10).
  20. Parameter _optimizer_fkr_index_cost_bias has been added to SQLT XPLORE.
  21. SQLTPROFILE and script coe_xfr_sql_profile.sql take now SQL statement with SQL Text larger than 32767 characters.
  22. Add metrics similar to what we have now on summary tables/indexes on SQLHC.
  23. Tables and Indexes sections on MAIN contain now links showing object counts instead of a constant. Similar to SQLHC.
  24. Execution Plans on SQLT to show with mouse-over, schema statistics for both: current and as per plan timestamp.
  25. Add new columns on for all V$, GV$ and DBA views accessed by SQLT.
  26. Include reason WHY a cursor is not shared (out of XML “reason” column on gv$sql_shared_cursor).
  27. MAIN report heading includes now a link to MOS SQLT document.

Written by Carlos Sierra

April 2, 2012 at 2:40 pm


Tagged with ,


with 3 comments

SQLTXPLAIN, also know as SQLT, is a tool that helps to diagnose SQL statements performing poorly in an Oracle database. This tool is free to download and use. You only need to have access to My Oracle Support (MOS) and search for Document Id: 215187.1.

Q: What do you get from this tool?

A: In short, everything you want to know about one SQL statement if you intention is to analyze this SQL and eventually tune it. When you use this SQLT tool on a SQL statement, it generates many files then compresses them all into one zip. Always start your analysis reading the file with name sqlt_sNNNNN_main.html.

Written by Carlos Sierra

April 1, 2012 at 1:48 pm


Tagged with ,