PLSQL_性能优化系列18_Oracle Explain Plan解析计划通过Baseline绑定

2015-05-28 Created By BaoXinjian

一、摘要

---

1. 应用场景

当运行很久的Job突然出现性能问题时，并排除数据量突然变大，可能原因有执行的脚本的某些对应的SQL的解析计划变更

2. 解决步骤

Step1. 通过对应的User或者其他信息查找Session ID

Step2. 通过Session_ID查看该SQL_ID历史的解析计划变换 

Step3. 如果最近发生解析计划变更，并导致效率变慢，通过SPM绑定之前的解析计划 

Step4. 确定解析计划是否绑定 

Step5. 如果脚本还在运行，让客户停掉脚本，并重新运行，以测试绑定的新的解析计划是否满足期望 

Step6. 如果绑定的解析计划并不能解决性能问题，验证后将其删除

 

二、步骤

---

Step1. 通过对应的User或者其他信息查找Session ID

select sql_id, username, osuser, machine, program
  from v$session
 where username = 'gavin'

Step2. 查看该SQL_ID历史的解析计划变换

select distinct a.instance_number,
                trunc(b.begin_interval_time, 'mi'),
                sql_id,
                plan_hash_value
  from dba_hist_sqlstat a, dba_hist_snapshot b
 where a.snap_id = b.snap_id
   and sql_id = '089dbukv1aanh'
 order by 2, 1

Step3. 如果最近发生解析计划变更，并导致效率变慢，通过SPM绑定之前的解析计划 (脚本coe_xfr_sql_baseline.sql)

SQL>@D:Gavincoe_xfr_sql_baseline.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 089dbukv1aanh


SNAP_DATE       SQL_ID             PLAN_HASH_VALUE
--------------- ------------- --------------------
2015/05/20      089dbukv1aanh           1388734953
2015/05/21      089dbukv1aanh           1388734953
2015/05/22      089dbukv1aanh           1388734953
2015/05/23      089dbukv1aanh           1388734953
2015/05/24      089dbukv1aanh           1388734953
2015/05/25      089dbukv1aanh           1388734953
2015/05/26      089dbukv1aanh           1388734953
2015/05/27      089dbukv1aanh           1388734953
2015/05/28      089dbukv1aanh           1388734953

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1388734953

Values passed to coe_xfr_sql_baseline:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID: "089dbukv1aanh"
PLAN_HASH_VALUE: "1388734953"

SQL>SET ECHO OFF;
SQL tuning set SS_089dbukv1aanh_1388734953 created.

SQLSET_NAME                    SQLSET_OWNER                   SQL_ID                              PLAN_HASH_VALUE
------------------------------ ------------------------------ ------------------------------ -------
SS_089dbukv1aanh_1388734953    GAVIN                          089dbukv1aanh                            1388734953
Plans Loaded: 1
sys_sql_handle: "SYS_SQL_70445f05461a3cd3"
sys_plan_name: "SQL_PLAN_70j2z0p31ng6m125daea2"
1 plan(s) modified description: "SQL_ID: 089DBUKV1AANH PLAN HASH VALUE: 1388734953 CREATED BY COE_XF
SQL tuning set SS_089dbukv1aanh_1388734953 dropped.
SQL>REM
SQL>REM SQL Plan Baseline
SQL>REM ~~~~~~~~~~~~~~~~~
SQL>REM
SQL>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;

           SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
-------------------- ------------------------------ ------------------------------ --- --- ---
 8089695306919853267 SYS_SQL_70445f05461a3cd3       SQL_PLAN_70j2z0p31ng6m125daea2 YES YES YES
SQL>
SQL>col description for a100
SQL>SELECT description
  2    FROM dba_sql_plan_baselines WHERE plan_name = :plan_name;

DESCRIPTION
----------------------------------------------------------------------------------------------------
SQL_ID: 089DBUKV1AANH PLAN HASH VALUE: 1388734953 CREATED BY COE_XFR_SQL_BASELINE.SQL
SQL>SET ECHO OFF;


coe_xfr_sql_baseline completed. 

Step4. 确定解析计划是否绑定

select sql_handle, plan_name, sql_text, creator, description, enabled, accepted, fixed
  from dba_sql_plan_baselines
 where description like '%089dbukv1aanh%'

Step5. 如果脚本还在运行，让客户停掉脚本，并重新运行，以测试绑定的新的解析计划是否满足期望

alter system disconnect session '102,102' immediate; 

Step6. 如果绑定的解析计划并不能解决性能问题，验证后将其删除

declare
  l_plan number;
begin
  l_plan := dbms_spm.drop_sql_plan_baseline('SYS_SQL_70445f05461a3cd3', 'SQL_PLAN_70j2z0p31ng6m125daea2');
  dbms_output.put_line('l_pan=' || l_plan);
end;

 

三、代码

---

Oracle Metalink下载代码 - ce_xfr_sql_baseline.sql

