Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

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

SQLTXPLAIN under new administration

with 5 comments

With great pleasure I am announcing here that SQLTXPLAIN (SQLT) is “under new administration”. What does it mean? In short, almost nothing to you. Under the covers, it means you will start seeing a new (and younger) face associated to SQLT. My good friend Mauro Pagano from Oracle has graciously accepted the role of been the first/main contact for this tool. He also has accepted the responsibility of supporting, maintaining and enhancing this tool.  Mauro won’t be alone on this task. Another good friend of mine – Abel Macias, will back up Mauro when needed.

Support of SQLT usually goes like this: If someone from Oracle Support requests you to provide a SQLT report, that engineer owning the Service Request (SR) is your main point of contact, including questions/concerns/issues installing SQLT. That same engineer can reach to Mauro or Abel for assistance if needed. In the other hand, if you are a business partner of Oracle or you are using SQLT on your own terms, and you have a question/concern/issue about a SQLT installation, then refer to Metalink (MOS) note 215187.1, where you can see who is the main contact for SQLT and how to email him/her. As of today that person is Mauro.

A bit about Mauro and Abel: I have worked with both for several years now, and besides been super qualified, I have learned that each of us has usually a different technical view on a given issue. So, a brainstorm between 2 or the 3 of us usually provides a much better outcome than using only one brain… Anyways, Mauro and Abel have been instrumental to the success of SQLT, always providing great ideas which translate into very useful features. SQLT is my baby, but it is also theirs! 😉

Of course I will continue advising on SQLT. That includes frequent communication with Mauro and Abel, but it also means to continue helping the Oracle community, specially when it comes to “how to make sense of the output”. So, SQLT is “under new administration”, but it should mean almost nothing different to you.

Keep using SQLT and keep providing your valuable feedback! SQLT is what it is today thanks to the great ideas we constantly receive and implement!

Advertisements

Written by Carlos Sierra

July 17, 2013 at 6:06 am

Oracle Queries are taking different Plans

with 4 comments

I recently saw a post with this title “Oracle Queries are taking different Plans” and since there was a lot of guessing I suggested to use SQLTXPLAIN and find there the root cause. For some reason or another the thread is still open after 20 days, and the guessing continues. Since the problem of “finding the root cause of a change on an execution plan” is old and still common, I decided to talk about it here.

In Oracle, execution plans do change for one SQL over time, that is a fundamental function of the Cost-based Optimizer (CBO). Not only is normal, but expected and desired in most circumstances. What is not desired, is when a SQL that had a good performing plan suddenly changes its plan to one that we consider sub-optimal in terms of performance. This should be the concern and focus. So WHY the CBO suddenly generates such a plan? The possible reasons are many but I enlist here the most common ones I can think of:

  • Schema object statistics changes
  • Small sample sizes when gathering CBO schema statistics (mostly on 10g)
  • Lack of statistics in some columns referenced by predicates
  • Inconsistent values of bind variables referenced by SQL
  • System wide changes like CBO parameters or system statistics
  • Index modifications including new and dropped indexes
  • Invalid or invisible indexes
  • Large SQL text with many predicates where several plans with similar cost are possible
  • Inconsistent ramp-up process of Adaptive Cursor Sharing (ACS)
  • SQL Profiles generated by the SQL Tuning Advisor (STA)
  • An index changing its blevel (for example from blevel 2 to 3)
  • Volatile values in some columns (for example value “Pending” exists in table column for a short time)
  • Asynchronous gathering of stats on volatile data instead of gathering right between load and consumption

The list above is not comprehensive. It only includes what came to my mind while writing it, so I may have forgotten several cases. And of course there are always some corner cases like special conditions activating uncommon heuristics, or simply hitting a CBO bug.

Guessing WHY a plan has changed is very time consuming and requires multiple queries to the metadata on the database. Since most of the metadata we are interested on is dynamic by nature, we base our guess work on an ever changing environment. Of course the root cause become elusive and we may give up for some time, or jump to false conclusions.

This is where SQLTXPLAIN (MOS 215187.1) or its younger and lighter sister SQLHC (MOS 1366133.1) come to the rescue. These free tools, specially SQLTXPLAIN (SQLT), capture the static and dynamic environment around the one SQL we want to analyze.

