/*
|| Oracle 11g SQL Performance Analyzer Listing 1
||
|| Demonstrates Oracle 11g SQL Performance Analyzer (SPA) basic techniques,
|| including:
|| - Setup and configuration of required sample database objects
|| - Creation of SQL Tuning Sets for "before" and "after" image comparson
|| - Execution of SQL Performance Analyzer "before" test image
|| - Execution of SQL Performance Analyzer "after" test image
|| - Execution of SQL Performance Analyzer comparison reports
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| SQL Performance Analyzer features, and they should be carefully proofread
|| before executing them against any existing Oracle database(s) to avoid
|| potential damage!
*/


/* 
|| Listing 1.1: 
|| Create a new table (SH.SALES_AGENTS) and corresponding objects for
|| simulated performance tuning
*/

DROP TABLESPACE lmt_data INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE TABLESPACE lmt_data
    DATAFILE '/u01/app/oracle/oradata/orcl/lmt_data01.dbf'
    SIZE 60M
    AUTOEXTEND ON
    LOGGING
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

DROP TABLESPACE lmt_idx INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE TABLESPACE lmt_idx
    DATAFILE '/u01/app/oracle/oradata/orcl/lmt_idx01.dbf'
    SIZE 60M
    AUTOEXTEND ON
    LOGGING
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

DROP TABLE sh.sales_agents PURGE;
CREATE TABLE sh.sales_agents(
     salesperson_id      NUMBER(10)     NOT NULL
    ,last_name           VARCHAR2(32)   NOT NULL
    ,first_name          VARCHAR2(32)   NOT NULL
    ,address_line_1      VARCHAR2(40)   
    ,address_line_2      VARCHAR2(40)   
    ,address_line_3      VARCHAR2(40)   NOT NULL
    ,city                VARCHAR2(40)   NOT NULL
    ,locale              VARCHAR2(40)
    ,state               VARCHAR2(2)    
    ,postal_code         VARCHAR2(10)   
    ,country             VARCHAR2(32)   
)
    TABLESPACE lmt_data
    STORAGE (INITIAL 1M)
;

DROP INDEX sh.sales_agents_pk_idx;
CREATE UNIQUE INDEX sh.sales_agents_pk_idx
    ON sh.sales_agents (salesperson_id)
    TABLESPACE lmt_idx
    STORAGE (INITIAL 1M)
;

ALTER TABLE sh.sales_agents
    ADD CONSTRAINT salesperson_pk
    PRIMARY KEY (salesperson_id)
;

/*
|| Initial Data Load
*/

@LoadSalesAgents.sql;

/*
|| Index Creation and Statistics Gathering
*/

-----
-- Create alternate indexes for reporting
-----
DROP INDEX sh.sales_agents_la_idx;
CREATE INDEX sh.sales_agents_la_idx
    ON sh.sales_agents (last_name ASC)
    TABLESPACE lmt_idx
    STORAGE (INITIAL 1M)
;

DROP INDEX sh.sales_agents_sa_idx;
CREATE INDEX sh.sales_agents_sa_idx
    ON sh.sales_agents (state ASC)
    TABLESPACE lmt_idx
    STORAGE (INITIAL 1M)
;

DROP INDEX sh.sales_agents_lafa_idx;
CREATE INDEX sh.sales_agents_lafa_idx
    ON sh.sales_agents (last_name ASC, first_name ASC)
    TABLESPACE lmt_idx
    STORAGE (INITIAL 1M)
;

DROP INDEX sh.sales_agents_saca_idx;
CREATE INDEX sh.sales_agents_saca_idx
    ON sh.sales_agents (state ASC, city ASC)
    TABLESPACE lmt_idx
    STORAGE (INITIAL 1M)
;

-----
-- Gather statistics on table and its indexes, and
-- then lock them to prevent unexpected recalculation
-- during nightly statistics regathering job
-----
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
         ownname => 'SH'
        ,tabname => 'SALES_AGENTS'
        ,cascade => TRUE
        ,force => TRUE
    );
END;
/

/* 
|| Listing 1.2: Load Generation
|| While the SQL Tuning Set task in Listing 1.x is "watching,"
|| generate a load on the database that  can be captured into a
|| SQL Tuning Set for later analysis with the SQL Performance Analyzer (SPA)
*/

