Oracle SQL Tuning 교육 정리 - Day 1

2. Introduction to SQL Tuning

<목표>

• Describe SQL tuning
• Summarize SQL tuning strategies
• Describe Oracle SQL Developer

 

튜닝의 목적은 응답 시간을 줄이거나 또는 많은 데이터 량을 처리하는 것이다.

(분석계에서는 throughput을 향상시키는게 더 중요하지)

 

[Recognize] 어떤 SQL이 성능이 나쁜가? → 성능이 나쁘다의 기준은?

[Define] 현재 문제를 정의한다

[Verify] 데이터 수집 및 분석

 

성능이 나빠졌다. → 원인 분석 단계 1) 변경점 확인 2) 튜닝 방법

 

What is Bad SQL?

- 과도한 parse time

- 과도한 I/O

- 과도한 CPU time

- 과도한 waits

 

DB 변경 사항들에는, DB 업그레이드, 통계정보 수집, 스키마 변경, DB parameter 변경, App. 변경, OS 및 HW 변경, 데이터 크기 변경 등.

 

Bad SQL을 확인하는 도구는 엄청 많다.

그중 Trace Analyzer(TRCANLZR), SQLTXPLAIN(SQLT)  - 오라클 support 사이트에서 다운로드 가능

 

Top-Down AnalysisSQL 실행된 환경을 먼저 확인한 후 SQL문을 살펴보는 방법  - CPU, Memory 리소스, DB config, 버그, NW, Top SQL(영향을 줄수도 있음)  - Hang은 시스템적 오류 (실시간 hang 분석 도구 있음)

 

80/20SQL문 80%가 리소스 20%를 사용하고,SQL문 10%가 리소스 50%를 사용한다.

 

* 튜닝 방법

1) Always check the basics first

- 최신 통계정보

- 합리적인 초기 parameter

- 적절한 옵티마이저 모드

- 알맞은 힌트

2) Find a good plan

- 통계 정보 및 Plan 해석

  예시)

  CPU : 작업 시간 100초

  Elapsed : 전체 실행시간 300초 -> wait 발생

  Disk : Disk에서 읽은 block수

  query : 메모리에 있는 block수 -> 데이터, 인덱스 등을 불필요하게 많이 검색하면 값이 크다

3) Implement the new good plan☆☆☆

- SQL Tuning Advisor를 사용하세요

- 옵티마이저 parameter 변경 (Session 단위로만. 운영 중인 시스템에 대해 변경하는 건 어려움)

  Initialization Parameters > 1.221 OPTIMIZER_MODE 

  OPTIMIZER_MODE (FIRST_ROWS_N : 빠른 응답시간을 목표로, ALL_ROWS : 많은 throughput을 목표로)

  OPTIMIZER_FEATURES_ENABLE : 옵티마이저 버전 :^D

  OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_INDEX_CACHING : 인덱스 사용 관련

- dynamic statitics : 최신 통계정보가 필요해

  (관련 글: Dynamic sampling and its impact on the Optimizer (oracle.com))

- hints : 옵티마이저가 plan을 잘 작성할 수 있도록 유도하는거야

- STA는 profile을 만들어줘서 실행되고 있는 SQL문에 바로 적용 가능. 저장도 됨.

  병렬 처리 단점: 프로세스를 많이 띄우고 있어서 리소스를 많이 쓴다

  파티션 단점: 운영 관점으로 비용이 든다 (손이 많이 든다)

 

SQL 분석 전략

* 데이터 수집

- 실행 계획

- 테이블 또는 뷰 관련 정보

* 수집 도구: SQL trace & TKPROF, DBMS_STATS

* SQL문 분석: 데이터 건수에 대한 이해 (테이블 전체, 일부), 조인 조건, where 조건, outer join (둘 중 하나는 full table scan), in 또는 or 성능 낮아짐, 계층 구조, 데이터 검색 방법, 조인 방법 및 순서, 검색 결과를 위해 조회한 데이터 수, plan step (estimated rows vs actual rows)

* 원인: SQL문 자체, 인덱스, 조인, 뷰, 통계정보 등

* 솔루션: 통계정보 적절하게 수집, 인덱스 생성 및 리빌딩, SQL Advisors 사용 (오라클이 자동으로 제안하는 효율적인 플랜을 사용할 수 있다), 힌트, 플랜을 DB에 저장 (plan 재사용, parse 시간 감소), dynamic statistics (플랜 작성할 때 통계정보 수집하는건가??), 데이터 타입 변환 (성능에 안좋음), function-based index 등

 

3. Using Application Tracing Tools

TKPROF : 대표적인 tracing tool

trace file 저장, trace 대상은 session 단위, DB 전체, 특정 SQL...

Tracing 활성화 → 부하가 될 수 있으므로 조심

trcsess utility : 여러 session의 정보를 종합적으로 보기 위해 

 

