Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Archive for the ‘SQL Healh-Check (SQLHC)’ Category


with 4 comments

I will be speaking at the Independent Oracle Users Group (IOUG) “COLLABORATE 13” conference next week (April 8-11, 2013). I will deliver two sessions:

  1. SQL Tuning made easier with SQLTXPLAIN (SQLT): Monday, April 8 between 3:45 and 4:45 pm. Mile High Ballroom 3A.
  2. How to improve SQL Performance with new SQL Health Check Tool: Wednesday, April 10 between 1:00 and 2:00 pm. Mile High Ballroom 3A.

This time I will demo SQLT installation and use of the SQLT XTRACT method. For the SQLHC I will demo an actual execution. So, I will spend less time in PPTs and more time showing how to use these tools and how to navigate their output. Feedback from prior events ask for more demo time…

Traveling to Denver in 3 hours (Saturday morning) and returning home (Orlando) on Thursday night. If you are attending this IOUG COLLABORATE 13 event I would love to meet you!

Also, if you have ANY questions about SQLTXPLAIN (SQLT) or SQL Health-Check (SQLHC) keep in mind I am just one email away!

Written by Carlos Sierra

April 6, 2013 at 7:07 am

SQLTXPLAIN capabilities – free webinar for Oracle partners (Wednesday 27)

with one comment

This Wednesday, February 27 at 8 am Pacific Time (11 am ET), I will deliver a one hour webinar to Oracle partners. The topic is about SQLTXPLAIN capabilities and how this tool helps to diagnose SQL statements performing poorly. Details are here. Format is 45 minutes of presentation and demo, followed by 15 minutes for a Q&A session.

Written by Carlos Sierra

February 25, 2013 at 11:27 am

SQL Tuning Workshop at the NoCOUG

with 5 comments

I am delivering a two-days SQL Tuning Workshop at the Norther California Oracle Users Group (NoCOUG) in Pleasanton, California. Today is the 2nd and last day of this class. We discussed yesterday: Cost-based Optimizer (CBO) Statistics, Event 10053, Adaptive Cursor Sharing (ACS) and SQL Plan Management (SPM). We will discuss today some SQL Tuning tools: Event 10046, Trace Analyzer TRCANLZR, SQLTXPLAIN (SQLT), SQL Health-Check (SQLHC) and SQLT Test Case (TC). There are around 35 participants and I would say the level of the class is between intermediate to advanced. Most participants are seasoned DBAs and some have a good level of expertise as Developers. Abel Macias is participating in this session as my co-instructor. He is bringing to class his extensive expertise in SQL Tuning. It is always a challenge to keep the attention of such a large and diverse group, but I would say the level of participation has been quite high. I appreciate all the effort from several contributors of the NoCOUG who have made this training possible. My special thanks to Gwen Shapira, Randy Samberg, Iggy Fernandez and Eric Hutchinson. It is very rewarding to see how Oracle Users Groups are putting together events like this where we can openly share knowledge with our Oracle users community.

I will speak at the actual NoCOUG Conference this Thursday. I will talk about the content of SQLTXPLAIN (SQLT) main report, which is quite comprehensive. I will go over some samples and I will allocate plenty of time for questions during and after my presentation. These days SQLT is widely used within Oracle and now by several enterprise Oracle customers.

Stelios Charalambides book about SQLT Tuning and SQLTXPLAIN will be released on March 20. The title is Oracle SQL Tuning with Oracle SQLTXPLAIN. This book can now be pre-ordered in Amazon. If you want to learn more about how to use SQLTXPLAIN to diagnose SQL Tuning issues you may want to get a copy of this book.

After NoCOUG I will spend one week at the office, where I will deliver a live Webinar in SQL Tuning to our Oracle Partners. Then my next conference is the popular Hotsos Symposium in March 3-7. In my humble opinion, this annual Hotsos event is the single most relevant in the area of Oracle Performance. Thus I am very excited to have the opportunity to speak there. A real honor!

RMOUG Training Days February 12 (day 2)

leave a comment »

Second day is over now. I attended several interesting sessions. The one I liked the most was about the role of women in technology. I am so glad RMOUG provides now this kind of session! I think we need to encourage women to fill positions that are still dominated by men, like speaking at conferences. If you consider that 40% of DBAs are women while only 11% are speakers, you get to understand something can be improved here…

Today, I will deliver two one-hour sessions:

  1. How to improve SQL Performance with new SQL Health Check Tool. Wednesday, February 13 at 8:30 am. Room 4a.
  2. How to create in 5 minutes a SQL Tuning Test Case using SQLTXPLAIN. Wednesday, February 13 at 1:30 pm. Room 403.

I am so happy to participate as a speaker at the RMOUG! My reward is simple: to see participants nodding and smiling when a concept clicks in, or when they get an answer to a question that has been boiling in their heads for some time. After 17 years at Oracle and having learned from so many smart guys, I am lucky to be in a position where I can answer many questions. Still I say “I do not know” when asked something I do not know, but when it comes to SQL Tuning, I feel pretty comfortable with most subtopics. I notice a great interest on Plan Stability and SQL Plan Management in particular. I will be offering sessions in this area within SQL Tuning…

Looking forward to a great third and last day of this event today!

Written by Carlos Sierra

February 13, 2013 at 6:32 am

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

SQL Dynamic eXtractor (SQLDX) is now available under SQLT and SQLHC

with 14 comments