CONNECT ldgn/ldgn;
SET ECHO ON
SET TIMING ON

-----
-- Scenario 1.0: 
-- Let the Cost-Based Optimizer choose the best query plan
-----
SELECT /*LDGN_1.0*/
    SA.last_name
   ,SA.first_name
   ,SA.city
   ,SA.state
   ,SA.postal_code
  FROM 
     sh.sales_agents SA
 WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
   OR SA.last_name LIKE 'Crystal%'
;

-----
-- Scenario 1.1: 
-- Force the query to ignore all indexes
-----
SELECT /*+ NO_INDEX(SA) LDGN_1.1*/
    SA.last_name
   ,SA.first_name
   ,SA.city
   ,SA.state
   ,SA.postal_code
  FROM 
     sh.sales_agents SA
 WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
   OR SA.last_name LIKE 'Crystal%'
;

-----
-- Scenario 1.2: 
-- Force the query to ignore at least one of the obvious 
-- "best choice" indexes
-----
SELECT /*+ NO_INDEX(SA sales_agents_la_idx) LDGN_1.2*/
    SA.last_name
   ,SA.first_name
   ,SA.city
   ,SA.state
   ,SA.postal_code
  FROM 
     sh.sales_agents SA
 WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
   OR SA.last_name LIKE 'Crystal%'
;

-----
-- Scenario 1.3: 
-- Force the query to use a wrong index
-----
SELECT /*+INDEX(SA sales_agents_pk_idx) LDGN_1.3*/
    SA.last_name
   ,SA.first_name
   ,SA.city
   ,SA.state
   ,SA.postal_code
  FROM 
     sh.sales_agents SA
 WHERE ((SA.city = 'Chicago' AND SA.state = 'IL') OR SA.state = 'IN')
   OR SA.last_name LIKE 'Crystal%'
;

-----
-- Scenario 2.0: 
-- Let the Cost-Based Optimizer choose the best query plan
-----
SELECT /*LDGN_2.0*/
     SA.state
    ,COUNT(SA.city) tot_cities
  FROM 
     sh.sales_agents SA
 WHERE (SA.last_name,SA.first_name) IN (('Wells','Billy'),('Alexander','Kirk'))
 GROUP BY SA.state
 ORDER BY SA.state;
;

-----
-- Scenario 2.1: 
-- Force the query to use a poorer index (SALES_AGENTS_SACA_IDX)
-- even though a better index (SALES_AGENTS_LAFA_IDX) exists
-----
SELECT /*+INDEX(SA, SALES_AGENTS_SACA_IDX) LDGN_2.1*/
     SA.state
    ,COUNT(SA.city) tot_cities
  FROM 
     sh.sales_agents SA
 WHERE (SA.last_name,SA.first_name) IN (('Wells','Billy'),('Alexander','Kirk'))
 GROUP BY SA.state
 ORDER BY SA.state;
;

-----
-- Scenario 3.0:
-- Let the Cost-Based Optimizer choose the best query plan
-----
SELECT /*LDGN_3.0*/
    SA.state
   ,SA.city
   ,COUNT(SA.last_name) namecnt
   ,MIN(SA.last_name) min_lname
   ,MAX(SA.last_name) max_lname
   ,MIN(SA.first_name) min_fname
   ,MAX(SA.first_name) max_fname
  FROM 
     sh.sales_agents SA
 WHERE (SA.city,SA.state) IN (
     ('Milpitas','CA')
    ,('Schaumburg','IL')
    ,('El Paso','TX')
    ,('Oshkosh','WI'))
 GROUP BY SA.state, SA.city
 ORDER BY SA.state, SA.city;
;

-----
-- Scenario 3.1:
-- Force the query to use a poorer index (SALES_AGENTS_LAFA_IDX)
-- even though a better index (SALES_AGENTS_SACA_IDX) exists
-----
SELECT /*+INDEX(SA, SALES_AGENTS_LAFA_IDX) LDGN_3.1*/
    SA.state
   ,SA.city
   ,COUNT(SA.last_name) namecnt
   ,MIN(SA.last_name) min_lname
   ,MAX(SA.last_name) max_lname
   ,MIN(SA.first_name) min_fname
   ,MAX(SA.first_name) max_fname
  FROM 
     sh.sales_agents SA
 WHERE (SA.city,SA.state) IN (
     ('Milpitas','CA')
    ,('Schaumburg','IL')
    ,('El Paso','TX')
    ,('Oshkosh','WI'))
 GROUP BY SA.state, SA.city
 ORDER BY SA.state, SA.city;
