Logo Background

Create Oracle SQL Profile For Tuning

  • By on October 8, 2012 | 4 Comments

    Looking for how to tune a SQL statement by creating a SQL Profile? The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans.

    Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement.

    During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.

    The output of this type of analysis is a recommendation to accept the SQL Profile. A SQL Profile, once accepted, is stored persistently in the data dictionary. Note that the SQL Profile is specific to a particular query.

    If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan. The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.

    It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile.

    The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.

    Here’s the set of SQL statement you can use to trace the execution time of ACTIVE running SQL query that you wish to tune.

    -- Get The SQL_ID From Active Session SQL
    SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
    b.type, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
    FROM v$process a, v$session b, v$sqltext c
    WHERE a.addr=b.paddr
    AND b.sql_hash_value = c.hash_value
    AND b.STATUS = 'ACTIVE'
    AND a.spid = '11696'
    ORDER BY a.spid, c.piece
     
    -- Trace SQL Query Execution Time Using SQL ID
    SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
    (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
    buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
    FROM v$sql s  WHERE s.sql_id='4n01r8z5hgfru'
     
    -- Append the /* + gather_table_statistics */ hint into SQL Statement and execute
    SELECT /* + gather_table_statistics */ sysdate ... (SQL Statement)
     
    Note: The execution OF SQL statement WITH hint /* + gather_table_statistics */ will
    subsequently provide the detail plan execution TIME IN the NEXT query below.
     
    -- Get The Detail Explain Plan Using SQL ID
    SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor('dtdqt19kfv6yx'))
     
    -- Get The History Of Explain Plan Execution Using SQL ID In AWR report
    SELECT plan_table_output FROM TABLE(dbms_xplan.display_awr('dtdqt19kfv6yx'))
     
    -- Use Longops To Check The Estimation Runtime
    SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time, 
    last_update_time, time_remaining "REMAIN SEC", round(time_remaining/60,2) "REMAIN MINS", 
    elapsed_seconds "ELAPSED SEC", round(elapsed_seconds/60,2) "ELAPSED MINS", 
    round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS", message TIME 
    FROM v$session_longops
    WHERE sofar<>totalwork
    AND time_remaining <> '0'

    Creating a SQL Profile Using DBMS_SQLTUNE

    SQL Profiles (commonly known as the SQL Tuning Advisor) were introduced in Oracle 10g. The feature tunes queries by gathering information about data distribution, relations between the columns and joined tables and more useful optimizer information.

    It provides recommendations which, when implemented are associated to the query and are used by the optimizer at parse time.

    The following will create a SQL Profile:

    SQL> DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => 'FULL QUERY TEXT', 
    profile => sqlprof_attr('HINT SPECIFICATION WITH FULL OBJECT ALIASES'), 
    name => 'PROFILE NAME', force_match => TRUE/FALSE);

    ‘FULL QUERY TEXT’ – The value can be obtained from the the SQL_FULLTEXT column in table GV$SQLAREA or DBA_HIST_SQLTEXT.

    SQL> SELECT SQL_FULLTEXT FROM GV$SQLAREA 
    WHERE sql_id = '4n01r8z5hgfru'
    SQL> SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT
    WHERE sql_id = '4n01r8z5hgfru'

    ‘HINT SPECIFICATION WITH FULL OBJECT ALIASES’ – The hint specification can be obtained from the table DBA_HIST_SQL_PLAN. For example:

    SQL> SELECT extractvalue(VALUE(d), '/hint') AS outline_hints
    FROM
    xmltable('/*/outline_data/hint'
    passing (
    SELECT
    xmltype(other_xml) AS xmlval
    FROM
    dba_hist_sql_plan
    WHERE sql_id = '4n01r8z5hgfru'
    AND plan_hash_value = '82930460'
    AND other_xml IS NOT NULL
    )
    ) d;

    Or you can get it from DBMS_XPLAN.DISPLAY_AWR function as below.

    SQL> SELECT plan_table_output FROM TABLE
    (dbms_xplan.display_awr('SQL_ID','PLAN_HASH',NULL,'OUTLINE'))

    You can also generate the Trace File 10053 and look for the hint specification between BEGIN_OUTLINE_DATA and END_OUTLINE_DATA. Download SQLTXPLAIN.sql from Oracle Metalink and run it to get Trace File 10053.

      /*+
        BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
          OPT_PARAM('_b_tree_bitmap_plans' 'false')
          OPT_PARAM('_fast_full_scan_enabled' 'false')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$335DD26A")
          MERGE(@"SEL$3")
          OUTLINE_LEAF(@"SEL$7286615E")
          MERGE(@"SEL$5")
          OUTLINE_LEAF(@"SEL$1")
          ......
        END_OUTLINE_DATA
      */

    FORCE_MATCH is really the main reason for using SQL Profiles, when set to TRUE it will ignore literals with exact queries and implement the profile on them (just like what cursor_sharing=force does to the entire DB). For Example: –

    When force match is set to TRUE, a.segment1 = 1234 will become a.segment1 = :b1

    To create a SQL Profile a user must have the following: ADVISOR role, create any sql profile privilege, alter any sql profile privilege, drop any sql profile privilege and execute priviliege on DBMS_SQLTUNE.

    SQL> GRANT EXECUTE ON SYS.DBMS_SQLTUNE TO <user>;
    SQL> GRANT ADVISOR TO <user>;
    SQL> GRANT CREATE ANY SQL PROFILE TO <user>;
    SQL> GRANT ALTER ANY SQL PROFILE TO <user>;
    SQL> GRANT DROP ANY SQL PROFILE TO <user>;

    List of example to create Oracle SQL Profile: –

    SQL> DBMS_SQLTUNE.IMPORT_SQL_PROFILE
    (
       sql_text => 'select * from emp',
       profile => sqlprof_attr('ALL_ROWS','IGNORE_OPTIM_EMBEDDED_HINTS', ...... ),
       category => 'DEFAULT',
       name => 'change_emp',
       force_match => TRUE
    );
    DECLARE
     
    cl_sql_text CLOB;
     
    BEGIN
     
    -- SELECT sql_fulltext INTO cl_sql_text from gv$sqlarea where sql_id='4n01r8z5hgfru';
     
    SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = '4n01r8z5hgfru';
     
    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
    sql_text => cl_sql_text, 
    profile => sqlprof_attr('HINT SPECIFICATION WITH FULL OBJECT ALIASES'),
    name => 'PROFILE NAME', 
    force_match => TRUE);
     
    END;
     
    /
    DECLARE
     
    cl_sql_text CLOB;
     
    hint_spec sys.sqlprof_attr;
     
    BEGIN
     
    --SELECT sql_fulltext INTO cl_sql_text FROM v$sql WHERE sql_id='gtwyx63711jp1';
     
    --SELECT sql_fulltext INTO cl_sql_text FROM v$sqlarea WHERE sql_id='gtwyx63711jp1';
     
    SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = 'gtwyx63711jp1';
     
    SELECT
    extractvalue(VALUE(d), '/hint') AS outline_hints
    BULK COLLECT
    INTO
    hint_spec
    FROM
    xmltable('/*/outline_data/hint'
    passing (
    SELECT
    xmltype(other_xml) AS xmlval
    FROM
    dba_hist_sql_plan	--use v$sql_plan if couldn't find the plan in dba_hist_sql_plan
    WHERE
    sql_id = 'gtwyx63711jp1'
    AND plan_hash_value = '82930460'
    AND other_xml IS NOT NULL
    )
    ) d;
     
    DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
    sql_text => cl_sql_text, 
    profile => hint_spec,
    name => 'PROFILE NAME', 
    force_match => TRUE);
     
    END;
     
    /

    Note: You may use the table v$sql_plan if there is no outline hints available in dba_hist_sql_plan.

    Once you have finish creating the Oracle SQL Profile, check on the database system for the new SQL Profile.

    SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;
     
    SQL> SELECT sql_attr.attr_val outline_hints 
    FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr 
    WHERE sql_profiles.signature = sql_attr.signature
    AND sql_profiles.name = 'PROFILE NAME'
    ORDER BY sql_attr.attr# ASC;

    Dropping a SQL Profile Using DBMS_SQLTUNE

    You can drop a SQL Profile with the DROP_SQL_PROFILE procedure. For example:

    BEGIN
      DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE NAME');
    END;
    /

    In this example, “PROFILE NAME” is the name of the SQL Profile you want to drop. You can also specify whether to ignore errors raised if the name does not exist. For this example, the default value of FALSE is accepted.

    Previous
    Next
    » Oracle SQL Tuning 101
  1. #1 Suresh Kumar Gogune
    November 17, 2010 9:10 am

    Couple of places in above web-page has errata like
    DMBS_SQLTUNE.

    It is supposed to be DBMS_SQLTUNE.

    Other than that this site is very useful.

    Thanks.

    Post ReplyPost Reply
  1. #2 Jing Hong
    November 22, 2010 8:35 pm

    Hi Suresh. Thanks. I have corrected the spell error to DBMS_SQLTUNE.

    Post ReplyPost Reply
  1. #3 Fusion
    November 25, 2010 8:42 pm

    Use the command ‘exec dbms_system.set_ev(sid, serial#, event, level, name);’ to set trace for 10053 where you can see the optimizer execution details.

    Example: exec dbms_system.set_ev(‘318’, ‘20097’, 10053, 12, ”);

    Post ReplyPost Reply
  1. #4 Pravar Shrivastava
    August 10, 2012 10:02 am

    Hi,
    The SQL PROFILE IMPORT script looks good and is very useful also, exactly what I was looking for. I tried the procedure with some of the queries. It ran fine. But on 1-2 queries which had long hints I guess, I am getting following error – (probably for line – hint_spec sys.sqlprof_attr;)

    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 5

    Please assist.

    Post ReplyPost Reply
Leave a Comment