sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle DBMS_SQLDIAG包使用详解

时间:2016-12-01 22:27   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle包的学习笔记,详细介绍DBMS_SQLDIAG 包的简单学习测试步骤。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: DBMS_SQLDIAG 包的简单学习

关于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 Oracleoracleplus.net   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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle DBMS_SQLDIAG包使用详解

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1353.html

Oracle研究中心

关键词:

DBMS_SQLDIAG包的功能测试

DBMS_SQLDIAG 包的简单学习