Dear Readers,
SPA by example:
Step 1)
Get the STS name
Step 1)
Get the STS name
select count(*), sqlset_name from dba_sqlset_statements group by sqlset_name order by 2;
Step 2)
Create an analysis task
Create an analysis task
Command:
VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SQLT_WKLD_STS',task_name => 'my_spa_task');
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SQLT_WKLD_STS',task_name => 'my_spa_task');
Actual Execution:
SQL> VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SQLT_WKLD_STS',task_name => 'my_spa_task');SQL>
PL/SQL procedure successfully completed.
SQL>
You have options to configure the analyze task.
Step 3)
Create pre change sql trail task
Command:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_BEFORE_change');
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_BEFORE_change');
Actual output:
SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_BEFORE_change');> >
PL/SQL procedure successfully completed.
SQL>
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_BEFORE_change');> >
PL/SQL procedure successfully completed.
SQL>
I wanted to update my init param filesystemio_options from none to setall, to see the effect. I am using cooked filesystem to store my datafile.
12:40:16 SQL> sho parameter filesyste
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
12:47:33 SQL> sho parameter filesys
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string SETALL
12:47:46 SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
12:47:33 SQL> sho parameter filesys
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string SETALL
12:47:46 SQL>
Param changed and db bounced.
Step 4)
Create post change sql trail task
Command:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_AFTER_change');
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_AFTER_change');
Actual output:
12:47:46 SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_AFTER_change');12:48:29 > 12:48:29 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.00
12:48:34 SQL>
Step 5)
Compare the pre/post analysis task result
Command:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'COMPARE PERFORMANCE', -
execution_name => 'my_exec_compare', -
execution_params => dbms_advisor.arglist(-
'comparison_metric', 'elapsed_time'));
overall arglist supported:
elapsed_time (default), cpu_time, buffer_gets, disk_reads, direct_writes, optimizer_cost, and io_interconnect_bytes
execution_type => 'COMPARE PERFORMANCE', -
execution_name => 'my_exec_compare', -
execution_params => dbms_advisor.arglist(-
'comparison_metric', 'elapsed_time'));
overall arglist supported:
elapsed_time (default), cpu_time, buffer_gets, disk_reads, direct_writes, optimizer_cost, and io_interconnect_bytes
Actual output:
12:55:37 SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'COMPARE PERFORMANCE', -
execution_name => 'my_exec_compare', -
12:58:21 > 12:58:21 > 12:58:21 > execution_params => dbms_advisor.arglist(-
'comparison_metric', 'elapsed_time'));12:58:21 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26
12:58:22 SQL>
Step 6)
Report the result:
Active html format:
Report the result:
Active html format:
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool spa_active.html
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'my_spa_task',
type => 'active', section => 'all') FROM dual;
spool off
Text form:
VAR rep CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool spa_active.html
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'my_spa_task',
type => 'active', section => 'all') FROM dual;
spool off
Text form:
VAR rep CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep
Actual output:
12:59:55 SQL> 12:59:55 SQL> VAR rep CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep13:00:32 SQL> 13:00:32 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
13:00:32 SQL> 13:00:32 SQL>
General Information
---------------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : my_spa_task SQL Tuning Set Name : SQLT_WKLD_STS
Task Owner : SYS SQL Tuning Set Owner : SYS
Description : Total SQL Statement Count : 27
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : my_exec_compare Started : 08/15/2022 12:58:22
Execution Type : COMPARE PERFORMANCE Last Updated : 08/15/2022 12:58:22
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0
Number of Unsupported SQL : 14
Analysis Information:
---------------------------------------------------------------------------------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : my_exec_BEFORE_change Execution Name : my_exec_AFTER_change
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 08/15/2022 12:40:04 Started : 08/15/2022 12:48:30
Last Updated : 08/15/2022 12:40:07 Last Updated : 08/15/2022 12:48:34
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Number of Errors : 0 Number of Errors : 0
---------------------------------------------
Comparison Metric: ELAPSED_TIME
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Report Summary
---------------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
Overall Impact : -53.36%
Improvement Impact : 2.95%
Regression Impact : -56.31%
SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 27 0
Improved 2 0
Regressed 3 0
Unchanged 8 0
Unsupported 14 0
Top 13 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
| | | Impact on | Execution | Metric | Metric | Impact | Plan |
| object_id | sql_id | Workload | Frequency | Before | After | on SQL | Change |
--------------------------------------------------------------------------------------------
| 65 | 3rnkw340dt3mj | -51.07% | 12819 | 7 | 107 | -1428.57% | n |
| 59 | 06852xf36pjwr | -3.41% | 1 | 170068 | 255758 | -50.39% | n |
| 68 | 4krsqvh5cgrus | 1.94% | 1 | 482556 | 433917 | 10.08% | n |
| 82 | fcyayutm5frcc | -1.83% | 6576 | 9 | 16 | -77.78% | n |
| 60 | 0cwuxyv314wcg | 1.02% | 8508 | 9 | 6 | 33.33% | n |
| 70 | 5tq075cva3dga | -.62% | 7725 | 14 | 16 | -14.29% | n |
| 66 | 4g2g8zv8tr8vv | .51% | 12819 | 13 | 12 | 7.69% | n |
| 74 | a8ntu3081hfgw | .51% | 1277 | 20 | 10 | 50% | n |
| 69 | 5mz8u3b34u9gw | .5% | 12579 | 17 | 16 | 5.88% | n |
| 81 | f90zn75aphu4w | .35% | 1261 | 722 | 715 | .97% | n |
| 67 | 4hbzjyh4p336s | .15% | 1277 | 18 | 15 | 16.67% | n |
| 63 | 29rsy84cajnjd | 0% | 5094 | 14 | 14 | 0% | n |
| 77 | bswc46zum45tj | 0% | 12579 | 9 | 9 | 0% | n |
--------------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
13:00:33 SQL>
General Information
---------------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : my_spa_task SQL Tuning Set Name : SQLT_WKLD_STS
Task Owner : SYS SQL Tuning Set Owner : SYS
Description : Total SQL Statement Count : 27
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : my_exec_compare Started : 08/15/2022 12:58:22
Execution Type : COMPARE PERFORMANCE Last Updated : 08/15/2022 12:58:22
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0
Number of Unsupported SQL : 14
Analysis Information:
---------------------------------------------------------------------------------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : my_exec_BEFORE_change Execution Name : my_exec_AFTER_change
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 08/15/2022 12:40:04 Started : 08/15/2022 12:48:30
Last Updated : 08/15/2022 12:40:07 Last Updated : 08/15/2022 12:48:34
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Number of Errors : 0 Number of Errors : 0
---------------------------------------------
Comparison Metric: ELAPSED_TIME
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Report Summary
---------------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
Overall Impact : -53.36%
Improvement Impact : 2.95%
Regression Impact : -56.31%
SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 27 0
Improved 2 0
Regressed 3 0
Unchanged 8 0
Unsupported 14 0
Top 13 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
| | | Impact on | Execution | Metric | Metric | Impact | Plan |
| object_id | sql_id | Workload | Frequency | Before | After | on SQL | Change |
--------------------------------------------------------------------------------------------
| 65 | 3rnkw340dt3mj | -51.07% | 12819 | 7 | 107 | -1428.57% | n |
| 59 | 06852xf36pjwr | -3.41% | 1 | 170068 | 255758 | -50.39% | n |
| 68 | 4krsqvh5cgrus | 1.94% | 1 | 482556 | 433917 | 10.08% | n |
| 82 | fcyayutm5frcc | -1.83% | 6576 | 9 | 16 | -77.78% | n |
| 60 | 0cwuxyv314wcg | 1.02% | 8508 | 9 | 6 | 33.33% | n |
| 70 | 5tq075cva3dga | -.62% | 7725 | 14 | 16 | -14.29% | n |
| 66 | 4g2g8zv8tr8vv | .51% | 12819 | 13 | 12 | 7.69% | n |
| 74 | a8ntu3081hfgw | .51% | 1277 | 20 | 10 | 50% | n |
| 69 | 5mz8u3b34u9gw | .5% | 12579 | 17 | 16 | 5.88% | n |
| 81 | f90zn75aphu4w | .35% | 1261 | 722 | 715 | .97% | n |
| 67 | 4hbzjyh4p336s | .15% | 1277 | 18 | 15 | 16.67% | n |
| 63 | 29rsy84cajnjd | 0% | 5094 | 14 | 14 | 0% | n |
| 77 | bswc46zum45tj | 0% | 12579 | 9 | 9 | 0% | n |
--------------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
13:00:33 SQL>
SPA has lot of limitation like it cant do DDL, PDML limitation etc... this made in my test nearly 14/27 sql unsupported. Which means nearly 50% of the sql execution arent supported.
I was hoping the filesystemio option brings in postive benefit, but it wasnt.
As such SPA and STS are very stright forward. Can use it as part of performance engineering.
I was hoping the filesystemio option brings in postive benefit, but it wasnt.
As such SPA and STS are very stright forward. Can use it as part of performance engineering.
Thanks
