Baselines and SQL_ID

I ran across a great post by Marcin Przepiorowski (How to find SQL_ID and PLAN_HASH_VALUE in Oracle SQL Plan Management Baselines) thanks to Karl Arao‘s tweet. Marcin posted a bit of code for calculating a SQL_ID using the text of a SQL statement (based on some research by Tanel Poder). I was aware of Tanel’s research but had not taken the time to try to figure out how to code it. The context of Marcin’s post was based on wanting to relate a Baseline to a SQL_ID. This is something I’ve wanted to do in the past so I was quite interested in the post. As a bonus Marcin also demonstrated a technique to pull the PLAN_HASH_VALUE associated with a Baseline. Of course I can never leave well enough alone and so I had to re-arrange Marcin’s code a little bit to suite my own purposes. So I created a function that returns either the PLAN_HASH_VALUE or the SQL_ID of a Baseline. Here’s the code to create the function: create_baseline_info.sql and a script that uses it: baselines2.sql

Here’s an example of how to use them:


SYS@SANDBOX1> select count(*) from kso.skew;

  COUNT(*)
----------
  32000004

SYS@SANDBOX1> @fsx
Enter value for sql_text: select count(*) from kso.skew
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
gm0w4as5hhr4m      0  578366071      1       1.30      8 Yes          71.78 select count(*) from kso.skew

SYS@SANDBOX1> @dplan
Enter value for sql_id: gm0w4as5hhr4m
Enter value for child_no: 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gm0w4as5hhr4m, child number 0
-------------------------------------
select count(*) from kso.skew

Plan hash value: 578366071

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |  6183 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |          |    32M|  6183   (1)| 00:01:15 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SKEW     |    32M|  6183   (1)| 00:01:15 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - storage(:Z>=:Z AND :Z<=:Z)


23 rows selected.

SYS@SANDBOX1> @create_baseline
Enter value for sql_id: gm0w4as5hhr4m
Enter value for plan_hash_value: 578366071
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (SQLID_sqlid_planhashvalue): 
sql_id: gm0w4as5hhr4m
plan_hash_value: 578366071
fixed: NO
enabled: YES
plan_name: SQLID_gm0w4as5hhr4m_578366071
sql_handle: SQL_e86d1a732b7a5c25
Baseline SQLID_gm0w4as5hhr4m_578366071 created.

PL/SQL procedure successfully completed.

SYS@SANDBOX1> select sql_handle, plan_name from dba_sql_plan_baselines where plan_name like upper('SQLID_gm0w4as5hhr4m_578366071');

SQL_HANDLE           PLAN_NAME
-------------------- ------------------------------
SQL_e86d1a732b7a5c25 SQLID_GM0W4AS5HHR4M_578366071

SYS@SANDBOX1> @baselines2
Enter value for sql_text: select count(*) from kso.skew%
Enter value for name: 
Enter value for plan_name: 

SQL_ID        SQL_TEXT                                           PLAN_HASH_VALUE SQL_HANDLE           PLAN_NAME                      ENABLED ACC FIX LAST_EXECUTED
------------- -------------------------------------------------- --------------- -------------------- ------------------------------ ------- --- --- ----------------
1jp7sjmt0wp1j select count(*) from kso.skew s , skew_gtt g where 2414665383      SQL_936b37ad684d18d4 SQL_PLAN_96utrppn4u66ncefcc71f YES     NO  NO
                                                                 2178955164      SQL_936b37ad684d18d4 SQL_PLAN_96utrppn4u66ne441e87d YES     NO  NO
gm0w4as5hhr4m select count(*) from kso.skew                      578366071       SQL_e86d1a732b7a5c25 SQLID_GM0W4AS5HHR4M_578366071  YES     YES NO

SQL> -- cool - it's a match

