In todays post, we explain how to create SQL Plan Analyzer using API ( we could use OEM as well )
SQLPA is very useful to analyze various performance data before and after a change for example an upgrade or an addition of index etc.
The broad overview of steps would be:
1. Create User for SPA on 10g:
2. Run the SQL Qs on 10g Database:
3. Create SQL Tuning set:
4. Start the capture using cursor (We can do it with AWR as well )
5. To run the Advisor on SQL tuning sets owned by other users, you must have the ADMINISTER ANY SQL TUNING SET privilege.
6. Create Staging table on 10g database used for storing SQL Tuning Sets as SPA_TRIAL.
7. Use PACK_STGTAB_SQLSET procedure to export SQL tuning sets into the staging table.
8. Export and import the Table to 11g database
9. Copy and Import onto 11g database.
10. Use the UNPACK_STGTAB_SQLSET procedure to import SQL Tuning Sets from the staging table
11. Create SPA and associate this with STS ( Imported earlier ):
12. Create a public database link from 11g to 10g:
13. Test Execution in 10g using DB_Link to 10g database from 11g DB
14. Test Execution in 11g locally for same tuning set
15. Execute the Comparison report based on some metric (ex Elapsed_time)
16. Generate the SPA Report
Detail Steps are below: Please note that we could use this
1. Create User for SPA on 10g:
SQL> create user spa_spatrial identified by XXXX default tablespace users;
User created.
SQL> grant connect,resource,dba to spa_spatrial;
Grant succeeded.
SQL> grant create session, create any table to spa_spatrial;
Grant succeeded.
2. Run the SQL Qs on 10g Database:
SQL> select inst_id, sid, serial# from gv$session where username='USER01';
INST_ID SID SERIAL#
---------- ---------- ----------
1 249 39887
1 254 49671
1 272 25686
1 255 41876
1 236 39118
3. Create Tuning set:
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'SPATrial01_vij',
description => 'SQL tuning set for 10g Processing Trial - Vij');
END;
/
SQL> BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'SPATrial01_vij',
description => 'SQL tuning set for 10g Processing Trial - Vij');
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
4. Start the capture using cursor, we could using other options
EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(sqlset_name => 'SPATrial01_vij', time_limit => 3600, repeat_interval => 300);
PL/SQL procedure successfully completed.
SQL> select name, owner, statement_count from dba_sqlset;
NAME OWNER STATEMENT_COUNT
------------------------------ ------------------------------ ---------------
SPATrial01_vij SYS 451
SPATrial01 SYS 426
5. To run the Advisor on SQL tuning sets owned by other users, you must have the ADMINISTER ANY SQL TUNING SET privilege.
SQL> grant ADMINISTER ANY SQL TUNING SET to SPA_spatrial;
Grant succeeded.
6. Create Staging table on 10g database used for storing SQL Tuning Sets as SPA_spatrial.
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => 'STSTAB' );
END;
/
SQL> sho user
USER is "SPA_spatrial"
SQL>
SQL> BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => 'STSTAB' );
END;
/ 2 3 4
PL/SQL procedure successfully completed.
7. Use PACK_STGTAB_SQLSET procedure to export SQL tuning sets into the staging table.
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'SPATrial01_vij',
staging_table_name => 'STSTAB',
sqlset_owner => 'SYS');
END;
/
SQL> sho user
USER is "SPA_spatrial"
SQL> BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'SPATrial01_vij',
staging_table_name => 'STSTAB',
sqlset_owner => 'SYS');
END;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
Verify:
SQL> SQL> select count(*) from STSTAB;
COUNT(*)
----------
451
8. Export and import the Table to 11g database
expdp parfile=SQL_TUNINGSET_EXP.par
9. Copy and Import onto 11g database.
10. Use the UNPACK_STGTAB_SQLSET procedure to import SQL Tuning Sets from the staging table
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => 'SPATrial01_vij',
replace => TRUE,
staging_table_name => 'STSTAB');
END;
/
SQL> sho user
USER is "SYS"
SQL> BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => 'SPATrial01_vij',
replace => TRUE,
staging_table_name => 'STSTAB');
END;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
Verify on 11g:
SQL> SQL> select count(*) from STSTAB;
COUNT(*)
----------
451
SQL>
SQL> select name, owner, statement_count from dba_sqlset;
NAME OWNER STATEMENT_COUNT
------------------------------ ------------------------------ ---------------
SPATrial01 SYS 426
SPATrial01_vij SYS 451
11. Create SPA and associate this with STS ( Imported earlier ):
SQL> VARIABLE t_name VARCHAR2(100);
SQL> EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SPATrial01_vij', task_name => 'spatrial_spa1');
PL/SQL procedure successfully completed.
12. Create a public database link from 11g to 10g:
create public database link SPA_11G_10G connect to SPA_spatrial identified by SPA_spatrial using
'(DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = yes) (FAILOVER = on) (ADDRESS = (PROTOCOL = TCP)(HOST = pracdba.blog.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = pracdba2.blog.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRAC_PRIM) (FAILOVER_MODE= (TYPE=SESSION) (METHOD=BASIC))))';
SQL> create public database link SPA_11G_10G connect to SPA_spatrial identified by SPA_spatrial using
'(DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = yes) (FAILOVER = on) (ADDRESS = (PROTOCOL = TCP)(HOST = pracdba.blog.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = pracdba2.blog.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TSE1DWHS_PRIM) (FAILOVER_MODE= (TYPE=SESSION) (METHOD=BASIC))))'; 2
Database link created.
13. Test Execution in 10g
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'spatrial_spa1',
execution_type => 'TEST EXECUTE',
execution_name => 'spatrial_10g',
execution_params => dbms_advisor.arglist('DATABASE_LINK', 'SPA_11G_10G'));
end;
/
SQL> grant execute on SYS.DBMS_SQLPA to SPA_spatrial;
Grant succeeded.
SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'spatrial_spa1',
execution_type => 'TEST EXECUTE',
execution_name => 'spatrial_10g',
execution_params => dbms_advisor.arglist('DATABASE_LINK', 'SPA_11G_10G'));
end; 2 3 4 5 6 7
8 /
14. Test Execution in 11g
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'spatrial_spa1',
execution_type => 'TEST EXECUTE',
execution_name => 'spatrial_11g');
end;
/
SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'spatrial_spa1',
execution_type => 'TEST EXECUTE',
execution_name => 'spatrial_11g');
end;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
15. Execute the Comparison report
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'spatrial_spa1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1', 'spatrial_10g', 'execution_name2', 'spatrial_11g', 'comparison_metric', 'elapsed_time') );
end;
/
SQL> begin
2 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
3 task_name => 'spatrial_spa1',
4 execution_type => 'COMPARE PERFORMANCE',
5 execution_name => 'Compare_elapsed_time',
6 execution_params => dbms_advisor.arglist('execution_name1', 'spatrial_10g', 'execution_name2', 'spatrial_11g', 'comparison_metric', 'elapsed_time') );
7 end;
8 /
PL/SQL procedure successfully completed.
16. Generate the SPA Report
set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off
spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task('spatrial_spa1', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time') FROM dual;
spool off
Building Pre-Upgrade ( 10g ) SQL Trial from 11g database:
Sample report shows the performance differences between 10g and 11g.
Limitations:
The only limitation i found was with Unsupported SQLs, We cannot view any performance data for these. Working to get around this.
