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

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

WBOY
WBOYOriginal
2016-06-07 17:27:151296browse

为快捷显示Oracle执行计划创建存储过程 第一种:不设置输出格式参数,即用默认的 SQLgt; create or replace procedure sql_expl

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

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

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.

linux

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn