使用 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' );
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2109117/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 斷號查詢問題
- 使用crsctl工具診斷cluster問題
- Oracle效能問題 - 常用查詢診斷及調整指令碼[不斷更新] (final)Oracle指令碼
- 如何使用Apple診斷程式檢查Mac硬體問題APPMac
- SQL問題診斷SQL
- 使用MTR命令診斷網路問題
- Mybatis各種模糊查詢MyBatis
- GreysJava線上問題診斷工具Java
- 問題診斷和PLSQL方面SQL
- 使用awr來診斷資料庫效能問題資料庫
- 使用truss、strace或ltrace診斷軟體問題
- 查詢Tuxedo積壓的Oracle診斷指令碼UXOracle指令碼
- GC BUFFER BUSY問題的診斷GC
- 各平臺安裝使用 MTR 診斷網路
- PostgreSQL多查詢條件，多個索引的選擇演算法與問題診斷方法SQL索引演算法
- 5種常見的 DNS 故障診斷及問題處理方法DNS
- oracle 查詢結果的各種格式Oracle
- 使用strace診斷奇怪的sqlplus登入問題SQL
- 使用pt-stalk診斷MySQL的間歇性問題MySql
- 一個使用JDBC按Date查詢查詢的問題JDBC
- Oracle Stream實戰(10)—問題診斷Oracle
- Oracle效能問題診斷一例Oracle
- 各種免費好用的api，含天氣查詢、IP查詢、物流查詢等API
- Mybatis中模糊查詢的各種寫法MyBatis
- [演算法]各種二分查詢演算法
- 記一次使用gdb診斷gc問題全過程GC
- .記一次使用gdb診斷gc問題全過程GC
- 如何使用AWR報告來診斷資料庫效能問題資料庫
- 如何診斷和解決db2問題DB2
- Timesten問題診斷手冊總結
- bea記憶體洩漏問題診斷記憶體
- Lotus Domino Administration Process 問題診斷
- 診斷Oracle資料庫Hanging問題Oracle資料庫
- GC機制和OutOfMemory問題的診斷GC
- javascript中的各種問題JavaScript
- RMQ問題的各種解法MQ
- Oracle效能 - 常用查詢診斷及調整指令碼[不斷更新] (final)Oracle指令碼
- TiDB 查詢優化及調優系列（三）慢查詢診斷監控及排查TiDB優化