[SQL] 튜닝 대상 query 확인

/*  [get_sqllist.sql]
    Execution마다 평균 몇 개의 Oracle Block을 읽었는지(Buffer로부터. 만일 Buffer
    에 오러클 Block이 없었다면 Disk로부터 가져온 값도 포함됨)의 역순으로 보여주므로 위의 Top
    순서 위주로 비효율적인 SQL로 판단하면 됨. 특히 ROWS_PROCESSED(처리된 Row수)가 작으면
    서 BUFGETSPEREXEC(평균 Exec당 Buffer Block Read Count)가 큰 SQL위주로 Tuning.
    또한 EXECUTIONS가 높은 SQL문장이 자주 실행되므로 이들 SQL문장은 반드시 Tuning. 그러나
    1회성 SQL들은 EXECUTIONS이 작지만 SQL로 자주 실행되는 형태를 판단해야 함.
*/

 

select sql_text /* SQL 의 Text(V$SQL.SQL_TEXT는 최대 1KB만 보여줌) */
     , round(decode(executions,null,0,0,0,(nvl(buffer_gets,0)/executions)),1)  BUFGETSPEREXEC
     , round(decode(executions,null,0,0,0,(nvl(ELAPSED_TIME,0)/executions)),1) ElapsedTimePerExec
     , EXECUTIONS        /* SQL문장이 실행된 횟수 (Instance Startup이후 의 누적치).비 공유 SQL일 경우 대부분 1. 공유 SQL일 경우 이 값이 높다. */
     , PARSE_CALLS       /* Parse Request를 요청한 수(대부분 < executions) */
     , DISK_READS        /* SQL을 실행하기 위해 Disk로부터 읽어온 Oracle Block수(읽은 Size는 이 값 * db_block_size)*/
     , BUFFER_GETS       /* SQL을 실행하기 위해 Buffer로부터 읽어온 Oracle Block수.주로 이 Column의 값을 exections으로 나누어 높은 값의 SQL이 비효율적인 SQL임. (Disk로부터 읽어 온 값도 여기에 포함되어 있음) */
     , ROWS_PROCESSED    /* SQL로 실행되어 처리된(Select Low수 또는 Transaction대상 Row수) */
     , SHARABLE_MEM     
     , PERSISTENT_MEM   
     , RUNTIME_MEM      
     , MODULE            /* 이 SQL문장을 실행했던 APP의 Module명 (예. SQL*Plus,T.O.A.D).DBMS_APPLICATION_INFO.SET_MODULE 로 Application에서 실행하게 되면 나타나며어떤 module에서 들어온 SQL인지 확인할 수 있다. */
     , USERS_EXECUTING  
     , SORTS      
     , LOADED_VERSIONS
     , OPEN_VERSIONS 
     , USERS_OPENING
     , LOADS      
     , FIRST_LOAD_TIME   /* SQL이 처음으로 Cache에 Loading된 시간 */
     , INVALIDATIONS    
     , COMMAND_TYPE     
     , OPTIMIZER_MODE    /* 이 SQL문장이 실행될 때의 OPTIMIZER_MODE */
     , OPTIMIZER_COST    /* 이 SQL문장이 실행될 때의 Optimizer(Cost Base Optimizer)에 의해 계산된 Cost수이며, 큰 값일수록 비 효율적이나 이 값은 다른 SQL의 Cost와는 비교대상이 아님) */
     , PARSING_USER_ID   /* 이 SQL문장을 실행했던 User ID(0은 SYS user로 대부분 Recursive SQL,5는 SYSTEM) */
     , PARSING_SCHEMA_ID
     , KEPT_VERSIONS    
     , ADDRESS      
     , TYPE_CHK_HEAP
     , HASH_VALUE   
     , CHILD_NUMBER  
     , MODULE_HASH  
     , ACTION       
     , ACTION_HASH  
     , SERIALIZABLE_ABORTS
     , OUTLINE_CATEGORY  
