【SQL Performance Analyzer】Oracle 11g SQL Performance Analyzer feature使用

landf發表於2014-08-19
SQLORMOracle

一、 機器環境
資料庫伺服器環境
? 硬體環境：
兩臺虛擬機器
  虛擬機器名稱：racdb01(Oracle 11g RAC on Red Hat Enterprise Linux 5.4(Tikanga)
所在目錄：J:\Virtual Machines\11gRAC\racdb01
     J:\Virtual Machines\11gRAC\11gRAC\RAC1
     J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM
    
  硬碟：
J:\Virtual Machines\11gRAC\11gRAC\RAC1\rhel_disk.vmdk     25GB
J:\Virtual Machines\11gRAC\racdb01\rhel_disk02.vmdk      20GB
  J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM\ShareDiskOCR.vmdk  1GB
 J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM\ShareDiskData01.vmdk  2GB
  J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM\ShareDiskData02.vmdk  2GB
J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM\ShareDiskFlash.vmdk  4GB
  記憶體：
  2.2GB
  CPU：

  虛擬機器名稱：racdb02(Oracle 11g RAC on Red Hat Enterprise Linux 5.4(Tikanga)
所在目錄：J:\Virtual Machines\11gRAC\racdb02
     J:\Virtual Machines\11gRAC\11gRAC\RAC2
     J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM

  硬碟：
J:\Virtual Machines\11gRAC\11gRAC\RAC2\rhel_disk.vmdk     25GB
J:\Virtual Machines\11gRAC\racdb02\rhel_disk02.vmdk      20GB
J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM\ShareDiskOCR.vmdk  1GB
J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM\ShareDiskData01.vmdk  2GB
J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM\ShareDiskData02.vmdk  2GB
J:\Virtual Machines\11gRAC\11gRAC\SharedDiskASM\ShareDiskFlash.vmdk  4GB
  記憶體：
  2.2GB
  CPU：

? 軟體環境：
作業系統：Red Hat Enterprise Linux 5.4(Tikanga)
Oracle：11gR2 11.2.0.4.0 RAC
資料庫名稱: racdb
RAC叢集兩節點名稱：racdb01，racdb02

? 網路環境：
故障主機所在的公網和私網資訊如下：
機器名 IP地址 地址名稱 備註
racdb01 192.168.1.111 racdb01 公共地址
 192.168.1.112 racdb01-vip 虛擬地址
 10.1.1.11 racdb01-priv 心跳線地址
racdb02 192.168.1.113 racdb02 公共地址
 192.168.1.114 racdb02-vip 虛擬地址
 10.1.1.12 racdb02-priv 心跳線地址
Oracle RAC叢集 192.168.1.117
192.168.1.118
192.168.1.119 racdb-cluster-scan.landf.com 叢集服務地址

二、SQL Performance Analyzer介紹
Oracle Database 11g 引入了 SQL 效能分析器；使用該工具可以準確地評估更改對組成工作量的 SQL 語句的影響。SQL 效能分析器可幫助預測潛在的更改對 SQL 查詢工作量的效能影響。這種功能可向 DBA 提供有關 SQL 語句效能的詳細資訊，例如，執行前後的統計資訊，提高或降低效能的SQL語句。這樣一來，您就可以執行諸如以下操作的操作：在測試環境中進行更改，以確定資料庫升級是否會改進工作量效能。
1. 11g 的新增功能
2. 目標使用者：DBA、QA、應用程式開發人員
3. 幫助預測系統更改對 SQL 工作量響應時間的影響
4. 建立不同版本的 SQL 工作量效能（即 SQL 執行計劃和執行統計資訊）
5. 以序列方式執行 SQL（不考慮併發性）
6. 分析效能差異
7. 提供對單個 SQL 的細粒度效能分析
8. 與 SQL 優化指導整合在一起以優化迴歸

系統變更會影響SQL的執行計劃，比如資料庫升級，優化引數調整，表結構變更，索引維護（建立，刪除，重建），統計資訊收集等。
在Oracle Database 11g之前的版本號中，我必須捕獲全部SQL語句，通過跟蹤執行這些語句，然後得到執行計劃———這是一項極其耗時又極易出錯的任務。新版本號中，我們不須要再那樣做了，我改用很easy而有效的SQL Performance Analyzer。
Oracle為了預防系統變更所引起的不良後果，特推出SPA，SPA做為RAT(Real Application Test)的一部分，可以消除一些不可預知的錯誤，Oracle提供dbms_sqlpa包來實現系統變更前後的效能分析，SPA主要應用在以下方面：
■ Database upgrade
■ Configuration changes to the operating system, hardware, or database
■ Database initialization parameter changes
■ Schema changes, for example, adding new indexes or materialized views
■ Gathering optimizer statistics
■ SQL tuning actions, for example, creating SQL profiles
---使用場景
1.資料庫升級
2.實施優化建議
3.更改方案
4.收集統計資訊
5.更改資料庫引數
6.更改作業系統和硬體

三、SQL Performance Analyzer使用前提條件
1 使用SPA的前提條件
由於資料庫的複雜性，如果想使用SPA來度量生產庫變更對效能影響的話，測試環境的軟硬體配置需要與生產環境是一致的，同樣DB的初始化引數等也要求與產品庫是一致的，可以用RMAN，DATA PUMP等工具建立測試環境。
四、SQL Performance Analyzer概要
1. 收集 SQL：在這個階段中，將收集用於表示生產系統中的 SQL 工作量的 SQL 語句集。可以使用 SQL 優化集或自動工作量資料檔案庫 (AWR) 來捕獲要傳送的資訊。因為 AWR 本質上是捕獲高負載的 SQL，所以應考慮修改預設的 AWR 快照設定和捕獲的頂級 SQL，以確保 AWR 捕獲最大數量的 SQL 語句。這可以確保捕獲更加完整的 SQL 工作量。
2. 傳送：在這個階段中，應將得到的工作量結果傳送到測試系統。從生產系統匯出 STS，然後將 STS 匯入到測試系統。
3. 計算“之前版本”效能：在進行任何更改之前，執行 SQL 語句，收集評估將來的更改對工作量效能的可能影響所需的基線資訊。在此階段收集的資訊給出了系統工作量當前狀態的一個快照。效能資料包括：
-執行計劃（如由解釋計劃生成的計劃）
-執行統計資訊（如由佔用時間、緩衝獲取次數、磁碟讀取次數和已處理的行陣列成的資訊）
4. 進行更改：獲得了之前版本資料後，可以實施計劃的更改，然後開始檢視對效能的影響。
5. 計算“之後版本”效能：在資料庫環境中進行了更改之後才執行此步驟。SQL 工作量的每個語句都在虛擬執行（僅收集統計資訊）模式下執行，收集與步驟 3 所捕獲的資訊相同的資訊。
6. 比較和分析 SQL 效能：在獲得了兩個版本的 SQL 工作量效能資料後，可以通過比較之後版本與之前版本的資料來進行效能分析。比較的根據是執行統計資訊，如所用時間、CPU 時間和緩衝區獲取次數等。
7. 優化迴歸的 SQL：在此階段中，已經準確地確認了哪些 SQL 語句在進行資料庫更改時可能導致效能問題。在此階段中可以使用任何一種資料庫工具來優化系統。例如，可以對確認的語句使用 SQL 優化指導或訪問指導，然後實施相應的建議。也可以使用在步驟 3 中捕獲的計劃植入 SQL 計劃管理 (SPM) 以確保計劃保持不變。在實施了任何優化操作後，應重複該過程來建立新的之後版本，然後分析效能差異以確保新的效能是可接受的。
預設情況下SPA若涉及到DML語句則只有查詢部分Query會被執行，但是貌似是從11.2開始可以執行完全的DML了，需要加入引數EXECUTE_FULLDML，但是該引數目前有一些BUG:
Bug 10428438 : WITH EXECUTE_FULLDML ROWS IS ALWAYS SET TO 0 11.2.0.1
Bug 14635522 : SPA SHOULD CAPTURE AND REPLAY TRANSACTIONS 11.2.0.3
By default, only the query portion of DMLs is executed. Using APIs, you can execute the full DML by using the EXECUTE_FULLDML task parameter.EXECUTE_FULLDML when set to TRUE executes DML statement fully, including acquiring row locks and modifying rows; When EXECUTE_FULLDML is set to FALSE (the default value is false) to execute only the query part of the DML without modifying data. When TRUE, SQL Performance Analyzer will issue a rollback following DML execution to prevent persistent changes from being made by the DML. So SPA does not make make any change to the data in the tables.
執行方法如下：
execute DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name   => 'TASK_21137', -
                                               parameter   => 'EXECUTE_FULLDML', -
                                               value       => 'TRUE');

五、SQL Performance Analyzer使用例子
A、
-1.-建立測試表空間test、建立測試表t1
create tablespace test
datafile '+ASM_FRA/test01.DBF'
size 2000m
autoextend on
next 100m maxsize unlimited
extent management local autoallocate
segment space management auto;
create table t1
(
sid int not null ,
sname varchar2(10)
)
tablespace test;

-2.-迴圈匯入資料
declare
maxrecords constant int:=1000000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t1 values(i,'OCMHU');
end loop;
dbms_output.put_line(' 成功錄入資料！ ');
commit;
end;
/

update t1 set sname='蘇州' where sid=500001;
update t1 set sname='南京' where sid=600001;
commit;
---3.收集統計資訊
exec dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE)

