Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

Skipping ACS ramp-up using a SQL Patch

leave a comment »

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;

 

 

Written by Carlos Sierra

June 19, 2014 at 5:14 pm

How to upgrade Dynamic Sampling on a Query already using an imported SQL Profile

with 3 comments

Problem

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“?

Solution

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('11.2.0.3')]',
q'[DB_VERSION('11.2.0.3')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 6)]',
q'[ALL_ROWS]',

 

 

Written by Carlos Sierra

May 21, 2014 at 7:24 am

Great Lakes Oracle Conference GLOC May 12-14

leave a comment »

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.

Tom Kyte at GLOC!

Written by Carlos Sierra

May 13, 2014 at 9:33 am

Why using SQLTXPLAIN

leave a comment »

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:

  1. SQL Text
  2. Version of the database (before and after upgrade)
  3. Database parameters (before and after)
  4. State of the CBO Statistics (before and after)
  5. Changes on Histograms
  6. Basics about the architecture (CPUs, memory, etc.)
  7. Values of binds if SQL has them
  8. Indexes compare, including state (visible?, usable?)
  9. Execution Plan (before and after)
  10. Plan stability? (Stored Outlines, Profiles, SQL Plan Management)
  11. Performance history as per evidence on AWR or StatsPack
  12. Trace from Event 10053 to understand the CBO
  13. Trace from Event 10046 level 8 or 12 to review Waits
  14. 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.

Conclusion

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.

 

What is new with EDB360?

with 6 comments

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:

  1. 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’.
  2. 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.
  3. Do you want HTML Reports? By default it is ‘Y’.
  4. Do you want Text Reports? Defaults to ‘Y’.
  5. Do you want CSV Files? Defaults to ‘Y’.
  6. 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

EDB360 menu (part 1)

EDB360 Dynamic Menu (part 1)

EDB360 menu (part 2)

EDB360 Dynamic Menu (part 2)

CPU Load per Instance (sample chart)

CPU Load per Instance (sample chart)

ASH Top Modules and Actions for Cluster (sample chart)

ASH Top Modules and Actions for Cluster (sample chart)

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.

Written by Carlos Sierra

April 2, 2014 at 10:04 am

How to execute eAdam and eDB360

leave a comment »

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?

Executing eAdam

  1. Take the eadam.zip file and unzip it on your server
  2. Navigate to the top eadam directory which contains a readme.txt and a sql subdirectory
  3. Connect into SQL*Plus as SYS, a DBA account, or a user with access to the Data Dictionary views
  4. 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

    eadam execution sample

    eadam execution sample

Executing eDB360

  1. Take the edb360.zip file and unzip it on your server
  2. Navigate to top edb360 directory which contains a readme.txt, an edb360.sql script, a run_edb360.sh script and a sql subdirectory
  3. Connect into SQL*Plus as SYS, a DBA account, or a user with access to the Data Dictionary views
  4. 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”

    edb360 execution sample

    edb360 execution sample

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.

Conclusion

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.

Written by Carlos Sierra

March 12, 2014 at 10:52 am

Enkitec’s free AWR data mining tool (eAdam) is now available!

with 2 comments

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 carlos.sierra@enkitec.com.

Written by Carlos Sierra

March 6, 2014 at 3:40 pm

Posted in eAdam

Follow

Get every new post delivered to your Inbox.

Join 1,429 other followers