11g Real Application Testing – Part 2

Analyze the impact of change on SQL statements using the SQL Performance Analyzer

In the earlier post, we had discussed the Database Capture and Replay component of 11g Real Application Testing – (http://gavinsoorma.wordpress.com/2009/09/11/11g-real-application-testing-making-changes-with-confidence/)

We can also analyse the impact of upgrading from 10.2.0.4 to 11.1.0.6 at the SQL statement level by using the other component of 11g Real Application Testing which is called SQL Performance Analyzer or SPA.

We will use the following two SQL statements to create a SQL Tuning Set (STS) in the 10.2.0.4 environment. This STS is then transported to the 11g target environment and then we will use the GUI Database Control to replay the statements in the STS and generate a Performance Analysis report which will help us identify if any statements have improved or digressed in the changed environment.

SELECT /*+TEST_SPA*/ p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day;

SELECT /*+TEST_SPA*/ p.prod_category, t.week_ending_day, s.cust_id, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_category = ‘Peripherals and Accessories’
GROUP BY p.prod_category, t.week_ending_day, s.cust_id;

We are using a ‘tag’ TEST_SPA to help us easily identify these SQL statements in the shared pool.

Source 10.2.0.4 environment – Create and Export SQL Tuning Set

SQL> exec dbms_sqltune.drop_sqlset(‘SH_10204’);

PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.create_sqlset(‘SH_10204′,’Demo Workload to test SPA’);

PL/SQL procedure successfully completed.

SQL> DECLARE
2 stscur dbms_sqltune.sqlset_cursor;
3 BEGIN
4 OPEN stscur FOR
5 SELECT VALUE(P)
6 FROM TABLE(dbms_sqltune.select_cursor_cache(
7 ‘sql_text like ”SELECT /*+TEST_SPA%”’,
8 null, null, null, null, null, null, ‘ALL’)) P;
9 — populate the sqlset
10 dbms_sqltune.load_sqlset(:sts_name, stscur);
11 end;
12 /

PL/SQL procedure successfully completed.

SQL> select sql_id,plan_hash_value, substr(sql_text,1,40) text, executions
2 from dba_sqlset_statements
3 where sqlset_name =’SH_10204′
4 order by sql_id, plan_hash_value;

SQL_ID PLAN_HASH_VALUE TEXT EXECUTIONS
————- ————— ————————————————– ———-
5930m31ryy4b1 3901800458 SELECT /*+TEST_SPA*/ p.prod_category, t. 1
5tbr81fmrnyc0 1679814994 SELECT /*+TEST_SPA*/ p.prod_name, s.time 1

In order to transport the SQL tuning set that we have just created, we need to create a staging table to hold the contents of the STS and then we will use the export utility to generate a dump file which will then be copied to the target 11g environment.

SQL> execute DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name =>’SH_STS’);

PL/SQL procedure successfully completed.

SQL> execute DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => ‘SH_10204’,staging_table_name => ‘SH_STS’);

PL/SQL procedure successfully completed.

SQL> select count (*) from sh_sts;

COUNT(*)
———-
2

Read More …..