Carlos Sierra's Tools and Tips

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 sqlt.zip 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.

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

Fixes:

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.

Enhancements:

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 11.2.0.3 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.

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.