alter system flush shared_pool;
---4.執行查詢
select count(*) from t1 where sid<=100;

select count(*) from t1 where sid<=500;

select count(*) from t1 where sid>50000;

---5.新建STS
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'OCMHU_STS'
);
END;
/
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCMHU_STS',
sqlset_owner => 'SYS',
description => 'OCMHUtest');
END;
/

---6.載入sql優化集
set serveroutput on
DECLARE
cur01 dbms_sqltune.sqlset_cursor;
BEGIN
open cur01 for select value(a) from table(dbms_sqltune.select_cursor_cache
(
basic_filter => 'sql_text like ''%t1%'' and parsing_schema_name =''SYS''',
attribute_list => 'ALL'
)
) a;
dbms_sqltune.load_sqlset(
sqlset_name => 'OCMHU_STS',
populate_cursor => cur01);
close cur01;
END;
/
/*********有兩個引數值得特別說明：
1）SELECT_CURSOR_CACHE的第一個引數是basic_filter ，它能夠取的值有：
sql_id VARCHAR(13),
force_matching_signature NUMBER,
sql_text CLOB,
object_list sql_objects,
bind_data RAW(2000),
parsing_schema_name VARCHAR2(30),
module VARCHAR2(48),
action VARCHAR2(32),
elapsed_time NUMBER,
cpu_time NUMBER,
buffer_gets NUMBER,
disk_reads NUMBER,
direct_writes NUMBER,
rows_processed NUMBER,
fetches NUMBER,
executions NUMBER,
end_of_fetch_count NUMBER,
optimizer_cost NUMBER,
optimizer_env RAW(1000),
priority NUMBER,
command_type NUMBER,
first_load_time VARCHAR2(19),
stat_period NUMBER,
active_stat_period NUMBER,
other CLOB,
plan_hash_value NUMBER,
sql_plan sql_plan_table_type,
bind_list sql_binds
2）SELECT_CURSOR_CACHE的最後一個引數是attribute_list
BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.
TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list
ALL - return all attributes Comma separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS: similar to SQL_PLAN + row source statistics
*********/

