oracle执行计划-001

查看数据库执行计划：
参考资料：
http://www.jb51.net/article/30465.htm
http://www.cnblogs.com/jianggc/articles/2029854.html

1，pl/sql developer f5

2,  sqlplus
SQL>EXPLAIN PLAN FOR SELECT * FROM SCOTT.EMP;
SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

3, autotrace
set autotrace traceonly
set autot on
set autot traceonly stat

4, sql trace
ALTER SESSION SET SQL_TRACE=TRUE;
TKPROF

SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE USERNAME = 'ETL'
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,TRUE);
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,FALSE);

SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T

5, 10046 /10053
ALTER SESSION SET TRACEFILE_IDENTIFIER = 10046;
ALTER SESSION SET EVENTS='10046 trace name context forever, level 8';
SELECT * FROM SCOTT.EMP;
ALTER SESSION SET EVENTS ='10046 trace name context off';

6, 系统视图
SELECT * FROM V$SQL_PLAN
SELECT * FROM V$RSRC_PLAN_CPU_MTH
SELECT * FROM V$SQL_PLAN_STATISTICS
SELECT * FROM V$SQL_PLAN_STATISTICS_ALL
SELECT * FROM V$SQLAREA_PLAN_HASH
SELECT * FROM V$RSRC_PLAN_HISTORY

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/89196/viewspace-1458190/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/89196/viewspace-1458190/