Archive for the ‘Adaptive Cursor Sharing’ Category
As I prepare for one of my sessions at ODTUG Kscope14 I came across the typical situation of having a SQL for which I wanted to produce multiple optimal execution Plans on an 11g environment. As you may know, with Adaptive Cursor Sharing (ACS) this is possible and automatic, but the problem is that sometimes the ACS ramp-up process causes some suboptimal Execution Plans. If you want to skip this ACS ramp-up process, lets say for a SQL that is part of a business-critical transaction and which is known to have unstable Plans, then you may want to create a SQL Patch with the BIND_AWARE Hint. Maria Colgan explained this method on this blog post. What I present here is a script I use, so I can easily implement SQL Patches for some SQL where I just need to inject one or two CBO Hints, like this BIND_AWARE. I use SQL Profiles or SQL Plan Management when I need to provide CBO Hints that affect access paths or join order, but if I just need something like skipping ACS ramp-up or a Hint to produce a SQL Monitor report, then I’d rather use SQL Patch.
Script below asks for SQL_ID and for a short list of CBO Hints to include. By default it includes these 3: “GATHER_PLAN_STATISTICS MONITOR BIND_AWARE”. Execute this script connecting as SYS.
---------------------------------------------------------------------------------------- -- -- File name: sqlpch.sql -- -- Purpose: Create Diagnostics SQL Patch for one SQL_ID -- -- Author: Carlos Sierra -- -- Version: 2013/12/28 -- -- Usage: This script inputs two parameters. Parameter 1 the SQL_ID and Parameter 2 -- the set of Hints for the SQL Patch (default to GATHER_PLAN_STATISTICS -- MONITOR BIND_AWARE). -- -- Example: @sqlpch.sql f995z9antmhxn BIND_AWARE -- -- Notes: Developed and tested on 188.8.131.52 and 184.108.40.206 -- --------------------------------------------------------------------------------------- SPO sqlpch.txt; DEF def_hint_text = 'GATHER_PLAN_STATISTICS MONITOR BIND_AWARE'; SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>; SET SERVEROUT ON SIZE UNL; COL hint_text NEW_V hint_text FOR A300; SET TERM ON ECHO OFF; PRO PRO Parameter 1: PRO SQL_ID (required) PRO DEF sql_id_1 = '&1'; PRO PRO Parameter 2: PRO HINT_TEXT (default: &&def_hint_text.) PRO DEF hint_text_2 = '&2'; PRO PRO Values passed: PRO ~~~~~~~~~~~~~ PRO SQL_ID : "&&sql_id_1." PRO HINT_TEXT: "&&hint_text_2." (default: "&&def_hint_text.") PRO SET TERM OFF ECHO ON; SELECT TRIM(NVL(REPLACE('&&hint_text_2.', '"', ''''''), '&&def_hint_text.')) hint_text FROM dual; WHENEVER SQLERROR EXIT SQL.SQLCODE; -- trim sql_id parameter COL sql_id NEW_V sql_id FOR A30; SELECT TRIM('&&sql_id_1.') sql_id FROM DUAL; VAR sql_text CLOB; VAR sql_text2 CLOB; EXEC :sql_text := NULL; EXEC :sql_text2 := NULL; -- get sql_text from memory DECLARE l_sql_text VARCHAR2(32767); BEGIN -- 10g see bug 5017909 FOR i IN (SELECT DISTINCT piece, sql_text FROM gv$sqltext_with_newlines WHERE sql_id = TRIM('&&sql_id.') ORDER BY 1, 2) LOOP IF :sql_text IS NULL THEN DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE); DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE); END IF; l_sql_text := REPLACE(i.sql_text, CHR(00), ' '); -- removes NUL characters DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text), l_sql_text); END LOOP; -- if found in memory then sql_text is not null IF :sql_text IS NOT NULL THEN DBMS_LOB.CLOSE(:sql_text); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM); :sql_text := NULL; END; / SELECT :sql_text FROM DUAL; -- get sql_text from awr DECLARE l_sql_text VARCHAR2(32767); l_clob_size NUMBER; l_offset NUMBER; BEGIN IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN SELECT sql_text INTO :sql_text2 FROM dba_hist_sqltext WHERE sql_id = TRIM('&&sql_id.') AND sql_text IS NOT NULL AND ROWNUM = 1; END IF; -- if found in awr then sql_text2 is not null IF :sql_text2 IS NOT NULL THEN l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0); l_offset := 1; DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE); DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE); -- store in clob as 64 character pieces WHILE l_offset < l_clob_size LOOP IF l_clob_size - l_offset > 64 THEN l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' '); ELSE -- last piece l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' '); END IF; DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text), l_sql_text); l_offset := l_offset + 64; END LOOP; DBMS_LOB.CLOSE(:sql_text); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM); :sql_text := NULL; END; / SELECT :sql_text2 FROM DUAL; SELECT :sql_text FROM DUAL; -- validate sql_text BEGIN IF :sql_text IS NULL THEN RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).'); END IF; END; / PRO generate SQL Patch for SQL "&&sql_id." with CBO Hints "&&hint_text." SELECT loaded_versions, invalidations, address, hash_value FROM v$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1; SELECT child_number, plan_hash_value, executions, is_shareable FROM v$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2; -- drop prior SQL Patch WHENEVER SQLERROR CONTINUE; PRO ignore errors EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'sqlpch_&&sql_id.'); WHENEVER SQLERROR EXIT SQL.SQLCODE; -- create SQL Patch PRO you have to connect as SYS BEGIN SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH ( sql_text => :sql_text, hint_text => '&&hint_text.', name => 'sqlpch_&&sql_id.', category => 'DEFAULT', description => '/*+ &&hint_text. */' ); END; / -- flush cursor from shared_pool 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; PRO *** flushing &&sql_id. *** BEGIN FOR i IN (SELECT address, hash_value FROM gv$sqlarea WHERE sql_id = '&&sql_id.') LOOP DBMS_OUTPUT.PUT_LINE(i.address||','||i.hash_value); BEGIN SYS.DBMS_SHARED_POOL.PURGE ( name => i.address||','||i.hash_value, flag => 'C' ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END LOOP; END; / 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; WHENEVER SQLERROR CONTINUE; SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>; SET SERVEROUT OFF; PRO PRO SQL Patch "sqlpch_&&sql_id." will be used on next parse. PRO To drop SQL Patch on this SQL: PRO EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'sqlpch_&&sql_id.'); PRO UNDEFINE 1 2 sql_id_1 sql_id hint_text_2 hint_text CL COL PRO PRO sqlpch completed. SPO OFF;
Every so often I see systems where there is a good amount of SQL that uses Literals instead of Binds, and executes enough times to create a large number of Cursors. Is this a red flag? As many questions regarding performance, I would say the right answer is: it all depends.
Of course we want to use Binds instead of Literals in order to reduce the frequency of Hard Parses, and in turn reduce CPU consumption and space utilization in the Shared Pool. Does it mean we want to replace all Literals with Binds? Do we declare war on Literals? In my opinion, the answer is simply: NO.
If a SQL has a Predicate on a date column, or a key column, then I would expect the Number of Distinct Values (NDV) for such column to be high, and in some cases as high as the number of rows in the Table (unique values for example). In the other hand, if the Predicate is in one of those columns that denotes a code, like Process Type or Status, and the NDV is small, then I’d rather keep the Literal in place. Specially if the data in such column is skewed and I have (or plan to have) Histograms on it.
What do I propose?
- If the SQL is executed sporadically, then it does not matter (Literals or Binds).
- If the SQL executes frequently, and the Predicate in question is on a Column where the Number of Distinct Values (NDV) is high, then use a Bind instead of a Literal (for this Predicate).
- If the SQL executes frequently, and the Predicate in question is on a Column where the NDV is low, then use a Literal (for this Predicate). This assumes the NDV for these Literals is also small.
- Regardless if using a Literal or a Bind for a particular Predicate: If the data in a Column referenced by a Predicate is heavily skewed, gather Statistics with Histograms on this Column.
Follow-up question: When the NDV is high or low? The answer is also: it all depends. I personally prefer to see Literals if the NDV for this Column (and this Literal) is less than 10 (or so).
Why having Binds and Literals on same SQL is better than having all Binds?
If we have good set of CBO Statistics, and we have Histograms on skewed data, and we are using bind peeking, and we are on 11g, and Adaptive Cursor Sharing (ACS) is enabled, and we plan using SQL Plan Management (SPM), then we are for a treat:
With all the “ands” above, by using Binds on predicates with high NDV and Literals in those with low NDV, then we will end up having a small number of different SQL_IDs for what we consider “the same SQL”. Each incarnation of this SQL could potentially have its own set of optimal Execution Plans created by ACS and the CBO (by making use of Histograms on the data and Selectivity Profiles on ACS). Then, with the aid of SPM we could provide stability to those multiple optimal Execution Plans for each version of the SQL. That means that SQL Q1 with Literal L1 could have a different set of optimal plans than Q1 with Literals L2.
Replacing some Literals with Binds but not all Literals sounds like a lot of work, but actually the extra work may be worth the effort. In my opinion, the end result is better if we replace most, but not all (as per proposal above). WHY? Even when ACS does a good job at finding multiple optimal plans for a SQL by using the selectivity of the predicates; by allowing a small number of cursors for the same SQL given the use of Literals in columns with low NDV, we are basically reducing the times we would have to execute a SQL with a sub-optimal plan due to current ACS ramp-up process. This extra granularity provided by a small number of incarnations of the “otherwise same SQL” could be crucial for tuning complex SQL or corner cases.
I will be delivering a couple of courses soon. One in January and the second in February. I will keep posting upcoming Training and Conferences on a new link at the right margin of this blog.
Exadata Optimizations Jan 13-14
This 2-days “Exadata Optimizations” course is for Developers and DBAs new to Exadata and in need to ramp-up quickly. As the name implies, its focus is on Exadata Optimizations. We talk about Smart Scans, Storage Indexes, Smart Flash Cache, Hybrid Columnar Compression (HCC) and Parallel Execution (PX). This course is hands-on, with a fair amount of demos and labs.
SQLTXPLAIN (SQLT) Feb 20-21
This “SQL Tuning with SQLTXPLAIN” 2-days course shows how to use SQLT to actually do SQL Tuning. We go over the ying-yang of the CBO, meaning: Plan Flexibility versus Plan Stability. We use SQLT for labs and we also go over some real-life SQL Tuning cases. If you are currently using SQLT, you are welcome to bring a SQLT Report to class and we could review it there.
New year, new resolutions. I will be investing part of my time sharing knowledge through formal courses and conferences. These days it is hard to find the time and budget to keep our knowledge on the edge, but again and again I see that many of our daily struggles could be mitigated by some concise technical training. So I encourage you to add some training to your list of resolutions for this new year; or at the very least, to get and read some fresh books.
Happy New Year 2014!
Next week I will be participating at the East Coast Oracle Users Group ECO. If you are not familiar with this Oracle Users Group you may want to check it out. It gathers close to 300 Oracle users, and every year it gets bigger and better. The environment is friendly, and I would say even cozy. There are 6 simultaneous tracks packed into 2 full days. Sometimes it is hard to decide which session to attend, but fortunately the diversity of topics usually reduces the choice to one or two (unless you are like me, and want to learn, and learn, and learn…)
Anyways, the schedule is here so you can check what is all about. If you decide to attend this year (next week), you can still register today and get a small discount using code SPEAKERVIP.
I will be speaking about two topics. One is about Adaptive Cursor Sharing, while the second is about SQLT XPLORE, which I call “the SQLT hidden child“. SQLT XPLORE is a stand-alone module inside SQLTXPLAIN (SQLT), but it does not require SQLT to be installed. This SQLT XPLORE uses brute force analysis in order to “discover” Execution Plans that may be elusive. Typical case is when you upgrade your database and your Execution Plan changed.
Stelios Charalambides writes about SQLT XPLORE in his book “Oracle SQL Tuning with Oracle SQLTXPLAIN“. In my session at ECO I will show some XPLORE samples, and will explain how to read its output. I am planing to do a live demo about executing this tool. And of course, all questions are welcomed!
I hope to see some familiar faces next week at ECO, and also to meet some new Oracle users and other speakers. Looking forward to speak at ECO next week!
Some of you may not know what is the Oaktable. I quote here a very precise description right from the Oaktable’s main page: <<< The OakTable network is a network for the Oracle scientist, who believes in better ways of administering and developing Oracle based systems. The network is an informal organisation, which will get together at various Oracle events. >>> To me, it is a lot more, since its members are a very collaborative bunch of smart and dedicated folks. A common denominator I have perceived so far is the honest desire to apply scientific methods to complex issues we encounter in the Oracle world. If you are serious about learning Oracle and its intricacies, you may want to do as I do: “feast from the crumbles of the Oaktable“.
The Oaktable World is a conference that happens to coincide with OOW. This year I have the honor to speak at Oaktable World. According to the agenda, I will speak on Tuesday, September 24 at 10am. My session is at the same time than Jonathan Lewis, thus I am prepared to speak to myself (unless lucky me you happen to not know who is JL). Anyways, I will be speaking about Adaptive Cursor Sharing (ACS) and how it actually works. I will do some live demos and my goal is that all participants (if any) can actually fully understand how and when ACS produces multiple optimal execution plans for a given SQL.
If you plan to attend any of the Oaktable World sessions, please be sure you register. Space is very limited.
I will be attending myself as many of the other Oaktable World sessions as I can, else I will be there at the Enkitec booth #501 in Moscone South in case you want to stop-by and say hello.
SQLTXPLAIN is a SQL Tuning tool widely used by the Oracle community. Available through My Oracle Support (MOS) under document 215187.1, this free tool is available for download and use to anyone with MOS access. It has helped thousands of times to expedite the resolution of SQL Tuning issues, and many Oracle DBAs and Developers benefit of its use on a daily basis.
Stelios Charalambides has done an excellent job writing a book on this topic. In his book Stelios covers many aspects about SQLTXPLAIN and some related topics. I highly recommend to get a copy of this book if you want to learn more about SQLTXPLAIN. It is available at Amazon and many other retailers.
The new 2-days SQLTXPLAIN Workshop offered by Enkitec (an Oracle Platinum business partner and my employer) is a completely new course that interleaves “how to use effectively SQLTXPLAIN” with important and related SQL Tuning Topics such as Plan Flexibility and Plan Stability. This hands-on workshop offers participants the unique opportunity to fully understand the contents of SQLTXPLAIN and its vast output through an interactive session. About half the time is dedicated to short guided labs, while the other half uses presentations and demos. This workshop is packed with lots of content. It was a real challenge packaging so much info in only two days, but I am very pleased with the result. It became a 2-days intensive knowledge transfer hands-on workshop on SQLTXPLAIN and SQL Tuning!
The first session of this workshop is scheduled for November 7-8 in Dallas, Texas. I expect this pilot session to fill out fast. Other sessions and onsite ones will be offered during 2014. I hope to meet many of you face to face on November 7!
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.