;

SET ECHO OFF
SET TIMING OFF
DISCONNECT;

/* 
|| Listing 1.3: SQL Tuning Set Creation
|| 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.CREATE_SQLSET(
        sqlset_name => 'STS_SPA_100'
    );
    DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(                         
         sqlset_name => 'STS_SPA_100'
        ,basic_filter=> q'#sql_text LIKE '%LDGN_%' AND parsing_schema_name = 'LDGN'#'
        ,time_limit  => 300
        ,repeat_interval => 5
    ); 
END;
/

/* 
|| Listing 1.4: Generate SQL Performance Analysis Task
|| In the following steps: 
|| 1.) A new SQL Performance Analysis (SPA) task is generated
|| 2.) The SPA task is executed in test execution mode to 
||     produce a "before" image
*/
-----
-- Create a SQL Performance Analysis Task
-----
DECLARE
    spa_task_name   VARCHAR2(64);
BEGIN
    spa_task_name := 
        DBMS_SQLPA.CREATE_ANALYSIS_TASK(
             sqlset_name => 'STS_SPA_100'
            ,basic_filter => NULL
            ,order_by => NULL
            ,top_sql => NULL
            ,description => 'Comparison of LDGN-executed SQL statements'
            ,sqlset_owner => 'SYS'
        ); 
END;
/

-----
-- Execute the SQL Performance Analysis Task to capture the "before" 
-- image of the SQL Tuning Set's performance
-----
BEGIN
    DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
         task_name => 'TASK_69'
        ,execution_type => 'test execute'
        ,execution_name => 'SQR_100_BEFORE'
        ,execution_desc => 'SPA Before Image'
        ,execution_params => DBMS_ADVISOR.ARGLIST('TEST_EXECUTE','FULL')
    );
END;
/

/* 
|| Listing 1.5: Change the Database Environment
|| Drop some of the indexes that were utilized heavily
|| by the previously-run SQL statements, and then regather
|| optimizer statistics
*/

DROP INDEX sh.sales_agents_la_idx;
DROP INDEX sh.sales_agents_saca_idx;
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
         ownname => 'SH'
        ,tabname => 'SALES_AGENTS'
        ,cascade => TRUE
        ,force => TRUE
    );
END;
/

/* 
|| Listing 1.6: Generate An "After" Performance Image
|| The existing the SQL Performance Analysis Task is re-executed 
|| to capture the "after" image of the SQL Tuning Set's performance
*/

BEGIN
    DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
         task_name => 'TASK_69'
        ,execution_type => 'test execute'
        ,execution_name => 'SQR_100_AFTER'
        ,execution_desc => 'SPA After Image'
        ,execution_params => DBMS_ADVISOR.ARGLIST('TEST_EXECUTE','FULL')
    );
END;
/

/* 
|| Listing 1.7: Compare Before vs. After Performance
*/

BEGIN
    DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
         task_name => 'TASK_69'
        ,execution_type => 'compare performance'
        ,execution_name => 'SPA_110_PC'
        ,execution_desc => 'SPA 110 Performance Comparison'
        ,execution_params => 
            DBMS_ADVISOR.ARGLIST(
                 'EXECUTION_NAME1','SQR_BEFORE_110'
                ,'EXECUTION_NAME2','SQR_AFTER_110'
                ,'COMPARISON_METRIC','ELAPSED_TIME'
            )
    );
END;
/

/* 
|| Listing 1.8: Generate Comparison Report
|| Now that the comparison is completed, generate the "delta" report
|| using DBMS_SQLPA.REPORT_ANALYSIS_TASK
*/

SET PAGESIZE 0
SET LINESIZE 1000
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
    tLOB CLOB;
BEGIN
    SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_69')
      INTO tLOB
      FROM DUAL;
    DBMS_OUTPUT.PUT_LINE(tLOB);
END;
/