---7.查詢sql優化集
select sql_id,sql_text from dba_sqlset_statements
where sqlset_name='OCMHU_STS' and sql_text like '% from t1%';

---8.新建SPA
var v_task varchar2(64);
begin
:v_task:=dbms_sqlpa.create_analysis_task(
sqlset_name => 'OCMHU_STS',
task_name => 'SPA01'
);
end;
/

/**********語法

Syntax
SQL text format. This form of the function is called to prepare the analysis of a single statement given its text.
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sql_text IN CLOB,
bind_list IN sql_binds := NULL,
parsing_schema IN VARCHAR2 := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQL ID format. This form of the function is called to prepare the analysis of a single statement from the cursor cache given its identifier.
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
Workload Repository format. This form of the function is called to prepare the analysis of a single statement from the workload repository given a range of snapshot identifiers.
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
begin_snap IN NUMBER,
end_snap IN NUMBER,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;

SQLSET format. This form of the function is called to prepare the analysis of a SQL tuning set.
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
order_by IN VARCHAR2 := NULL,
top_sql IN VARCHAR2 := NULL,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;

**********/

---9.執行SPA
begin
dbms_sqlpa.execute_analysis_task
(
task_name => 'SPA01',
execution_type => 'test execute',
execution_name => 'before_change'
);
end;
/
/*********語法
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name IN VARCHAR2,
execution_type IN VARCHAR2 := 'test execute',
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc IN VARCHAR2 := NULL)
RETURN VARCHAR2;

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name IN VARCHAR2,
execution_type IN VARCHAR2 := 'test execute',
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc IN VARCHAR2 := NULL);
*********/

