technical

● Trace 파일은 시스템을 튜닝하는데 필요한 아주 유요한 정보를 제공한다.
● 시스템 전체에 대해서 SQL_TRACE를 수행시키면 전체적인 수행성능은 20%~30% 정도 감소한다.
● SQL문의 실행통계를 Session별로 모아서 Trace 파일을 만든다.
     ▶ SQL Parsing, Execute, Fetch를 수행한 횟수
     ▶ CPU Time, Elapsed Time(총 경과시간)
     ▶ Disk(물리적), Memory(논리적) 읽기를 수행한 횟수
     ▶ 추출된 Row의 수
     ▶ 라이브러리 캐쉬 miss 수

● SQL_TRACE에 의해서 생성되는 Trace 파일의 확장자는 .trc이다.
     ▶TKPROF 유틸리티로 읽을 수 있는 파일을 생성해야 한다.

♣ INIT.ORA의 파라미터 변경
    ▶ SQL_TRACE를 생성하려면 먼저 다음과 같은 파라미터들을 INIT.ORA에 지정해야한다.
        ■ TIMED_STATISTICS=TRUE
         ·시간 통계를 모을 수 있게 한다.
        ■ SQL_TRACE=TRUE
         ·Session을 종료하는 모든 사용자들의 Trace를 수행한다.
        ■ USER_DUMP_DEST=directory path
         ·SQL_TRACE가 Trace 파일을 저장하는 디렉토리를 지정한다.
         ·보통 시스템 덤프(dump) 디레토리이다.(예; oracle_home/rdbms/log)
        ■MAX_DUMP_SIZE=number
         · Trace 파일의 물리적인 크기를 바이트 단위로 지정할 수 있게 한다.
         ·SQL_TRACE는 공간이 부족하면, 완전한 출력이 되지 않는다.

♣ INIT.ORA의 파라미터 변경 예제
      timed_statistics = true
      user_dump_dest = /oracle7/app/oracle/product/7.3.2/rdbms/log
      max_dump_file_size = 10240

♣ SQL_TRACE를 수행하는 방법
     ▶ 오라클 툴과 사용자 세션들에 따라 다르며 오라클 툴이 아닌 경우에는 조금씩 다를 수 있다.
     ▶ SQL*Plus에서는 다음과 같이 세션을 변경시켜야 한다.
         ■ SQL>alter session set sql_trace=true;
     ▶ optimizer goal의 변경은 다음과 같다.
         ■ SQL>alter session set optimizer_goal=rule;(optimizer goal을 정의)
     ▶ 이렇게 설정해 두면 실행되는 질의문에 대해서 트레이스 파일이 생성된다.
     ▶ 생성되는 위치는 init.ora에서 정의한 user_dump_dest 디렉토리에 *.trc형태로 생성된다.

♣ 트레이스 파일 보기
     ▶ 생성된 트레이스 파일은(*.trc) 바로 볼 수 없다. 그래서 tkprof라는 유틸리티를 사용하여 생성된
         트레이스 파일을 분석이 가능한 형식으로 전환하여 준다. 이미 생성된 트레이스 파일이나 트레이스
         파일을 생성하고 있는 중에도 tkprof를 수행시킬 수 있다. 트레이스 파일은 SQL문에 대한 실행계획뿐만
         아니라 실행시간, 다양한 옵션을 이용하여 분석하기 쉬운 형태 등의 정보를 보여준다.
     ▶ tkprof는 다음과 같이 실행시킬 수 있다.
         Usage: tkprof tracefile outputfile [explain=user/passwd] [table=schema.tablename]
                    [print=integer] [insert=filename] [sys=yes/no] [sort=option]

	outputfile	tkprof가 출력하는 텍스트 파일명(디폴트로 확장자가 .prf임)
	explain=user/passwd	해당 트레이스 파일이 수행된 세션의 사용자 및 패스워드
	table=schema.tablename	실행계획(execution plan)을 저장할 TKPROF 임시 테이블의 이름
	print=intege	트레이스 파일별로 출력시킬 SQL문의 수
	aggregate=yes\|no
	insert=filename	List SQL statements and data inside INSERT statements.
	sys=yes/no	TKPROF does not list SQL statements run as user SYS.
	record=filename	Record non-recursive statements found in the trace file.
	sort=option	Set of zero or more of the following sort options

           < sort option의 종류 >
            prscnt   number of times parse was called
            prscpu  cpu time parsing
            prsela   elapsed time parsing
            prsdsk   number of disk reads during parse
            prsqry   number of buffers for consistent read during parse
            prscu    number of buffers for current read during parse
            prsmis   number of misses in library cache during parse
            execnt   number of execute was called
            execpu cpu time spent executing
            exeela   elapsed time executing
            exedsk number of disk reads during execute
            exeqry   number of buffers for consistent read during execute
            execu    number of buffers for current read during execute
            exerow number of rows processed during execute
            exemis number of library cache misses during execute
            fchcnt   number of times fetch was called
            fchcpu cpu time spent fetching
            fchela   elapsed time fetching
            fchdsk number of disk reads during fetch
            fchqry   number of buffers for consistent read during fetch
            fchcu    number of buffers for current read during fetch
            fchrow number of rows fetched
            userid   userid of user that parsed the cursor

      ▶ 여기서 SQL문을 실행하는데 걸린 CPU시간을 보여주는 EXECPU가 가장 실용적이다.
      ▶ 만약 init.ora 파라미터를 TIMED_STATISTICS=FALSE로 지정했을 때는 수행 중에 액세스된 블록 수를
          보여주는 EXEQRY가 가장 실용적이다.
      ▶ 예) tkprof ccdb_ora_1124.trc 1124.txt explain=scott/tiger

