[20150105]使用dbms_sqldiag.dump_trace.txt
--11gR2可以使用10053事件来获得执行计划。我感觉oracle 11gr2中使用dbms_sqldiag.dump_trace获得执行计划更方便，首先不需要做
--一次硬分析(10053是必须的).
--我感觉使用它会更加方便，特别是生产系统。自己做一个测试看看。
1.建立测试例子：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> var x number;
SCOTT@test> exec :x := 10 ;
PL/SQL procedure successfully completed.
SCOTT@test> select * from dept ,emp where dept.deptno= emp.deptno and dept.deptno=:x;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------- ----------- --------- ----- ---------- --------- ----- ------------------- ----- ----- -------
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g6xdugp97v21h, child number 0
-------------------------------------
select * from dept ,emp where dept.deptno= emp.deptno and dept.deptno=:x
Plan hash value: 568005898
----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 (100)|
| 1 | NESTED LOOPS | | 5 | 4 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 (0)|
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 0 (0)|
|* 4 | TABLE ACCESS FULL | EMP | 5 | 3 (0)|
----------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPT"."DEPTNO"=:X)
4 - filter("EMP"."DEPTNO"=:X)
--sql_id = g6xdugp97v21h
2.建立分析文件：
--建立一个脚本来执行它。
$ cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'&&1');
SCOTT@test> @10053x g6xdugp97v21h 0
PL/SQL procedure successfully completed.
SCOTT@test> @pp
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_18310_g6xdugp97v21h.trc
--还可以执行指定tracefile_identifier，这样查找文件更加方便。
3.转储内容忽略。
sql_text_length=98
sql=/* SQL Analyze(396,0) */ select * from dept ,emp where dept.deptno= emp.deptno and dept.deptno=:x
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | NESTED LOOPS | | 5 | 275 | 4 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 17 | 1 | 00:00:01 |
| 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 | |
| 4 | TABLE ACCESS FULL | EMP | 5 | 190 | 3 | 00:00:01 |
------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("DEPT"."DEPTNO"=:X)
4 - filter("EMP"."DEPTNO"=:X)
...
Peeked Binds
============
Bind variable information
position=1
datatype(code)=2
datatype(string)=NUMBER
precision=0
scale=0
max length=22
value=10
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1" "EMP"@"SEL$1")
LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
USE_NL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
