Since between Migrations we were required to compare sql performance with different optimizer modes . Here old tool SPA helped us .
Documenting handy steps of myself and will help others Too .
The steps involved in the SQL PERFORMANCE Analyzer are
1) Create the Sql Tuning Set (STS).
2) Create a task to run Sql Performance Analyzer.
3) Execute Before Change TEST EXECUTE (Pre-Change SQL Trial).
Make a change that needs to tested.
4) Execute After Change TEST EXECUTE (Post-Change SQL Trial).
5) Comparing SQL Trials.
6) Generate Compare report.
You can use the SQL Performance Analyzer to analyze the SQL performance impact of any type of system change. Examples of common system changes include:
•Database upgrades
•Configuration changes to the operating system, hardware, or database
•Database initialization parameter changes
•Schema changes, such as adding new indexes or materialized views
•Gathering optimizer statistics
•SQL tuning actions, such as creating SQL profiles
Check what metrics SPA compare performance of :
SQL> SELECT metric_name FROM v$sqlpa_metric;
METRIC_NAME
-------------------------
PARSE_TIME
ELAPSED_TIME
CPU_TIME
USER_IO_TIME
BUFFER_GETS
DISK_READS
DIRECT_WRITES
OPTIMIZER_COST
IO_INTERCONNECT_BYTES
9 rows selected.
Report generation Options :
DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name IN VARCHAR2,
type IN VARCHAR2 := 'text',
level IN VARCHAR2 := 'typical',
section IN VARCHAR2 := 'summary',
object_id IN NUMBER := NULL,
top_sql IN NUMBER := 100,
task_owner IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL)
RETURN CLOB;
| Level | Description |
| BASIC | Same as typical |
| TYPICAL (default) | Information about all statements |
| ALL | Details of all SQL |
| IMPROVED | Only improved SQL |
| REGRESSED | Only regressed SQL |
| CHANGED | SQL with changed performance |
| UNCHANGED | Opposite of CHANGED |
| CHANGED_PLANS | Only SQL with plan changes |
| UNCHANGED_PLANS | Opposite of above |
| ERRORS | SQL with errors only |
SPA Steps :
1) create a STS
exec DBMS_SQLTUNE.create_sqlset (sqlset_name=> '94rn6s4ba24wn') ;
DECLARE
cursor1 DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cursor1 FOR SELECT VALUE(p)
FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''94rn6s4ba24wn''')) p;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS01', populate_cursor => cursor1);
END;
/
2) create a SPA task and associate the STS with it
DECLARE
task_name VARCHAR2(64);
sts_task VARCHAR2(64);
BEGIN
task_name := 'Task01';
sts_task:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'STS01', task_name => task_name, description => 'Task for sql_id 94rn6s4ba24wn');
END;
/
3) Execute CONVERT SQLSET Before Change TEST EXECUTE
SQL> EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK('Task01');
SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'CONVERT SQLSET', execution_name => 'convert_sqlset');
Generate the report with a SQL statement like this:
SQL> SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off
SQL> spool task01_before_change.html
SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;
SQL> spool off
result file that with this particular execution all different plans are displayed, while in compare performance objective SPA is taking only one plan (the one it is currently parsing with information currently available). In any case comparing all plans of same sql_id would provide very complex reports that would probably be not usable…
4) Create Tets execute task ( pre and post changes )
SQL> EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK('Task01');
SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'TEST EXECUTE', execution_name => 'before_change');
SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off
SQL> spool task01_before_change.html
SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;
SQL> spool off
SQL> ALTER SESSION SET optimizer_features_enable='11.2.0.2';
SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'TEST EXECUTE', execution_name => 'after_change');
SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off
SQL> spool task01_after_change.html
SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;
SQL> spool off
5) Run Compare Performance :
SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'COMPARE PERFORMANCE', execution_name => 'compare_performance');
PL/SQL PROCEDURE successfully completed.
Or
BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :v_task,
execution_type => 'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'before_change',
'execution_name2',
'after_change')
);
END;
/
SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off
SQL> spool task01_compare_performance.html
SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;
SQL> spool off
Sample Compare report is published below :
https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/02/spa-sample-compare-report.html
Views :
select sql_id, plan_hash_value, buffer_gets, elapsed_time, substr(sql_text,1, 30
) text, executions from dba_sqlset_statements
where sqlset_name = :sts_name
order by sql_id, plan_hash_value;
SQL> SET lines 200
SQL> col description FOR a30
SQL> SELECT * FROM dba_sqlset;
SELECT task_name, status
FROM dba_advisor_tasks
WHERE task_name = :tname;
select execution_name,status, execution_end
from DBA_ADVISOR_EXECUTIONS where task_name='my_sqlpa_demo_task';
SQL> SELECT execution_name, execution_type, TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,
TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end, status
FROM dba_advisor_executions
WHERE task_name='Task01';
SQL> SELECT execution_name, execution_type, TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,
TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end, advisor_name, status
FROM dba_advisor_executions
WHERE task_name='Task01';
SQL> SELECT last_execution,execution_type,TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,
TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end,status
FROM dba_advisor_tasks
WHERE task_name='Task01';
SQL> col EXECUTION_NAME FOR a15
SQL> SELECT execution_name, plan_hash_value, parse_time, elapsed_time, cpu_time,user_io_time,buffer_gets,disk_reads,direct_writes,
physical_read_bytes,physical_write_bytes,rows_processed
FROM dba_advisor_sqlstats
WHERE task_name='Task01';
SQL> col PLAN FOR a140
SQL> SET pages 500
SQL> SELECT p.plan_id, RPAD('(' || p.ID || ' ' || NVL(p.parent_id,'0') || ')',8) || '|' ||
RPAD(LPAD (' ', 2*p.DEPTH) || p.operation || ' ' || p.options,40,'.') ||
NVL2(p.object_owner||p.object_name, '(' || p.object_owner|| '.' || p.object_name || ') ', '') ||
'Cost:' || p.COST || ' ' || NVL2(p.bytes||p.CARDINALITY,'(' || p.bytes || ' bytes, ' || p.CARDINALITY || ' rows)','') || ' ' ||
NVL2(p.partition_id || p.partition_start || p.partition_stop,'PId:' || p.partition_id || ' PStart:' ||
p.partition_start || ' PStop:' || p.partition_stop,'') ||
'io cost=' || p.io_cost || ',cpu_cost=' || p.cpu_cost AS PLAN
FROM dba_advisor_sqlplans p
WHERE task_name='Task01'
oder BY p.plan_id, p.id, p.parent_id;
SQL> col message FOR a80
SQL> col FINDING_NAME FOR a30
SQL> col EXECUTION_NAME FOR a20
SQL> SELECT execution_name,finding_name,TYPE,impact,message FROM dba_advisor_findings WHERE task_name='Task01';
Reference :
SQL Performance Analyzer Example (Doc ID 455889.1)
https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/analyzing-database-changes-sql-performance.html#GUID-28869D5B-ECCD-4A89-8391-116AE4C6A7D4
