DBMS_SQLDIAG 包的简单学习
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: DBMS_SQLDIAG 包的简单学习
|
1 2 |
关于dbms_sqldiag包，该包是10.2.0.4版本才开始提供的，10g中功能相对简单，在11g中就比较强悍了。 如下是自己今天做的几个简单的测试，算是初步了解如何去应用这个包了。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 |
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 '<CURRENT_DIRECTORY>'; dbms_sqldiag.import_sql_testcase( directory => 'TMP_TCB23061967_DIR' , filename => '<TESTCASEBUILDER_METADATA>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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
++++++ 似乎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) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
++++++ 通过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 |
|
1 2 3 |
由于其是wallet我以前就删除了，不知道为什么这里抛出这样的错误， 当时这里你可以可以手工去修改这个undo block，然后用rman copy回asm中。 由于本身是加密的，所以比较没法，来个简单的处理方式： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 |
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. |
|
1 |
还可以利用该包来看某个sqlid的执行计划，甚至是存在child number的，如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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> ! |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
[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 */ |
|
1 2 3 4 5 6 7 8 |
其实跟10053 的trace极度相似，查看某个sql的真实执行计划，以后完全可以使用该包来进行。 11gR2中，dbms_sqldiag就非常强悍了，虽然我这里测试感觉对于使用绑定变量的sql似乎没啥作用。 可能我这里测试不够仔细，当然是为了熟悉下该包的强大功能，大家也可以去研究研究！ 大家可以去这里了解该包的相关信息： <span style="color: #0000ff;"> $ORACLE_HOME/rdbms/admin/dbmsdiag.sql </span> |
Leave a Reply
You must be logged in to post a comment.