Oracle 11g database SPA example

Oracle 11g launched the new SQL performance Analyze (SQL performance Analyze), which is now widely used in upgrade and migration scenarios. Of course, some other scenarios can also be considered for use, for example (optimizer parameter modification, IO subsystem change, etc.), this function can provide DBA with detailed information about SQL statement performance, such as statistics before and after execution, statements that improve or reduce performance are mainly used to determine whether the SQL execution performance degrades after the environment changes.

The main implementation steps of SPA are as follows:
1. Capture SQL loads in the source environment and generate SQLSET

Exec dbms_sqltune.create_sqlset ('spa _ sqlset ');

Collect SQLSET from cursor cache:

Cat sts_add.sh
Date
Sqlplus-s spa/spa_jzdb3 <EOF1
DECLARE
Cur sys_refcursor;
BEGIN
OPEN cur
SELECT value (P)
From table (dbms_sqltune.select_cursor_cache ('parsing _ schema_name not in (''sys '') and module not in (''pl/SQL Developer '') and force_matching_signature not in (select force_matching_signature from DBA_SQLSET_STATEMENTS) ', NULL, 1, NULL, 'all') p;
Dbms_sqltune.load_sqlset ('spa _ sqlset', cur, load_option => 'merge ');
CLOSE cur;
END;
/
Collect SQLSET from awr snapshot:

Declare
Cur sys_refcursor;
Begin
Open cur for select value (P) from table (dbms_sqltune.select_workload_repository (77589,78343) p;
Dbms_sqltune.load_sqlset (sqlset_name => 'spa _ sqlset', populate_cursor => cur, load_option => 'merge', update_option => 'accumulate ');
Close cur;
End;
    /
Collect SQLSET from awr baseline
Collect SQLSET from another SQL set
From 10046 trace file

2. Import SQLSET to the intermediate table

Create a stage table at the source end
Exec DBMS_SQLTUNE.create_stgtab_sqlset (table_name => 'sqlset11 _ tab', schema_name => 'Pa', tablespace_name => 'users ');

Package sqlset to stage table:
Exec merge (sqlset_name => 'spa _ sqlset', sqlset_owner => 'SPA', staging_table_name => 'sqlset11 _ tab', staging_schema_owner => 'spa ');
3. Import the secondary table to the new database environment, and extract the stage table data to SQLSET.

Impdp spa/spa_jzdb3 directory = back dumpfile = sqlset11_tab.dmp logfile = sqlset11_tab.log table_exists_action = replace

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (SQLSET_NAME => 'spa _ sqlset', SQLSET_OWNER => 'Pa', REPLACE => TRUE, STAGING_TABLE_NAME => 'sqlset11 _ tab ', STAGING_SCHEMA_OWNER => 'spa'); decompress the stage table sqlset11_tab to sqlset.
4. Create a SPA Task. Create a trail of 10 GB and generate a trail of 11 GB in 11 GB.

Create a SPA task:
Var tname varchar2 (30 );
Var sname varchar2 (30 );
Exec: sname: = 'spa _ sqlset ';
Exec: tname: = 'spa _ task ';
Exec: tname: = DBMS_SQLPA.CREATE_ANALYSIS_TASK (sqlset_name =>: sname, task_name =>: tname );

Generate oracle 10g trail
Begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (
Task_name => 'spa _ task ',
Execution_type => 'convert sqlset ',
Execution_name => 'convert _ 10g ');
End;
/

The trail generated in the target database may take a long time to execute SQL statements in the target database.
Begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (
Task_name => 'spa _ task ',
Execution_type => 'test EXECUTE ',
Execution_name => 'exec _ 11g ');
End;
/
5. Execute the comparison task and generate the SPA report.

Begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (
Task_name => 'spa _ task ',
Execution_type => 'Compare PERFORMANCE ',
Execution_name => 'Compare _ elapsed_time ',
Execution_params => dbms_advisor.arglist ('execution _ name1', 'convert _ 10g', 'execution _ name2', 'exec _ 11g', 'Comparison _ metric ', 'elapsed _ time '));
End;
/

Begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (
Task_name => 'spa _ task ',
Execution_type => 'Compare PERFORMANCE ',
Execution_name => 'Compare _ CPU_time ',
Execution_params => dbms_advisor.arglist ('execution _ name1', 'convert _ 10g', 'execution _ name2', 'exec _ 11g', 'Comparison _ metric ', 'CPU _ time '));
End;
/

Begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (
Task_name => 'spa _ task ',
Execution_type => 'Compare PERFORMANCE ',
Execution_name => 'Compare _ BUFFER_GETS_time ',
Execution_params => dbms_advisor.arglist ('execution _ name1', 'convert _ 10g', 'execution _ name2', 'exec _ 11g', 'Comparison _ metric ', 'buffer _ gets '));
End;
/
Generate a SPA report:

Set trimspool on
Set trim on
Set pages 0
Sets long 999999999
Set linesize 1000
Alter session set events = '31156 trace name context forever, LEVEL 0X400 ';
Set lines 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED

Spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task ('spa _ task', 'HTML ', 'all', 'all', top_ SQL => 300, execution_name => 'Compare _ elapsed_time') FROM dual;
Spool off;

Spool spa_report_CPU_time.html
SELECT dbms_sqlpa.report_analysis_task ('spa _ task', 'HTML ', 'all', 'all', top_ SQL => 300, execution_name => 'Compare _ CPU_time') FROM dual;
Spool off;

Spool spa_report_buffer_time.html
SELECT dbms_sqlpa.report_analysis_task ('spa _ task', 'HTML ', 'all', 'all', top_ SQL => 300, execution_name => 'Compare _ BUFFER_GETS_time') FROM dual;
Spool off;

Spool changed_plans.html
SELECT dbms_sqlpa.report_analysis_task ('spa _ task', 'HTML ', 'changed _ PLANS', 'all', top_ SQL => 300) FROM dual;
Spool off;

Spool spa_report_errors.html
SELECT dbms_sqlpa.report_analysis_task ('spa _ task', 'HTML ', 'errors', 'summary ') FROM dual;
Spool off;

Spool spa_report_unsupport.html
SELECT dbms_sqlpa.report_analysis_task ('spa _ task', 'HTML ', 'unororted', 'all') FROM dual;
Spool off;
/
6. Analyze SQL statements with degraded performance

Common SPA scripts:

-- Check the running status of the process running the SPA
Select sid, TASK_ID, SOFAR, TOTALWORK, START_TIME, START_TIME + (SYSDATE-START_TIME)/SOFAR * TOTALWORK EST_END_TIME
From v $ ADVISOR_PROGRESS
Where sofar <> TOTALWORK
And sofar <> 0
Order by 3;

If cancel is dropped during execution, the following error occurs when the sqlset operation is performed again:
SQL> EXEC DBMS_SQLTUNE.DELETE_SQLSET (sqlset_name => 'spa _ sqlset', basic_filter => 'executions <3 ');
BEGIN DBMS_SQLTUNE.DELETE_SQLSET (sqlset_name => 'spa _ sqlset', basic_filter => 'executions <1 '); END;

*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "SPA_SQLSET" owned by user "SPA" is active.
ORA-06512: at "SYS. DBMS_SQLTUNE", line 5712
ORA-06512: at line 1

Locate the task and delete it.
SQL> SELECT TASK_ID, OWNER, TASK_NAME FROM DBA_ADVISOR_TASKS WHERE TASK_NAME LIKE 'spa % 'ORDER BY 1;

TASK_ID OWNER TASK_NAME
--------------------------------------------------------------------------------------------------------------
1235 SPA SPA_TASK

SQL> EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK ('spa _ task ');

PL/SQL procedure successfully completed.

Delete sqlset
EXEC DBMS_SQLTUNE.DROP_SQLSET ('spa _ sqlset', 'spa ');

If there is too much SQL information, we need to capture the SQL statement that needs to be analyzed as soon as possible, such as the number of executions, the user who executes the SQL statement, the execution module, and bind variables for filtering:

Delete SQL statements without variable binding
Create index IDX_SQLSET11_TAB_F_S ON SQLSET11_TAB (FORCE_MATCHING_SIGNATURE, SQL _ID) PARALLEL 8;
BEGIN
For x in (SELECT FORCE_MATCHING_SIGNATURE, MIN (SQL _ID) SQL _ID FROM SQLSET11_TAB
Group by FORCE_MATCHING_SIGNATURE
Having count (*)> 1)
LOOP
Delete from SQLSET11_TAB WHERE FORCE_MATCHING_SIGNATURE = X. FORCE_MATCHING_SIGNATURE AND SQL _ID <> X. SQL _ID;
COMMIT;
End loop;
END;
/

Delete SQL statements that are executed less than 10 times
Exec dbms_sqltune.delete_sqlset (sqlset_name => 'spa _ sqlset', basic_filter => 'executions <10', sqlset_owner => 'spa ');

Delete SQL statements from unspecified users
Delete from spa. sqlset11_tab where PARSING_SCHEMA_NAME not in ('accounting', 'SPS ');

Deletes the SQL statement of a specified module, such as PL/SQL Developer.
Delete from spa. sqlset11_tab where MODULE = 'Pl/SQL developer ';