Using SQL Performance Analyzer in the planning of 11g upgrade for a critical database

In my working environment, there is a one-way replication setup from database ttpseprd to ttpsefh. Database ttpseprd has OLTP-type workload and ttpsefh is a reporting database. The two databases have almost same schemas and objects. Some time ago ttpsefh was upgraded from 10g to 11g and now the ttpseprd is planned to be upgraded to 11g soon. So the ttpsefh could be an ideal environment to test if there are any SQLs currentely run in the 10g ttpseprd database would be regressed after upgrade. For that purpose, I have conducted several SQL Peformnace Analyzer (SPA) experiments in a test 11gR2 database environment called dbahrgrd.

From the Grid Control homepage of the dbahrgrd database instance, by clicking the following links: Advisor Central => SQL Performance Analyzer => Guided Workflow, I can see the following five steps regarding how to execute a successful two-trial SPA test:

1. Create SQL Performance Analyzer Task based on SQL Tuning Set
2. Create SQL Trial in Initial Environment
3. Create SQL Trial in Changed Environment
4. Compare Step 2 and Step 3
5. View Trial Comparison Report

To complete the step 1, I need a STS obviously. I first created a STS called STS_TTPSEPRD_10G in the ttpseprd database and used OEM Grid Control to load the set from past 7 days of AWR data and from cursors in the shared pool as well. Behind the scene, the code used by OEM looks like:

DECLARE 
   sqlset_cur dbms_sqltune.sqlset_cursor;
   bf VARCHAR2(80); 
BEGIN 
   bf := q'#UPPER(PARSING_SCHEMA_NAME) IN ('TTQMIGADMIN', 'TTQ2TACPVIEW', 'TTQ2_TACP') #'; 
   OPEN sqlset_cur FOR 
      SELECT VALUE(P) 
         FROM TABLE( dbms_sqltune.select_workload_repository( '57490', '58146', bf, NULL, NULL, NULL, NULL, 1, NULL, NULL)) P; 
   dbms_sqltune.load_sqlset( sqlset_name=>'STS_TTPSEPRD_10G'
                           , populate_cursor=>sqlset_cur
                           , load_option => 'MERGE'
                           , update_option => 'ACCUMULATE'
                           , sqlset_owner=>'VUSERID'); 
END;
 


DECLARE 
   sqlset_cur dbms_sqltune.sqlset_cursor; 
   bf VARCHAR2(80); 
BEGIN 
   bf := q'#UPPER(PARSING_SCHEMA_NAME) IN ('TTQMIGADMIN', 'TTQ2TACPVIEW', 'TTQ2_TACP') #'; 
   OPEN sqlset_cur FOR 
      SELECT VALUE(P) FROM TABLE( dbms_sqltune.select_cursor_cache(bf, NULL, NULL, NULL, NULL, 1, NULL, 'TYPICAL')) P; 
   dbms_sqltune.load_sqlset( sqlset_name=>'STS_TTPSEPRD_10G'
                           , populate_cursor=>sqlset_cur
                           , load_option => 'MERGE'
                           , update_option => 'ACCUMULATE'
                           , sqlset_owner=>'VUSERID'); 
END;

Then following the procedure described in this Oracle document , I've transported STS_TTPSEPRD_10G from ttpseprd to dbahrgrd. There are 25993 SQLs in the set originally. I deleted all SQLs starting with 'insert', 'update' and 'detele' using the following syntax:

BEGIN
  DBMS_SQLTUNE.DELETE_SQLSET(
      sqlset_name  => 'STS_TTPSEPRD_10G',
      basic_filter => 'UPPER(SQL_TEXT) LIKE ''DELETE%''');
END;
/

After the deletion, the total number of the SQLs in the set come down to 19201. The SQLs in the set can be further categorized based on the number of executions. The following queries showed the number of SQLs in each category based on the executions range I chose.

SQL> select count(*) from DBA_SQLSET_STATEMENTS WHERE SQLSET_OWNER ='VUSERID'  AND SQLSET_NAME= 'STS_TTPSEPRD_10G' and executions > 200;

  COUNT(*)
----------
      3210

SQL> select count(*) from DBA_SQLSET_STATEMENTS WHERE SQLSET_OWNER ='VUSERID'  AND SQLSET_NAME= 'STS_TTPSEPRD_10G' and executions between 5 and 200;

  COUNT(*)
----------
      3509

SQL> select count(*) from DBA_SQLSET_STATEMENTS WHERE SQLSET_OWNER ='VUSERID'  AND SQLSET_NAME= 'STS_TTPSEPRD_10G' and executions between 2 and 4;

  COUNT(*)
----------
      8160

SQL> select count(*) from DBA_SQLSET_STATEMENTS WHERE SQLSET_OWNER ='VUSERID'  AND SQLSET_NAME= 'STS_TTPSEPRD_10G' and executions=1;

  COUNT(*)
----------
      4322

It can be seen that there are many SQLs with executions equal one. Most of them was due to SQLs not using bind variables. For each category, I created a corresponding STS and did a SPA test on it. Below I will describe the test on the STS_TTPSEPRD_4 that contains SQLs with executions > 200.

The STS_TTPSEPRD_4 was created through the following code:

-- create a new STS from an existing STS
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'STS_TTPSEPRD_10G_4', 
    description  => 'subset of STS_SSODPRD_10G executions > 200');
END;
/

-- select from a sql tuning set and pass cursor to load_sqlset
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(dbms_sqltune.select_sqlset(
        'STS_TTPSEPRD_10G',
        basic_filter => 'executions > 200' 
   )) P;
 
  -- Process each statement (or pass cursor to load_sqlset)

    DBMS_SQLTUNE.LOAD_SQLSET(
             sqlset_name     => 'STS_TTPSEPRD_10G_4',
             populate_cursor => cur);
 
  CLOSE cur;
END;
/

With the STS ready, it is straightforward to create the SPA test task through OEM.

In step 2, I executed the SQLs  in the 10g ttpseprd database:

begin 
  dbms_sqlpa.execute_analysis_task(task_name => 'TTPSEPRD_TASK4'
            , execution_type => 'TEST EXECUTE'
            , execution_name => 'SQL_TRIAL_1349293404432'
            , execution_desc => 'execute in prd'
            , execution_params => dbms_advisor.arglist('LOCAL_TIME_LIMIT', '300', 'TIME_LIMIT', 'UNLIMITED', 'DATABASE_LINK', 'TTPSEPRD.MYCOMPA.COM')
                                  ); 
end;

In step 3, I executed the SQLs in the 11g ttpsefh database:

begin 
  dbms_sqlpa.execute_analysis_task(task_name => 'TTPSEPRD_TASK4'
                                 , execution_type => 'TEST EXECUTE'
                                 , execution_name => 'SQL_TRIAL_1349294911119'
                                 , execution_desc => 'in ttpsefh'
                                 , execution_params => dbms_advisor.arglist('LOCAL_TIME_LIMIT', '300', 'TIME_LIMIT', 'UNLIMITED', 'DATABASE_LINK', 'TTPSEFH.MYCOMPA.COM')
                                  ); 
end;

It should be noted that in order to execute step 3 and 4, a database link should be valid from dbahrgrd to ttpsefhd and ttpseprd, respectively. In addition, the connecting user should be granted ADVISOR role and the privillege to execute SYS.DBMS_SQLPA procedure.

In step 4, I did comparsizon based on Buffer Gets:

begin 
  dbms_sqlpa.execute_analysis_task(task_name => 'TTPSEPRD_TASK4'
                                 , execution_type => 'compare performance'
                                 , execution_name => 'COMPARE_1349297988711'
                                 , execution_params => dbms_advisor.arglist('comparison_metric', 'BUFFER_GETS', 'execution_name1', 
                                           'SQL_TRIAL_1349293404432', 'execution_name2', 'SQL_TRIAL_1349294911119', 'TIME_LIMIT', 'UNLIMITED')
                                  ); 
end;

At last,  come to the exciting part - step 5. It took just one click to view the result. The following is a screenshot of the overall report.

It can be seen that there are Improvement Impact 21% and Regression Impact -2% in the 11g environment. By click the -2% link, we can check in detail about the regressed SQL. In this case only one SQL found and actually the plan was same essentially. So no real regression at all. I did similar tests on other three categories of SQLs. With these experiments, I am very confident that the upgrade will be successful at least in the area of SQL performance.