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

最新推荐文章于 2023-02-12 22:41:03 发布

congbeili7411

最新推荐文章于 2023-02-12 22:41:03 发布

阅读量251

点赞数

文章标签：数据库操作系统运维

一、机器环境
数据库服务器环境
? 硬件环境：
两台虚拟机
  虚拟机名称：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.