So in the previous listing I ran a select statement, found the SQL_ID and PLAN_HASH_VALUE for that statement (using fsx.sql), displayed the plan (using dplan.sql) and created a Baseline for the statement (using create_baseline.sql). I then verified that the Baseline existed in DBA_SQL_PLAN_BASELINES and ran the baselines2.sql script which returns the SQL_ID and PLAN_HASH_VALUE using the baseline_info() function, which as you can see matched the original query.

15 Comments

  1. Bryan Grenn says:

    Thanks Kerry, that looks great ! Do you have a similar procedure to create baselines from AWR history ? Most of the time, I am creating a profile because I want to go back to the “last known good” plan. I use COE_PROFILE.SQL (part of the SQLT stuff) all the time to accomplish this. I want to do the same with Baselines ?

  2. Of course, signature is used for a reason and due to force matching, the plan in the baseline might work against more than one sql id.

    • Actually, I didn’t mean the force matching, more the space and case normalisation – i.e. statements which have different sql ids and differ only in space and case will have the same signature. Multiple sql_ids, one signature, one baseline entry.

      • osborne says:

        Quite right. Baselines can be used by slightly different SQL statements because the matching is slightly relaxed from the algorithm used for matching existing cursors (i.e. SQL_ID). This approach will show us the SQL_ID for the statement text used to actually create the baseline though – although it is possible that another statement who’s text is very slightly different could also use the baseline. I think this would be a fairly unusual situation though (except possibly in systems where users were manually typing in SQL statements). You wouldn’t expect to run into a system that had the same statement coded in different places with varying formatting (spaces, etc…) The baseline being used by a statement is easily seen by querying the v$sql family of objects as there is a SQL_PLAN_BASELINE column that shows what baseline was used by a statement. So that makes it pretty easy to see what baseline has been used by a specific statement. Kind of the reverse of the approach shown in this post, where we’re going from a baseline to SQL_ID as opposed to the other way around.

  3. […] have been some interesting posts from Martin Przepiorowski and Kerry Osborne about mapping sql plan management (spm) baselines to sql […]

  4. osborne says:

    Hi Bryan,

    Yes, try this one: create_baseline_awr.sql It prompts for a SQL_ID and a PLAN_HASH_VALUE.

    Kerry

  5. Hi Kerry,

    Your link to “create_baseline_info.sql” in the 1st paragraph seems broken, I’m getting Error 404 – Not Found

    Thanks a lot for sharing.

    Laurent

  6. Kapil Goyal says:

    Hi Kerry,

    As usual great stuff!!! I was wondering what if we have a sqlid to signature mapping then we can have plan_hash_value from dba_hist_sqlstat and rest from dba_sql_plan_baselines. Isn’t it?

    SET SERVEROUTPUT ON
    DECLARE
    ST CLOB;
    SIG NUMBER;
    BEGIN
    SELECT SQL_TEXT
    INTO ST
    FROM DBA_HIST_SQLTEXT
    WHERE SQL_ID = ‘&sql_id’;
    SIG := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (ST, FALSE);
    DBMS_OUTPUT.PUT_LINE (‘SIGNATURE=’ || SIG);
    END;
    /

    Thanks
    Kapil Goyal

  7. rom27a says:

    Why would one want to produce the Baseline out of AWR? Finding a good plan via DBA_HIST_SQLSTAT and using dbms_spm.load_plans_from_cursor_cache with sql_id and plan_hash_value seems simpler to me.

    Can someone please tell me the difference and advantages of using AWR for that?

  8. osborne says:

    rom27,

    Because sometimes a good plan has existed in the past but has since been flushed memory.

    Kerry

  9. Ram says:

    Hi osborne,

    I am not able to access the scripts location. could you please check and help me to get the access of the scripts location.

    //Ram

  10. osborne says:

    All the links seem to work for me. Can you be more specific?

    Kerry

  11. Johnnyq72 says:

    Great post and great script! I was looking for this and you saved me tons of time.
    The second hyper link (to baselines2.sql) is linked to the same script as the first one (create_baseline_info.sql), although the script is there if you open in manually.
    Regards,
    Jan

Leave a Reply