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
No comments:
Post a Comment