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 18.104.22.168 and 22.214.171.124 -- --------------------------------------------------------------------------------------- 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;
I have this query that references a couple of Global Temporary Tables (GTT). These GTT have no CBO Statistics, thus Dynamic Sampling (DS) is used on them. The problem is default value of 2 at the instance level samples only 64 blocks and these GTT are large. Testing with DS on larger samples provides better quality on these dynamic statistics and a better performing Execution Plan for this particular SQL.
This SQL already uses an imported SQL Profile generated by coe_xfr_sql_profile.sql out of SQLT (MOS 215187.1) under sqlt/utl directory. This SQL Profile contains CBO Hints created out of the Outline Data contained on the other_xml column of the Plan. Still a higher level of DS is needed for these two GTT in order to produce a better performing Plan. Setting DS at the instance level or session level is not an option. Modifying the query is not an option. So the question is: “how to embed a dynamic sampling hint on a SQL that already has a SQL Profile based on an Outline“?
On the script generated by coe_xfr_sql_profile.sql add one extra Hint with the DS level that is needed. Look at sample below, where one extra Hint has added right below DB_VERSION. Since the two GTT have no CBO Statistics, they invoke DS, which would use now level 6 instead of 2.
h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('126.96.36.199')]', q'[DB_VERSION('188.8.131.52')]', q'[OPT_PARAM('optimizer_dynamic_sampling' 6)]', q'[ALL_ROWS]',
Each Oracle Users Group has its own personality. This week I had the honor to co-speak with Mauro Pagano at GLOC, and the experience has been great! We delivered a 4 hours workshop on “Oracle Performance Tuning 101″. We talked about fundamentals and tools of the trade for both Database and SQL Tuning. It was really nice to have over 50 attendees and it was a challenge to keep them engaged for such a long time! Still the feedback was: we want more!
Anyways, as I am listening to Tom Kyte speaking about the new Oracle In-memory Database, I take a moment to write some words about this GLOC. What can I say? In short: It has been awesome! This Oracle Users Group is well organized and growing slowly for surely (over 300 attendees). This is my second year speaking at GLOC and I see it bigger, better and very welcoming. As far as I understand, membership is inexpensive and still manages to attract such good speakers as Tom Kyte, Alex Gorbachev, Scott Spendolini, Tim Gorman, Kyle Hailey, Steven Feuerstein, Carol Dacko, Ric Van Dyke and many others. My kudos to GLOC. Way to go! Looking forward to contribute again next year, and hoping to see more speakers and attendees traveling from other Regions.
Every so often I see on a distribution list a posting that starts like this: “I upgraded my application from database release X to release Y and now many queries are performing poorly, can you tell why?”
As everyone else on a distribution list, my first impulse is to make an educated guess permeated by a prior set of experiences. The intentions are always good, but the process is painful and time consuming. Many of us have seen this kind of question, and many of us have good hunches. Still I think our eagerness to help blinds us a bit. The right thing to do is to step back and analyze the facts, and I mean all the diagnostics supporting the observation.
What is needed to diagnose a SQL Tuning issue?
The list is large, but I will enumerate some of the most important pieces:
- SQL Text
- Version of the database (before and after upgrade)
- Database parameters (before and after)
- State of the CBO Statistics (before and after)
- Changes on Histograms
- Basics about the architecture (CPUs, memory, etc.)
- Values of binds if SQL has them
- Indexes compare, including state (visible?, usable?)
- Execution Plan (before and after)
- Plan stability? (Stored Outlines, Profiles, SQL Plan Management)
- Performance history as per evidence on AWR or StatsPack
- Trace from Event 10053 to understand the CBO
- Trace from Event 10046 level 8 or 12 to review Waits
- Active Session History (ASH) if 10046 is not available
I could keep adding bullets to the list, but I think you get the point: There are simply too many things to check! And each takes some time to collect. More important, the state of the system changes overtime, so you may need to re-collect the same diagnostics more than once.
SQLTXPLAIN to the rescue
SQLT or SQLTXPLAIN, has been available on MetaLink (now MOS) under note 215187.1 for over a decade. In short, SQLT collects all the diagnostics listed above and a lot more. That is WHY Oracle Support uses it every day. It simply saves a lot of time! So, I always encourage fellow Oracle users to make use of the FREE tool and expedite their own SQL Tuning analysis. When time permits, I do volunteer to help on an analysis. So, if you get to read this, and you want to help yourself while using SQLT but feel intimidated by this little monster, please give it a try and contact me for assistance. If I can help, I will, if I cannot, I will let you know.
It is fun to guess WHY a SQL is not performing as expected, and trying different guesses is educational but very time consuming. If you want to actually find root causes before trying to fix your SQL, you may want to collect relevant diagnostics. SQLT is there to help, and if installing this tool is not something you can do in a short term, consider then SQL Health-Check SQLHC.
Many things, but most important is that it got bigger and better. This EDB360 free tool provided by Enkitec is maturing over time. Its core function has not changed although, which is to present a 360-degree view of a database (10g or higher).
EDB360 is a nice complement to other tools like Exacheck, Raccheck or Oracheck. It has some additional benefits, like taking a snapshot of a system to then be analyzed offline or simply to preserve this snapshot as a baseline.
Keep in mind that EDB360 does not install anything on the database, nor it changes any data on it. In some cases, where direct access to the database server is not an option, having the capability of executing EDB360 through a SQL*Plus client connection is a big plus.
I use EDB360 as a starting place to perform a whole database health-check.
Since pictures tell more than words, please find below 4. The first two are about the new entries on EDB360 main menu (menu is a tad bigger than what you see in these two pictures, and its content is dynamic). The last two pictures are just a sample of the charts that are now part of EDB360.
EDB360 execution parameters changed from 4 to 6:
- Oracle Pack License: If your site has the Tuning Pack, then enter ‘T’, else if your site has the Diagnostics Pack enter ‘D’, else enter ‘N’.
- Days of History to consider. If you entered ‘T’ or ‘D’ on first parameter, then specify on 2nd parameter up to how many days of history you want EDB360 to use. By default it uses 31, assuming your AWR history is at least that big.
- Do you want HTML Reports? By default it is ‘Y’.
- Do you want Text Reports? Defaults to ‘Y’.
- Do you want CSV Files? Defaults to ‘Y’.
- Do you want Charts? Defaults to ‘Y’.
Once you login into SQL*Plus while on top of the edb360 directory, simply execute script edb360.sql and pass all 6 parameters one by one or all of them inline. For example: @edb360 T 31 Y Y Y Y
If you have downloaded EDB360 before, then I encourage you to download and test the new version. If you have never used it, I hope you find this tool useful.
Both eAdam and eDB360 tools are available at the Enkitec web site under the Products tab. You can download a free copy from there; or maybe you were given by an Enkitec consultant a newer version for you to execute. In any case, the question is: “how do I execute these tools?“
- Take the eadam.zip file and unzip it on your server
- Navigate to the top eadam directory which contains a readme.txt and a sql subdirectory
- Connect into SQL*Plus as SYS, a DBA account, or a user with access to the Data Dictionary views
- Execute START sql/eadam_01_xtr.sql using default values for input parameters. Be aware this tool reads from DBA_HIST views, so you can only use it if you have a license for the Oracle Diagnostics or Tuning Packs
- Take the edb360.zip file and unzip it on your server
- Navigate to top edb360 directory which contains a readme.txt, an edb360.sql script, a run_edb360.sh script and a sql subdirectory
- Connect into SQL*Plus as SYS, a DBA account, or a user with access to the Data Dictionary views
- Execute START edb360.sql passing values to the input parameters. The first one asks about the Oracle License Pack you have, so respond to this with a “T” if you have the Tuning Pack license, with a “D” if you have the Diagnostics Pack or with an “N” if you don’t have any of these two. For the other parameters about the output format just hit enter or pass a “Y”
About the Output
Both of these tools generate an output proportional to the size of the selected objects out of the Data Dictionary and Dynamic Views. So it is common for the output to be large, meaning between 100MB and 1 or 2 GB. I strongly recommend to execute these tools from an staging directory with plenty of space. Plan for 10GB, even if the tool end consuming a lot less.
Another consideration is the time these tool take to execute. They extract everything sequentially and on serial execution, so they do not impose a significant load to your system. But since the amount of metadata they extract is large, they may take more than one hour to execute. I have seen cases where they take 2 or 3 hours, so plan accordingly.
The output of each of these two tools is a compressed file. eAdam produces just a set of CSV files, which will be loaded by the requestor into a stand-alone staging Oracle database for data mining and reporting. eDB360 produces HTML, Text and CSV files. The HTML set is the one that is used the most, while the CSV set is used on Excel or similar tool to produce some Charts.
Executing eAdam or eDB360 is easy, but you need access to Data Dictionary views and a large staging area on disk. So before executing these tools, plan on provision space and time.
As of today, you can download this free tool out of our Enkitec‘s web page. Just select “eAdam” under the Products Tab.
The next version of eAdam will incorporate the GV$views, equivalent to the currently extracted DBA_HIST set. The reason of this enhancement is to handle read-only databases (DataGuard – DG). So views like GV$ACTIVE_SESSION_HISTORY will be available for data mining within the eAdam staging repository.
Any ways, I hope you enjoy this new tool. Feel free to provide constructive feedback on this blog, or by sending an email to email@example.com.