AUTOTRACE

• 실제 실행과 관련되 몇 가지 중요한 통계를 제공함으로써 실제로 쿼리를 수행하는 데 얼마나 많은 일이 수행되었는가를 알려준다.
• 모든 SQL DML 문(INSERT, UPDATE, DELETE, SELECT, MERGE)이 실행된 후에 보고서를 생성한다.

AUTOTRACE 설치

• 1. cd $ORACLE_HOME/rdbms/admin
• 2. create table과 create public sysnonym 권한을 가진 사람으로 SQL*Plus 로그인
• 3. PLAN_TABLE을 모두에게 공개
• 4. SQL*Plus를 빠져나와 cd $ORACLE_HOME/sqlplus/admin
• 5. SYSDBA로 SQL*Plus에 로그인 (sqlplus "/ as sysdba")
• 6. > @plustrace 실행
• 7. > grant plustrace to public

AUTOTRACE 사용

SET 명령을 이용하여 보고서를 제어할 수 있다.

• SET AUTOTRACE OFF : 쿼리는 정상적으로 실행되지만 보고서 생성하지 않는다. 기본값
• SET AUTOTRACE ON EXPLAIN : 쿼리가 정상적으로 실행되고 AUTOTRACE 보고서에 최적화기의 실행 경로만 나타난다.
• SET AUTOTRACE ON STATISTICS : 쿼리가 정상적으로 실행되고 AUTOTRACE 보고서에는 SQL문의 실행 통계만 나타난다.
• SET AUTOTRACE ON : 쿼리가 수행되고 보고서에 최적화기의 실행 경로와 SQL문의 실행 통계가 모두 포함된다.
• SET AUTOTRACE TRACEONLY : SET AUTOTRACE ON과 유사하지만 사용자의 쿼리 출력을 인쇄하지 않는다.
• SET AUTOTRACE TRACEONLY STATISTICS : SET AUTOTRACE TRACEONLY 와 같지만 쿼리 계획을 표시하지 않는다.
• SET AUTOTRACE TRACEONLY EXPLAIN : SET AUTOTRACE TRACEONLY 와 같지만 통계의 표시를 생략한다.
• SELECT 문의 경우에는 실제로 이 쿼리를 실행하지 않고 쿼리를 파싱한 후 설명만 해준다.

AUTOTRACE의 출력 포맷 바꾸기

• $ORACLE_HOME/sqlplus/admin/glogin.sql

COLUMN id_plus_exp FORMAT 990 HEADING i          
COLUMN parent_id_plus_exp FORMAT 990 HEADING p    <= 플랜에 나타난 첫 두 개의 수
COLUMN plan_plus_exp FORMAT a60                   <= TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)와 같이 계획 단계 자체
COLUMN object_node_plus_exp FORMAT a8            
COLUMN other_tag_plus_exp FORMAT a29             
COLUMN other_plus_exp FORMAT a44                  <= 병렬 쿼리 계획의 출력 정보가 표시되는 방법을 결정

• p.162에 나온 설정을 바꾸어도 똑같음 (10g에서 실행)

AUTOTRACE 출력 이해하기

• 비용(Cost) : CBO가 쿼리 계획의 각 단계에 할당한 비용. CBO는 동일한 쿼리에 대해 다양한 실행 경로/계획을 생성함으로써 동가하며 모든 쿼리에 대해 비용을 할당한다. 위의 실행 계획에선 전체 비용이 13인것을 알수 있다.
• 카드(Card) : 개수(Cardinality)의 줄임말. 해당 쿼리 계획 단계에서 나올 것으로 예상되는 행의 수. 최적화기가 emp에는 14개의 행이, dept에는 4개의 행이 존재할 것이라고 예측하고 있음을 볼 수 있다.
• 바이트(Byte) : 계획의 각 단계가 반환할 것으로 예상 되는 데이터의 크기를 바이트로 나타낸 수. 이 수는 행의 수와 행의 예상 길이에 따라 결정된다.
• 비용, 카드, 바이트 정보가 나타나지 않는 다면 이는 쿼리가 RBO를 사용하여 실행 되었음을 나타낸다.

AUTOTRACE 출력에서 무엇을 찾고 있는가?

재귀 호출(Recursive Calls)

• 다른 SQL문의 부작용으로서 실행된 SQL을 가리킨다
  • 삽입을 실행하면 쿼리를 실행하는 트리거가 촉발, 쿼리의 파싱, 추가적인 공간 요구, 임시 공간 작업 등...
• 하드 파스(Hard Parses) 처음에 재귀 호출의 수가 높게 나오면 쿼리를 다시 수행해서 이 통계가 여전히 높게 나오는지를 살펴본다. (p.165,166)
• PL/SQL 함수 호출 SQL에서 PL/SQL 함수를 호출하고 이 함수가 직접 다수의 SQL문을 실행하거나 묵시적으로 SQL을 사용하는 USER 같은 내장 함수를 참조하는 경우에도 재귀 호출이 높게 나타난다.(p.167)
  

• 수정의 부작용 재귀 수정으로 인한 부작용(트리거, 함수 기반의 인덱스 등등)
• 공간 요청 디스크 정렬 또는 공간 확장이 필요할 정도로 테이블이 크게 수정된 경우에 공간 요청을 수용하기 위하여 재귀 SQL 작업이 빈번하게 수행될 수도 있다.(p.168~171)

DB 블록 입수 및 일관된 입수

• 오라클은 두 가지 방식으로 블록을 가져와서 사용한다.
  • 현재 모드 입수 방식에서는 블록을 현재 상태 그대로 읽어 온다.
  • 수정 문(반드시 해당 블록의 최신 사본만을 갱신해야 한다)이 수행되는 중에 가장 비번하게 볼 수 있는 것이 이들이다.
  • 일관된 입수(consistent gets)방식에서는 "읽기 일관성" 모드의 개시 버퍼로부터 블록을 읽어 오며 언두(롤백 세그먼트)의 읽기를 포함 할 수 있다.

