SQL) 오라클 성능 관리 Ⅰ

jinsung·2025년 12월 8일
SQLPoraclesql데이터자격증

SQL

목록 보기
16/46
post-thumbnail

1. Explain plan

오라클 10g부터는 기본적으로 sys.plan_table 테이블이 만들어진다. 그리고 이를 가리키는 pulbic synonym을 기본적으로 생성해 두기 때문에 사용자가 별도로 plan_table을 만들지 않아도 된다.

explain plan for 명령을 수행하고 나면 해당 SQL에 대한 실행계획이 plan_table에 저장된다.

explain plan for 
select * from  emp where empno = 30303;

오라클 9i부터는 개별적으로 쿼리를 작성할 필요없이 오라클이 제공해주는 utlxpls.sql 또는 utlxplp.sql 스크립트를 이용하면 실행계획을 볼 수 있다.

요즘은 SQL을 프로그램 속에 하드코딩하지 않고 XML 포맷으로 Repository에 저장해 둔다.
SQL Repository에 저장된 모든 SQL에 대해 매일 explain plan 명령을 수행해 그 실행계획을 별도 테이블에 저장해 둔다면 이를 활용해 안정적인 시스템 운영 및 성능관리를 활용할 수 있다.

2. AutoTrace

AutoTrace 결과에는 SQL을 튜닝하는데 유용한 정보들을 많이 포함하고 있다.

set autotrace on
select * from emp where empno = 23123;

set autotrace on 으로 쿼리 실행 시

  • 쿼리 실행결과
  • 실행계획(Explain plan) + Predicate Information 포함
  • 실행통계(Statustics)

set autotrace의 옵션 조합

1. set autotrace on

  • SQL 실제 수행

  • 쿼리 실행결과 + 실행계획 + 실행통계

2. set autotrace explain

  • SQL 실제 수행

  • 쿼리 실행결과 + 실행계획

3. set autotrace statistics

  • SQL 실제 수행

  • 쿼리 실행결과 + 실행통계

4. set autotrace traceonly

  • SQL 실제 수행

  • 실행계획 + 실행통계

5. set autotrace traceonly explain

  • SQL 실제 수행 X

  • 실행계획

6. set autotrace traceonly statistics

  • SQL 실제 수행

  • 실행통계

3. SQL 트레이스

SQL 튜닝을 할 때 가장 많이 사용하는 도구이다. explain plan과 AutoTrace 결과만으로 부하원인을 찾을 수 없을 때 SQL 트레이스를 통해 쉽게 찾을 수 있다.

1. 자신 세션에 트레이스 걸기

성능 문제가 발생한 튜닝 대상 SQL 목록을 이미 확보해서 현재 자신이 접속해 있는 세션에만 트레이스를 설정하는 방법이다.

alter session set sql_trace = true; --> 세션에 트레이스 걸기
select * from emp where empno = 23132 --> SQL 실행
alter session set sql_trace = false --> 세션에 트레이스 끄기

위처럼 세션에 트레이스를 걸고 SQL을 실행하면 user_dump_dest 파라미터로 지정된 서버 티렉토리 밑에 트레이스 파일(.trc)이 생성된다.

TKProf 유틸리티

SQL 트레이스를 열어보면 파일 그대로 분석하기가 쉽지 않아 몇 가지 분석 팩터에 따라 좀더 보기 쉬운 형태로 포맷팅하는 것이 필요하다.
TKProf 유틸리티를 사용하면 트레이스 파일을 보기 쉽게 포맷팅해 준다.
유닉스 쉘(shell)이나 도스 트폼프트 상에서 tkprof 치면 사용법이 나온다.

$ tkprof ora10g_ora_2312_oraking.trc report.prf sys=no

TKProf 유틸리티의 가장 일반적인 사용 형태로 sys=no 옵션은 SQL을 파싱하는 과정에서 내부적으로 수행되는 SQL 문장을 제외시킨다.

트레이스 결과 분석

  • Call 통계 컬럼들
항목설명
call커서 상태에 따라 Parse, Execute, Fetch 세 개의 Call로 나뉘어 각각에 대한 통계 정보를 보여준다
1. Parse : 커서를 파싱하고 실행계획을 생성하는 데 대한 통계
2. Execute : 커서의 실행 단계에 대한 통계
3. Fetch : 레코드가 실제로 Fetch 하는 데 대한 통계
countParse, Execute, Fetch 각 단계가 수행된 횟수
cpu time현재 커서가 각 단계에서 사용한 cpu time
elapsed현재 커서가 각 단계를 수행하는 데 소요된 시간
disk디스크로부터 읽은 블록 수
queryConsistent 모드에서 읽은 블록 수
currentCurrent 모드에서 읽은 블록 수
rows각 단계에서 갱신한 처리 건수

AutoTrace의 실행통계와 항목 비교

  • db block gets = current
  • consistent gets = query
  • physical reads = disk
  • SQL*Net roundtrips to/from client = fetch count
  • rows processed = fetch rows

Call 통계의 Row Source Operation의 분석 방법

(cr=2 pr=0 pw=0 time=80 us)

  • cr = Consistent read
  • pr = Physical read (디스크 블록 읽기)
  • pw = Physical write (디스크 블록 쓰기)
  • time = 소요시간

중요한 사실은 부모는 자식 노드의 값을 누적한 값을 갖는다.
예를 들어 부모 cr=2이고 자식 cr=1이면 부모 cr은 1인 것이다.

10046 이벤트 트레이스

오라클은 오래 전부터 다양한 종류의 이벤트 트레이스르 제공해 왔고, 이를 통해서도 SQL 트레이스를 걸 수 있다.

alter session set events '10046 trace name context forever, level 1';
alter settion set events '10046 trace name context off';

