Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Archive for the ‘Adaptive Cursor Sharing’ Category

Great Lakes Oracle User Group 2013 Conference

with 4 comments

I delivered my two sessions at the Great Lakes Oracle Users Group today. It was a great experience! I had the honor to fill to capacity both sessions, and both were scheduled in the largest room out of 5 concurrent tracks! I estimate that in each session I had more than 50% of the total audience. It is very rewarding been able to share some knowledge with such a great crowd. I was asked if I would do a half -day pre-conference workshop next year. I would certainly do if I can.

Anyways, with the conference behind, I am sharing here both presentations. For the one in Adaptive Cursor Sharing, if you want to perform the labs we did today, please post your request here and I will find a way to share those demo scripts.

  1. SQL Tuning made much easier with SQLTXPLAIN (SQLT)
  2. Using Adaptive Cursor Sharing (ACS) to produce multiple Optimal Plans

Scripts are now uploaded into acs_demo. Download this “doc” file and change its type to “zip” before opening it.

Written by Carlos Sierra

May 15, 2013 at 5:10 pm

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

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

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!

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.

Adaptive Cursor Sharing (ACS) free Webinar on December 11 at 4pm UCT (11am ET, 9am PT)

with 8 comments

Understanding how Adaptive Cursor Sharing (ACS) works is easy. But finding out WHY sometimes it works and sometimes it doesn’t is not trivial. This one-hour webinar explains how ACS is expected to work. Then it uses a demo to show how a SQL statement may or may not become “bind aware”. Concepts like ACS Histogram and ACS Selectivity Profile are explained and demonstrated. At the end of this session, participants should have a better understanding of ACS, its benefits, and its ramp-up process. If interested to attend please sign in to this free webinar under WebEx.

Recording is now available.

Written by Carlos Sierra

December 10, 2012 at 8:49 am