总结了执行计划的几种方式
1、autotrace
@?/rdbms/admin/utlxplan
grant all on plan_table to public;
@?/sqlplus/admin/plustrce
grant plustrace to public
SCOTT@fyl>set autotrace on
SCOTT@fyl>select * from t where object_id=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4182247035
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SCOTT@fyl>set autotrace off
2、EXPLAIN
SCOTT@fyl>explain plan for select * from t where object_id=1;
SCOTT@fyl>select plan_table_output from table(dbms_xplan.display('plan_table'));
SCOTT@fyl>select * from table(dbms_xplan.display); ----两语句左右相等
3、dbms_xplan包
按照场景不同,有以下四种方法:
1.select * from table(dbms_xplan.display);
2.select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
3.select * from table(dbms_xplan.display_cursor(’sql_id/hash_value’,child_cursor_number,’advanced’));
4.select * from table(dbms_xplan.display_awr(’sql_id'));
第一种方法就是explain plan。
第二种方法是查看刚刚执行过的sql的执行计划。
第三种方法是查看指定sql的执行计划,需要sql_id或hash_value,还有child_number(v$sql)。
select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like ‘%xxxx%’;
第四种方法是查看指定sql所有历史执行计划,第二、第三种方法需要其sql执行计划还在shared pool种,如果被age out出shared pool(v$sqlarea中查询不到),只要其被采集到awr repository中,就可以用第四种方法查看。(v$sqlarea中的version_count代表有几个执行计划)
select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘xxx’; —可以查询到
exec dbms_workload_repository.create_snapshot; --采集awr
alter system flush shared_pool; —清理shared pool
select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘xxx’; —查询不到
此时已经无法使用第二、第三种方法查看。但是第四种方法无法显示谓词条件,不完美。
4、视图中查询v$sql_plan/DBA_HIST_SQL_PLAN
SELECT sp.id,lpad(' ',sp.DEPTH*3,' ')||sp.OPERATION||' '||sp.OPTIONS operation ,sp.OBJECT_NAME FROM V$SQL_PLAN sp WHERE SQL_ID='2qhwh0nzrzx2r' ORDER BY ID;
5、使用TOAD/PLSQL DEVELOPER等工具
6、printsql.prc
http://blog.csdn.net/u013820054/article/details/43405749
使用sys用户@/xxx/printsql.prc创建过程
set serveroutput on size 1000000
exec printsql(xxxxx,'SID')
exec printsql(xxxxx,'SPID')
7、10046事件
10046 event的追踪级别大致有:
level 1:跟踪sql语句,包括解析、执行、提取、提交和回滚等。
level 4:包括变量的详细信息
level 8:包括等待事件
level 12:包括绑定变量与等待事件
其中,level 1相当于打开了sql_trace
http://www.askmaclean.com/archives/maclean-10046-sql-trace.html
使用前设置参数
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
max_dump_file_size string unlimited
tracefile_identifier string --trace标示符
启动10046方法1:
SCOTT@fyl>alter session set events '10046 trace name context forever, level 8';
SCOTT@fyl> sql语句
SCOTT@fyl>alter session set events '10046 trace name context off';
2:)使用oradebug工具
使用oradebug工具必须要知道所要处理的进程的OS进程PID,OS PID可以使用下面的语句得到:
select spid from v$process where addr in (select paddr from v$session where username ='SCOTT')
得到PID之后就可以使用oradebug工具了,注意需要使用sysdba登陆到数据库:
SYS@fyl>oradebug setospid 5016; ---oradebug setmypid
Oracle pid: 28, Unix process pid: 5016, image: oracle@lx01 (TNS V1-V3)
SYS@fyl>oradebug unlimit;
Statement processed.
SYS@fyl>oradebug event 10046 trace name context forever ,level 12;
Statement processed.
在5016session执行SQL
SYS@fyl>Oradebug event 10046 trace name context off;
Statement processed.
SYS@fyl> oradebug tracefile_name
格式化trace文件(直接生成的trace不方便阅读) 可以用tkprof命令
tkprof fyl_ora_5016.trc my.txt (sys等参数可以自己查看)
原trace文件中部分参数含义
PARSING IN CURSOR 部分:
Len: 被解析SQL的长度
Dep: 产生递归SQL的深度
Uid:user id
Oct: Oracle command type 命令的类型
Lid: 私有用户id
Tim:时间戳
Hv: hash value
Ad:SQL address
PARSE,EXEC,FETCH 部分
C: 消耗的CPU time
E:elapsed time 操作的用时
P: physical reads 物理读的次数
Cr: consistent reads 一致性方式读取的数据块
Cu:current 方式读取的数据块
Mis:cursor misss in cache 硬分析次数
R: -rows 处理的行数
Dep: depth 递归SQL的深度
Og: optimizer goal 优化器模式
Tim:timestamp时间戳
STATS 部分:
Id: 执行计划的行源号
Cnt:当前行源返回的行数
Pid:当前行源号的父号
Pos:执行计划中的位置
Obj:当前操作的对象id(如果当前行原始一个对象的话)
Op:当前行源的数据访问操作
8、使用DBMS_SYSTEM包(可以追踪其他session)
SYS@fyl>DESC DBMS_SYSTEM
PROCEDURE ADD_PARAMETER_VALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARNAME VARCHAR2 IN
VALUE VARCHAR2 IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
POSITION BINARY_INTEGER IN DEFAULT
PROCEDURE DIST_TXN_SYNC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
INST_NUM NUMBER IN
PROCEDURE GET_ENV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAR VARCHAR2 IN
VAL VARCHAR2 OUT
PROCEDURE KCFRMS
PROCEDURE KSDDDT
PROCEDURE KSDFLS
PROCEDURE KSDIND
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LVL BINARY_INTEGER IN
PROCEDURE KSDWRT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DEST BINARY_INTEGER IN
TST VARCHAR2 IN
PROCEDURE READ_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IEV BINARY_INTEGER IN
OEV BINARY_INTEGER OUT
PROCEDURE REMOVE_PARAMETER_VALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARNAME VARCHAR2 IN
VALUE VARCHAR2 IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
PROCEDURE REMOVE_PARAMETER_VALUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARNAME VARCHAR2 IN
POSITION BINARY_INTEGER IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
PROCEDURE SET_BOOL_PARAM_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
BVAL BOOLEAN IN
PROCEDURE SET_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
PROCEDURE SET_INT_PARAM_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
INTVAL BINARY_INTEGER IN
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
PROCEDURE WAIT_FOR_EVENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
EVENT VARCHAR2 IN
EXTENDED_ID BINARY_INTEGER IN
TIMEOUT BINARY_INTEGER IN
DBMS_SYSTEM包提供了两个开启10046 Trace的方法,一个是使用SET_SQL_TRACE_IN_SESSION过程,不过使用这个过程的效果和sql_trace是一样的:
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(:sid, :serial#, true);
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(:sid, :serial#, false);
另一个方法是使用SET_EV过程,当然这个过程不仅仅用来设置10046事件,还能设置所有的其他的事件,使用方法为:
PROCEDURE SET_EV
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
使用例子:
EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 12, '');
EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 0, '');
9、使用DBMS_MONITOR包(摘抄、未测试)
Oracle 10g中DBMS_MONITOR包的出现改变了以往一次只能开启一个会话的历史,开启了一个批量启用10046 Trace的新纪元。
使用DBMS_MONITOR包可以根据client identifier来Trace多个不同的会话,client identifier可以通过V$SESSION里面client_identifier字段看到,使用方法如下:
PROCEDURE SESSION_TRACE_DISABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SESSION_ID BINARY_INTEGER IN DEFAULT
SERIAL_NUM BINARY_INTEGER IN DEFAULT
PROCEDURE SESSION_TRACE_ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SESSION_ID BINARY_INTEGER IN DEFAULT
SERIAL_NUM BINARY_INTEGER IN DEFAULT
WAITS BOOLEAN IN DEFAULT
BINDS BOOLEAN IN DEFAULT
PLAN_STAT VARCHAR2 IN DEFAULT
trace单个会话12级
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>true,BINDS=>true); ---本会话
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(:sid, :serial, true, true);
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE();
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(:sid, :serial);
根据Client Identifier追踪
-- 找出要Trace的client_identifier信息
SQL> SELECT sid, program, client_identifier FROM V$SESSION;
SID PROGRAM CLIENT_IDENTIFIER
---------- ------------------------------------------------ ----------------------------------------
71 sqlplus@orainst.desktop.mycompany.com (TNS V1-V3) oracle@orainst.desktop.mycompany.com
72 rman@orainst.desktop.mycompany.com (TNS V1-V3) oracle@orainst.desktop.mycompany.com
75 rman@orainst.desktop.mycompany.com (TNS V1-V3) oracle@orainst.desktop.mycompany.com
-- 假设要Trace client_identifier是“oracle@orainst.desktop.mycompany.com”的所有会话
-- 使用下面的语句即可,开启一个level 12的Trace
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('oracle@orainst.desktop.mycompany.com', true, true);
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('oracle@orainst.desktop.mycompany.com');
根据Service, Module和Action追踪
DBMS_MONITOR包的SERV_MOD_ACT_TRACE_ENABLE过程用来根据Service, Module和Action三个属性开启多个会话的Trace。
在进行Trace之前要确保你的应用程序设置了这三个相应的属性,Oracle提供了包DBMS_APPLICATION_INFO用来设置module、action等信息,使用方法如下:
EXEC dbms_application_info.SET_MODULE('Trace Test', 'No Trace');
EXEC DBMS_APPLICATION_INFO.SET_ACTION(''No Trace');
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('A Haaaa');
设置完成之后我们就可以在V$SESSION看到这些信息了:
SQL> COL ACTION FOR A10
SQL> COL MODULE FOR A45
SQL> COL SERVICE_NAME FOR A12
SQL> COL SID FOR 999
SQL> SELECT SID , service_name, module, action FROM v$session WHERE TYPE<>'BACKGROUND';
SID SERVICE_NAME MODULE ACTION
---- ------------ --------------------------------------------- ----------
67 SYS$USERS SQL Developer
71 SYS$USERS sqlplus@orainst.desktop.mycompany.com (TNS V1-V3)
72 SYS$USERS Trace Test No Trace
知道了相应的信息再使用DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE来启用对相应会话的Trace:
-- 针对service name为“SYS$USERS”的所有会话开启Trace
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'SYS$USERS', waits => TRUE, binds => FALSE);
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'SYS$USERS');
-- 针对特定的action开启Trace
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'SYS$USERS',
module_name=>'Trace Test',
action_name => 'Trace',
waits => TRUE, binds => FALSE);
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'SYS$USERS',
module_name=>'Trace Test',
action_name => 'Trace')
开启数据库级别Trace
DBMS_MONITOR还能开启数据库级别的Trace。
-- 定义
PROCEDURE DATABASE_TRACE_DISABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
INSTANCE_NAME VARCHAR2 IN DEFAULT
PROCEDURE DATABASE_TRACE_ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
WAITS BOOLEAN IN DEFAULT
BINDS BOOLEAN IN DEFAULT
INSTANCE_NAME VARCHAR2 IN DEFAULT
PLAN_STAT VARCHAR2 IN DEFAULT
-- 开启数据库级的level 12的Trace
EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE(true, true);
-- 停止数据库级的Trace
EXEC DBMS_MONITOR.DATABASE_TRACE_DISABLE();
开启多个会话Trace的注意点,这个功能影响整个数据库,很强大,慎用!
使用DBMS_MONITOR开启多个会话的Trace是动态的,比如说当你要追踪某个特定的action的Trace的时候,你并不需要先确定那个特定的action对应的会话正在运行中才能开启相应的Trace,相
反的是一旦某个进程的action满足当前开启的Trace的条件的时候,那个会话就会开始输出Trace信息,当会话的action发生改变之后,Trace信息也会停止输出,下面是一个简单的测试。
先在一个进程中开始针对module为“Trace Test”,action为“Trace”的Trace:
SQL> EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'SYS$USERS',
module_name=>'Trace Test',
action_name => 'Trace',
waits => TRUE, binds => FALSE);
然后在另外一个进程中执行下面的一序列语句:
-- 设置当前会话的module和action
SQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE('Trace Test', 'Trace');
PL/SQL procedure successfully completed.
-- 执行一个简单的查询
SQL> select 'trace' from dual;
TRACE
------
trace
-- 改变当前会话的action
SQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE('Trace Test', 'No Trace');
PL/SQL procedure successfully completed.
-- 再执行另一个查询
SQL> select 'no trace' from dual;
NOTRACE
--------
no trace
执行完毕之后打开Trace文件就会发现第一次执行module、action设置的语句和第二次执行的“select ‘no trace’ from dual;”都没有出现在Trace文件