关于dbms_sqldiag包，该包是10.2.0.4版本才开始提供的，10g中功能相对简单，在11g中就比较强悍了。
如下是自己今天做的几个简单的测试，算是初步了解如何去应用这个包了。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> create directory exp_tc as '/tmp/trc';
Directory created.
SQL> select sql_id, sql_text
2 from v$sql
3 where sql_Text like '%count(*) from dba_objects%'
4 /
SQL_ID SQL_TEXT
------------- -----------------------------------------------------------------------------------
g4pkmrqrgxg3b select count(*) from dba_objects
9avr6xu38fhpr select sql_id from v$sql where sql_Text like '%count(*) from dba_objects%'
g7zqrq3wqqva0 select sql_id,sql_text from v$sql where sql_Text like '%count(*) from dba_objects%'
6c798vnwdbmdg select sql_id,sql_text from v$sql where sql_Text like '%count(*) from dba_objects%'
SQL> declare
2 test_out clob;
3 begin
4 dbms_sqldiag.export_sql_testcase( directory => 'EXP_TC',
5 sql_id => 'g4pkmrqrgxg3b',
6 testcase => test_out );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select directory_path
2 from DBA_DIRECTORIES
3 where directory_name='EXP_TC';
DIRECTORY_PATH
-------------------------------------------------------------------
/tmp/trc
SQL> !ls -ltr /tmp/trc
total 236
-rw-r--r-- 1 ora10g oinstall 1157 Mar 3 23:27 README.txt
-rw-r--r-- 1 ora10g oinstall 1026 Mar 3 23:27 oratcb1_009500120001sql.xml
-rw-r--r-- 1 ora10g oinstall 2858 Mar 3 23:27 oratcb1_009500120001ol.xml
-rw-r--r-- 1 ora10g oinstall 4774 Mar 3 23:27 oratcb1_009500120001dpexp.sql
-rw-r--r-- 1 ora10g oinstall 395 Mar 3 23:27 oratcb1_009500120001dpexp.log
-rw-r--r-- 1 ora10g oinstall 409 Mar 3 23:27 oratcb1_009500120001xpls.sql
-rw-r--r-- 1 ora10g oinstall 1283 Mar 3 23:27 oratcb1_009500120001xplo.sql
-rw-r--r-- 1 ora10g oinstall 388 Mar 3 23:27 oratcb1_009500120001ssimp.sql
-rw-r--r-- 1 ora10g oinstall 3768 Mar 3 23:27 oratcb1_009500120001dpimp.sql
-rw-r----- 1 ora10g oinstall 131072 Mar 3 23:27 oratcb1_009500120001dpexp.dmp
-rw-r--r-- 1 ora10g oinstall 157 Mar 3 23:27 oratcb1_009500120001xpl.txt
-rw-r--r-- 1 ora10g oinstall 432 Mar 3 23:27 oratcb1_009500120001xplf.sql
-rw-r--r-- 1 ora10g oinstall 1764 Mar 3 23:27 oratcb1_009500120001main.xml
SQL> !cat /tmp/trc/README.txt
-----------------------------------------------------------------
-- Scripts generated by DBMS_SQLDIAG package,
-- Use this script to import objects referenced in a given SQL
--
-- This SQL test case contains a set of files needed to help
-- reproduce a SQL failure on a different machines:
--
-- It contains:
--
-- 1. a dump file containing schemas objects and statistics (.dmp)
-- 2. the explain plan for the statements (in advanced mode)
-- 3. diagnostic information gathered on the offending statement
-- 4. an import script to execute to reload the objects.
-- 5. a SQL scripts to replay system statistics of the source
-- 6. A table of content file describing the SQL test case
-- metadata.
--
-- This last file is the one to provide to the import API to
-- import the SQL test case. This file ends with
-----------------------------------------------------------------
BEGIN
create directory TMP_TCB23061967_DIR as '';
dbms_sqldiag.import_sql_testcase(
directory => 'TMP_TCB23061967_DIR' ,
filename => 'main.xml');
END;
SQL> !cat /tmp/trc/oratcb1_009500120001xplo.sql
-----------------------------------------------------------------
-- Script generated by DBMS_SQLDIAG package,
-- Use this script to execute a simple explain plan of the
-- offending SQL
--
-- NOTE: this script may need to be edited for your system
-----------------------------------------------------------------
explain plan for
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$3" "S"@"SEL$3" ("SUM$"."OBJ#"))
INDEX_RS_ASC(@"SEL$4" "I"@"SEL$4" ("IND$"."OBJ#"))
USE_HASH(@"SEL$2" "O"@"SEL$2")
LEADING(@"SEL$2" "U"@"SEL$2" "O"@"SEL$2")
FULL(@"SEL$2" "O"@"SEL$2")
FULL(@"SEL$2" "U"@"SEL$2")
USE_NL(@"SEL$5" "U"@"SEL$5")
LEADING(@"SEL$5" "L"@"SEL$5" "U"@"SEL$5")
INDEX(@"SEL$5" "U"@"SEL$5" "I_USER#")
INDEX(@"SEL$5" "L"@"SEL$5" ("LINK$"."OWNER#" "LINK$"."NAME"))
NO_ACCESS(@"SEL$1" "DBA_OBJECTS"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$3")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
select count(*) from dba_objects;
SQL>
###### wrong result ######
SQL> select * from v$version where rownum < 3;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
SQL> show user
USER is "SYS"
SQL> select count(*) from dba_objects;
COUNT(*)
----------
72395
SQL> set lines 120
SQL> col sql_text for a80
SQL> set long 99999
SQL> select sql_id,sql_text from v$sql where sql_Text like '%count(*) from dba_objects%';
SQL_ID SQL_TEXT
------------- -----------------------------------------------------------------------------------
g4pkmrqrgxg3b select count(*) from dba_objects
g7zqrq3wqqva0 select sql_id,sql_text from v$sql where sql_Text like '%count(*) from dba_objects%'
SQL> declare
2 l_sql_diag_task_id varchar2(100);
3 begin
4 l_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (
5 sql_id => 'g4pkmrqrgxg3b' ,
6 problem_type => dbms_sqldiag.PROBLEM_TYPE_WRONG_RESULTS,
7 task_name => 'roger_fortest_diagnostic_task' );
8 dbms_sqltune.set_tuning_task_parameter (
9 l_sql_diag_task_id,
10 '_SQLDIAG_FINDING_MODE',
11 DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS );
12 end;
13 /
declare
*
ERROR at line 1:
ORA-28365: wallet is not open
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 132
ORA-06512: at "SYS.DBMS_SQLDIAG", line 830
ORA-06512: at line 4
SQL> alter system set encryption key authenticated by "111111";
System altered.
SQL> col WRL_PARAMETER for a60
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ------------------------------------------------------------ ------------------
file /home/ora11g/admin/roger/wallet OPEN
SQL> !ls -ltr /home/ora11g/admin/roger/wallet
total 8
-rw-r--r-- 1 ora11g oinstall 1309 Mar 3 23:52 ewallet.p12
SQL> declare
2 l_sql_diag_task_id varchar2(100);
3 begin
4 l_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (
5 sql_id => 'g4pkmrqrgxg3b' ,
6 problem_type => dbms_sqldiag.PROBLEM_TYPE_WRONG_RESULTS,
7 task_name => 'roger_fortest_diagnostic_task' );
8 dbms_sqltune.set_tuning_task_parameter (
9 l_sql_diag_task_id,
10 '_SQLDIAG_FINDING_MODE',
11 DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS );
12 end;
13 /
PL/SQL procedure successfully completed.
SQL> exec dbms_sqldiag.execute_diagnosis_task ( task_name => 'roger_fortest_diagnostic_task' );
BEGIN dbms_sqldiag.execute_diagnosis_task ( task_name => 'roger_fortest_diagnostic_task' ); END;
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 3, block # 3412)
ORA-01110: data file 3: '+DATA1/roger/undotbs01.dbf'
ORA-06512: at "SYS.PRVT_ADVISOR", line 2735
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLDIAG", line 939
ORA-06512: at line 1
++++++ 似乎undo 有些问题 ++++++
RMAN> copy datafile 3 to '/home/ora11g/undotbs01.dbf';
Starting backup at 03-MAR-12
using channel ORA_DISK_1
ignoring encryption for proxy or image copies
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA1/roger/undotbs01.dbf
output file name=/home/ora11g/undotbs01.dbf tag=TAG20120303T235949 RECID=5 STAMP=776995192
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-MAR-12
RMAN> exit
Recovery Manager complete.
[ora11g@11gr2test ~]$ dbv file=/home/ora11g/undotbs01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.2.0 - Production on Sun Mar 4 00:00:20 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /home/ora11g/undotbs01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 5760
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 5758
Total Pages Processed (Seg) : 10
Total Pages Failing (Seg) : 0
Total Pages Empty : 1
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 1 <== 有个加密block的前镜像还在undo里面
Highest block SCN : 1657430 (0.1657430)
SQL> exec dbms_sqldiag.execute_diagnosis_task ( task_name => 'roger_fortest_diagnostic_task' );
BEGIN dbms_sqldiag.execute_diagnosis_task ( task_name => 'roger_fortest_diagnostic_task' ); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2032], [12586324], [12586324], [8192], [2], [255], [1255973], [707068671], [], [], [],
[]
ORA-06512: at "SYS.PRVT_ADVISOR", line 2735
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLDIAG", line 939
ORA-06512: at line 1
SQL> shutdown immediate;
ORA-00600: internal error code, arguments: [2032], [12586324], [12586324], [8192], [2], [255], [1255973], [707068671], [], [], [],
[]
SQL> alter system set events '10046 trace name context forever,level 12';
System altered.
SQL> shutdown immediate;
ORA-03113: end-of-file on communication channel
Process ID: 9831
Session ID: 23 Serial number: 103
++++++ 通过trace可以发现如下信息：++++++
*** 2012-03-03 23:43:08.718
*** SESSION ID:(23.103) 2012-03-03 23:43:08.718
*** CLIENT ID:() 2012-03-03 23:43:08.718
*** SERVICE NAME:(SYS$USERS) 2012-03-03 23:43:08.718
*** MODULE NAME:(sqlplus@11gr2test (TNS V1-V3)) 2012-03-03 23:43:08.718
*** ACTION NAME:() 2012-03-03 23:43:08.718
kcbztek_get_tbskey: decrypting encrypted key for tablespace 0 without opening the wallet
kcbztek_get_tbskey: wallet is not opened (tsn 0)
kcbz_encdec_tbsblk: DIAG DUMP tsn 2 rdba 12586324, afn 3, mode 4
buffer tsn: 2 rdba: 0x00c00d54 (3/3412)
scn: 0x0000.00132a25 seq: 0x01 flg: 0x14 tail: 0x2a250201
frmt: 0x02 chkval: 0xabce type: 0x02=KTU UNDO BLOCK
Dump of buffer cache at level 1 for tsn=2, rdba=12586324
BH (0x21fecc4c) file#: 3 rdba: 0x00c00d54 (3/3412) class: 20 ba: 0x21da2000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 92,19
dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f
hash: [0x314b5388,0x314b5388] lru: [0x21fecdcc,0x257f6f48]
ckptq: [NULL] fileq: [NULL] objq: [0x243f84e8,0x2f1f6210] objaq: [0x21fee374,0x2f1f6208]
use: [0x3150070c,0x3150070c] wait: [NULL]
st: READING md: EXCL tch: 0
flags: only_sequential_access
cr pin refcnt: 0 sh pin refcnt: 0
由于其是wallet我以前就删除了，不知道为什么这里抛出这样的错误，
当时这里你可以可以手工去修改这个undo block，然后用rman copy回asm中。
由于本身是加密的，所以比较没法，来个简单的处理方式：
SQL> create undo tablespace undotbs datafile '+DATA1/roger/undotbs.dbf' size 20m;
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace=UNDOTBS;
System altered.
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL> alter system checkpoint;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> exec dbms_sqldiag.execute_diagnosis_task ( task_name => 'roger_fortest_diagnostic_task' );
PL/SQL procedure successfully completed.
SQL> set echo on lines 132 pages 999 long 20000 serveroutput on;
SQL> select dbms_sqldiag.report_diagnosis_task ('roger_fortest_diagnostic_task' )
2 as recommendations
3 from dual;
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : roger_fortest_diagnostic_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Execution Count : 4
Current Execution : EXEC_140
Execution Type : SQL DIAGNOSIS
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/04/2012 00:31:53
Completed at : 03/04/2012 00:31:59
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : g4pkmrqrgxg3b
SQL Text : select count(*) from dba_objects
-------------------------------------------------------------------------------
No SQL patch was found to resolve the problem.
-------------------------------------------------------------------------------
SQL> select object_id, count(*)
2 from t
3 group by object_id;
OBJECT_ID COUNT(*)
---------- ----------
100 982
10 18
SQL> set autot traceonly exp
SQL> select owner, object_name, object_type
2 from t
3 where object_id=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 827754323
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 1512 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 18 | 1512 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 18 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
SQL> select owner, object_name, object_type
2 from t
3 where object_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 982 | 82488 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 982 | 82488 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
SQL> alter system flush shared_pool;
System altered.
SQL> exec :a := 10;
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type
2 from t
3 where object_id = :a;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 42000 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 500 | 42000 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:A))
###### 发现这里autotrace显示有问题，执行计划不对 ######
SQL> set autot off
SQL> select sql_id, sql_text
2 from v$sql
3 where sql_text like '%object_id%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
............. ........................
............. ......省略部分内容......
............. ........................
2yk93v48yj2fw select sql_id,sql_text from v$sql where sql_text like '%object_id%'
gur3jnky1tvx1 select owner,object_name,object_type from t where object_id=:a
c349vc68ng898 EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select owner,object_name,obje
ct_type from t where object_id=:a
SQL> DECLARE
2 v_sql_diag_task_id varchar2(100);
3 BEGIN
4 v_sql_diag_task_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (
5 sql_id => 'gur3jnky1tvx1' ,
6 problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_EXECUTION_ERROR,
7 time_limit => 0.1,
8 task_name => 'problem_sql2_diagnostic_task' );
9 DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
10 v_sql_diag_task_id,
11 '_SQLDIAG_FINDING_MODE',
12 DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS );
13 END;
14 /
PL/SQL procedure successfully completed.
SQL> exec dbms_sqldiag.execute_diagnosis_task ( task_name => 'problem_sql2_diagnostic_task' );
PL/SQL procedure successfully completed.
SQL> select dbms_sqldiag.report_diagnosis_task ('problem_sql2_diagnostic_task' )
2 as recommendations
3 from dual;
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : problem_sql2_diagnostic_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): .1
Completion Status : INTERRUPTED
Started at : 03/04/2012 01:20:59
Completed at : 03/04/2012 01:21:00
-------------------------------------------------------------------------------
Error: ORA-13639: The current operation was interrupted because it timed out.
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : gur3jnky1tvx1
SQL Text : select owner,object_name,object_type from t where object_id=:a
-------------------------------------------------------------------------------
No SQL patch was found to resolve the problem.
-------------------------------------------------------------------------------
SQL> exec dbms_sqldiag.drop_diagnosis_task ( task_name => 'problem_sql_diagnostic_task' );
PL/SQL procedure successfully completed.
SQL> exec dbms_sqldiag.drop_diagnosis_task ( task_name => 'problem_sql2_diagnostic_task' );
PL/SQL procedure successfully completed.
还可以利用该包来看某个sqlid的执行计划，甚至是存在child number的，如下：
SQL> select sql_id,child_number from V$sql where CHILD_NUMBER =5;
SQL_ID CHILD_NUMBER
------------- ------------
3ktacv9r56b51 5
8swypbbr0m372 5
SQL> execute DBMS_SQLDIAG.DUMP_TRACE( p_sql_id=>'8swypbbr0m372',
2 p_child_number=>5,
3 p_component=>'Compiler',
4 p_file_id=>'roger_sqltest' );
PL/SQL procedure successfully completed.
SQL> !
[ora11g@11gr2test trace]$ ls -ltr *test*
-rw-r----- 1 ora11g oinstall 30156 Mar 4 01:37 roger_ora_14879_roger_sqltest.trm
-rw-r----- 1 ora11g oinstall 80566 Mar 4 01:37 roger_ora_14879_roger_sqltest.trc
摘取该trace的片段：
sql=/* SQL Analyze(1,0) */ select order#,columns,types from access$ where d_obj#=:1
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | TABLE ACCESS BY INDEX ROWID | ACCESS$ | 4 | 172 | 3 | 00:00:01 |
| 2 | INDEX RANGE SCAN | I_ACCESS1| 4 | | 2 | 00:00:01 |
------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("D_OBJ#"=:1)
Content of other_xml column
===========================
db_version : 11.2.0.2
parse_schema : SYS
plan_hash : 893970548
plan_hash_2 : 968792012
Peeked Binds
============
Bind variable information
position=1
datatype(code)=2
datatype(string)=NUMBER
precision=0
scale=0
max length=22
value=9188
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "ACCESS$"@"SEL$1" ("ACCESS$"."D_OBJ#" "ACCESS$"."ORDER#"))
END_OUTLINE_DATA
*/
其实跟10053 的trace极度相似，查看某个sql的真实执行计划，以后完全可以使用该包来进行。
11gR2中，dbms_sqldiag就非常强悍了，虽然我这里测试感觉对于使用绑定变量的sql似乎没啥作用。
可能我这里测试不够仔细，当然是为了熟悉下该包的强大功能，大家也可以去研究研究！
大家可以去这里了解该包的相关信息：
$ORACLE_HOME/rdbms/admin/dbmsdiag.sql
发表回复