• 쿼리 튜닝 인덱스가 항상 좋은 것은 아니며 전체 스캔을 항살 피해야 하는 것이 아니다. CBO를 사용하고 데이터 검색 필요에 따라 데이터 구조에 인덱스를 추가하는 것이다(p.172~176)
• 배열 크기 효과 배열 크기는 서버에 의해 한 번에 인출(또는 삽입, 갱신, 삭제시에 송신)된 행의 수를 말하는데 성능에 지대한 영향을 미칠 수 있다.(p.177~179)

물리적인 읽기(physical reads)

• 쿼리가 얼마나 많은 실제 I/O, 즉 물리적인 I/O를 수행하였는가를 나타낸다.
• 물리적인 읽기는 테이블 또는 인덱스 데이터의 해당 블록이 버퍼 캐시에 가져다 놓는 것을 의미하면 논리적인 I/O는 이 블록을 가져오는 것을 의미한다.
• 대부분의 물리적인 읽기 다음에는 논리적인 I/O가 수반된다.
• 일반적으로 물리적인 I/O는 크게 두 가지로 나뉜다. (p.180~184)
  • 데이터 파일로 부터 데이터 읽기 : 인덱스와 테이블 데이터를 인출하기 위하여 데이터 파일을 대상으로 수행된 I/O. 이들 작업 직후에는 캐쉬에 대한 논리적인 I/O가 뒤따른다.
  • TEMP로부터 직접 읽기 : 정렬 영역 또는 해시 영역이 메모리 내 전체 정렬/해시를 지원할 만큼 충분하지 않을 때 발생. 일부 데이터를 TEMP에 보냈다가
• 나중에 이를 다시 읽어온다. 이들 물리적인 읽기는 버퍼 캐시를 우회하므로 논리적인 I/O를 일으키지 않는다.(p.181~183)

리두 크기

• SQL이 실행되면서 생성된 리두의 양을 나타내며 대용량 작업의 효율성을 판단하는 데 가장 유용하게 쓰인다.
• 리두는 직접 경로 삽입 또는 CTAS(CREATE TABLE AS SELECT)문에서 가장 빈번하게 나타난다.
• MERGE, UPDATE, DELETE 문에 의해 생성되는 리두의 양은 통재권 밖에 있다.
• 인덱스를 최대한 사용하지 않고 이들 작업을 수행할 경우에는 리두의 양을 줄일 수는 있겠지만 궁극적으로는 이들 비활성 인덱스를 다시 구축하여야 한다.

직접 경로 삽입(APPEND)시 리두 생성하지 않는 경우 (p.185~187)

• NOACHIVELOG 모드.
• NOLOGGING으로 표시된 테이블을 대상으로 작업하고 있는 경우
  • 단순히 테이블을 NOLOGGING 모드로 전환하였다고 이 테이블에 대해 리두가 완전히 없어지는 것은 아니다.
  • 삽입, 갱신, 삭제, 병합과 같은 기타 모든 작업에 대해서는 일반적인 방식으로 로그가 기록된다.

NOLOGGING을 설정하고 /*+ APPEND */을 사용하지 말아야 할 이유

• 직접 경로 적재와 마찬가지로 직접 경로 삽입은 테이블의 최고 수위선(HWM) 위에 데이터를 씀으로써 사용 가능 목록에 있는 여유 공간을 모두 무시한다.
• 직접 경로 삽입이 성공적으로 끝나고 동일 트랜잭션에서 해당 테이블로부터 데이터를 읽기 위해서는 반드시 커밋이 수행되어야 한다.
• 한 번에 한 세션만 테이블에 직접 경로 삽입을 수행할 수 있다. 나머지 모든 수정은 차단되어 직렬화가 유발된다.
• (단, 단일 세션 내에서는 병렬 직접 경로 삽입을 수행할 수 있다)

• 리두와 인덱스 작업 NOLOGGING모드로 설정된 테이블에 INSERT /*+ APPEND */ AS SELEECT를 수행하여도 테이블에 인덱스가 설정되었기 때문에 리두와 아카이브는 생성된다. (p.188~189)

• 데이터 웨어하우스 또는 데이터 마트에 대량 데이터를 적제하는 경우
  • 인덱스를 unusable 상태로 설정 : alter index idx_nm unusable;
  • 대량 적재시 상태가 unusable인 인덱스를 지나치도록 세션 설정 : alter session set skip_unusable_indexes=true;
  • 인덱스 다시 활성화 : alter index idx_nm rebuild nologging;

SQL*Net 통계

• 서버에 전송한 양 (Byte recevied via SQL*Net from client)
• 서버가 전송한 양 (Byte sent via SQL*Net to client)
  • 클라이언트에 전송된 데이터를 최소화하려면 필요한 열만을 선택.
• 왕복의 수 (SQL*Net roundtrips to/from client)
  • 왕복은 적으면 적을수록 좋고 배열 가져오기 크기가 100과 500 사이일 때 전체적인 성능/메모리 사용도가 가장 좋다.

정렬과 처리된 행

• Sorts(memory)는 전적으로 메모리 내에서 수행된 정렬의 수를 보여준다.
• Sorts(disk) 일부나마 임시 디스크 공간을 필요로 한 정렬의 수를 보여 준다.
• Rows processed는 영향을 받은 행의 수를 보여 준다.
• (SELECT 문으로부터 반환되거나, INSERT, UPDATE, DELETE, MERGE에 의해 수정된 수)