---10.改變
create index index_01 on t1(sid,sname)
tablespace test;

exec dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE)

---11.改變後執行
begin
dbms_sqlpa.execute_analysis_task
(
task_name => 'SPA01',
execution_type => 'test execute',
execution_name => 'after_change'
);
end;
/

col TASK_NAME format a30
col EXECUTION_NAME for a30
select execution_name,
status,
execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='SPA01'
order by execution_end
/
EXECUTION_NAME STATUS EXECUTION_END
------------------------------ ----------- -------------------
before_change                  COMPLETED   2014-08-18 12:53:13
after_change                   COMPLETED   2014-08-18 12:54:37

---12.執行任務比較
begin
dbms_sqlpa.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA01',
execution_type => 'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'before_change',
'execution_name2',
'after_change'));
end;
/

---13.生產報告
set serveroutput on size 999999
set long 100000000
set pagesize 0
set linesize 200
set longchunksize 200
set trimspool on
spool report.txt
select DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA01') from dual;
spool off;


17:25:12 SYS@racdb1@RACDB >set serveroutput on size 999999
spool report.txt
select DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA01') from dual;
spool off;
set long 100000000
set pagesize 0
set linesize 200
set longchunksize 200
set trimspool on
spool report.txt
select DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA01') from dual;
spool off;
General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : SPA01                           SQL Tuning Set Name        : OCMHU_STS
  Task Owner   : SYS                             SQL Tuning Set Owner       : SYS
  Description  :                                 Total SQL Statement Count  : 6

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name  : EXEC_2873              Started             : 08/18/2014 12:54:55
  Execution Type  : COMPARE PERFORMANCE    Last Updated        : 08/18/2014 12:54:56
  Description     :                        Global Time Limit   : UNLIMITED
  Scope           : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED
  Status          : COMPLETED              Number of Errors    : 0

Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                       After Change Execution:
 ---------------------------------------------  ---------------------------------------------
  Execution Name      : before_change            Execution Name      : after_change
  Execution Type      : TEST EXECUTE             Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE            Scope               : COMPREHENSIVE
  Status              : COMPLETED                Status              : COMPLETED
  Started             : 08/18/2014 12:53:06      Started             : 08/18/2014 12:54:34
  Last Updated        : 08/18/2014 12:53:13      Last Updated        : 08/18/2014 12:54:37
  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      :  39.95%
 Improvement Impact  :  49.57%
 Regression Impact   :  -9.62%

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall               6                  2
 Improved              3                  2
 Regressed             2                  0
 Unchanged             1                  0

