Archive for the ‘Conferences’ Category
Adapting and adopting SQL Plan Management (SPM)
Introduction
This post is about: “Adapting and adopting SQL Plan Management (SPM) to achieve execution plan stability for sub-second queries on a high-rate OLTP mission-critical application”. In our case, such an application is implemented on top of several Oracle 12c multi tenant databases, where a consistent average execution time is more valuable than flexible execution plans. We successfully achieved plan stability implementing a simple algorithm using PL/SQL calling DBMS_SPM public APIs.
Chart below depicts a typical case where the average performance of a large set of business-critical SQL statements suddenly degraded from sub-millisecond to 15 or 20ms, then beccome more stable around 3ms. Wide spikes are a typical trademark of an Execution Plan for one or more SQL statements flipping for some time. In order to produce a more consistent latency we needed to improve plan stability, and of course the preferred tool to achieve that on an Oracle database is SQL Plan Management.
Algorithm
We tested and ruled out adaptive SQL Plan Management, which is an excellent 12c new feature. But, due to the dynamics of this application, where transactional data shifts so fast, allowing this “adaptive SPM” feature to evaluate auto-captured plans using bind variable values captured a few hours earlier, rendered unfortunately false positives. These false positives “evolved” as execution plans that were numerically optimal for values captured (at the time the candidate plan was captured), but performed poorly when executed on “current” values a few hours later. Nevertheless, this 12c “adaptive SPM” new feature is worth exploring for other applications.
We adapted SPM so it would only generate SQL Plan Baselines on SQL that executes often, and that is critical for the business. The algorithm has some complexity such as candidate evaluation and SQL categorization; and besides SPB creation it also includes plan demotion and plan promotion. We have successfully implemented it in some PDBs and we are currently doing a rollout to entire CDBs. The algorithm is depicted on diagram on the left, and more details are included in corresponding presentation slides listed on the right-hand bar. I plan to talk about this topic on an international Oracle Users Group in 2018.
This algorithm is scripted into a sample PL/SQL package, which you can find on a subdirectory on my shared scripts. If you consider using this sample script for an application of your own, be sure you make it yours before attempting to use it. In other words: fully understand it first, then proceed to customize it accordingly and test it thoroughly.
Results
Chart below shows how average performance of business-critical SQL became more stable after implementing algorithm to adapt and adopt SPM on a pilot PDB. Not all went fine although: we had some outliers that required some tuning to the algorithm. Among challenges we faced: volatile data (creating a SPB when table was almost empty, then using it when table was larger); skewed values (create a SPB for non-popular value, then using it on a popular value); proper use of multiple optimal plans due to Adaptive Cursor Sharing (ACS); rejected candidates due to conservative initial restrictions on algorithm (performance per execution, number of executions, age of cursor, etc.)
Conclusion
If your OLTP application contains business critical SQL that executes at a high-rate, and where a spike on latency risks affecting SLAs, you may want to consider implementing SQL Plan Management. Consider then both: “adaptive SPM” if it satisfies your requirements, else build a PL/SQL library that can implement more complex logic for candidates evaluation and for SPBs maintenance. I do believe SPM works great, specially when you enhance its out-of-the-box functionality to satisfy your specific needs.
SQLT and SQLd360 interview and one-day class on Practical SQL Tuning announcement
With permission of the Northern California Oracle Users Group (NoCOUG) I am reproducing a warm interview on SQLTXPLAIN and SQLd360. During this interview Mauro Pagano and myself talk about the history behind these two free tools and how the former has evolved into the latter. You can find the full transcript of the interview here: YesSQL(T). If you want to read the entire free online NoCOUG Journal, you will discover other cool articles.
Anyways, I am glad Iggy Fernandez invited us to participate first on this interview, and second to collaborate on the meeting planned for January. On that meeting Mauro and I will conduct a one full day workshop on “Practical SQL Tuning” (January 28) in Northern California. We hope to see many of you guys there, and please bring questions and case studies.
Video: Introducing the eDB360 Tool
Some of you have asked if the “Introducing the eDB360 Tool” session at the Oaktable World 2014 was recorder. Actually it was, and thanks to Kyle Hailey it is now available as well as the slides. Just go to the agenda of this event and click on corresponding link. There you will also find video and/or slides for all other sessions.
Thanks Kyle for making this possible!
East Cost Oracle Users Group Conference 2014
The East Cost Oracle Users Group Conference 2014 is next week. To me, ECO is quite special. I am speaking there for my 3rd time!
The ECO group is kind of new (about 4 years old), and it is the clustering of several regional user groups including the Virginia Oracle Users Group, the Eastern States Oracle Applications Users Group, the Hampton Roads Oracle Users Group, and the Southeastern Oracle Users Group.
What I like about ECO is its size: small enough to remain cozy, and large enough to be a good opportunity to have one-on-one conversations with colleagues and friends.
This time at ECO 14, I will be delivering a session on “How a Developer Can Troubleshoot a SQL Performing Poorly on a Production DB” on Tuesday, November 4 at 3:15 PM (see agenda here). I will also co-deliver a 4-hours pre-conference workshop on “Oracle Performance Tuning” on Monday, November 3 at 1:00 PM. I will do this with Mauro Pagano, who is now a regular speaker and he is becoming a blogger. You can read his brand new blog at mauro-pagano.com.
Looking forward to meet old friends at ECO 14, and to make new ones!
About Oracle ACE and Kscope14 at ODTUG
Last night we had our get together for Oracle ACE members at Kscope14. Of course food and drinks were great, but a lot more important was the crowd. I am surprised to see how fast this ACE program is growing, and I believe the impact to our Oracle community is gaining momentum with it.
I will miss Kscope14 and I will submit some topics to Kscope15 soon. I already have a few new topics in mind, tailored to this “developers” Oracle Users Group at ODTUG.
Kscope14 has been great. I have attended a few sessions, and I enjoyed both the content and delivery. I am amazed to see how many topics are completely alien to me. And a new area of personal interest awakened on me yesterday: APEX! I would say: Once a Developer, always a Developer!
What I like most about Oracle Users Groups and Oracle ACE activities in general, is the opportunity to share with fellow techies, and learn from each other. Sessions are important, networking is important, but it is those short but intensive discussions on an aisle or booth that make an impact on me. It is refreshing to acknowledge there are many other passionate techies that would rather have an informal design discussion than watch the world cup!
I met Vikki from the Oracle ACE program and I finally put a face to a name. I actually met in person many other peers that I knew only by name or on the media. This Oracle ACE program is helping to build interrelations that translate into a better integrated Oracle community.
My kudos to the organizers of both Kscope14 and Oracle ACE. I take back home a great experience and a strong desired to continue contributing to our Oracle community.
Creating a SQL Plan Baseline out of a modified SQL
While delivering a session at KSCOPE 14, I was asked a very good question: Can I create a SQL Plan Baseline for an Original SQL out of a Modified SQL? In other words, query Q1, which performs poorly, has SQL_ID1 and SQL_HANDLE1 with a poorly performing Execution Plan with Hash Value PHV1. With a small modification to this query, like adding a CBO Hint or removing one, we obtain query Q2, which performs well, and has SQL_ID2, SQL_HANDLE2 and PHV2. So what we want it to associate PHV2 to SQL_ID1. The questions is: how do we do that with an API?
Maria Colgan explains on this blog how to manually do exactly what is requested above. So the question becomes: can we do this manual procedure with an easy to use API?
Script coe_load_sql_baseline.sql below inputs SQL_ID1, SQL_ID2 and PHV2, which correspond to the Original SQL_ID, the Modified SQL_ID and the Plan Hash Value from the Modified SQL (it could have more than one plan); then it produces a SQL Plan Baseline for the Original SQL out of the Execution Plan of the Modified SQL. By doing this we can “fix” a Query performing poorly by attaching to it a good performing Execution Plan that was produced by making a small temporary change to the Original SQL, like adding or removing a small set of CBO Hints. So we can generate good Execution Plans without actually changing the application.
Script coe_load_sql_baseline.sql is provided inside SQLT (MOS 215187.1) under sqlt/utl directory. Use always the latest version. As of June 2014 the latest version is the one below. This script does not require to install SQLT.
SPO coe_load_sql_baseline.log; 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 NUM 20 SQLP SQL>; SET SERVEROUT ON SIZE UNL; REM REM $Header: 215187.1 coe_load_sql_baseline.sql 11.4.5.8 2013/05/10 carlos.sierra $ REM REM Copyright (c) 2000-2013, Oracle Corporation. All rights reserved. REM REM AUTHOR REM carlos.sierra@oracle.com REM REM SCRIPT REM coe_load_sql_baseline.sql REM REM DESCRIPTION REM This script loads a plan from a modified SQL into the SQL REM Plan Baseline of the original SQL. REM If a good performing plan only reproduces with CBO Hints REM then you can load the plan of the modified version of the REM SQL into the SQL Plan Baseline of the orignal SQL. REM In other words, the original SQL can use the plan that was REM generated out of the SQL with hints. REM REM PRE-REQUISITES REM 1. Have in cache or AWR the text for the original SQL. REM 2. Have in cache the plan for the modified SQL REM (usually with hints). REM REM PARAMETERS REM 1. ORIGINAL_SQL_ID (required) REM 2. MODIFIED_SQL_ID (required) REM 3. PLAN_HASH_VALUE (required) REM REM EXECUTION REM 1. Connect into SQL*Plus as user with access to data dictionary REM and privileges to create SQL Plan Baselines. Do not use SYS. REM 2. Execute script coe_load_sql_baseline.sql passing first two REM parameters inline or until requested by script. REM 3. Provide plan hash value of the modified SQL when asked. REM REM EXAMPLE REM # sqlplus system REM SQL> START coe_load_sql_baseline.sql gnjy0mn4y9pbm b8f3mbkd8bkgh REM SQL> START coe_load_sql_baseline.sql; REM REM NOTES REM 1. This script works on 11g or higher. REM 2. For a similar script for 10g use coe_load_sql_profile.sql, REM which uses custom SQL Profiles instead of SQL Baselines. REM 3. For possible errors see coe_load_sql_baseline.log REM 4. Use a DBA user but not SYS. Do not connect as SYS as the staging REM table cannot be created in SYS schema and you will receive an error: REM ORA-19381: cannot create staging table in SYS schema REM SET TERM ON ECHO OFF; PRO PRO Parameter 1: PRO ORIGINAL_SQL_ID (required) PRO DEF original_sql_id = '&1'; PRO PRO Parameter 2: PRO MODIFIED_SQL_ID (required) PRO DEF modified_sql_id = '&2'; PRO WITH p AS ( SELECT DISTINCT plan_hash_value FROM gv$sql_plan WHERE sql_id = TRIM('&&modified_sql_id.') AND other_xml IS NOT NULL ), m AS ( SELECT plan_hash_value, SUM(elapsed_time)/SUM(executions) avg_et_secs FROM gv$sql WHERE sql_id = TRIM('&&modified_sql_id.') AND executions > 0 GROUP BY plan_hash_value ) SELECT p.plan_hash_value, ROUND(m.avg_et_secs/1e6, 3) avg_et_secs FROM p, m WHERE p.plan_hash_value = m.plan_hash_value ORDER BY avg_et_secs NULLS LAST; PRO PRO Parameter 3: PRO PLAN_HASH_VALUE (required) PRO DEF plan_hash_value = '&3'; PRO PRO Values passed to coe_load_sql_baseline: PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PRO ORIGINAL_SQL_ID: "&&original_sql_id." PRO MODIFIED_SQL_ID: "&&modified_sql_id." PRO PLAN_HASH_VALUE: "&&plan_hash_value." PRO WHENEVER SQLERROR EXIT SQL.SQLCODE; SET TERM OFF ECHO ON; -- trim parameters COL original_sql_id NEW_V original_sql_id FOR A30; COL modified_sql_id NEW_V modified_sql_id FOR A30; COL plan_hash_value NEW_V plan_hash_value FOR A30; SELECT TRIM('&&original_sql_id.') original_sql_id, TRIM('&&modified_sql_id.') modified_sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL; -- open log file SPO coe_load_sql_baseline_&&original_sql_id..log; GET coe_load_sql_baseline.log; . -- get user COL connected_user NEW_V connected_user FOR A30; SELECT USER connected_user FROM DUAL; VAR sql_text CLOB; VAR plan_name VARCHAR2(30); EXEC :sql_text := NULL; EXEC :plan_name := NULL; -- get sql_text from memory BEGIN SELECT REPLACE(sql_fulltext, CHR(00), ' ') INTO :sql_text FROM gv$sqlarea WHERE sql_id = TRIM('&&original_sql_id.') AND ROWNUM = 1; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting original sql_text from memory: '||SQLERRM); :sql_text := NULL; END; / -- get sql_text from awr BEGIN IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN SELECT REPLACE(sql_text, CHR(00), ' ') INTO :sql_text FROM dba_hist_sqltext WHERE sql_id = TRIM('&&original_sql_id.') AND sql_text IS NOT NULL AND ROWNUM = 1; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('getting original sql_text from awr: '||SQLERRM); :sql_text := NULL; END; / -- sql_text as found SELECT :sql_text FROM DUAL; -- check is sql_text for original sql is available SET TERM ON; BEGIN IF :sql_text IS NULL THEN RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id. was not found in memory (gv$sqlarea) or AWR (dba_hist_sqltext).'); END IF; END; / -- check phv is found DECLARE l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM gv$sql WHERE sql_id = TRIM('&&modified_sql_id.') AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.')); IF l_count = 0 THEN RAISE_APPLICATION_ERROR(-20110, 'PHV &&plan_hash_value. for modified SQL_ID &&modified_sql_id. was not be found in memory (gv$sql).'); END IF; END; / SET ECHO OFF; DECLARE plans NUMBER; description VARCHAR2(500); sys_sql_handle VARCHAR2(30); sys_plan_name VARCHAR2(30); BEGIN -- create sql_plan_baseline for original sql using plan from modified sql plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id => TRIM('&&modified_sql_id.'), plan_hash_value => TO_NUMBER(TRIM('&&plan_hash_value.')), sql_text => :sql_text ); DBMS_OUTPUT.PUT_LINE('Plans Loaded: '||plans); -- find handle and plan_name for sql_plan_baseline just created SELECT sql_handle, plan_name INTO sys_sql_handle, sys_plan_name FROM dba_sql_plan_baselines WHERE creator = USER AND origin = 'MANUAL-LOAD' AND created = ( -- past 1 minute only SELECT MAX(created) max_created FROM dba_sql_plan_baselines WHERE creator = USER AND origin = 'MANUAL-LOAD' AND created > SYSDATE - (1/24/60)); DBMS_OUTPUT.PUT_LINE('sys_sql_handle: "'||sys_sql_handle||'"'); DBMS_OUTPUT.PUT_LINE('sys_plan_name: "'||sys_plan_name||'"'); -- update description of new sql_plan_baseline description := UPPER('original:'||TRIM('&&original_sql_id.')||' modified:'||TRIM('&&modified_sql_id.')||' phv:'||TRIM('&&plan_hash_value.')||' created by coe_load_sql_baseline.sql'); plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle => sys_sql_handle, plan_name => sys_plan_name, attribute_name => 'description', attribute_value => description ); DBMS_OUTPUT.PUT_LINE(plans||' plan(s) modified description: "'||description||'"'); -- update plan_name of new sql_plan_baseline :plan_name := UPPER(TRIM('&&original_sql_id.')||'_'||TRIM('&&modified_sql_id.')); :plan_name := sys_plan_name; -- avoids ORA-38141: SQL plan baseline SQL_PLAN_64b0jqr2t1h3558b5ab4d does not exist IF :plan_name <> sys_plan_name THEN plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle => sys_sql_handle, plan_name => sys_plan_name, attribute_name => 'plan_name', attribute_value => :plan_name ); DBMS_OUTPUT.PUT_LINE(plans||' plan(s) modified plan_name: "'||:plan_name||'"'); END IF; -- drop baseline staging table for original sql (if one exists) BEGIN DBMS_OUTPUT.PUT_LINE('dropping staging table "STGTAB_BASELINE_'||UPPER(TRIM('&&original_sql_id.'))||'"'); EXECUTE IMMEDIATE 'DROP TABLE STGTAB_BASELINE_'||UPPER(TRIM('&&original_sql_id.')); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('staging table "STGTAB_BASELINE_'||UPPER(TRIM('&&original_sql_id.'))||'" did not exist'); END; -- create baseline staging table for original sql DBMS_OUTPUT.PUT_LINE('creating staging table "STGTAB_BASELINE_'||UPPER(TRIM('&&original_sql_id.'))||'"'); DBMS_SPM.CREATE_STGTAB_BASELINE ( table_name => 'STGTAB_BASELINE_'||UPPER(TRIM('&&original_sql_id.')), table_owner => '&&connected_user.' ); -- packs new baseline for original sql DBMS_OUTPUT.PUT_LINE('packaging new sql baseline into staging table "STGTAB_BASELINE_'||UPPER(TRIM('&&original_sql_id.'))||'"'); plans := DBMS_SPM.PACK_STGTAB_BASELINE ( table_name => 'STGTAB_BASELINE_'||UPPER(TRIM('&&original_sql_id.')), table_owner => '&&connected_user.', sql_handle => sys_sql_handle, plan_name => :plan_name ); DBMS_OUTPUT.PUT_LINE(plans||' pla(s) packaged'); END; / -- display details of new sql_plan_baseline SET ECHO ON; REM REM SQL Plan Baseline REM ~~~~~~~~~~~~~~~~~ REM SELECT signature, sql_handle, plan_name, enabled, accepted, fixed--, reproduced (avail on 11.2.0.2) FROM dba_sql_plan_baselines WHERE plan_name = :plan_name; SELECT description FROM dba_sql_plan_baselines WHERE plan_name = :plan_name; SET ECHO OFF; PRO PRO **************************************************************************** PRO * Enter &&connected_user. password to export staging table STGTAB_BASELINE_&&original_sql_id. PRO **************************************************************************** HOS exp &&connected_user. tables=&&connected_user..STGTAB_BASELINE_&&original_sql_id. file=STGTAB_BASELINE_&&original_sql_id..dmp statistics=NONE indexes=N constraints=N grants=N triggers=N PRO PRO If you need to implement this SQL Plan Baseline on a similar system, PRO import and unpack using these commands: PRO PRO imp &&connected_user. file=STGTAB_BASELINE_&&original_sql_id..dmp tables=STGTAB_BASELINE_&&original_sql_id. ignore=Y PRO PRO SET SERVEROUT ON;; PRO DECLARE PRO plans NUMBER;; PRO BEGIN PRO plans := DBMS_SPM.UNPACK_STGTAB_BASELINE('STGTAB_BASELINE_&&original_sql_id.', '&&connected_user.');; PRO DBMS_OUTPUT.PUT_LINE(plans||' plan(s) unpackaged');; PRO END;; PRO / PRO SPO OFF; HOS zip -m coe_load_sql_baseline_&&original_sql_id. coe_load_sql_baseline_&&original_sql_id..log STGTAB_BASELINE_&&original_sql_id..dmp coe_load_sql_baseline.log HOS zip -d coe_load_sql_baseline_&&original_sql_id. coe_load_sql_baseline.log 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 NUM 10 SQLP SQL>; SET SERVEROUT OFF; UNDEFINE 1 2 3 original_sql_id modified_sql_id plan_hash_value CL COL PRO PRO coe_load_sql_baseline completed.
Skipping ACS ramp-up using a SQL Patch
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 11.2.0.3 and 12.0.1.0 -- --------------------------------------------------------------------------------------- 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;
Great Lakes Oracle Conference GLOC May 12-14
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.
About AWR, SQLT and DB360
This blog posting is about answering this first question below, which I often get asked:
Can I use SQLTXPLAIN (SQLT) instead of AWR?
The answer is: it depends. If you are doing SQL Tuning and you already know the SQL_ID, then you may want to use SQLT XTRACT (MOS 215187.1) directly on the SQL_ID of concern. But even in that case, keep in mind that SQLT accesses AWR data, so your environment must have a valid license to use the Oracle Diagnostics Pack. In fact, when you install SQLT, it asks if you have the Oracle Tuning Pack, the Oracle Diagnostics Pack or none. Depending how you respond, SQLT access or not the set of views and APIs included on those Oracle Packs. That means you can configure SQLT to access or not AWR data.
What is the difference between AWR and SQLT?
In short, the difference between these two is the scope. You use AWR to diagnose database performance, while you use SQLT to diagnose one SQL. Sometimes I explain this difference by saying: “AWR is to an entire database to what SQLT is to one SQL”. One is for system-wide performance, the other is very centric to one SQL at a time.
Why SQLT exists?
I envisioned SQLT on the late 90’s when I was a road-warrior fighting fires in the area of performance, and in particular SQL performance. I found back then that Oracle-provided tools like TKPROF were excellent, but I always needed something more, like knowing the state of the CBO Statistics, Tables and Indexes, etc.
These days, my good friend Mauro Pagano from Oracle, is keeping the ball rolling. He is keeping SQLT in constant motion, making it a better tool on every new version. So, I would say this: SQLT is filling up some gaps that me, and many others, consider important in order to be diligent on root cause analysis for cases were a SQL performs poorly.
What is DB360?
As SQLT brings to the table several pieces of information that we need for SQL Tuning, and which are not available using out-of-the-box tools like TKPROF or SQL Monitoring, the new DB360 tool is doing something similar for the entire database: It complements what AWR provides by producing a report with meaningful information about an entire database. This DB360 is a tool that installs nothing on the database, and produces an HTML report with sections such as Configuration, Sizing, CBO Statistics, Performance Trends, etc.
Is DB360 a licensed product?
No. This DB360 tool belongs to Enkitec. It is not yet available to the Oracle community, but it will be soon. Same as SQLT, if you have an Oracle Tuning or Diagnostics Pack, then when you execute DB360 you would get to see in your DB360 report some pieces of information generated out of views and APIs covered by those Oracle Packs, else you get only the pieces which require no Oracle Pack license. Besides the restriction to limit your use of DB360 as per your Oracle Pack license, DB360 itself will be available to the Oracle community for free, and with no strings attached, same as SQLT.
Why are SQLT and DB360 free to download and use?
These tools are simply a contribution to the Oracle community. “Sharing tools is like sharing knowledge: it makes our IT experience more pleasurable”. The only payback comes when you share back with the Oracle community some of your knowledge, or some of your tools and scripts. If you have been a speaker in an Oracle Users Groups, then you may relate to this gratifying experience to share with others what you know. At RMOUG these past 3 days, I have had the opportunity to experience once again this special sense of community, that is always eager to share and to learn from each other.
Conclusion
SQLT complements TKPROF and SQL Monitor. DB360 complements AWR. When it comes to diagnostics, either for one SQL or for an entire Database, having to our disposal additional diagnostics in the context of our analysis, improves our chances to do a diligent job, while reducing the time that it would take to assembly those pieces manually; all with the extra benefit of having these extra diagnostics pieces consistent to a point in time. So, I hope you continue enjoying SQLT and in the near future DB360!
Exadata Optimizations and SQLTXPLAIN Courses
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.
Conclusion
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!