01 Explain plan
explain plan: SQL수행하기전 실행계획을 확인하고자 할때 사용
-plan_table 생성: @?/rdbms/admin/utlxplan.sql
explain plan for명령 수행하고 나면 해당 sql에 대한 실행계획이 plan_tavble에 저장된다.
SQL Repository에 저장된 모든 SQL에 대해 매일 explain plan명령을 수행해 그 실행계획을 별도 테이블로 저장해 둔다면 이를 활용해 안정적인 시스템 운영 및 성능관리에 활용할 수 있다.
02 AutoTrace
AutoTrace: 결과에는 SQL을 튜닝하는데 유용한 정보들을 많이 포함하고 있어 가장 즐겨 사용되는 도구 중 하나다.
① set autotrace on
SQL을 실제 수행하고 그 결과와 함께 실행계획 및 실행통계를 출력한다.
② set autotrace on explain
SQL을 실제 수행하고 그 결과와 함께 실행계획을 출력한다.
③ set autotrace on statistics
SQL을 실제 수행하고 그 결과와 함께 실행통계를 출력한다.
④ set autotrace traceonly
SQL을 실제 수행하지만 그 결과는 출력하지 않고 실행계획과 통계만을 출력한다.
⑤ set autotrace traceonly explain
SQL을 실제 수행하지 않고 실행계획만을 출력한다.
⑥ set autotrace traceonly statistics
SQL을 실제 수행하지만 그 결과는 출력하지 않고 실행통계만을 출력한다.
①~③은 수행결과를 출력해야 하므로 쿼리를 실제 수행한다.
④，⑥는 실행통계를 보여줘야 하므로 쿼리 를 실제 수행한다.
⑤는 실행계획만 출력하면 되므로 쿼리를 실제 수행하지 않는다. SQL*Plus에서 실행
계획을 가장 쉽고 빠르게 확인해 볼 수 있는 방법이다.
03 SQL 트레이스
AutoTrace결과만으로 부화원인을 찾을 수 없을 때, SQL트레이스를 통해 쉽게 찾아낼 수 있다.
(1)자기 세션에 트레이스 걸기
자신이 접속해 있는 세션에만 트레아스 설정
SQL 수행후 user_dump_dest 파라미터로 지정된 서버 디렉토리 밑에 트레이스 파일 생성.
SQL> alter session set tracefile_identifire='oraking';
파일뒤에 식별자 붙어 쉽게 자신의 트레이스파일 찾을 수 있다.
TKProf유틸리티
TKProf유틸리티사용하면 트레이스 파일을 보기 쉽게 포맷팅해 준다.
TKProf의 Full Name
-Trace Kernel PROFile
-Transient Kernel PROFile
-ToolKit PROFiler
트레이스 결과 분석
call: 커서 상태에 따라 Parse. Execute. Fetch 세 개의 Call로 나누어 각각에 대한 통계정보를 보여준다
• Parse : 커서를 파싱하고 실행계획을 생성하는 데 대한 통계
• Excute : 커서의 실행 단계에 대한 통계
• Fetch : 레코드를 실제로 Fetch하는 데 대한 통계
count: Parse, Execute, Fetch 각 단계가 수행된 횟수
cpu: 현재 커서가 각 단계에서 사용한 cpu time
elapsed: 현재 커서가 각 단계를 수행하는 데 소요된 시간
disk: 디스크로부터 읽은 블록 수
query: Consistent 모드에서 읽은 버퍼 블록 수
current: Current 모드에서 읽은 버퍼 블록 수
rows: 각 단계에서 읽거나 갱신한 처리 건수
Rows Row Source Operation
---- -------------------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=O pw=O tυre=80 us)
1 INDEX UNIQUE SCAN EMP_PK (cr=l pr=O pw=O time=44 us) (object id 5278)
Rows: 각 수행 단계에서 출력된 로우의 수
부모는 자식 노드의 값을 누적한 값을 갖는다.
이벤트 트레이스
설정할 수 있는 레벨 값: 1,4,8,12
레벨1: 일반적인 SQL 트레이스
레벨4,12: 바인드 변수에 대한 정보 확인
레벨8,12: 수행 도중 대기 이벤트가 발생할 때마다 트레이스 파일에 정보들이 로그처럼 계속 기록
TKRrof로 포맷팅하면 각 이벤트별로 집꼐된 정보를 볼 수 있다.
10046트레이스를 걸 때 레벨 4이상 설정하면 트레이스 파일이 매우 급격하게 커지므로 주의 필요
Elapsed time = CPU time + Wait time
• Elapsed time = CPU time + Wait time=Response시점-Call시점
• SELECT문 = Parse Call + Execute Call + Fetch Call(-> 1 회 이상)
• DML문 = Parse Call + Execute Call
하나의 SQL을 수행할 때의 Total Elapsed time은, 수행 시 발생하는 모든 Call의 Elapsed time을 더해서 구한다.
(2)다른 세션에 트레이스 걸기
튜닝 대상 SQL이 수집되지 않은 상황이라면 커넥션 Pool에 놓인 세션 또는 시스템레벨로 트레이스를 걸어 SQL 수행 정보를 수집해야 한다.
특정 세션에서 심한 성능 부하를 일으키고 있다면 이미 수행 중인 그 세션에 트레이스를 걸어야 하는데, 그럴 때 사용할수 있는방법들이 제공되며 버전에 따라따르다.
오라클 10g 이후부터는 dbms_monutor 패키지를 사용하면 된다.
트레이스를 해제할 때는 session_trace_disable 프로시저를 사용한다.
시스템 전체에 트레이스를 걸 때도 10046 이벤트 트레이스를 이용하면 레벨 설정을 할 수 있지만 심각한 부하를 일으키므로 시용할 일이 없다. 부득이한 경우, 짧은 시간 동안만 걸었다가 해제하는 용도로 시용해야 한다.
(3)Service, Module, Action 단위로 트레이스 걸기
service_name이 eCRM 인 세션에 모두 트레이스를 건다. 현재 접속해 있는 4개 세션 뿐 아니라 앞으로 새로 커넥션을 맺는 세션 중에서도 service_name이 eCRM이면 자동으로 트레이스가 설정된다.
SQL 단위로set action을 자주 수행하는 것은 시스템에 다소 부하를 줄 수 있으므로 SQL 수행이 빈번한 OLTP성 프로그램보다는 배치 프로그램에만 적용한다.
04 DBMS_XPLAN 패키지
(1)예상 실행계획 출력
ROWS, BYTES, COST 이외에 추가로 사용할 수 있는 옵션
• PARTITION
• PARALLEL
• PREDICATE
• PROJECTION
• ALIAS
• REMOTE
• NOTE
모든 항목들 출력하는법:
select * from table (dbms xplan . display ( ’ PLAN TABlE ’ t ’ SQL1 ’ I ’ ALL ’ ));
(2)캐싱된 커서의 실제 실행계획 출력
커서: 하드 파싱과정을 거쳐 메모리에 적재된 SQL과 Parse Tree, 실행계획, 그리고 그것을 실행하는데 필요한 정보를 담은 SQL Area
오라클은 라이브러리 캐시에 캐싱돼 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql 뷰를 제공한다.
이것과 함께 sql id 값으로 조인해서 시용할 수 있도록 제공되는 뷰가 몇 가지 더 있는데 , 그 중 활용도가 가장 높은 것 이 v$sql_ plan 과 v$sql_plan_statisticsc다 . 그리고 이 두 뷰를 합쳐서 보여주는것이 v$sqtplan_statistics_all 이다.
(3)캐싱된 커서의 Row Source별 수행 통계 출력
SQL문에 gather_plan_statistics 힌트를 시용하거나, 시스램 또는 세션 레벨에서 statistics_level 파라미 터를 all로 설정하면 오라클은 실제 SQL을 수행하는 동안의 실행계획 각 오퍼레이션 단계 별로 수행 통계를 수집한다.
'_rowsource_execution_statistics’ 파라미터 를 true로 설정하거나, SQL 트레이스를 걸어도 Row Source별 수행 통계가 수집된다.
조회할 때는 v$sql_plan_statistics 또는 v$sql_plan_statisticεall 뷰를 이용하면 된다.
v$sql_plan_statistics 뷰에는 모든 통계항목에 대해 마지막 수행 통계치와 누적 통계치 를조회할수 있도록 컬 럼이 두 개씩 제공된다.
• output_rows, last_output_rows
• cr_buffer-gets, last_cu_buffer_gets
• disk_reads. last_disk_reads
05 V$SYSSTAT
인스턴스 기동 후 현재까지 누적된 수행 통계치를 시스템 레벨로 확인하고자 할 때 사용히는 뷰가 v$sysstat이고, 개별 세션별로 확인할 때 사용하는 뷰가 v$sesstat이다. 현재 접속해 있는 본인 세션에 대한 수행통계는 v$mystat을 통해 확인할 수 있다.
(1) 시스템 수행 통계 수집 및 분석
v$sysstat이나 v$sesstat 두 구간 사이의 변화량을 구해 SQL 수행 도중에 내부적으로 어떤 일들이 발생했는지를 판명하는 것이다.
작업을 수행할 세션과 별도로 세션을 하나 더 열어 아래 create문을 수행한다. 작업을 수행할 세션의 SID를 미리 알아놔야 하며 AutoTrace에서 설명했듯 별도의 세션을 이용하는 것은 v$sesstat를 조회해 insert 할 때의 통계치가 섞이는 것을 방지하기 위함이다.
(2)Raio 기반 성능 분석
• Buffer Nowait %: 버퍼 블록을 읽으려 할 때 buffer busy waits 대기 없이 곧바로 읽기에 성공한 비율이다
• Redo No Wait %: Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율을 말한다. 이 비율이 낮다면 로그 스위칭이 느리거나 너무 자주 발생함을 의미한다.
LGWR 프로세스가 Redo 로그 버퍼를 Redo 로그에 기록하는 시점.
1 3초마다 DBWR 프로세스로부터 신호를 받을 때
2 로그 버퍼의 1/3 이 차거나 기록된 Redo 레코드량이 1MB를 념을 때
3 사용자가커빗또는롤백 명령을날릴 때
• Buffer Hit %: 디스크 읽기를 수반하지 않고 버퍼 캐시에서 블록 찾기에 성공한 비율이다.
• Latch Hit %: 래치 경합 없이 첫 번째 시도에서 곧바로 래치를 획득한 비율
• In-memory Sort %: 전체 소트 수행 횟수에서 In-Memory 소트 방식으로 수행한 비율
• Library Hit %: 항목부터 ‘% Non-Parse CPU’ 까지는 파싱 부하와 관련 있는 측정 항목
• Soft Parse %: 실행 계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL을 수행한 비율
• Execute to Parse %: Parse Call 없이 곧바로 SQL을 수행한 비율
• Parse CPU to Parse Elapsd %: 파싱 총 소요 시간 중 CPU time이 차지한 비율
• % Non-Parse CPU: SQL을 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율
• Memory Usage %: Shared Pool 내에서 현재 사용 중인 메모리 비중
• % 8QL with executions > 1: 전체 SQL 개수에서 두 번 이상 수행된 SQL이 차지하는 비중
• % Memory for 8QL w/exec > 1: 전체 SQL이 차지하는 메모리 중 두 번 이상 수행된 SQL이 차지하는 메모리 비중
06 V$SYSTEM_EVENT
쿼리결과
• WAIT TIME > 0 : 마지막 대기이벤트를 대기한 시간
• WAIT TIME = 0 : 이 이벤트를 현재 대기 중
• WAIT TIME = - 1: 마지막 대기 이벤트를 대기한 시간이 10ms 미만
• WAIT TIME = -2 : 타이밍이 활성화되지 않음
v$system_event까지 저장하도록 프로시저에 추가해 주면 대기 이벤트 발생 현황도 함께 분석할 수 있다.
v$system_event를 5분 주기로 수집해 하루 동안의 이벤트 발생 현황 그래프
오후 1시 20분부터 50분 가량 Application 관련 대기 이벤트가 지속적으로 발생
Application 관련 대기 이벤트가 발생하는 것은 대부분 lock이 원인
새벽 1시부터 과도하게 발생한 User_IO 대기 이벤트는 오브젝트 통계 정보를 수집하는 Job이 수행되었기 때문