/*
|| 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;
/