查看执行计划的几种方式总结

悠悠 2022-08-11 16:37 235阅读 1赞

总结了执行计划的几种方式

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文件