Carlos Sierra's Tools and Tips

I uploaded a new release of SQLTXPLAIN (SQLT) today. The 20 changes for this 11.4.4.7 release are listed below, but I’d rather explain what is important and what is not.

Long ago my friend Lester Gutierrez suggested me an interesting addition to SQLT. In those days he was managing an EBS performance development team, now he does the same for Fusion performance. Anyways, he mentioned it would be nice to include with SQLT XTRACT some relevant AWR reports. In particular, at the time when the SQL had performed poorly. The idea here is this: since SQLT already presents lots of details about the SQL and its environment, some broader diagnostics at the time the SQL had poor execution times would help to better understand the system in terms of load and concurrency. This new feature is one of the 20 changes below. SQLT now includes up to 6 AWR reports (6 is a default value for a new configuration parameter). These 6 reports correspond to the periods between two consecutive AWR snapshots where the SQL has had the largest delta elapsed time, across all nodes in case of RAC.

So, why limit this functionality to AWR? On this new release of SQLT, besides the new set of relevant AWR reports, SQLT also produces up to 6 ADDM reports, some ASH reports from memory and from AWR (one set per instance), up to 12 SQL Monitor Active reports, and one SQL Detail report. All driven by the SQL being analyzed. Prior releases included only one SQL Monitor Active report and under some special cases one SQL Detail report. So this 11.4.4.7 is more aggressive in terms of including reports that sometimes are simply unknown or under-utilized. I am setting the defaults for the parameters controlling the number of these reports to some small values, at least on 11.4.4.7. I will monitor their performance as I get new cases, and I may adjust these default values on subsequent releases. In terms of SQLT performance, the impact of these new features seems marginal.

Another relevant change is an addition of a new health-check recently requested by Abel Macias. It detects bug 14013094, which in short it causes Index Partition statistics to go wrong. This is when the order of partition names on an index do not match their table at index creation time. I have already seen a couple of cases and I feel more will come. This new health-check is also included into the new SQL Health-Check script (SQLHC). So if the SQL being analyzed may suffer from this bug, SQLT and SQLHC will create an “Observation”, which are basically the outcome of the dozens of health-checks performed around the SQL.

That brings another important change. When for some reason or another it is hard to install SQLT on a system, I am recommending to use SQLHC. The former requires to install a schema SQLTXPLAIN and hundreds of objects on it. The latter requires no installation at all. The gap of the diagnostics generated is brutal: SQLT provides all we need to diagnose a SQL performing poorly, while SQLHC provides only some pieces. But these pieces have been carefully selected. During several interactions with a key developer from the Fusion performance team, we agreed to add some important new sections to SQLHC. So in this release of SQLHC (also 11.4.4.7), this script has several new features. It will never compete with SQLT, but now it provides a good starting point to a SQL tuning case. Think SQLHC as a lite version of SQLT. By the way, SQLHC had its own note 1366133.1 on My Oracle Support (MOS), but it is also packaged within SQLT under sqlt/utl.

Parallel Execution new tool (MOS 1460440.1) did not escape a few changes in this 11.4.4.7 release. Mostly minor bugs. Same about profiler.sql (243755.1), which produces a report for PL/SQL Profiler data. Both tools are also packaged inside SQLT under sqlt/utl. This way it is easier to access them. If you are not familiar with them, you may want to go to their own MOS notes and look for the output sample.

I would say the other changes not mentioned explicitly, are less relevant. Anyways, try always to use the latest version of SQLT out of 215187.1. If you are a seasoned user of SQLT you know it changes often, always with some new features and some fixes. I hope you start using 11.4.4.7 soon!

1. BUG: Readme report for PeopleSoft was showing “Gathering Statistics” twice.
2. BUG: Parameter “_db_file_optimizer_read_count” was showing an incorrect value on Main report.
3. BUG: Number of executions above Execution Plans was showing zero when statistics_level was not set to ALL at the time the cursor was parsed.
4. BUG: Avoid error below when DBMS_METADATA is not granted to PUBLIC. 1452/29 PLS-00201: identifier ‘DBMS_METADATA’ must be declared
5. ENH: New HC to detect bug 14013094 on statistics gathering for partitioned tables and indexes where partition names are created in different order between table and index.
6. ENH: New tool parameter tcb_export_data to control value of parameter exportData on API DBMS_SQLDIAG.EXPORT_SQL_TESTCASE.
7. ENH: Handle non-stopper error: ORA-06550: line 4, column 18 with “FROM applsys.fnd_product_groups”.
8. ENH: Scripts roxtract and roxecute are now deprecated. Use sqlhc.sql and sqlhcxec.sql instead.
9. ENH: Main report, sqlhc.sql and sqlhcxec.sql include now Plan Line and Plan summaries for Active Session History from memory and AWR.
10. ENH: Scripts sqlhc.sql and sqlhcxec.sql to include ASH summaries, Cursor Sharing, set of SQL Monitor reports, SQL Detail report, SQL Profiles, SQL Plan Baseline and SQL Patches.
11. ENH: Readme for PSFT includes now instructions to gather statistics using both PSCBO_STATS and DBMS_STATS.
12. ENH: SQL Text now shows in red these keywords: SELECT, INSERT, UPDATE, DELETE, MERGE, FROM, WHERE.
13. ENH: COUNT(*) on tables accessed by SQL being analyzed is reduced to a sample size 1% of current values. This will make this COUNT less accurate but much faster.
14. ENH: SQLT main methods produce now up to 6 AWR reports for snaphots where the SQL being analyzed produced considerable elapsed time.
15. ENH: SQLT main methods produce now up to 12 most recent SQL Monitor reports for the SQL being analyzed.
16. ENH: SQLT main methods produce now ASH reports from memory and from AWR.
17. ENH: SQLT main methods produce now up to 6 ADDM reports for snaphots where the SQL being analyzed produced considerable elapsed time.
18. ENH: Workaround: disconnect ORA-07445 on SYS.DBMS_SQLTUNE_INTERNAL, which happened in some 11.2 systems.
19. ENH: SQL Test Case (TC) now provides a script tc_pkg.sql to generate a stand-alone TC out of an implemented SQLT TC.
20. ENH: All main methods record now into sqltxhost.log a set of 5 vmstat and sar samples.