Oracle 檢視SQL的執行計劃

2、利用explain plan for語句產生執行計劃
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>
這樣可以直接產生執行計劃，沒有產生SQL結果，應該相當於SQL SERVER中的預執行計劃。 

啟用會話級的SQL_TRACE跟蹤

SQL> alter session set sql_trace=true;

Session altered.

SQL> select count(*) from dual;

 COUNT(*)
----------
        1

SQL> alter session set sql_trace=false;

Session altered. 

對其他使用者進行跟蹤

SQL> select sid,serial#,username from v$session where username='MYUSER';

      SID    SERIAL# USERNAME
---------- ---------- ------------------------------
      143          5 MYUSER

SQL> exec dbms_system.set_SQL_TRACE_in_session(143,5,true);

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_SQL_TRACE_in_session(143,5,false);

PL/SQL procedure successfully completed. 

最後可以得用ORACLE工具tkprof格式化跟蹤檔案即可。

10046事件級別：    
Lv1 - 啟用標準的SQL_TRACE功能,等價於SQL_TRACE    
Lv4 - Level 1 + 繫結值(bind values)    
Lv8 - Level 1 + 等待事件跟蹤    
Lv12 - Level 1 + Level 4 + Level 8 

全域性設定格式，在引數檔案加入：

EVENT='10046 trace name context forever,level 12';

當前session的設定：
SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select * from dual;

D
-
X

SQL> alter session set events '10046 trace name context off';

Session altered. 

跟蹤其他使用者：
SQL> select sid,serial#,username from v$session where username='MYUSER';

      SID    SERIAL# USERNAME
---------- ---------- ------------------------------
      142         71 MYUSER
      143          5 MYUSER

SQL> exec dbms_system.set_ev(143,5,10046,8,'A');

PL/SQL procedure successfully completed.

SQL> select * from dual;

D
-
X

SQL> exec dbms_system.set_ev(143,5,10046,0,'A');

PL/SQL procedure successfully completed. 

5、使用tkprof格式化跟蹤檔案

檢視當前session的跟蹤檔案：

--當前會話

SQL> SELECT    d.VALUE
 2        || '\'
 3        || LOWER (RTRIM (i.INSTANCE, CHR (0)))
 4        || '_ora_'
 5        || p.spid
 6        || '.trc' trace_file_name
 7   FROM (SELECT p.spid
 8           FROM v$mystat m, v$session s, v$process p
 9          WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10        (SELECT t.INSTANCE
11           FROM v$thread t, v$parameter v
12          WHERE v.NAME = 'thread'
13            AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14        (SELECT VALUE
15           FROM v$parameter
16          WHERE NAME = 'user_dump_dest') d;

TRACE_FILE_NAME
------------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_3868.trc 

檢視其他session的跟蹤檔案

--其他session

SQL> SELECT    d .VALUE
 2        || '\'
 3        || LOWER (RTRIM ( i .INSTANCE, CHR ( 0 )))
 4        || '_ora_'
 5        || p . spid
 6        || '.trc' trace_file_name
 7    FROM (SELECT p . spid
 8            FROM v$session s , v$process p
 9           WHERE s .SID = '143' AND s . serial# = '5' AND p . addr = s . paddr ) p ,
10         (SELECT t .INSTANCE
11            FROM v$thread t , v$parameter v
12           WHERE v .NAME = 'thread'
13             AND ( v .VALUE = 0 OR t . thread# = TO_NUMBER ( v .VALUE))) i ,
14         (SELECT VALUE
15            FROM v$parameter
16           WHERE NAME = 'user_dump_dest' ) d ;

TRACE_FILE_NAME
---------------------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_3812.trc 

SQL> host tkprof C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_3868.trc F:\test\3868.txt

TKPROF: Release 10.2.0.4.0 - Production on Fri Sep 10 11:18:10 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.



SQL> host tkprof C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_3812.trc F:\test\3812.txt

TKPROF: Release 10.2.0.4.0 - Production on Fri Sep 10 11:18:37 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

--The End---