为快捷显示Oracle执行计划创建存储过程

yui

为快捷显示Oracle执行计划创建存储过程

第一种：不设置输出格式参数，即用默认的

• [SQL] 纯文本查看 复制代码

  SQL> create or replace procedure sql_explain(v_sql varchar2)  
    2  is  
    3  type explain_cursor_type is ref cursor;  
    4  explain_cursor explain_cursor_type;  
    5  a varchar2(2048);  
    6  begin  
    7    execute immediate 'explain plan for '||v_sql;  
    8    open explain_cursor for select PLAN_TABLE_OUTPUT  from table(dbms_xplan.display());  
    9    loop  
   10      fetch explain_cursor into a;  
   11      exit when explain_cursor%NOTFOUND;  
   12     dbms_output.put_line(a);  
   13    end loop;  
   14  end;  
   15  /  
    
  Procedure created.  
  SQL> exec sql_explain('select a.name,b.name from t1 a,t2 b where a.id=b.id and a.id=1');  
  Plan hash value: 2680223496  
  --------------------------------------------------------------------------------------  
  | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
  --------------------------------------------------------------------------------------  
  |   0 | SELECT STATEMENT             |       |     1 |    17 |     4   (0)| 00:00:01 |  
  |   1 |  NESTED LOOPS                |       |     1 |    17 |     4   (0)| 00:00:01 |  
  |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     8 |     1   (0)| 00:00:01 |  
  |*  3 |    INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |  
  |*  4 |   TABLE ACCESS FULL          | T2    |     1 |     9 |     3   (0)| 00:00:01 |  
  --------------------------------------------------------------------------------------  
  Predicate Information (identified by operation id):  
  ---------------------------------------------------  
  3 - access("A"."ID"=1)  
  4 - filter("B"."ID"=1)  
    
  PL/SQL procedure successfully completed.  
    
  SQL> exec sql_explain('select a.name,b.name from t1 a,t2 b where a.id=b.id and a.id=''1''');  
  Plan hash value: 2680223496  
  --------------------------------------------------------------------------------------  
  | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
  --------------------------------------------------------------------------------------  
  |   0 | SELECT STATEMENT             |       |     1 |    17 |     4   (0)| 00:00:01 |  
  |   1 |  NESTED LOOPS                |       |     1 |    17 |     4   (0)| 00:00:01 |  
  |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     8 |     1   (0)| 00:00:01 |  
  |*  3 |    INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |  
  |*  4 |   TABLE ACCESS FULL          | T2    |     1 |     9 |     3   (0)| 00:00:01 |  
  --------------------------------------------------------------------------------------  
  Predicate Information (identified by operation id):  
  ---------------------------------------------------  
  3 - access("A"."ID"=1)  
  4 - filter("B"."ID"=1)  
    
  PL/SQL procedure successfully completed. 

  
  

第二种：添加format参数，灵活选择

• [SQL] 纯文本查看 复制代码

  SQL> create or replace procedure sql_explain(v_sql varchar2,v_format varchar2)  
    2  is  
    3  type explain_cursor_type is ref cursor;  
    4  explain_cursor explain_cursor_type;  
    5  a varchar2(2048);  
    6  begin  
    7    execute immediate 'explain plan for '||v_sql;  
    8    open explain_cursor for select PLAN_TABLE_OUTPUT  from table(dbms_xplan.display(null,null,v_format));  
    9    loop  
   10      fetch explain_cursor into a;  
   11      exit when explain_cursor%NOTFOUND;  
   12     dbms_output.put_line(a);  
   13    end loop;  
   14  end;  
   15  /  
    
  Procedure created.  
  SQL> exec sql_explain('select a.name,b.name from t1 a,t2 b where a.id=b.id and a.id=1','all');  
  Plan hash value: 2680223496  
  --------------------------------------------------------------------------------------  
  | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
  --------------------------------------------------------------------------------------  
  |   0 | SELECT STATEMENT             |       |     1 |    17 |     4   (0)| 00:00:01 |  
  |   1 |  NESTED LOOPS                |       |     1 |    17 |     4   (0)| 00:00:01 |  
  |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     8 |     1   (0)| 00:00:01 |  
  |*  3 |    INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00:01 |  
  |*  4 |   TABLE ACCESS FULL          | T2    |     1 |     9 |     3   (0)| 00:00:01 |  
  --------------------------------------------------------------------------------------  
  Query Block Name / Object Alias (identified by operation id):  
  -------------------------------------------------------------  
  1 - SEL$1  
  2 - SEL$1 / A@SEL$1  
  3 - SEL$1 / A@SEL$1  
  4 - SEL$1 / B@SEL$1  
  Predicate Information (identified by operation id):  
  ---------------------------------------------------  
  3 - access("A"."ID"=1)  
  4 - filter("B"."ID"=1)  
  Column Projection Information (identified by operation id):  
  -----------------------------------------------------------  
  1 - (#keys=0) "A"."NAME"[VARCHAR2,32], "B"."NAME"[VARCHAR2,32]  
  2 - "A"."NAME"[VARCHAR2,32]  
  3 - "A".ROWID[ROWID,10]  
  4 - "B"."NAME"[VARCHAR2,32]  
    
  PL/SQL procedure successfully completed.  

  
  

0

微机原理闹危机，随机过程随机过，实变函数学十遍，汇编语言不会编！

xiaui520

生，容易。活，容易。生活，不容易。

zhangli-s

谢谢楼主，共同发展

阿尔哦覅和

没看完~~~~~~ 先顶，好同志

西湖鱼

脱了衣服我是禽兽，穿上衣服我是衣冠禽兽!

jmton

老天，你让夏天和冬天同房了吧？生出这鬼天气!