Create a stored procedure for quick display of Oracle execution plans

Create a stored procedure for quick display of Oracle execution plans first: use the default [SQL] www.2cto.com SQL> create or replace procedure SQL _explain (v_ SQL varchar2) without setting output format parameters) 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 terminate (a); 13 end loop; 14 end; 15/Procedure created. www.2cto.com SQL> exec SQL _explain ('select. name, B. name from t1 a, t2 B where. id = B. id and. id = 1'); Plan hash value: 2680223496 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 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 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 3-access ("". "ID" = 1) 4-filter ("B ". "ID" = 1) PL/SQL procedure successfully completed. SQL> exec SQL _explain ('select. name, B. name from t1 a, t2 B where. id = B. id and. id = '1'); Plan hash value: 2680223496 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 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 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 3-access ("". "ID" = 1) 4-filter ("B ". "ID" = 1) PL/SQL procedure successfully completed. type 2: add the format parameter and select [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. name, B. name from t1 a, t2 B where. id = B. id and. id = 1', 'all'); Plan hash value: 2680223496 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 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 | invalid 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 ("". "ID" = 1) 4-filter ("B ". "ID" = 1) Column Projection Information (identified by operation id): --------------------------------------------------------- 1-(# keys = 0) "". "NAME" [VARCHAR2, 32], "B ". "NAME" [VARCHAR2, 32] 2-"". "NAME" [VARCHAR2, 32] 3-"". ROWID [ROWID, 10] 4-"B ". "NAME" [VARCHAR2, 32] PL/SQL procedure successfully completed.