SQL 튜닝 대상선정(10g)하기

-- SQL 튜닝 대상선정(10g)하기 select trunc(a.disk_reads/a.executions,0) diskreads,        trunc(a.buffer_gets/a.executions,0) bufferget,         trunc(a.elapsed_time/a.executions/1000000,0) elapsetime,        trunc(a.ROWS_PROCESSED/a.executions,0) return_rows,        a.executions,        a.last_load_time,        module,action, length(sql_fulltext), sql_fulltext, address,sql_id,parsing_schema_name   from v$sql  a   where executions > 0    and command_type in ( 3, 6,7)    and module not in ( 'SQL Developer','Orange for ORACLE DBA')    and buffer_gets / executions > 1000  order by elapsetime desc ;      command_type - 2 (insert) command_type - 3 (select) command_type - 7 (delete) command_type - 6 (update)    -- bind 변수 확인 select * from v$sql_bind_capture where address = 'C000000095EFDDC0'; select * from dba_hist_sqlbind where sql_id = '0b5b05k3akd1w'  order by snap_id desc, position;    -- full text select 'AA'||sql_text||'BB' from  v$sqltext_with_newlines where address = 'C000000095EFDDC0'  order by Piece select 'AA'||sql_text||'BB' from  v$sqltext_with_newlines where sql_id = 'gzcf51wp0pqxt'  order by Piece    -- plan보기 select p.plan_table_output   from (select distinct sql_id, child_number           from v$sql_plan s          where s.address = '' or                 s.sql_id = '0as4u6a4fky2n') s,         table(dbms_xplan.display_cursor (s.sql_id, s.child_number, 'typical')) p;    --  awr plan보기 select sql_id,lpad(' ',depth*4,'  ')||' '||operation|| ' ' ||options|| ' '  ||object_name|| ' (' ||cost||')'plan, to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') as "date"    from DBA_HIST_SQL_PLAN  where sql_id in ('fac0jhjuwg9k9')  order by timestamp,sql_id,plan_hash_value, id;    -- awr 성능 보기 select sql_id, module, b.begin_interval_time,        trunc(buffer_gets_delta/decode(executions_delta,0,1,executions_delta)) buffer_gets,        trunc(disk_reads_delta/decode(executions_delta,0,1,executions_delta)) disk_reads,        trunc(fetches_delta/decode(executions_delta,0,1,executions_delta)) fetchs,        trunc(ROWS_PROCESSED_DELTA/decode(executions_delta,0,1,executions_delta)) ROWS_PROCESSED,        trunc(elapsed_time_delta/1000000/decode(executions_delta,0,1,executions_delta))     as elapsed_time,        trunc(IOWAIT_DELTA/1000000/decode(executions_delta,0,1,executions_delta)) IOWAIT,        trunc(APWAIT_DELTA/1000000/decode(executions_delta,0,1,executions_delta)) APWAIT,        trunc(CLWAIT_DELTA/1000000/decode(executions_delta,0,1,executions_delta)) CLWAIT,        trunc(CCWAIT_DELTA/1000000/decode(executions_delta,0,1,executions_delta)) CCWAIT,        executions_delta executions  from DBA_HIST_SQLSTAT a,        dba_hist_snapshot b where a.sql_id = '7rcjrfsh81jy2'   and a.snap_id  = b.snap_id   and a.dbid = b.dbid   and b.instance_number = 1   and b.begin_interval_time between to_timestamp('20110701','YYYYMMDD')  and to_timestamp('2012','YYYY')   order by a.snap_id;    -- trace를 못 뜰때?ㅋ  select /*+ gather_plan_statistics */ *  from SCOTT.TEST where key > 10000; select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));    dbms_xplan.display_cursor format 종류    - Basic, Typical, Outline, All, Advanced,     - allstats last, +peeked_binds

sql_tuning_script_etc1.txt