As most of you know SQLTXPLAIN (SQLT) and SQL Health-Check (SQLHC) include lots of information about one SQL statement. SQLT requires to install an schema and some objects while SQLHC requires no installation. The main difference between the two are:

  1. The level of details: (SQLT provides a lot more).
  2. The capability of automatic Test Case (TC) extraction (SQLT does this).

SQLT is always the preferred method, while SQLHC is the fall back when SQLT cannot be installed in a timely manner.

Both SQLT and SQLHC work on 10g and 11g databases. SQLT presents details about many GV$ and DBA views referencing your SQL statement. These views include both 10g and 11g. In other words, SQLT is not restricted to the minimum common denominator (10g) but it expects to extract information from any relevant view referencing a SQL_ID on 11g. There is one small limitation although: SQLT does not show in its main report all views and tables referencing a SQL_ID, but most of what is usually needed for SQL Tuning.

What is the SQL Dynamic eXtractor (SQLDX)?

SQL Dynamic eXtractor (SQLDX) is a new stand-alone SQL script (sqldx.sql) that requires no installation. It dynamically generates another script that selects from all SYS views and tables which reference a column SQL_ID, extracting all rows matching the SQL_ID passed. It then computes the  SIGNATURE for the SQL’s text and proceeds to generate dynamic SQL to select from all SYS tables and views referencing this SIGNATURE. For SIGNATURE it uses exact and force matching.

SQLDX input and output

SQLDX output is a ZIP file that contains the output of all dynamic SELECTs on all SYS tables and views referencing your SQL_ID and its SIGNATURE (exact and force). The output of every dynamic SELECT is a file on its own. Their format is HTML and Coma Separated Values (CSV). The latter can be opened in Excel to ease aggregations, finer selections, sorts, etc.

SQLDX takes as input 3 parameters:

  1. Oracle Pack License: Diagnostics Pack or Tuning Pack (D|T).
  2. Output Type: HTML, CSV or BOTH (H|C|B).
  3. SQL_ID: resident in memory or AWR.

SQLDX is included on SQLT and SQLHC and higher (released on December 31, 2012). SQLDX is automatically executed at the end of any SQLT main method and at the end of SQLHC. Keep in mind that SQLDX can also be executed stand-alone and it requires no installation whatsoever. So it can be used in a Production environment or in a read-only database.

Where do you get SQLDX from?

SQLT (MOS 215187.1) includes sqlt/run/sqldx.sql and SQLHC (MOS 1366133.1) includes sqlhc/sqldx.sql. It is the same sqldx.sql script. When sqldx.sql is executed from within SQLT or SQLHC it generates a ZIP file identified as “sqldx”. Look for it next time you use SQLT or SQLHC (version or higher). Or you may want to try sqldx.sql stand-alone.

Written by Carlos Sierra

January 2, 2013 at 8:05 am

Speaking at the UKOUG 2012

leave a comment »

I had the honor to be accepted as a speaker of two sessions at the UK Oracle Users Group 2012. So I will be in Birmingham, UK from December 1st to December 5, 2012.

My topics are in SQLT Test Case (TC) functionality and in new SQL Health-Check (SQLHC) script. Both sessions will include a demo since I believe it is easier to grasp capabilities by showing them instead of just navigating through a PPT.

SQLT has now a 3rd method to generate a Test Case. So now we have 11g Test Case Builder (from the product), plus SQLT TC and the new SQLT TCX or Test Case eXpress. This last method was requested by James Cremonini. SQLT TCX is mostly an internal tool to expedite the implementation of a test case for SQL Tuning. I am making it available to anyone interested.

SQLHC continues growing. I will work soon on an enhancement that will capture a lot more regarding one SQL, while not installing anything in the database. SQLHC is used in cases where for some reason SQLT cannot be installed any time soon.

Anyways, if you are attending UK OUG I hope to meet face to face.

Written by Carlos Sierra

November 29, 2012 at 4:28 pm

Mutating histograms, mutating number of rows, mutating blevel, mutating number of distinct levels… What do they mean?

with 2 comments

Every so often I get an email from a SQLTXPLAIN user asking the meaning of these “mutating” observation from the health-checks engine on SQLT. On next SQLT release (due on November 21, 2012), these messages will read “fluctuating” instead of “mutating”, but the health checks will remain the same. So, what do they mean?

Take “fluctuating number of histogram endpoints count”. What it means is the following: every time we gather CBO statistics on a Table, we have a new “version” of the statistics. We store the old one automatically (DBMS_STATS does that starting on 10g) and the new version becomes “current”. We store 31 days of history by default. So if we gather statistics on a Table lets say daily, we may have about 30 statistics versions for that Table and its columns (and Indexes). Now, if a column has lets say 5 buckets in its histogram and the next version has 6, there was a recorded change of 20%, which may be relevant for your investigation. If a column during its stored history has a number of buckets that fluctuates more than 10% from one version to the next, then it is flagged as “fluctuating” (use to be mutating in SQLT and older). Changes of less than 10% are ignored.

Same concept applies to other elements like the number of distinct values in a column, or the height of an index, or the number of rows in a Table or Partition. If they fluctuate more than 10% from one version of the CBO statistics to the next, then they are flagged and reported in the Observations section of the main SQLT report.

Are we supposed to do anything with those health-checks? All depends. Lets say your SQL suddenly stop using index X and now uses index Y. If blevel on X has increased recently, you can suspect a cost increased due to the new blevel and investigate accordingly. Keep in mind that “Observations” section of SQLT, which reports outcome of health-checks, is basically a collection of heads-up. Things to investigate and maybe pointing to the root cause of a recent change in an execution plan for the SQL being investigated.

Written by Carlos Sierra

November 14, 2012 at 5:59 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.