[20150105]使用dbms_sqldiag.dump_trace

[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
  */