해야할 일 Before Tracing- trace file 위치 지정 (12c default : DIAGNOSTIC_DEST)- trace 대상 선정  예시) CPU를 많이 쓰는 세션, SQL 처리 시간(DB time)이 많이 걸리는 세션, 특정 wait가 많은 세션  모니터링 Tool로 확인 가능 - Oracle Enterprise Manage, Maxguage- client, service(connection pool), module, action, user or seseeion 단위로  module? 하나의 프로그램, 여러 프로그램 집합 (정의하기 나름)  action? module보다 작은 단위. 하나의 SQL문, 여러 SQL문  user: DB 계정  session: 1개 DB 계정으로 여러 session을 잡을 수 있음

 

대상을 선정하는 쿼리 예시가 있지만,일시적인 시점의 결과가 아닌 일정하게 반복적으로 확인한 결과로 대상을 선정 해야 한다.

 

Trace Tool 소개 :DSQL Trace- DBMS_MONITOR.DATABASE_TRACE_ENABLE : DB 전체 대상- DBMS_SESSION.SET_SQL_TRACE : 특정 session에 대해- ALTER SESSION SET SQL_TRACE : 내가 실행하려는 SQL에 대해 트레이스 하겠음둥End-to-End application tracing- DBMS_SQL_MONITOR.BEGIN_OPERATION ~ %.END_OPERATION : 여러 프로그램이 수행되는 구간...

 

기본적으로 Client 별로 즉 서버 프로세스 별로 trace file이 만들어집니다.(기본적으로 = Dedicated server)v 내 session에 trace 걸기v 특정 user에게 trace 걸기 - user가  login 하면 trigger로 DBMS_MONITOR.SESSION_TRACE_ENABLE

 

그러면 Shared server로 사용하는 경우는?

서버 별로 trace file이 생성되기 때문에 여러 trace file에 여러 client의 정보가 섞인다.

→ trcsess 유틸리티 통해서 사용자를 구분해줄 수 있다

 

Trace 대상 설정하기

service

Q. TNS의 service_name은 DB 서비스 이름 아닌가? ㅇㅅㅇ??

module, action 정의

- 내가 수행하는 코드 내에 SQL 수행하기 전에 module, action명을 정의한다

  (DBMS_APPLICATION_INFO.SET_MODULE()...)

  exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENAGLE()

action은 module부터 정의하고 특정 SQL문 1개 또는 이상에 대해 정의client 세션 ID를 client의 identifier로 쓰겠다는건데 

 

Trcsess Utility 

동일 connection pool에 여러 service가 있을 수 있음

DB 입장에서는 동일한 서버 프로세스지 

 

DBMS_SESSION.SET_IDENTIFIER는 client ID 정의하는가봉가

 

TKPROF Utilitytrace file을 쉽게 읽을 수 있는 형태로 변환해주는 역할

 

(교재에 설명 있음)waits: sort: 내용 정렬 순서 (elapsed time이 큰 순서...)print: 처음 n문장 출력 aggregate:insert: 많이 사용하지는 않음. 별로 파일에 저장sys: recursive call? internal로 실행되는 SQL문을 볼래 안볼래 (자세히 보려면 보는게 좋지)table: 12c 버전에서는 별도로 필요 없음. plan 정보를 저장하는 임시 테이블.explain: user에 대해 plan 정보도 같이 보여준대record: trace된 SQL문을 별도 저장하는 파일

 

(예시 p.115) 이런거 유용햐!Parse 1회, Execute 1회Fetch 2회 : 데이터가 2번에 걸쳐서 사용자에게 보내졌다record row=1→ 한 사용자가 SQL문장을 2번 실행했다는 뜻(만약 Fetch>1, rows가 많을 때는 사용자가 설정한 array size 때문에 여러번 전달되는겅미)CPU time과 Elapsed time이 거의 일치 → waits 없음disk=0 & query=15 → 디스크에서는 0 block, 메모리에서 15 blocks을 읽음 (data dictionary도 포함됨)current는 insert/update/delete 진행할 때의 block수가 나온대 --> query vs current 차이 확인 필욧Misses in library cache during parse = 1 → 1이면 좋음. SQL에 대한 실행코드가 만들어지지 않아서 1개 만듬

Optimizer mode : FIRST_ROWS → 하나의 레코드를 빨리 전달할 수 있도록 옵티마이저 목표 설정 (good)

Parsing user id : 88 → session ID 말고 user ID라는게 있대 v$session에서 조회됨

 

Plan 정보를 읽어보자

TABLE ACCESS FULL EMPLOYEES  - cr=7 메모리에서 데이터를 읽은 것에 대한 cost, Rows=107개 레코드를 검색했고,SORT AGGREGATE → 그룹 함수 (max)  - cr=7TABLE ACCESS FULL EMPLOYEES

  - cr=15 (실제 distinct한 block 수는 7개일거야, 7개를 1번 읽고 또 읽은거니까)

 

Elapsed time