특히, 이 방식을 사용하면 레벨 설정을 통해 바인드 변수와 대기 이벤트 발생 현황까지 수집할 수 있다. 설정할 수 있는 레벨은 1, 4, 8, 12 이며, 레벨 1은 일반적인 SQL 트레이스와 같다.
레벨 4 or 12 는 바인드 변수에 대한 정보를 확인 가능 하다.
레벨 8 or 12 는 SQL 수행 도중 대기 이벤트가 발생할 때마다 트레이스 파일에 정보들이 로그처럼 계속 기록된다.
이 정보들을 TKprof 유틸리티로 포맷팅하면 각 이벤트별로 집계된 정보를 볼 수 있다.

Elapsed time(경과시간) = CPU time(Cpu 시간) + Wait time(대기 시간)

Elapsed time = CPU time + Wait time

= Response 시점 - Call 시점

Elapsed time은 Call 단위로 측정이 이루어진다.
사용자로부터 데이터베이스 Call을 받은 순간부터 Response를 보내는 순간까지의 소요시간을 말하며, Response 시점에서 Call 시점을 차감해서 구한다.
예를 들어, 12초에 Call을 보냈는데 25초에 Response를 받았다면 Elapsed time은 13초이다.

또 한가지 알아야 할 것은, 애플리케이션 커서 캐싱기법을 사용하지 않는 한 하나의 SELECT문을 수행하는 동안 최소 3번의 Call이 발생하고, DML문은 단 두 번의 Call이 발생한다는 사실이다.

  • SELECT문 : Parse Call + Execute Call + Fetch Call
  • DML문 : Parse Call + Execute Call

특히 SELECT문에서 다량의 데이터를 사용자에게 전송할 때는 Fetch Call이 '전송 레코드 건수 / Arraysize'만큼 여러 번 발생한다.
따라서 하나의 SQL을 수행할 때의 Total Elapsed time은, 수행 시 발생하는 모든 Call의 Elapsed time을 더해서 구한다.

arraysize = fetch rows // (fetch count -1)
-> one-row fetch때문에 한 건 빼줘야함

쿼리 수행 시간 : Elapsed time + 애플리케이션으로부터 Call을 기다리면서 Idle 상태로 대기한 시간(대기 이벤트 시간이나 App or Network 구간에서 소모된 시간)

10046 이벤트 트레이스 다음으로 자주 사용하는 것은 10053 이벤트 트레이스이다.
이는 실행계획을 생성하는 CBO의 의사결정 과정을 추적하는 것을 가능케 하며, 이를 통해 옵티마이저가 이상한 돌출 행동을 원인을 찾아낼 수도 있다.

2. 다른 세션에 트레이스 걸기

아직 튜닝 대신 SQL이 수집되지 않은 상황이라면 커넥션 Pool에 놓인 세션 또는 시스템 레벨로 트레이스를 걸어 SQL 수행 정보를 수집해야 한다.
또는 특정 세션에서 심한 성능 부하를 일으키고 있다면 이미 수행 중인 그 세션에 트레이스를 걸어야 하는데, 그럴 때 사용할 수 있는 방법들이 제공되며 버전에 따라 다르다.

exec dbms_system.set_ev(145, 3, 10046, 12, '');

오라클 9i에서 Serial 번호가 3인 145번 세션에 레벨 12로 10046 이벤트 트레이스를 거는 쿼리이다.

exec dbms_system.set_ev(145, 3, 10046, 0, '');

트레이스를 해제하려면 레벨을 0으로 두면 된다.

begin
	dbms_moniter.session.trace_enable(
    	session_id => 145
        , serial_num => 3
        , waits => TRUE
        , binds => TRUE);
end;

오라클 10g 이후부터는 dbms.moniter 패키지를 사용하면 된다.

begin
	dbms_moniter.session.trace_disable(
    	session_id => 145
        , serial_num => 3);
end;

트레이스를 해제할 때는 session_trace_disable 프로시저를 사용한다.

문제가 발생한 세션에 트레이스를 걸 때, 버전에 상관없이 오래 전부터 사용하던 명령어에 oradebug라는 것도 있다.

3. Service, Module, Action 단위로 트레이스 걸기

최근 개발된 n-Tier 구조의 애플리케이션은 WAS에서 DB와 미리 맺어놓은 커넥션 Pool에서 세션을 할당 받기 때문에 특정 프로그램 모듈이 어떤 세션에서 실행될지 알 수 없고, 한 모듈 내에서 여러 SQL을 수행할 때 각각 다른 세션을 통해 처리될 수도 있다.
하는 수 없이, DB 트리거를 이용해 로그온 시점에 트레이스가 걸리도록 하거나 시스템 레벨로 전체 트레이스를 걸어야만 한다.

하지만 오라클 10g부터는 service, module, action별로 트레이스를 설정하고 해제할 수 있는 dbms_moniter 패키지가 소개되면서 전체 트레이스를 걸어야하는 불편함이 사라졌다.

dbms.monitor 패키지를 이용해 row source operation과 대기 이벤트 발생현황만 수집할 수 있는 게 아니라 service, module, action 단위로 v$sesstat 통계정보를 수집하는 기능도 제공한다.

dbms.monitor 패키지로 세션에 트레이스를 걸 때 serv_mod_stat_enable을 쓰고 트레이스를 해제할 때, serv_mod_act_stat_disable 프로시저를 이용한다.

dbms_monitor의 dba_enabled_aggregations 뷰를 이용해 어떤 단위로 정보수집이 진행중인지 확인할 수 있다.

profile
jinsung
Data Engineer
이전 포스트

SQL) 트랜잭션과 Lock Ⅲ

다음 포스트

SQL) 오라클 성능 관리 Ⅱ

0개의 댓글