explain plan获取sql的执行计划

小鱼儿 2022-04-02 10:56 246阅读 0赞

explain plan工作实质、前提及操作方法  
     1. 工作实质  
      将SQL语句预估的执行计划加载到表plan\_table,是对表plan\_table 执行了DML操作，故不会执行隐式提交  
      可以对select,insert,update,merge,delete,create table, create index,alter index等加载执行计划到plan\_table

2. 前提条件  
      需要先创建plan\_table，创建方法：@?/rdbms/admin/utlxplan  
      对当前的SQL语句有执行权限以及对依赖的对象有相应操作的权限

3. 使用方法：  
       explain plan for select \* from scott.emp where ename='SCOTT';    --未设置标记位  
       explain plan set statement\_id='TEST' for select \* from scott.emp where ename='SCOTT'  --设置标记位为TEST

练习

1.创建测试表演示获取执行计划

[scott@ORCL][scott_ORCL]> create table t as select \* from all\_objects where rownum<=1000;                                        
                                                                                                                  
Table created.                                                                                                     
                                                                                                                  
\--加载创建表的执行计划(DDL 执行计划)                                                                                
[scott@ORCL][scott_ORCL]> explain plan set statement\_id='T1' for create table t1 as select \* from t;                             
                                                                                                                   
Explained.                                                                                                         
                                                                                                                  
\--对plan\_table执行一个层次查询获取执行计划                                                                           
col OPERATION format a25                                                                                           
col OPTIONS format a25                                                                                             
col OBJECT\_NAME format a25                                                                                         
SELECT lpad(' ', 2 \* (LEVEL - 1)) || operation operation                                                           
    ,options                                                                                                       
    ,object\_name                                                                                                   
    ,position pos                                                                                                  
    ,bytes                                                                                                         
    ,cost                                                                                                          
FROM plan\_table                                                                                                    
START WITH id = 0                                                                                                  
AND statement\_id =upper( '&input\_statement\_id')                                                                    
CONNECT BY PRIOR id = parent\_id;                                                                                   
                                                                                                                  
Enter value for input\_statement\_id: T1                                                                             
old   9: AND statement\_id =upper( '&input\_statement\_id')                                                           
new   9: AND statement\_id =upper( 'T1')                                                                            
                                                                                                                   
OPERATION                 OPTIONS                   OBJECT\_NAME                      POS      BYTES       COST     
\------------------------- ------------------------- ------------------------- ---------- ---------- ----------      
CREATE TABLE STATEMENT                                                                 8      79000          8     
  LOAD AS SELECT                                    T1                                 1                           
    TABLE ACCESS          FULL                      T                                  1      79000          5     
                                                                                                                   
\---为了更好的查看执行计划可以使用dbms\_xplan包如下  
select \* from table(dbms\_xplan.display());

[scott_ORCL]: mailto:scott@ORCL