Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Archive for the ‘Scripts’ Category

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
 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'';';

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.


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

How a Cursor becomes Bind Aware?

with 7 comments

On 11g we have Adaptive Cursor Sharing (ACS) as a mechanism to allow the generation of multiple optimal execution plans for a query. Before ACS we would only have one optimal execution plan in memory for a query given an optimizer environment. There were some exceptions to the rule like having different plans among RAC nodes, or different plans among child cursors when they were not shared. But in essence, before 11g we had one plan in memory for each query.

On ACS we have “bind sensitive” and “bind aware” cursors. The former stage only means “been monitored”, while the latter means: selectivity of predicates during a soft parse determine which optimal plan to use among the ones in memory. So it requires two mechanisms: one to control how to change state from “sensitive” to “aware” and another to select a plan in memory as per the values of the binds.

Becoming Bind Aware

Column v$sql_cs_statistics.rows_processed seems to drive this mechanism. This column is updated during a hard parse and it is a fuzzy representation of the amount of data the query manipulates during its execution (correlated to actual rows in execution plan).

For small values of v$sql_cs_statistics.rows_processed we increment by one v$sql_cs_histogram.bucket_id(0). For medium values we increase by one v$sql_cs_histogram.bucket_id(1). And for large values we do v$sql_cs_histogram.bucket_id(2). Observations on a test case built on seem to indicate that boundaries for these 3 buckets are: less than 1,000; between 1,000 and 1,000,000; and more than 1,000,000.

Cursor Sharing histogram buckets 0-2 are updated on every execution of a bind sensitive query. They represent data volume manipulation of every execution. If there are significant variances then the query would benefit of ACS and it becomes Bind Aware.

I have observed a couple of scenarios where the values stored on these 3 buckets cause the cursor to become bind aware:

  1. When two of the buckets have the same value, and this value is not zero
  2. When the smallest and the largest buckets (0 and 2) have a value other than zero

Keep in mind that observations above are not comprehensive and they may change in future releases. But they give you some clues WHY in some cases your query seems to be erratic in regards to ACS. WHY sometimes it stays as bind sensitive and sometimes it becomes bind aware. Basically ACS requires a ramp-up process. If your SQL executes mostly in one of the 3 volume ranges (as per buckets boundaries), it is possible that sometimes it becomes bind aware but not always. In such cases you may want to use the /*+ bind_aware */ CBO Hint. See this Optimizer Blog.

To help you inserting the /*+ bind_aware */ CBO Hint on an application that cannot be easily modified, you may want to use sqlt/utl/coe_gen_sql_patch.sql which is part of SQLTXPLAIN (MOS/Metalink 215187.1). You can also create a SQL Patch as documented in this other Optimizer Blog.

Script sqlt/utl/coe_gen_sql_patch.sql implements a SQL Patch adding by default 3 CBO Hints to your SQL: GATHER_PLAN_STATISTICS, MONITOR and BIND_AWARE. These 3 Hints would help to force “bind aware” status, and would also enable diagnostics on your query. When you execute this script sqlt/utl/coe_gen_sql_patch.sql you can change the CBO Hints you want in your SQL Patch. I recommend you only pass diagnostics Hints and not access path modifiers.

Written by Carlos Sierra

March 29, 2013 at 7:04 am

Hotsos Symposium 2013

with 5 comments

Today is the 3rd and last day of the Hotsos Symposium 2013. I will stay the extra day so I can attend Maria Colgan’s 1-day class. I am delighted to be here. This annual event is awesome. I will certainly apply to speak next year!

In my opinion, most sessions have been of good quality. Topics are interesting, and having 2 sessions at the same time allows me to pick one out of two instead of many. Of course there are times where I would like to attend the two!

Since this is my second year, I feel like coming back home. I know more people than last year, including other speakers. My kudos to Hotsos for keeping up with the quality of this event.

I would love to see more Oracle attendees, specially Support managers so they can better sense what the Oracle community needs… Anyways, I am happy to see some Oracle faces here… Since many of us work from home these days, participating in events like this helps to reestablish relationships, that would otherwise fade over time.

Yesterday, we paid a visit to Enkitec‘s offices. Veronica showed us the office and we ate some pizza there. The office is modern, stylish and cool. It invites to work! One thing I noticed is the natural synergy among employees. I find this intimate networking of smart guys so important to achieve better results. It encourages constant collaboration and kind of explains me the success of this firm. It brought to my memory my first years at Oracle, when we used to gathered in the aisles and talk about anything, including work! Anyways, it was nice to go and visit…

My to-do list after this Symposium grows:

  1. I noticed some misconceptions about statistics gathering. I have to chase down some Metalink notes which are causing this trouble…
  2. Without knowing it, some speakers have created some “enhancement requests” to SQLT, so I have to incorporate some of these soon…
  3. I need to review some SQL tuning complex cases that some participants want to discuss with me. I will review and provide some advice…
  4. I expect to receive some emails with enhancements to either the CBO or the database. I will log them to corresponding groups…
  5. Oracle Support would benefit of some refreshment in “diagnostics collection” for SQL tuning issues. That means another round of internal training. Maybe short remote sessions…
  6. I have a list of at least half a dozen topics that could be of interest for future conferences. I need to polish this list and draft some abstracts. Then I will review with some OUG’s key players to sense interest.
  7. I need to purchase (and read) like 3-4 books.
  8. I need to evaluate the possibility of writing a new tool. It would be a holistic review of CBO statistics (including schema, system, data dictionary and fixed objects). The tool would perform many health-checks and return a set of observations. A natural component could be the dynamic generation of a second script to actually implement those observations. Statistics is still a hot issue and I noticed very diverse opinions. Some are good and some are simply wrong.
  9. We need to investigate if “STATISTICS_LEVEL to ALL” can be improved in terms of performance in EXADATA.
  10. I need to learn STRACE and determine its real value for Support. Then review existing Metalink notes and update them accordingly.
  11. Big data and fussy data mining seem to be hot. Performance of data mining may become a concern… I have to research.
  12. Method-R and Delphix have excellent products. I need to learn more about them since I expect their adoption by the Oracle community will make them more visible to all of us. I may write a Metalink note on the former so Support can make better sense of it.
  13. I have to document in this Blog more about the many ramifications of SQLT, specially in the area of Plan Stability and Plan Migration. It seems to be an eternal concern. SQLT provides a lot of help in this area but I think we need better explanations on how to exploit these capabilities.

Ok. Time for a coffee and my daily email race… So far, email is winning by far…

Written by Carlos Sierra

March 6, 2013 at 7:53 am

How to flush one Cursor out of the Shared Pool

with 15 comments

During my 2-days SQL Tuning session at the NoCOUG last week, someone asked me how to flush just one Cursor out of the Shared Pool. We were playing with Adaptive Cursor Sharing (ACS) and between tests we were simply flushing the entire Shared Pool. On 11g there is an API that allows us to flush just one Cursor. I wrote the small script flush_cursor.sql below, and I also placed it inside SQLTXPLAIN (SQLT) under directory sqlt/utl. Feel free to use it and share as needed:

REM Flushes one cursor out of the shared pool. Works on 11g+
REM To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.
REM The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs.
REM These scripts are not run by as part of standard database creation.
SPO flush_cursor_&&sql_id..txt;
PRO *** before flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
 FOR i IN (SELECT address, hash_value
 FROM gv$sqlarea WHERE sql_id = '&&sql_id.')
 SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
PRO *** after flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
UNDEF sql_id;

Written by Carlos Sierra

February 28, 2013 at 2:21 pm