Top 6 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 |
------------------------------------------------------------------------------------------
|        18 | 4sxcusx5ts2q7 |    24.07% |         2 |  49543 |    122 |  99.75% | y      |
|        21 | fxju70btt1fxm |    13.17% |         2 |  89289 |  62257 |  30.27% | n      |
|        19 | 66cc15g0xndxr |    12.33% |         1 |  50670 |     38 |  99.93% | y      |
|        17 | 17bht0ggvvys0 |    -5.37% |         1 |  38850 |  60881 | -56.71% | n      |
|        20 | 6wfdxvkruvyc8 |    -4.25% |         1 |  31412 |  48873 | -55.59% | n      |
|        16 | 09n2gkwf8xw6x |     -.72% |         1 |  12035 |  14994 | -24.59% | n      |
------------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------

 


B、

六、附錄一：參考文件
SQL Performance Analyzer SPA常用指令碼彙總
http://www.askmaclean.com/archives/spa-script.html

11g新特性:SQL Performance Analyzer(SPA)
http://blog.csdn.net/crazy_xiaoxiao/article/details/8076627

實戰:ORACLE SQL Performance Analyzer
http://www.cnblogs.com/mengfanrong/p/3772404.html

Oracle 11g SQL Performance Analyzer震撼感受
http://database.ctocio.com.cn/tips/472/8110472.shtml

七、附錄二：ORA-13757: "SQL Tuning Set" "OCMHU_STS" owned by user "SYS" is active.
1,錯誤描述
sqlset_name => 'OCMHU_STS'
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'OCMHU_STS'
10:27:23   4  );
10:27:23   5  END;
10:27:23   6  /
BEGIN
*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "OCMHU_STS" owned by user "SYS" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13226
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4563
ORA-06512: at line 2

2,錯誤分析
If the SQL set is still being used by other tuning tasks, the sql set can not be dropped. If we try to drop it, we would get the error: ORA-13757
Read more at http://www.sqlpanda.com/2013/02/sql-performance-analyzer-spa.html#d1Eu24m6ECYVCfUp.99
Cause:
The user attempted to update an active SQL Tuning Set.

(1) Check which all sql tuning set are available in your database :
10:39:29 SYS@racdb1@RACDB >col description for a20
10:39:53 SYS@racdb1@RACDB >col owner for a10
10:40:09 SYS@racdb1@RACDB >col name for a10
10:40:18 SYS@racdb1@RACDB >select * from dba_sqlset;
        ID NAME       OWNER      DESCRIPTION          CREATED             LAST_MODIFIED       STATEMENT_COUNT
---------- ---------- ---------- -------------------- ------------------- ------------------- ---------------
         3 OCMHU_STS  SYS        OCMHUtest            2014-08-05 16:16:53 2014-08-05 16:17:26               2
 
(2) Select the tuning set which you need to drop and issue following command for dropping sql tuning set :
NOTE: This is a test case to show the solution for below mentioned error. Please check all the details mentioned below before dropping sql tuning set.
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'OCMHU_STS'
);
END;
/
BEGIN
*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "OCMHU_STS" owned by user "SYS" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13226
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4563
ORA-06512: at line 2
 
The above error stats that the sql tuning set which we are trying to drop is in active state i.e. an task is dependent on this sql tuning set. You cannot drop sql tuning set before dropping all depenedent sql tasks.
 
(3) To check all the dependent advisory tasks attached to sql tuning set you need to issue following command :
SQL> SET WRAP OFF
SQL> SET LINE 140
SQL> COL NAME FOR A15
SQL> COL DESCRIPTION FOR A50 WRAPPED
SQL>
SQL> select description, created, owner
 from DBA_SQLSET_REFERENCES
 where sqlset_name ='OCMHU_STS';