SPO coe_xfr_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 Created according to coe_load_sql_baseline.sql and MOS note 789888.1 2013/06/10
REM
REM AUTHOR
REM   gavin.bao
REM
REM SCRIPT
REM   coe_xfr_sql_baseline.sql
REM
REM DESCRIPTION
REM   This script loads a plan from AWR into the SQL
REM   Plan Baseline of the specified SQL.
REM
REM PRE-REQUISITES
REM   1. Have in AWR the text for the specified SQL.
REM
REM PARAMETERS
REM   1. SQL_ID (required)
REM   2. PLAN_HASH_VALUE in AWR (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_xfr_sql_baseline.sql passing first two
REM      parameters inline or until requested by script.
REM
REM EXAMPLE
REM   # sqlplus system
REM   SQL> START coe_xfr_sql_baseline.sql gnjy0mn4y9pbm b8f3mbkd8bkgh
REM   SQL> START coe_xfr_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_xfr_sql_profile.sql,
REM      which uses custom SQL Profiles instead of SQL Baselines.
REM   3. For possible errors see coe_xfr_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 SQL_ID (required)
PRO
DEF sql_id = '&1';
PRO
col SNAP_DATE for a15
select distinct to_char(trunc(b.BEGIN_INTERVAL_TIME),'yyyy/mm/dd') SNAP_DATE,SQL_ID,PLAN_HASH_VALUE
from dba_hist_sqlstat a,dba_hist_snapshot b
where SQL_ID=TRIM('&&sql_id.')
and a.snap_id=b.snap_id order by 1;
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
PRO
DEF plan_hash_value = '&2';
PRO
PRO Values passed to coe_xfr_sql_baseline:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO SQL_ID: "&&sql_id."
PRO PLAN_HASH_VALUE: "&&plan_hash_value."
PRO
WHENEVER SQLERROR EXIT SQL.SQLCODE;
SET TERM OFF ECHO ON;

-- trim parameters
COL sql_id NEW_V sql_id FOR A30;
COL plan_hash_value NEW_V plan_hash_value FOR A30;
SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;

-- open log file
SPO coe_xfr_sql_baseline_&&sql_id..log;
GET coe_xfr_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);
VAR b_snap_id NUMBER;
VAR e_snap_id NUMBER;
VAR ss_name VARCHAR2(100);
EXEC :sql_text := NULL;
EXEC :plan_name := NULL;
EXEC :b_snap_id := NULL;
EXEC :e_snap_id := NULL;
EXEC :ss_name := 'SS_'||TRIM('&&sql_id.')||'_'||TRIM('&&plan_hash_value.');

-- get sql_text from awr
BEGIN
    SELECT REPLACE(sql_text, CHR(00), ' ')
      INTO :sql_text
      FROM dba_hist_sqltext
     WHERE sql_id = TRIM('&&sql_id.')
       AND sql_text IS NOT NULL
       AND ROWNUM = 1;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
    :sql_text := NULL;
END;
/

-- sql_text as found
SELECT :sql_text FROM DUAL;

-- check is sql_text for sql is available
SET TERM ON;
SET ECHO OFF;
BEGIN
  IF :sql_text IS NULL THEN
    RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in AWR (dba_hist_sqltext).');
  END IF;
END;
/


-- get snap id
DECLARE
  l_count NUMBER;
BEGIN
  SELECT min(SNAP_ID), max(SNAP_ID)
    INTO :b_snap_id, :e_snap_id
    FROM dba_hist_sqlstat
   WHERE sql_id = TRIM('&&sql_id.')
     AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'));

   IF (:b_snap_id is null or :e_snap_id is null) THEN
     RAISE_APPLICATION_ERROR(-20110, 'PHV &&plan_hash_value. for SQL_ID &&sql_id. was not be found in AWR (dba_hist_sqlstat).');
   END IF;
END;
/


DECLARE
  --ss_name VARCHAR2(100) default 'SS_'||TRIM('&&sql_id.')||'_'||TRIM('&&plan_hash_value.');
  baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  -- create sql_plan_baseline for sql using plan from AWR
  DBMS_SQLTUNE.CREATE_SQLSET(:ss_name);
  open baseline_ref_cursor for
  select VALUE(p) from
  table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(:b_snap_id, :e_snap_id,
                                                'sql_id='||CHR(39)||TRIM('&&sql_id.')||CHR(39)||
                                                ' and plan_hash_value='||TRIM('&&plan_hash_value.'),
                                                NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
  DBMS_SQLTUNE.LOAD_SQLSET(:ss_name, baseline_ref_cursor);
  DBMS_OUTPUT.PUT_LINE('SQL tuning set '||:ss_name||' created.');
END;
/


-- display details of the SQL tuning set
col PLAN_HASH_VALUE clear
select SQLSET_NAME,SQLSET_OWNER,SQL_ID,PLAN_HASH_VALUE from dba_sqlset_statements where sqlset_name = :ss_name ;

-- load the plan from SQL tuning set into baseline
declare
  plans NUMBER;
  description VARCHAR2(500);
  sys_sql_handle VARCHAR2(30);
  sys_plan_name VARCHAR2(30);
begin
  plans := dbms_spm.load_plans_from_sqlset(sqlset_name => :ss_name,
                                              sqlset_owner => USER,
                                              fixed => 'YES',
                                              enabled => 'YES');
  commit;
  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||'"');

  :plan_name := sys_plan_name;

  -- update description of new sql_plan_baseline
  description := UPPER('SQL_ID: '||TRIM('&&sql_id.')||' Plan Hash Value: '||TRIM('&&plan_hash_value.')||' created by coe_xfr_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||'"');

  DBMS_SQLTUNE.DROP_SQLSET(:ss_name);
  DBMS_OUTPUT.PUT_LINE('SQL tuning set '||:ss_name||' dropped.');
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;

col description for a100
SELECT description
  FROM dba_sql_plan_baselines WHERE plan_name = :plan_name;
SET ECHO OFF;
PRO
SPO OFF;
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 sql_id plan_hash_value
CL COL
PRO
PRO coe_xfr_sql_baseline completed.

Thanks and Regards