–Test version: 11.1.0.6
–
–create an analysis task
–
–Use the following procedures to create a sql tuning set
– populate the tuning set from the cursor cache
DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => ‘my_sts’,
description => ‘Sql tunning set for SPA’);
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
‘parsing_schema_name <> ”SYS” ‘,
NULL, NULL, NULL, NULL, 1, NULL,
‘ALL’)) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => ‘my_sts’,
populate_cursor => cur);
END;
/
–show sql tune set contents
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘my_sts’));
–create analysis task
VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => ‘my_sts’,task_name => ‘my_spa_task’);
–Also you can create a analysis task by giving sql_text
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sql_text => ’select * from t2 where object_id=990′,task_name => ’single_spa_task’);
–create a pre-change sql trail
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => ‘my_spa_task’, execution_type => ‘TEST EXECUTE’, execution_name => ‘my_exec_BEFORE_change’);
–perform changes on the test system
SYS@ORA11G>alter system set memory_target=140m;
T@ORA11G>drop index t2_oid;
…
–create a post-change sql trial
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => ‘my_spa_task’, execution_type => ‘TEST EXECUTE’, execution_name => ‘my_exec_AFTER_change’);
–compare sql trials
–analysis
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(‘execution_name1′,’my_exec_BEFORE_change’,'execution_name2′,’my_exec_AFTER_change’,'comparison_metric’, ‘buffer_gets’));
–report
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
REP
————————————————————————————————————-
———-
General Information
———————————————————————————————
Task Information: Workload Information:
——————————————— ———————————————
Task Name : my_spa_task SQL Tuning Set Name : my_sts
Task Owner : SYS SQL Tuning Set Owner : SYS
Description : Total SQL Statement Count : 17
Execution Information:
———————————————————————————————
Execution Name : my_exec_compare Started : 12/12/2008 15:52:51
Execution Type : COMPARE PERFORMANCE Last Updated : 12/12/2008 15:52:51
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 8
Analysis Information:
———————————————————————————————
Comparison Metric: BUFFER_GETS
——————
Workload Impact Threshold: 1%
————————–
SQL Impact Threshold: 1%
———————-
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
Description : Description :
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 12/12/2008 15:43:56 Started : 12/12/2008 15:46:02
Last Updated : 12/12/2008 15:43:58 Last Updated : 12/12/2008 15:46:03
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Number of Errors : 1 Number of Errors : 2
Report Summary
———————————————————————————————
Projected Workload Change Impact:
——————————————-
Overall Impact : -3.57%
Improvement Impact : 0%
Regression Impact : -3.57%
SQL Statement Count
——————————————-
SQL Category SQL Count Plan Change Count
Overall 17 2
Regressed 2 2
Unchanged 7 0
with Errors 8 0
Projected Workload Performance Distribution
————————————————–
——————————————————————–
| | Cumulative Perf. | | Cumulative Perf. | |
| Bucket | Before Change | (%) | After Change | (%) |
——————————————————————–
| < = 1 | 0 | 0% | 0 | 0% |
| < = 4 | 9 | .11% | 6 | .07% |
| < = 32 | 31 | .38% | 0 | 0% |
| < = 256 | 328 | 4.04% | 652 | 7.75% |
| < = 8192 | 7756 | 95.47% | 7756 | 92.18% |
——————————————————————–
Single SQL Statement Execution Count Distribution
——————————————————-
————————————————————-
| | SQL Count | | SQL Count | |
| Bucket | Before Change | (%) | After Change | (%) |
————————————————————-
| < = 1 | 2 | 11.76% | 2 | 11.76% |
| < = 4 | 3 | 17.65% | 2 | 11.76% |
| < = 32 | 1 | 5.88% | 0 | 0% |
| < = 256 | 2 | 11.76% | 4 | 23.53% |
| < = 8192 | 1 | 5.88% | 1 | 5.88% |
————————————————————-
SQL Statements Sorted by their Absolute Value of Change Impact on the Workload
————————————————————————————–
———————————————————————————————————
| | | Impact on | Metric | Metric | Impact | % Workload | % Workload | Plan |
| object_id | sql_id | Workload | Before | After | on SQL | Before | After | Change |
———————————————————————————————————
| 49 | 2ydjxgd87t9hk | -1.96% | 3 | 162 | -5300% | .04% | 1.93% | y |
| 47 | 94dwfa8yd87kw | -1.61% | 31 | 162 | -422.58% | .38% | 1.93% | y |
———————————————————————————————————
SQL Statements with Errors Sorted by their object_id (8)
————————————————————
——————————————————————————–
| object_id | sql_id | Error Message |
——————————————————————————–
| 36 | 572674pu6mra0 | Type of SQL statement not supported. |
| 37 | 8qhjcm5uv4j85 | Type of SQL statement not supported. |
| 38 | 2jpf0qbj9vxq3 | Type of SQL statement not supported. |
| 40 | g4y6nw3tts7cc | Type of SQL statement not supported. |
| 43 | 36s7502m471xz | Error in execution ‘my_exec_AFTER_change’: |
| | | ORA-01760: illegal argument for function |
| 46 | ccuwpvmn6rp9t | Error in execution ‘my_exec_AFTER_change’: |
| | | ORA-00907: missing right parenthesis |
| 51 | bsaf69ghqw2ja | Type of SQL statement not supported. |
| 52 | caj558xbnmytz | Type of SQL statement not supported. |
——————————————————————————–
———————————————————————————————
–find out the sql
SYS@ORA11G>SELECT sql_text FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘my_sts’)) where sql_id=’2ydjxgd87t9hk’;
SQL_TEXT
———————————————————————————————————–
———-
select count(*) from t2 where object_id<1000
SYS@ORA11G>SELECT sql_text FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘my_sts’)) where sql_id=’94dwfa8yd87kw’;
SQL_TEXT
———————————————————————————————————
———-
select count(*) from t2
–tune regressed sql statement,reference SQL Tuning Advisor and SQL plan baselines procedures
–For sql ‘94dwfa8yd87kw’, the sql plan before change is
T@ORA11G>select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
————————————————————————–
————
SQL_ID 94dwfa8yd87kw, child number 0
————————————-
select count(*) from t2
Plan hash value: 3659760998
————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
————————————————————————
| 0 | SELECT STATEMENT | | | 9 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T2_OID | 11892 | 9 (0)| 00:00:01 |
————————————————————————
–the sql plan after change, index is deleted, is
T@ORA11G>select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
——————————————————————-
————
SQL_ID 2hkvgb0cwhvvn, child number 0
————————————-
select count(*) from t2
Plan hash value: 3321871023
——————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | SELECT STATEMENT | | | 48 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T2 | 11892 | 48 (0)| 00:00:01 |
——————————————————————-
–views related to SPA
DBA_ADVISOR_TASKS
DBA_ADVISOR_EXECUTIONS
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_SQLPLANS
DBA_ADVISOR_SQLSTATS
V$ADVISOR_PROGRESS
Filed under: Performance Management | Tagged: 11g, scratch