DESCRIPTION                                        CREATED             OWNER
-------------------------------------------------- ------------------- ----------
created by: SQL Performance Analyzer - task: SPA01 2014-08-05 16:22:27 SYS
From the above output we can evaluate that task “SPA01″ is dependent on sql tuning set “OCMHU_STS”. So, if you want to drop sql tuning set ”OCMHU_STS” you need to drop task ”SPA01“.
NOTE : Think before dropping sql tuning task as if you drop it, all the information related to sql_profile, stats, indexes related to this advisory task will be deleted.
(4) To check the details regarding SQL Performance Analyzer - task: “SPA01” you can issue following command :
a. Normal Output :
SQL> SET WRAP OFF
SQL> SET LINE 140
SQL> COL NAME FOR A15
SQL> COL OWNER FOR A10
SQL> COL DESCRIPTION FOR A50 WRAPPED
SQL>
SQL> select owner,description, created,last_modified
 from DBA_ADVISOR_TASKS
 where task_name = 'SPA01';
OWNER      DESCRIPTION                                        CREATED             LAST_MODIFIED
---------- -------------------------------------------------- ------------------- -------------------
SYS                                                           2014-08-05 16:22:26 2014-08-05 17:25:12
If you are getting the above mentioned output then you can solve the issue by dropping the abve mentioned sql advisory tasks. The command for dropping sql advisory task is mentioned below.
 
SQL> execute dbms_sqltune.drop_tuning_task('SPA01');
PL/SQL procedure successfully completed.
b. Issue based output :
SQL> select owner,description, created,last_modified
 from DBA_ADVISOR_TASKS
 where task_name = 'SPA01';
no rows selected
If you are getting the above output, this means that your sql tuning set “OCMHU_STS” is dependent on advisory task “SPA01″ but in this case “SPA01″ does not exists in database. This can be a case when you have previously deleted SQL Tuning advisor task “SPA01″ but it has not been updated in table. You can check / test the same by dropping sql tuning advisor task “SPA01″ :
SQL> execute dbms_sqltune.drop_tuning_task('SPA01');
BEGIN dbms_sqltune.drop_tuning_task('SPA01'); END;
*
ERROR at line 1:
ORA-13605: The specified task or object SPA01 does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2388
ORA-06512: at "SYS.DBMS_ADVISOR", line 172
ORA-06512: at "SYS.DBMS_SQLTUNE", line 870
ORA-06512: at line 1

To resolve this issue you need to follow the below mentioned procedure :
 
(5) Check the no. of dependent task using below mentioned query.
SQL> SELECT count(*)
 FROM wri$_sqlset_definitions d, wri$_sqlset_references r
 WHERE d.name = 'OCMHU_STS'
 AND r.sqlset_id = d.id;
COUNT(*)
———-
0
In normal case the output of the query should be “0”. In this case, it is taking advisory task “SPA01″ as it has not been deleted from database.
(6) You need to manually edit table and remove the entry which contains information regarding sql tuning task :
conn / as sysdba
delete from wri$_sqlset_references
where sqlset_id in (select id
from wri$_sqlset_definitions
where name in ('SPA01'));
commit;
This command will update oracle table and will remove dependency from sql tuning set .
 
(7) You can drop sql tuning set by issuing following command :
SQL>BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'OCMHU_STS'
);
END;
/
PL/SQL procedure successfully completed.


3,解決方案
Action:
Remove all reference to the SQL Tuning Set and retry the operation.

4,參考文件
SQL Performance Analyzer (SPA)
http://www.sqlpanda.com/2013/02/sql-performance-analyzer-spa.html

ORA-13757 – Error Dropping SQL Tuning Set
http://raadin.wordpress.com/2014/05/26/ora-13757-error-dropping-sql-tuning-set/

Good luck!

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14359/viewspace-1253599/，如需轉載，請註明出處，否則將追究法律責任。

相關文章