♣ 트레이스 파일의 분석
▶ tkprof는 정형화된 리스트(출력파일)를 생성한다.
▶ 생성된 파일에는 다음과 같은 내용들을 포함하고 있다.

         call     count     cpu    elapsed      disk       query    current       rows
         ------- ------ -------- ---------- ---------- ---------- ---------- ----------
         Parse     1       0.01      0.01        0           0          0           0
         Execute 1       0.00      0.00        0           0          0           0
         Fetch      1       0.00      0.02        2           3          0           1
         ------- ------ -------- ---------- ---------- ---------- ---------- ----------
         total        3       0.01      0.03        2           3          0           1

      ■ parse
      ·SQL문이 파싱되는 단계에 대한 통계이다. 새로 파싱을 했거나, 공유 풀에서 찾아 온 것도 포함된다.
      ·단, PL/SQL 내에서 반복 수행(Loop)된 SQL이나 PRO*SQL에서 보존커서(Hold cursor)를 지정한
         경우에는 한번만 파싱된다.
      ■ execute
      ·SQL문의 실행 단계에 대한 통계이다. UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타난다.
      ·전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이 나타나며 fetch에는 아주 적은
         값이 나타난다.
      ■ fetch
      ·SQL문이 실해되면서 페치된 통계이다.
      ·부분범위 방식으로 처리된 SELECT문들이나 전체범위 처리를 한 후 한 건을 추출하는 경우(AGGREGATE,
         전체집계, Count 등)는 주로 여기에 많은 값들이 나타나고 execute에는 아주 적은 값이 나타난다.
      ■ count
      ·SQL문이 파싱된 횟수, 실행된 횟수, 페치가 수행된 횟수이다.
      ■ cpu
      ·pares, execute, fetch가 실제로 사용한 CPU 시간이다.(1/100초 단위)
      ■ elapsed
      ·작업의 시작에서 종료시까지 실제 소요된 총 시간이다.
      ■ disk
      ·디스크에서 읽혀진 데이타 블록의 수
      ■ query
      ·메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경 되었으나 아직 Commit되지 않아
         복사해 둔 스냅샷 블록을 읽은 블록의 수이다.
      ·SELECT문에서는 거의가 여기에 해당하며 UPDATE, DELETE, INSERT 시에는 소량만 발생한다.
      ■ current
      ·현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한 블록(Dirty Block)을 액섹스한
         블록 수이다.
      ·주로 UPDATE, INSERT, DELETE 작업시 많이 발생한다. SELECT 문에서는 거의 없으나 아주 적은 양인
         경우가 대부분이다.
      ■ rows
      ·SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수이다.
      ·서브쿼리에 의해서 추출된 로우는 제외된다.
      ·만약 SUM, AVG, MAX, MIN, COUNT 등의 그룹함수를 사용한 경우라면 큰 의미가 없다.

