Oracle plan 관련

-- plan
select id, parent_id p_id,
lpad(' ',2*(level-1))||operation operation,
options, object_name
from plan_table
start with id=0 and statement_id='temp_1'
connect by prior id=parent_id and statement_id='temp_1';

column operation format a25
column options format a20
column object_name format a20
column id format 999
column p_id format 999

-- trace on/off

alter session set sql_trace=true;
alter session set timed_statistics=true;

alter session set sql_trace=false;
alter session set timed_statistics=false;

$ORACLE_HOME/admin/$ORACLE_SID/udump

tkprof a.trc a.prf
cat a.prf

-- optimizer
alter session set optimizer_mode=choose;
alter session set optimizer_mode=all_rows;
alter session set optimizer_mode=first_rows;
alter session set optimizer_mode=rule;

-- create index
create index index_name on table_name (attr_name, ...);