这篇文章主要介绍通过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' );