☞분석결과의 예
   ● execute, fetch의 횟수가 동일하다는 것은 SQL 수행시마다 기본키에 의해 한건씩만 처리되고 있다는
      것을 의미한다. 만약 pares가 1인데 execurte와 fetch가 100이라면 루프가 100번 수행되면서(어프리케인션은
      한번만 수행되고 SQL은 루프 내에서 반복수행되었다. 왜냐하면, 어플리케이션이 여러번 실행되었다면
      비록 SQL이 실제 파싱하지 않고 Shared SQL Area에서 찾아 왔다고 해도 parse의 횟수는 증가되기
      때문이다) 보관커서 상태의 SQL이 한 건씩을 추출한 상태이다. 이 경우의 SQL문은 'SELECT ... INTO
       ...'형식으로 사용되었을 것이다.
   ●parse가 1이고 execute가 1이며, fetch가 100이라면 SQL은 단 한번 수행되었고(루프 내에서 수행되지
      않았음) 페치만 연속해서 100번을 수행한 것이다. 이 경우의 SQL문은 대개 'DECLARE CURSOR'로 선언한
      SQL이 'FETCH ... INTO ...'에 의해 SQLCODE가 '1403'(Date Not Found)일 때까지 수행되었거나 부분범위
      처리에 의해 일정 양만큼만 수행하고 멈추었을 때이다.
   ●parse : execute : fetch의 비율은 공통 작업이 여러번 수행되면 그 배수로 나타난다. 예를 들면 parse :
      execute : fetch가 10 : 10 : 1000인 경우는 1 : 1 : 100인 작업이 10번 수행되었다는 것을 의미한다.
   ● fetch가 10인데 rows가 100이라면 운반단위가 10인 다중처리(Array Processing)를 사용하여 한번 페치마다
       10건의 로우가 추출되었음을 의미한다.
   ● 트레이스의 중간부분에 'Misses im library cache during parse : 1'이라는 문장이 있다. 이것은 공유 SQL
        영역에서 파상된 결과를 찾지 못하여 실제 파싱작업을 하게 되었다는 것을 의미한다.
   ● 최종적으로 추출된 로우의 수는 적으나 많은 CPU 시간이 소요되었다면 이것은 분명히 적절한 액세스
       경로로 수해되지 않았음을 의미한다.
   ● CPU 시간과 ELAPSED 시간의 차이는 적을수록 좋다. 만약 CPU시간에 비해 ELAPSED 시간이 훨씬
       많다면, 그 원인은 다음 중 하나일 가능성이 높다.
        ▶ 주변의 다른 세션에서 많은 부하를 발생시켜 시스템 전체에 부하가 많이 걸려있는 경우
        ▶ 어플리케이션의 문제이거나 다량의 데이타 처리에 따른 I/O 병목현상이 발생한 경우
   ● disk, query, current의 숫자는 적을수록 좋다. 이 숫자들이 커다는 것은 메모리 공유영역의 적중률(Hit
       Ratio)이 낮다는 것을 의미한다.
   ● Overall totals For All Statements에서 적중률 계산은 다음과 같다.
        ▶ (Execute 'disk' + Fetch 'Disk')/(Execute 'query' + Execute 'current' + Fetch 'query' +
             Fetch 'Current') * 100
        ▶ 이 값이 10%이상이라면 메모리 캐쉬에서 데이타를 찾는 비율(적중률)이 너무 낮은 것이다.
   ● 다음은 아주 빠른 응답이 요구되는 온라인 프로세싱 시스템의 경우에서만 적용되는 규칙들이다.
        ▶ 모든 Execute 'CPU'가 1초보다 적어야 한다.
        ▶ Parse 'CPU' 시간이 Parse당 0.01초보다 적어야 한다.
        ▶ 작은 테이블(200로우 이하)에서만 전체 테이블 스캔이 일어나게 한다.
        ▶ sysdate만 찾아오거나, 오직 연산만 하거나, 'SELECT ... INTO ...'로 값을 복사하는 경우를 위해서 DUAL
            테이블들을 불필요하게 사용하는 것은 모두 없앤다.
        ▶ 동시에 작업되는 SQL들은 가능한 PL/SQL을 사용한다.
        ▶ 조인시에 옵티마이져가 적절한 드라이빙 테이블을 선택하는지를 확인하거나, 여러개의 조건들
            중에서 주(드라이빙)가 되는 조건들과 부(체크)가 되는 조건들을 확인한다. 또한 적적한 인덱스가 사용될
            수 ??는지를 확인하여 주조건의 처리범위가 넓지 않도록 항상 유의한다.

col event form A50
col Prev form 9,999
col Curr form 9,999
col Tot form 99,999
select
event,
sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",
count(*) "Tot"
from
v$session_Wait
group by event
order by 4
/

buffer_busy_waits	waiting for a buffer to be read into SGA by another user or in incompatible mode
db_file_scattered_read	full table scan, waiting for buffer to be read into SGA
db_file_sequential_read	rowid (index) block read, waiting for block to be read into SGA
direct_access_io	direct io waiting for async write request to complete
direct_loader_io	pre 7.3 , same as DIRECT ACCESS I/O
enqueue	wait on an application lock or internal table lock
free_buffer_waits	wait for a free buffer to be available in SGA
free_global_transaction_table_entry	wait for space in clobal transaction table in Distributed transaction
index_block_split	wait for a full index block to split
latch_free	wait for a latch to be free
library_cache_load_lock	wait for another user to load demanded package into library cache
library_cache_lock	wants object in incompatible mode, or searching for object in cache
library_cache_pin	waiting in an incompatible mode to ppin the object
log_buffer_space	need space in the log buffer
log_file_space_switch	most any log waits on pre v7.3
log_switch_archive	waiting 'alter system archive log change ' command
log_file_switch_archiving_needed	waiting for archiver to archive the log file we want ot overwrite
log_file_switch_checkpoint_incomplete	waiting for checkpoint to complete for the log file we want to overwrite
log_file_switch_clearing_log_file	clear logfile command or implicit clear logfile executed by recovery.
log_file_switch_full_log_file (?)	after filling one log file waiting for lgwr to switch to next
log_file_switch_switch_logfile_command	waiting for user command to switch logfiles
log_file_sync	waiting for write to redo log on file to be confirmed
pipe_get	idle pipe
pipe_put	wait for space in pipe
rdbms_ipc_reply	waiting for a background process to confirm an action
sqlnet_message_to_client	if MTS, idle event, o/w net problem? communication problem?
sqlnet_more_data_to_client	high waits could indicate network latency
undo_segment_extension	waiting for RBS to extend, maybe rbs need to be better sized, or optimal not correctly set
write_complete_waits	dbwr is writing out the block we want to use