Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Archive for the ‘SQL Monitor’ Category

2-days open SQL Tuning Class in Pleasanton, CA this month!

with 9 comments

I will be delivering a two-days SQL Tuning class on February 19-20 in Pleasanton, CA. It is available to registered Oracle Users thanks to the help from NoCOUG. Space  is limited, so if you want to attend you need to hurry up.

This class covers:

  • CBO statistics (fundamentals and good practices);
  • Tracing with Events 10046 and 10053;
  • Plan Stability with Adaptive Cursor Sharing (ACS) and SQL Plan Management (SPM);
  • SQL Tuning tools: Trace Analyzer TRCANLZR (TRCA), SQLTXPLAIN (SQLT) and SQL Health-Check (SQLHC); and
  • SQL Tuning Test Cases (creation and implementation).

This is the first time this 2-days class is offered outside Oracle. Its content is an extract of the internal SQL Tuning Workshops I have developed and delivered to over 700 Oracle Engineers around the globe.

This is not an Oracle University (OU) class, which means the scope is more into the actual trades of the day-to-day life of SQL Tuning and less into product features. So if you have taken a SQL Tuning OU class, then this 2-days session would be a nice addition.

Another thing that you should expect from this class is lots of participation. In my sessions I always encourage participants to bring to the table their current SQL Tuning issues or concerns, so we can openly analyze and discuss them during class. The environment is friendly, welcoming and open.

I hope you can join us on February 19-20.


Deep-dive on SQLTXPLAIN offered externally for the first time

with 8 comments

The Rocky Mountain Oracle Users Group (RMOUG) Training Days is approaching soon: February 11-13, 2013. I hope you are attending this Oracle relevant event. There are so many good sessions that it is hard to decide which ones to attend!

For the first time, I will be conducting a 4-hours deep-dive external-session on SQLTXPLAIN (SQLT). The title is Using SQLTXPLAIN (SQLT) to Diagnose SQL Statements Performing Poorly. If you want to know SQLT from the basics to an intermediate level, consider attending this 4-hrs session. It will be offered on Monday, February 11 from 1 to 5 pm at the Colorado Convention Center in Denver, Colorado (USA). I love interactive sessions, so I will encourage voluntary participation and open discussions.

In addition to the 4-hrs deep-dive on SQLT, I will also speak on Wednesday, February 13 at 8:30 am about How to Improve SQL Performance with New SQL Health Check Tool. This is a 1-hour seminar with plenty of time for Q&A.

If you attend the event and want to discuss any issue with regard to SQLTXPLAIN (SQLT), SQL Health-Check (SQLHC) or simply SQL Tuning, please approach me. I will be there to help, share, listen, learn, discuss, etc.

Written by Carlos Sierra

January 23, 2013 at 6:39 am

What is new on SQLTXPLAIN released on July 2?

leave a comment »

I uploaded a new release of SQLTXPLAIN (SQLT) today. The 20 changes for this 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 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 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, 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 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 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.

Monitor one SQL using AWR

with 7 comments

Sometimes we want to keep an eye on a SQL statement that may not be part of any of the Top-SQL as per AWR reports. Use API below passing just the SQL_ID and from now on this SQL will be captured by AWR as long as the SQL is in memory at the time of the snapshot.


This API is available on 11.1 and higher.

Written by Carlos Sierra

June 29, 2012 at 5:01 pm

Posted in SQL Monitor