client로부터 메시지가 받을 때 wait 2건 발생 중 1건이 9.62초 

CPU time과는 관련 없고 사용자가 느끼는 response time에는 영향이 있을거야

일반적으로 NW 문제겠지요

 

(예시 p.118)

update 문

Execute 단계에서 어느 정도의 데이터를 검색하고 있는지 확인할 수 있다

disk 5건 검색

memory 297건 검색 → 어느 정도 

실제 update 되는건 3건 (undo block도 포함됨)

레코드는 rows=1

그리고 Fetch에서는 당연히 처리되는 데이터 없음

 

(예시 p.119)

Parse 2회 

Misses : 1 → Parse=2 & Misses=1이라는 건 Hard parse 1회, Soft parse 1회라는 뜻

(Soft parse도 실행계획 정보를 검색해야 한다)

CPU = 221 < elapse = 329 → wait도 있었다

query = 45 blocks(buffers) 검색 (2번에 걸쳐서 45, 중복이 있을 수도 있음)

Execute = 3 & Fetch = 3 & rows=1 → SQL cursor 정보를 session이 잡고 있는 상태에서 SQL문을 실행하면 soft parse 없이 바로 execute 할 수 있음.

SQL문을 사용자 2명이 사용한 것으로 추정됨Fetch : 메모리에 올리고 사용자한테 보내주는데 8초 Parse가 오래 걸린다 = 데이터 검색 자체 보다는 시스템 문제일 가능성이 높다

 

(예시 p.120)update문 looping으로 실행되었을 것 같아실행할 때마다 1건씩 update 하는 것 같아CPU=66476.95초

query= 488729 업데이트를 위해 검색한 데이터

current=1970566 업데이트 하고 있는 데이터 (undo 포함)

parse=0 → PL/SQL 내에서는 parse를 미리 해놓을 수 있음 (다른 문법을 통해 이미 parse 되었음)

 

일반적으로 Execute > Parse인게 좋다 (실행 계획이 공유되었다는 뜻)

 

p.122

SQL*Net message from client 78.39초...! 

원인 1) NW 환경 (보안 기술 적용되어 있을 때) 2) 개발 프로그램 로직 문제

 

p.124

SQL문 없이 실행 계획이 나온다?

프로그램이 서브프로그램을 호출을 통해 SQL문이 실행된 경우??

SQL문 실행 될 때 트레이스 시작한 경우??

Parse 단계에서 데이터 읽은건 없었는데? 

 

<실습 교재>

C:\Users\Jae-eun\Downloads\실습파일\labs\solutions\Application_Tracing\myreport.txt

 

select /*+ ORDERED USE_HASH(s2) */ count(*) 
from sales s1, sales s2

where s1.cust_id=s2.cust_id

 

case 1. work area를 매뉴얼 설정 (sort area=50000, hash area=5000 -> 목적에 맞는 area(memory)에 올려두고 처리)

  Fetch CPU=58.51, elapsed=75.54, disk=950047

  조회하려는 데이터들은 거의 disk에서 새로 읽어 옴

  index fast full scan : index 통해 모든 데이터를 읽음

  ordered, hash를 처리하는데 sort area, hash area가 부족하다

  event waits 리스트 중에 direct path read temp=797634 → 스와핑 발생

 

case 2. work area를 자동으로 설정

  Fetch CPU=7.49, elapsed=11.34, disk=0, query=3926

  마찬가지로 index fast full scan, order, hash로 처리하는 데이터 건수는 동일해

  case 1에서 데이터를 memory에 올려뒀기 때문에 disk=0일 거야

begin
    for i in 1 .. 5000 loop
        dbms_sql.parse(c,'select object_name from dba_objects where object_id = '||i , dbms_sql.native); -- use literal
        dbms_sql.define_column(c, 1, oname, 50);
        ignore := dbms_sql.execute(c);
        if dbms_sql.fetch_rows(c)>0 then
            dbms_sql.column_value(c, 1, oname);
        end if;
    end loop;

 

dynamic SQL

disk=0, query=0인데 CPU=4.27, elapsed=5.77 많이 걸리는 편

plan이 복잡해

recursive SQL에 문제가 있을 것 같아

dba_objects를 조회하기 위한 내부적인 SQL이 문제인 듯 해

 

begin
    dbms_sql.parse(c,'select object_name from dba_objects where object_id = :y' , dbms_sql.native); -- use bind var
    for i in 1 .. 5000 loop
        dbms_sql.bind_variable(c,':y',i);
        dbms_sql.define_column(c, 1, oname, 50);
        ignore := dbms_sql.execute(c);
        if dbms_sql.fetch_rows(c)>0 then
            dbms_sql.column_value(c, 1, oname);
        end if;
    end loop;

 

리터럴SQL: 조건을 상수값으로 직접 선언한 값

바인드 변수: 바인드 변수 값이 달라지더라도 동일한 SQL문으로 인식하여 동일 실행계획 사용