| SQL 4DBA's Plan table introduction |
This is the syntax for explaining the plan of an sql-statement:
EXPLAIN PLAN FOR
<sql_statement>
The result of this is put in the Plan_table.
Note: you must have access to a "Plan_table" in the database in order to explain a execution plan.
Id-Pid OPERATION OBJECT_NAME OBJECT_TYPE OPTIONS Optmzer ------ ---------------- ----------------------- ----------- ----------- ------- 0- SELECT STATEMENT CHOOSE 1-0 NESTED LOOPS 2-1 TABLE ACCESS EMP FULL 3-1 TABLE ACCESS DEPT BY ROWID 4-3 INDEX PK_DEPT UNIQUE UNIQUE SCAN
rem name: utlxplan.sql rem This statement creates the Plan table for Oracle 9i rem You can find it in $ORACLE_HOME/rdbms/admin on the Database server create table PLAN_TABLE ( statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000)) ;
rem PLAN1.sql rem Show plan_table execution from Plan table. col COL1 format A6 head 'Id-Pid' col parent_id format 99 head pnt col operation format A16 col object_name format A23 truncate col object_type format A11 col options format A11 truncate col optimizer format A7 head 'Optmzer' select id||'-'||parent_id COL1 ,operation ,object_name ,object_type ,options ,optimizer from plan_table order by id ; OUTPUT:Id-Pid OPERATION OBJECT_NAME OBJECT_TYPE OPTIONS Optmzer ------ ---------------- ----------------------- ----------- ----------- ------- 0- SELECT STATEMENT CHOOSE 1-0 NESTED LOOPS 2-1 TABLE ACCESS EMP FULL 3-1 TABLE ACCESS DEPT BY ROWID 4-3 INDEX PK_DEPT UNIQUE UNIQUE SCAN
rem PLAN2.sql rem Show plan_table execution by level col Q1 format A80 heading QueryPlan select lpad(' ',2*(level-1))||level||'.'||nvl(position,0) ||' '||operation||' '||options ||' '||object_type||' '||object_name Q1 from plan_table start with id = 0 connect by prior id = parent_id ; OUTPUT:QueryPlan -------------------------------------------------------- 1.0 SELECT STATEMENT 2.1 NESTED LOOPS 3.1 TABLE ACCESS FULL EMP 3.2 TABLE ACCESS BY INDEX ROWID DEPT 4.1 INDEX UNIQUE SCAN UNIQUE PK_DEPT
rem PLAN3.sql rem desc: Select from Plan table with timestamped spool file rem This creates a spool file named mondd.HHMI (jan18.1412) rem of the execution plan in the plan_table records. rem col COL1 format A6 head 'Id-Pid' col parent_id format 99 head pnt col operation format A16 col object_name format A23 truncate col object_type format A11 col options format A10 truncate col optimizer format A9 prompt creating timestamped spool file... set verify off echo off feed off wrap off heading off spool tmpspool.sql select 'spool '||to_char(sysdate,'mondd.HH24MI') from sys.dual ; spool off @tmpspool.sql host delete tmpspool.sql -- Windows rem !rm tmpspool.sql -- Unix rem rem select from Plan table: set heading ON term ON select id||'-'||parent_id COL1,operation ,object_name,object_type ,options,optimizer from plan_table order by id ; spool OFF set feed ON OUTPUT in file mondd.HHMI:Id-Pid OPERATION OBJECT_NAME OBJECT_TYPE OPTIONS Optmzer ------ ---------------- ----------------------- ----------- ----------- ------- 0- SELECT STATEMENT CHOOSE 1-0 NESTED LOOPS 2-1 TABLE ACCESS EMP FULL 3-1 TABLE ACCESS DEPT BY ROWID 4-3 INDEX PK_DEPT UNIQUE UNIQUE SCAN
rem PLAN4.sql rem desc: Show plan_table execution AND index and table name from User_ind_columns set feed OFF col C1 format A6 head 'Id-Pid' col parent_id format 99 head pnt col operation format A16 col object_name format A22 truncate head 'Object name' col object_type format A11 col options format A11 truncate col optimizer format A9 select id||'-'||parent_id C1,operation ,object_name,object_type ,options,optimizer from plan_table order by id ; rem Part II col object_type format A11 head 'Scan type' col object_name format A22 truncate head 'Index name' col column_name format A20 truncate col table_name format A16 truncate break on object_name on C1 select id||'-'||parent_id C1 ,I.table_name,object_name,I.column_name,object_type from plan_table ,user_ind_columns I where object_name = I.index_name(+) and operation = 'INDEX' order by id ; set feed ON OUTPUT:Id-Pid OPERATION Object name Scan type OPTIONS OPTIMIZER ------ ---------------- ---------------------- ----------- ----------- --------- 0- SELECT STATEMENT CHOOSE 1-0 NESTED LOOPS 2-1 TABLE ACCESS EMP FULL 3-1 TABLE ACCESS DEPT BY ROWID 4-3 INDEX PK_DEPT UNIQUE UNIQUE SCAN Id-Pid TABLE_NAME Index name COLUMN_NAME Scan type ------ ---------------- ---------------------- -------------------- ----------- 4-3 DEPT PK_DEPT DEPTNO UNIQUE
rem indX.sql - Indexed columns of 1 or more tables of 1 user rem desc : Shows Indexed columns, uniqueness, distinct_keys. rem usage: Enter a table_name or first 10 characters of a table_name. rem Return gives ALL indexes of the user running the statement. col table_name format A16 trunc col index_name format A18 trunc col column_name format A20 trunc col column_position format 99 head 'Col|pos' col uniqueness format A9 head 'Unique?' col distinct_keys format 9999999 head 'Distinct|keys' break on table_name skip 1 select I.table_name,I.index_name ,I.uniqueness,I.distinct_keys ,C.column_name,C.column_position from USER_IND_COLUMNS C ,USER_INDEXES I where C.index_name = I.index_name and I.table_name like upper('&table_name%') order by table_name,index_name,column_position ; OUTPUT:Distinct Col TABLE_NAME INDEX_NAME Unique? keys COLUMN_NAME pos ---------------- ------------------ --------- -------- -------------------- --- DEPT PK_DEPT UNIQUE DEPTNO 1 EMP PK_EMP UNIQUE EMPNO 1
| Plan table introduction |