oracle如何查看存储过程的执行计划,为快捷显示Oracle执行计划创建存储过程-Oracle...

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

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

[sql]   www.2cto.com

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.    www.2cto.com

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