Mimicking Real Application Testing (RAT) with SQL Tuning Set

The idea is simple, testing of backup database itself with production workload and environment. There are cases when we do major changes in database, for example, changing system parameters, upgrading to newer versions or even adding new indexes. But how could we simulate the production environment in new born system so that we can sleep at night ensuring changes was not harmful!

Here, RAT comes into play. The basic idea is, capture workload from production system, replay it on new system and analyze impact and performance.

In 10g Release 2, I don't have whole RAT (only capture is available) - replay and performance analysis features are available only in 11g!



What I could do, create a SQL Tuning Set in production database for a specified duration (like workload capture) and then use this in new system to see how Tuning Advisor and Access Advisor suggest after the changes. New execution plans might be dramatically good or bad!

The step are -

1. Creating SQL Tuning Set at source/production db

BEGIN
dbms_sqltune.create_sqlset(sqlset_name => 'PROD-TS', sqlset_owner =>'SYSTEM');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name => 'CREATE_STS_PROD-TS',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE bf VARCHAR2(71);
                begin bf := q''#UPPER(PARSING_SCHEMA_NAME) = ''PROD'' AND ELAPSED_TIME >= 300000.0 #'';
                dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name=>''PROD-TS'', time_limit=>''600'',
                repeat_interval=>''5'',
                basic_filter=>bf, sqlset_owner=>''SYSTEM''); end;',
enabled => TRUE);
END;
/

2. Packing Tuning Set to an exportable format

BEGIN
   DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
         table_name => 'TAB_PROD_TS');
      
   DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
         sqlset_name => 'PROD-TS',
         staging_table_name => 'TAB_PROD_TS');
END;
/

3. Export from source db

# expdp system directory=dump_dir dumpfile=prod-ts-20091220.dmp tables=TAB_PROD_TS

4. Import into target db

impdp system directory=dump_dir dumpfile=prod-ts-20091220.dmp

5. Unpack the SQL Tuning Set

BEGIN
   DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
         sqlset_name => 'PROD-TS',
         sqlset_owner => '%',
         replace => TRUE,
         staging_table_name => 'TAB_PROD_TS');
        
END;
/

6. Run SQL Tuning Advisor and/or SQL Access Advisor to analyze query performance

We could easily do the analysis using EM on test database. This does not exactly fulfill the RAT things, still, something is always better than nothing!


***