Oracle 11g：SQL性能分析器（三）(7)

作者: Jim Czuprynski/黄永兵 译 出处:51CTO.com　

 

 

列表三

/*
|| Oracle 11g SQL Performance Advisor Listing 3
||
|| Demonstrates Oracle 11g SQL Performance Advisor (SPA) advanced techniques,
|| including:
|| - Creation of SQL Workload on an Oracle 11gR1 database
|| - Packing, transport, and unpacking of SQL Tuning Set on Oracle 11g database
|| - Executing SQL Performance Advisor "before" and "after" test executions
|| - Comparing results from SQL Performance Advisor analysis reports
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| SQL Performance Advisor features, and they should be carefully proofread
|| before executing them against any existing Oracle database(s) to avoid
|| potential damage!
*/

/* 
|| Listing 3.1: Creating a SQL Workload on Oracle 11gR1 database
|| Create and prepare to populate a SQL Tuning Set (STS)
|| for selected SQL statements. Note that this STS will capture
|| all SQL statements which are executed by the LDGN user account
|| within a 5-minute period, and Oracle will check every 5 seconds
|| for any new statements
*/

BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'STS_SPA_300'
);
END;
/

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_SPA_300'
);
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(                        
sqlset_name => 'STS_SPA_300'
,basic_filter=> q'#sql_text LIKE '%LDGN_%' AND parsing_schema_name = 'LDGN'#'
,time_limit  => 120
,repeat_interval => 5
);
END;
/

-----
-- ... and now generate a workload so it can be captured into
-- the SQL Tuning Set.
-----

@GenerateSPAWorkload_3.sql;

/*
|| Listing 3.2: Generate SQL Performance Analysis Task
|| In the following steps:
|| 1.) A new SQL Performance Analysis (SPA) task is created
|| 2.) The SPA task is executed in test execution mode to
||     produce a "before" image
*/

BEGIN
DBMS_SQLPA.DROP_ANALYSIS_TASK(
task_name => 'SPA_IPC_300'
);
END;
/

DECLARE
sts_name            VARCHAR2(30)    := 'STS_SPA_300';
sts_owner           VARCHAR2(30)    := 'SYS';
task_name           VARCHAR2(30)    := 'SPA_IPC_300';
task_desc           VARCHAR2(256)   := 'SQLTRACING';
execution_type      VARCHAR2(30)    := 'TEST EXECUTE';
persql_timelimit    VARCHAR2(30)    := NULL;
compare_metric      VARCHAR2(30)    := 'ELAPSED_TIME';
param_name          VARCHAR2(256)   := 'optimizer_index_cost_adj';
param_value1        VARCHAR2(32767) := '25';
param_value2        VARCHAR2(32767) := '100';
curval              VARCHAR2(32767) := NULL;
tname               VARCHAR2(30)    := task_name;
ename1              VARCHAR2(30);
ename2              VARCHAR2(30);
ename3              VARCHAR2(30);
edesc               VARCHAR2(256);
pvalue1             VARCHAR2(32767) := param_value1;
pvalue2             VARCHAR2(32767) := param_value2;
l_status            VARCHAR2(30);

BEGIN
-----
-- Capture current value for the selected initialization parameter
-- so it can be reset in case this SPA task fails
-----
SELECT value
INTO curval
FROM v$parameter
WHERE name = param_name;

-----
-- Create a new SQL Performance Analyzer task
-----
tname :=
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sqlset_name => 'STS_SPA_300'
,basic_filter => NULL
,order_by => NULL
,top_sql => NULL
,task_name => 'SPA_IPC_300'
,description => '3rd Initialization Parameter Change impact analysis'
,sqlset_owner => 'SYS'
);

-----
-- Set SQL Performance Analyzer parameters apppropriately
-----
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => tname
,parameter => 'TIME_LIMIT'
,value => 'UNLIMITED'
);         
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => tname
,parameter => 'LOCAL_TIME_LIMIT'
,value => persql_timelimit
);

-----
-- Reset the SQL Performance Analyzer task
-----
DBMS_SQLPA.RESET_ANALYSIS_TASK(task_name => 'SPA_IPC_300');

-----
-- Set up the execution environment for the "BEFORE" execution
-- (i.e. OPTIMIZER_COST_INDEX_ADJ = 25). Note my addition of the
-- statements to flush the Database Buffer Cache and the Shared Pool.
-----
EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH BUFFER_CACHE';
EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL';
EXECUTE IMMEDIATE 'ALTER SESSION SET ' || param_name || ' = ' || pvalue1;

edesc := 'parameter ' || param_name || ' set to ' || pvalue1;

-----
-- Perform the first execution of task SPA_IPC_300
-----
ename1 :=
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => tname
,execution_type => execution_type
,execution_name => 'initial_sql_trial'
,execution_desc => SUBSTR(edesc, 1, 256)
);

-----
-- When the first execution of task SPA_IPC_300 is done, perform
-- the "after" evaluation (i.e. OPTIMIZER_COST_INDEX_ADJ = 100)
-----
SELECT status
INTO l_status
FROM sys.dba_advisor_tasks
WHERE task_name = tname;

IF (l_status = 'COMPLETED') THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH BUFFER_CACHE';
EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL';
EXECUTE IMMEDIATE 'ALTER SESSION SET ' || param_name || ' = ' || pvalue2;

edesc := 'parameter ' || param_name || ' set to ' || pvalue2;

ename1 :=
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => tname
,execution_type => execution_type
,execution_name => 'second_sql_trial'
,execution_desc => substr(edesc, 1, 256)
);
END IF;

-----
-- Now that the "before" and "after" executions are complete, perform a
-- comparison report of the two executions
-----
SELECT status
INTO l_status
FROM sys.dba_advisor_tasks
WHERE task_name = tname;

IF (l_status = 'COMPLETED') THEN
ename3 :=
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => tname
,execution_type => 'compare performance'
,execution_params => dbms_advisor.argList('comparison_metric', compare_metric));    
END IF;

-----
-- Exception processing:
-- If any errors occur, simply reset the modified initialization parameter
-- to its initial value and raise the exception to the next level
-----
EXCEPTION
WHEN OTHERS THEN
IF (tname IS NOT NULL AND param_name IS NOT NULL AND curval IS NOT NULL)
THEN EXECUTE IMMEDIATE 'ALTER SESSION SET ' || param_name || ' = ' || curval;
END IF;
RAISE;
END;
/