使用 DBMS_SQLDIAG诊断各种查询问题

这篇文章主要介绍通过dbms_sqldiag来解决与SQL相关的各种问题。dbms_sqldiag是与标准版本数据库软件一起发布，使用它并不需要额外的许可。
dbms_sqldiag可以用于以下问题类型的诊断:
.problem_type_performance 怀疑是性能问题
.problem_type_wrong_results 怀疑查询返回了不一致的结果
.problem_type_compilation_error 在编译时的错误
.problem_type_execution_error 在执行时的错误

诊断problem_type_performance
执行查询并将该语句来作为诊断的SQL语句

SQL> set timing on SQL> set autotrace traceonly SQL> select * from t1 where c1=500000;  Elapsed: 00:00:03.43  Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013  -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |     3 |     5   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     5   (0)| 00:00:01 | --------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter(TO_NUMBER("C1")=500000)   Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets        1646  consistent gets           0  physical reads           0  redo size         420  bytes sent via SQL*Net to client         419  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed   SQL> select sql_text,sql_id from v$sqlarea where sql_text like 'select * from t1 where c1=500000';   SQL_TEXT                                                                         SQL_ID -------------------------------------------------------------------------------- ------------- select * from t1 where c1=500000                                                 456naq6s2fcpq  

创建诊断任务

SQL> set echo on SQL> set linesize 132 SQL> set pagesize 999 SQL> set long 999999 SQL> set serveroutput on SQL> declare   2  v_sql_diag_task_id varchar2(100);   3  begin   4  --   5  -- create diagnostic task   6  --   7  v_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (   8  sql_id=>'456naq6s2fcpq',   9  problem_type => dbms_sqldiag.problem_type_performance,  10  time_limit => 900,  11  task_name => 'problem_type_performance_task' );  12  --  13  -- setup parameters for the task to give verbose output  14  --  15  dbms_sqltune.set_tuning_task_parameter(v_sql_diag_task_id,'_sqldiag_finding_mode',dbms_sqldiag.sqldiag_findings_filter_plans);  16  end;  17  /  PL/SQL procedure successfully completed. 

有时sql_id因为一些原因可能在v$sql视图中找不到，因此在这时就需要使用sql_text来代替sql_id,用户想要执行诊断任务必须至少有advisor权限。task_name作为唯一键使用并且在相同用户使用相同任务名之前必须要删除。

检查任务是否创建成功

SQL> select distinct owner, task_name, advisor_name from dba_advisor_tasks where advisor_name ='SQL Repair Advisor' and task_name like '%perf%'order by 1;    OWNER                          TASK_NAME                      ADVISOR_NAME ------------------------------ ------------------------------ ------------------------------ SYS                            problem_type_performance_task  SQL Repair Advisor       

执行论断任务

SQL> exec dbms_sqldiag.execute_diagnosis_task (task_name => 'problem_type_performance_task' );  PL/SQL procedure successfully completed. 

生成报告

SQL> select dbms_sqldiag.report_diagnosis_task ('problem_type_performance_task' ) as recommendations from dual;  RECOMMENDATIONS -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name   : problem_type_performance_task Tuning Task Owner  : SYS Workload Type      : Single SQL Statement Scope              : COMPREHENSIVE Time Limit(seconds): 900 Completion Status  : COMPLETED Started at         : 05/30/2016 10:20:17 Completed at       : 05/30/2016 10:20:22  ------------------------------------------------------------------------------- Schema Name: SYS SQL ID     : 456naq6s2fcpq SQL Text   : select * from t1 where c1=500000  ------------------------------------------------------------------------------- No SQL patch was found to resolve the problem.  ------------------------------------------------------------------------------- 

这上面的这个例子中，没有得到任何patch建议。然而，如果得到了patch，可以执行以下命令来接受patch

begin  dbms_sqldiag.accept_sql_patch(    task_name =>'problem_type_performance_task',     task_owner => 'sys',     replace => true); end; 

验证SQL Patch是否启用

SQL> SELECT name, status FROM dba_sql_patches WHERE name LIKE '%SYS%';   NAME                           STATUS ------------------------------ -------- SYS_SQLPTCH_                   ENABLED  

删除任务

SQL> exec dbms_sqldiag.drop_diagnosis_task('problem_type_performance_task');  PL/SQL procedure successfully completed.  SQL> select distinct owner, task_name, advisor_name   2  from dba_advisor_tasks where advisor_name ='SQL Repair Advisor' and task_name like '%perf%'order by 1;   OWNER                          TASK_NAME                      ADVISOR_NAME ------------------------------ ------------------------------ ------------------------------ 

删除SQL Patch

--- find the name of the sql patch --- select name, status from dba_sql_patches where name like '%sys%';   ---drop the sql patch. ---replace following patch name with actual name of the sql patch --- from previous query output. exec  dbms_sqldiag.drop_sql_patch (name=> 'sys_sqlptch_'); --- verify that the sql patch has been dropped.  select name, status from dba_sql_patches where name like '%sys%'; 

诊断problem_type_wrong_results
创建两个测试表

SQL> create table a_test   2  (   3  id number not null,   4  clss number not null   5  );  Table created.  SQL> create table as_test   2  (   3  as_id number   4  );  Table created.  SQL> insert into a_test values(11,5);  1 row created.  SQL> insert into a_test values(1,5);  1 row created.  SQL> insert into as_test values(11);  1 row created.  SQL> commit;  Commit complete. 

错误结果--返回0行记录