By having a consistent view of all elements affecting the execution plan, it becomes much easier to find the root cause of a plan change. And since SQLT also includes current and past details about parameters, statistics and bind variable values, we can even correlate those values to prior execution plans. So I encourage you to try SQLTXPLAIN on a SQL next time you are confronted by the old question: “WHY Oracle Queries are taking different Plans?”.

I am aware SQLTXPLAIN is intimidating. Its output is exuberant, but the point is that it includes what we need to find the root cause of most if not all SQL Tuning cases where a plan has changed.

I have presented this SQLT tool in several conferences during the past year or so. I think it is appropriate now to put together a 3 days workshop to master this tool… Just an idea at this time.

Understanding SQLTXPLAIN Health-Checks

with 2 comments

SQLTXPLAIN (SQLT) collects diagnostics details about a SQL statement that either performs poorly or it produces unexpected results (also known as wrong results). In addition to that, it also performs over a hundred health-checks around the SQL statement being analyzed. The results of these so called health-checks are displayed on the main HTML report under the “Observations” section. Within each observation there is description of the meaning and in some cases a pointer to known bugs or notes. Since there are so many health-checks, it happens that every once in a while we want to dig further into WHY we get this “observation”. If that is your case and you understand SQL and PL/SQL, I invite you to “hack” into the SQLT source code and see how this health-check is triggered.

Finding WHY SQLT triggered a particular Health-Check

Assume you get this “observation” in your main HTML report “Table contains 2 column(s) referenced in predicates where the number of distinct values does not match the number of buckets.“.

Open sqlt/install/sqcpkgh.pkb (this h is for health-checks) and search for a portion of this text “number of distinct values does not match the number of buckets”. You will find a piece of code that looks like this:

 -- 10174050 frequency histograms with less buckets than ndv
 BEGIN
 SELECT COUNT(*)
 INTO l_count
 FROM sqlt$_dba_all_table_cols_v
 WHERE statement_id = p_tab_rec.statement_id
 AND owner = p_tab_rec.owner
 AND table_name = p_tab_rec.table_name
 AND histogram = 'FREQUENCY'
 AND num_distinct <> num_buckets
 AND in_predicates = 'TRUE';

IF l_count > 0 THEN
 s_obs_rec.type_id := E_TABLE;
 s_obs_rec.object_type := 'TABLE';
 s_obs_rec.object_name := p_tab_rec.owner||'.'||p_tab_rec.table_name;
 s_obs_rec.observation := 'Table contains '||l_count||' column(s) referenced in predicates where the number of distinct values does not match the number of buckets.';
 s_obs_rec.more := 'Review <a href="#tab_cols_cbo_'||p_tab_rec.object_id||'">column statistics</a> for this table and look for "Num Distinct" and "Num Buckets". If there are values missing from the frequency histogram you may have Bug 10174050. If you are referencing in your predicates one of the missing values the CBO can over estimate table cardinality, and this may produce a sub-optimal plan. As a workaround: alter system/session "_fix_control"=''5483301:off'';';
 ins_obs;
 END IF;
 END;

In this case the health-check derives from view sqlt$_dba_all_table_cols_v. You can find all view definitions inside file sqlt/install/sqcvw.sql. This file shows that view sqlt$_dba_all_table_cols_v selects from tables sqlt$_dba_tab_cols and sqlt$_dba_nested_table_cols.

There is a predicate on the health-check that reads “num_distinct <> num_buckets“. So this condition is what triggered this particular health-check.

In some cases, the column driving the health-check is an addition to the base DBA or GV$ views, so in such cases you may have to search for that column in one of these two packages: sqlt/install/sqcpkgd.pkb or sqlt/install/sqcpkgt.pkb. Where the “d” stands for Diagnostics data collection and the “t” for Transformation of diagnostics data.

Conclusion

When you get a SQLT health-check that is of your concern and you need to find out WHY it was raised, you may need to look into SQLT source code. Always start on the “h” module and continue either in view definitions or in the two packages that seed special columns. Those two packages “d” and “t” are the ones responsible for data collection and transformations.

SQLT source code is transparent. It is written in an easy to follow style. Feel free to reverse engineer any part of it. No mysteries, no hidden pieces, no black magic…

Written by Carlos Sierra

April 19, 2013 at 10:43 am

IOUG COLLABORATE 13

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