1、直接使用sqlplus系统参数:
SQL> set autotrace on explain
SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
-
X
Execution Plan
----------------------------------------------------------
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 |
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------
SQL> set autotrace off
但是这样操作的结果是先执行SQL,再出执行计划,如果SQL耗时巨大,则不现实;
2、使用explain plan for语句:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(DBMS_XPLAN.display);
Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089
Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 21 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | |
| |
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 21 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | |
| |
---------------------------------------------------------------------------------------------
这样就可以在执行SQL之前查看执行计划了
3、启用SQL_TRACE跟踪所有后台进程活动:
全局参数设置:
..OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)
当前session中设置:
SQL> alter session set SQL_TRACE=true;
SQL> select * from dual;
SQL> alter session set SQL_TRACE=false;
对其他用户进行跟踪设置:
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
------ ---------- ------------------
127 31923 A
128 54521 B
129 48940 B
SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
SQL> select * from dual;
SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自带的tkprof命令行工具格式化跟踪文件。
4、使用10046事件进行查询:
10046事件级别:
Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE
Lv4 - Level 1 + 绑定值(bind values)
Lv8 - Level 1 + 等待事件跟踪
Lv12 - Level 1 + Level 4 + Level 8
全局设定:
..OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
当前session设定:
SQL> alter session set events '10046 trace name context forever, level 8';
SQL> select * from dual;
SQL> alter session set events '10046 trace name context off';
对其他用户进行设置:
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
------ ---------- ------------------
127 31923 A
128 54521 B
129 48940 B
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
SQL> select * from dual;
SQL> exec dbms_system.set_ev(127,31923,10046,0,'A');
5、使用tkprof格式化跟踪文件:
使用一下SQL找到当前session的跟踪文件:
----
当前
session
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
SELECT
d.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'
) d;
----
其他用户
session
SELECT
d.value||
'/'
||lower(rtrim(i.instance, chr(
0
)))||
'_ora_'
||p.spid||
'.trc'
trace_file_name
from
(
select
p.spid
from
v$session s, v$process p
where
s.sid=
'127'
and
s. SERIAL#=
'31923'
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'
) d;
查找后使用tkprof命令:
SQL> $tkprof D:\......\SID_ora_5352.trc D:\......\SID_ora_5352.txt
注:无法使用autotrace的解决办法(9i):
SQL>start $ORACLE_HOME/rdbms/admin/utlxplan.sql;
SQL>create public synonym plan_table for plan_table;
SQL>grant ALL on plan_table to public;
-The End-
转载于:https://blog.51cto.com/mibon/143248