Logo Background
  • SQL Performance Tuning With ASH And SQL Profile
    By on October 8, 2012 | No Comments

    Having issue with your Oracle CBO (Cost Based Optimizer) where sometimes it execute a SQL using a different explain plan or execution plan? We encountered such issue before and the particular SQL run for almost 2 days just to complete the scanning of few hundred million records table.

    The same SQL run without any performance issue before that and so we need to refer to Oracle ASH (Active Session History) table to check on what is the explain plan used before the performance issue.

    You can use the SQL as below to check the plan hash value with the best execution time.

    -- Check The Details Of Running 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 = '10600'
    ORDER BY a.spid, c.piece
     
    -- Trace Query ID 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='fdr7qnnnnmm64'
     
    -- Execution Time Of SQL_ID By Session Using Active Session History (ASH)
    SELECT h.session_id, h.session_serial#, h.sql_id, h.sql_child_number,
    h.sql_plan_hash_value,  h.module,
    SUM(h.wait_time + h.time_waited) / 1000 "Total Wait Time (Sec)"
    FROM dba_hist_active_sess_history h --alternative table is v$active_session_history
    WHERE h.sql_id = 'fdr7qnnnnmm64'
    --AND h.module = 'XLAACCPB'
    --AND h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
    GROUP BY h.session_id, h.session_serial#, h.sql_id,
    h.sql_child_number, h.sql_plan_hash_value,h.module
    ORDER BY SUM(h.wait_time + h.time_waited) DESC
     
    -- Execution Time Of SQL_ID By Date Using Active Session History (ASH)
    SELECT trunc(h.sample_time) "Execution Date", h.sql_id, h.sql_child_number,
    h.sql_plan_hash_value, h.module,
    SUM(h.wait_time + h.time_waited) / 1000 "Total Wait Time (Sec)"
    FROM dba_hist_active_sess_history h --alternative table is v$active_session_history
    WHERE h.sql_id = 'fdr7qnnnnmm64'
    --AND h.module = 'XLAACCPB'
    --AND h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
    GROUP BY trunc(h.sample_time), h.sql_id,
    h.sql_child_number, h.sql_plan_hash_value, h.module
    ORDER BY trunc(h.sample_time), SUM(h.wait_time + h.time_waited) DESC
     
    -- Get The Detail Explain  Plan Using SQL ID
    SELECT plan_table_output FROM TABLE
    (dbms_xplan.display_cursor('fdr7qnnnnmm64'))
     
    -- Get The Detail Explain  Plan With Advanced Details Using SQL ID
    SELECT plan_table_output FROM TABLE
    (dbms_xplan.display_cursor('fdr7qnnnnmm64','0','ADVANCED'))
     
    -- Get The History Of Explain Plan Execution Using SQL ID
    SELECT plan_table_output FROM TABLE
    (dbms_xplan.display_awr('fdr7qnnnnmm64'))
     
    -- Get The History Of Explain Plan Execution With Advanced Details Using SQL ID
    SELECT plan_table_output FROM TABLE
    (dbms_xplan.display_awr('fdr7qnnnnmm64','2761201612', NULL,'ADVANCED'))

    From what we saw, the problematic has 3 set of plan hash value and we are going to pick one of them with the best execution time and create SQL Profile on it.

    The Oracle CBO (Cost Based Optimizer) will have follow the execution plan as forced by the SQL Profile.

    Here’s the command on how to create the SQL Profile.

    DECLARE
     
    cl_sql_text CLOB;
     
    hint_spec sys.sqlprof_attr;
     
    BEGIN
     
    --SELECT sql_fulltext INTO cl_sql_text FROM gv$sqlarea WHERE sql_id='fdr7qnnnnmm64';
     
    SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = 'fdr7qnnnnmm64';
     
    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 --alternative table is v$sql_plan
    WHERE
    sql_id = 'fdr7qnnnnmm64'
    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;
     
    /

    You can also create the SQL Profile in UAT environment, providing that you can find a good execution and migrate the SQL Profile to Production.

    Try to re-run the program once you have locked the particular explain plan with SQL Profile and check on the result.