from v$sql
where round(decode(executions,null,0,0,0,(nvl(ELAPSED_TIME,0)/executions)),1) >= 1000000
and PARSING_USER_ID <> 0
order by executions DESC

 

 

 

 

 

/* 응용형태 (SQL ElapseTime이 1초 이상 SQL유형 보기, 단 Recursive SQL은 제외) */

 

select sql_text
     , round(decode(executions,null,0,0,0,(nvl(buffer_gets,0)/executions)),1)  BUFGETSPEREXEC
     , round(decode(executions,null,0,0,0,(nvl(ELAPSED_TIME,0)/executions)),1) ElapsedTimePerExec
     , EXECUTIONS
     , PARSE_CALLS
     , DISK_READS,BUFFER_GETS
     , ROWS_PROCESSED
     , SHARABLE_MEM
     , PERSISTENT_MEM
     , RUNTIME_MEM
     , MODULE
     , USERS_EXECUTING
     , SORTS
     , LOADED_VERSIONS
     , OPEN_VERSIONS
     , USERS_OPENING
     , LOADS
     , FIRST_LOAD_TIME
     , INVALIDATIONS
     , COMMAND_TYPE
     , OPTIMIZER_MODE
     , OPTIMIZER_COST
     , PARSING_USER_ID
     , PARSING_SCHEMA_ID
     , KEPT_VERSIONS
     , ADDRESS
     , TYPE_CHK_HEAP
     , HASH_VALUE
     , CHILD_NUMBER
     , MODULE_HASH
     , ACTION
     , ACTION_HASH
     , SERIALIZABLE_ABORTS
     , OUTLINE_CATEGORY
  from v$sql
 where 1=1
   and round(decode(executions,null,0,0,0,(nvl(ELAPSED_TIME,0)/executions)),1) >= 1000000
   and PARSING_USER_ID <> 0
 order by executions DESC
 

 
 
 
 
/* 과다한 TEMPORARY Tablespace의 I/O유발 SQL 보기 (sort_usg.sql) */

select /*+ ORDERED */
       se.username /* Disk Sort를 유발시킨 SQL실행 User */
     , session_num /* V$SESSION.SID     */
     , se.process  /* V$PROCESS.PROCESS */
     , segfile#    /* TEMPORARY Segment(SORT 또는 HASH, Temp Table,..) 의 Start File #  */
     , segblk#     /* TEMPORARY Segment(SORT 또는 HASH, Temp Table,..) 의 Start Block # */
     , segtype     /* SORT(Sort에 의한 Sort Segment), HASH(Hash Join에 의한 Segment)    */
     , extents     /* 해당 Operation(SEGTYPE 형태별)의 발생시킨 Extent 개수 */
     , blocks      /* 해당 Operation(SEGTYPE 형태별)의 발생시킨 Block 개수  */
     --, getfullsql(hash_value) full_sqltext /* 관련 SQL의 Full Text */
  from v$sort_usage so, v$session se, v$sql sq
 where 1=1
   and so.session_addr = se.saddr
   and se.sql_address = sq.address
   --and se.audsid != userenv('sessionid')

 

 

 

 

 

/* 현재 I/O 발생 (Full Table Scan 포함) Session 정보 보기 */

 

select /*+ORDERED USE_NL(s) USE_NL(b) */
       s.sid
     , s.process client
     , w.event
     , segment_name
     , partition_name
     , w.p1 file#, w.p2 block#, w.p3 blocks
  from v$session_wait w
     , v$session s
     , dba_extents b
 where 1=1
   and s.sid = w.sid
   and ( w.event like '%scatter%' or w.event like '%sequential%' or w.event like '%direct%' )
   and b.file_id = w.p1
   and w.p2 between b.block_id and b.block_id + b.blocks -1

 

 

 

 

[참조] - OSR Analyzer Report (SQL튜닝 및 개발 가이드 中)