SQL> select 'working' as is_working,id from   a_test a,   2    3         as_test asi   4  where  a.id=asi.as_id(+)   5  and    a.clss in (1,3,4,5)   6  and    a.clss = '5';  IS_WORK         ID ------- ----------                                                                                                 SQL> select sql_id, sql_text from v$sql where sql_text like 'select%as is_working%';   SQL_ID        SQL_TEXT ------------- -------------------------------------------------------------------------------- 9a15z3d14krcm select 'working' as is_working,id from   a_test a,        as_test asi where  a.i 

正确结果--返回2行记录

SQL> select 'working' as is_working,id from   a_test a,   2    3         as_test asi   4  where  a.id=asi.as_id(+)   5  and    a.clss in (1,3,4,5)   6  and    a.clss = '5';  IS_WORK         ID ------- ---------- working         11 working          1 

SQL> set echo on lines 132 pages 999 long 20000 serveroutput on; SQL> declare   2  l_sql_diag_task_id  varchar2(100);    3            4  begin   5  --   6  -- create diagnostic task   7  --   8      l_sql_diag_task_id :=  dbms_sqldiag.create_diagnosis_task (   9        sql_id => '9a15z3d14krcm',  10        problem_type => dbms_sqldiag.problem_type_wrong_results,   11        task_name => 'test_wr_diagnostic_task' );  12     13  --  14  -- setup parameters for the task to give verbose output  15  --  16      dbms_sqltune.set_tuning_task_parameter(  17        l_sql_diag_task_id,  18        '_sqldiag_finding_mode',  19        dbms_sqldiag.sqldiag_findings_filter_plans);  20  end;  21  /  PL/SQL procedure successfully completed.  SQL> exec dbms_sqldiag.execute_diagnosis_task ( task_name  => 'test_wr_diagnostic_task');  PL/SQL procedure successfully completed.   SQL> select dbms_sqldiag.report_diagnosis_task ('test_wr_diagnostic_task') as recommendations  from dual;   RECOMMENDATIONS -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name   : test_wr_diagnostic_task Tuning Task Owner  : SYS Workload Type      : Single SQL Statement Scope              : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status  : COMPLETED Started at         : 05/30/2016 10:46:22 Completed at       : 05/30/2016 10:46:24  ------------------------------------------------------------------------------- Schema Name: SYS SQL ID     : 9a15z3d14krcm SQL Text   : select 'working' as is_working,id              from   a_test a,                     as_test asi              where  a.id=asi.as_id(+)              and    a.clss in (1,3,4,5)              and    a.clss = '5'  ...省略... 

诊断PROBLEM_TYPE_COMPILATION_ERROR & PROBLEM_TYPE_EXECUTION_ERROR
创建诊断任务

set echo on set linesize 132 set pagesize 999 set long 999999 set serveroutput on  declare  v_sql_diag_task_id varchar2(100);   begin --- --- create a diagnostic task. use any name you want under task_name argument. --- v_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task ( sql_text => 'select distinct min(src.cf_table_group) from ( select pc.cf_table_group, min(pc.cf_prg_next_run) from testcase1 pc where nvl(pc.cf_prg_next_run, sysdate) < = sysdate group by pc.cf_table_group order by nvl(min(pc.cf_prg_next_run),sysdate) ) src', problem_type => dbms_sqldiag.problem_type_execution_error, time_limit => 3600, task_name => 'error_diagnostic_task' ); -- -- setup parameters for the task to give verbose output -- dbms_sqltune.set_tuning_task_parameter(v_sql_diag_task_id,'_sqldiag_finding_mode',dbms_sqldiag.sqldiag_findings_filter_plans);   end; 

执行诊断任务

exec dbms_sqldiag.execute_diagnosis_task ( task_name => 'error_diagnostic_task' ); 

生成报告

set long 9999999 select dbms_sqldiag.report_diagnosis_task ('error_diagnostic_task' ) as recommendations from dual;  recommendations -------------------------------------------------------------------------------- general information section ------------------------------------------------------------------------------- tuning task name : error_diagnostic_task tuning task owner : tc2533 workload type : single sql statement scope : comprehensive time limit(seconds): 3600 completion status : completed started at : 10/27/2011 22:35:07 completed at : 10/27/2011 22:35:07  ------------------------------------------------------------------------------- schema name: tc2533 sql id : 4k1tdq940wvpk sql text : select distinct min(src.cf_table_group) from ( select pc.cf_table_group, min(pc.cf_prg_next_run) from testcase1 pc where nvl(pc.cf_prg_next_run, sysdate) < = sysdate group by pc.cf_table_group order by nvl(min(pc.cf_prg_next_run),sysdate) ) src ------------------------------------------------------------------------------- findings section (1 finding) ------------------------------------------------------------------------------- 1- sql patch finding (see explain plans section below) ------------------------------------------------------ a potentially better execution plan was found for this statement. recommendation -------------- - consider accepting the recommended sql patch. execute dbms_sqldiag.accept_sql_patch(task_name => 'error_diagnostic_task', task_owner => 'tc2533', replace => true);  rationale --------- recommended plan with hash value 3673393522 has number of rows 1, check sum 2342552567, execution time 0 and 6 buffer gets  

接受建议

execute dbms_sqldiag.accept_sql_patch(task_name =>'error_diagnostic_task', task_owner => 'tc2533', replace => true); 

删除诊断任务

exec dbms_sqldiag.drop_diagnosis_task ( task_name => 'error_diagnostic_task' );