为快捷显示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.