使用 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' );

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.