[Performance Tuning]기본 튜닝 진단

기본 튜닝 진단

성능 튜닝 진단

• 누적통계
• 시간 정보가 포함된 대기 이벤트 (wating event)
• 시간 모델
• Metrics
• 기준에 따라(초당, tx당, user당) physical read를 얼마나 했나에 대한 수치를 의미있게 가공한 지표
• 판단의 근거가 될 수 있다.
• 샘플링 통계(Active Session History)
• 지금 활성화된 세션들의 정보
• 세션별 통계
• SQL별 통계
• 서비스별 통계
• 기타 차원(Dimension)

튜닝 목표

• 대기의 최소화를 통해 응담시간을 최소화한다.
• 일하는 얘들이 cpu를 조금쓰게 하여 처리량을 증가한다.
• 리커버리 시간을 단축한다.

AWR(Automatic Workload Repository)

•  모든 주요 통계와 적업 로드에 대한 정보를 스냅샷 형태로 저장하는 저장소
• 기본적으로 1시간에 한번 캡쳐된다.
• DB 대기 이벤트 및 통계정보
• 시스템 통계정보
• 데이터베이스 부하정보
• SQL 수행정보
• 활동 세션 정보(ASH)
• SYSAUX가 관리
• Top 5 wait event에서 시간
• time model을 보자

Dynamic Performance View

• SYS 가 소유
• v$fixed_table에서 모든 뷰 이름을 볼 수 있다.
• select_catalog_role 권한
• statistics_level = 'all' : SQL실행통계, os time 정보
• v$statistics_level에서 확인 가능

-- 소요된 CPU 시간이 200,000마이크로초보다 큰 SQL 문

SYS@orcl> select sql_text, executions

          from v$sqlstats

          where cpu_time > 200000;

          

-- 어제 EDRSR9P1 컴퓨터에서 로그인한 세션

SYS@orcl> select * from v$session

          where machine = 'EDRSR9P1' and

          logon_time > sysdate -1;

          

-- 현재 다른 유저를 차단 중인 Lock을 보유하고 있는 세션의 세션 ID는 무엇이며 Lock 상태가 얼마 동안 유지되고 있는가? (차단은 1 또는 0일 수 있음, 1은 해당 세션이 블록을 유발함을 나타냄)

SYS@orcl> select sid, ctime

          from v$lock where block > 0;

SYS@orcl> create user u1 identified by u1;

User created.

SYS@orcl> grant connect, select_catalog_role to u1;

Grant succeeded.

U1@orcl> select count(*) from v$sysstat;

  COUNT(*)

----------

       604

-- v$sysstat : 통계정보를 확인

-- 604개의 통계정보를 모으고 있다.

U1@orcl> select count(*) from dba_users;

  COUNT(*)

----------

        60

SYS@orcl> select statistics_name, activation_level

          from v$statistics_level

          order by 2;

통계표시

• 다음에 대한 Instatnce 작업 통계 수집
• 세션(Session)
• 모든 세션 : v$sesstat
• 현재 세션 : v$mystat
• 서비스 통계(동일한 app을 수행하는 세션들의 묶음) : v$service_stats
• 시스템 자체 통계 : v$sysstat
• 통계
• 논리적 읽기 수
• 메모리 sort를 몇번이나 했는가
• SGA 통계(메모리 통계 정보) : v$sgainfo (sga 각 부분에 대한 정보)

SYS@orcl> select statistic#, name, value, class

          from v$sysstat

          where class <= 64

          order by class;

SYS@orcl> select statistic#, name, value, class

          from v$sysstat

          where name in ('physical reads', 'consistent gets', 'db block gets')

          order by class;

-- trace를 뜰때 disk IO 에 대한 class

STATISTIC# NAME                                                                  VALUE      CLASS

---------- ---------------------------------------------------------------- ---------- ----------

        63 db block gets                                                        502462          8

        67 consistent gets                                                     1029733          8

        72 physical reads                                                        16157          8

SYS@orcl> select statistic#, name, value, class

          from v$sysstat

          where name like '$sort$'

          order by class;

SYS@orcl> select * from v$sgainfo;

NAME                                  BYTES RES

-------------------------------- ---------- ---

Fixed SGA Size                      1337352 No

Redo Buffers                        5963776 No

Buffer Cache Size                 142606336 Yes

Shared Pool Size                  130023424 Yes

Large Pool Size                     4194304 Yes

Java Pool Size                      4194304 Yes

Streams Pool Size                         0 Yes

Shared IO Pool Size                       0 Yes

Granule Size                        4194304 No

Maximum SGA Size                  481259520 No

Startup overhead in Shared Pool    58720256 No

Free SGA Memory Available         192937984

SYS@orcl> select * from v$sga;

NAME                      VALUE

-------------------- ----------

Fixed Size              1337352

Variable Size         331352056

Database Buffers      142606336

Redo Buffers            5963776

Variable Size = shared pool + large pool + java pool + free memory

대기이벤트(wait event)

• v$system_wait_class
• 인스턴스가 구동된 후부터 발생한 인스턴스 레벨의 대기 정보를 클래스별로 저장
• v$service_wait_class
• v$session_wait_class
• v$system_event
• 시스템 전체 이벤트 누적
• v$session_event
• 세션별 이벤트 누적
• v$session_wait
• 현재 세션들의 대기 이벤트 p1, p2, p3
• v$event_name
• 이벤트별 p1, p2, p3의 의미
• v$statname

SYS@orcl> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')

--------------------------------------------------------------------------------------------

23

-- 현재 나의 session의 sid 볼수 있다.

SYS@orcl> select sid, seq#, event, wait_time, state

          from v$session_wait

          where sid = 23;

       SID       SEQ# EVENT                                     WAIT_TIME STATE

---------- ---------- ---------------------------------------- ---------- -------------------

        23        136 SQL*Net message to client                        -1 WAITED SHORT TIME

-- 1/100초보다 적은 단위면 -1로 표시된다.

SYS@orcl> select statistic#, name, value, class

          from v$sysstat

          where name like '%sort%';

          

STATISTIC# NAME                                                                  VALUE      CLASS

---------- ---------------------------------------------------------------- ---------- ----------

       565 sorts (memory)                                                       160364         64

       566 sorts (disk)                                                              0         64

       567 sorts (rows)                                                        1014995         64

SYS@orcl> select * from v$statname;

-- class 정보를 보여준다

SYS@orcl> select *

          from v$session_wait

          where event = 'buffer busy waits';

SYS@orcl> select *

          from v$event_name

          where name = 'buffer busy waits';

자주 발생하는 대기 이벤트

• Buffer busy waits
• 버퍼캐시
• DBWR
• Free buffer waits
• 버퍼캐시
• DBWR
• I/O
• DB fuke scattered read(Full table access multi block IO을 했을때)
• I/O
• SQL 튜닝
• DB file sequential read(single block IO를 했을때)
• I/O
• SQL 튜닝
• Enqueue waits(enq)
• lock
• Library cache waits
• 메모리 구조를 보호하기 위한 Letch
• Log buffer space
• 로그 버퍼 I/O
• Log file sync
• 오버 커밋
• LGWR가 LOG file을 쓰는 것을 기다리는 것.
• I/O

alert log, trace file => ADR

• adrci 를 이용해 편하게 alert를 볼 수 있다.
• IPS(incident packaging service)
• 에러의 기록정보를 패키징해주는 서비스
• adrci> ips pacek incident {incident number} in /tmp

[orcl:~]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Mon May 20 14:09:27 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"

adrci> show alert

Choose the alert log from the following homes to view:

1: diag/asm/+asm/+ASM

2: diag/rdbms/orcl/orcl

3: diag/tnslsnr/edydr1p0/listener

Q: to quit

Please select option: 2

~~~~~

......

2019-05-20 13:14:00.303000 +09:00

Thread 1 advanced to log sequence 19 (LGWR switch)

  Current log# 1 seq# 19 mem# 0: +DATA/orcl/onlinelog/group_1.261.798656537

  Current log# 1 seq# 19 mem# 1: +FRA/orcl/onlinelog/group_1.257.798656539

~~~~~

:wq

event : log file sync

• 정의
• 서버 프로세스가 commit, rollack 수행후 LGWR 프로세스가 redo기록을 redo buffer에서 redo log file로 기록할 때까지 대기하는 이벤트
• 원인
• 과다한 commit
• commit을 줄일 수 있는 지 개발자에게 점검 요청
• redo log file의 disk IO 성능 저하로 인한 대기증가
• IO를 개선할 수 있는 disk 요청

• v$system_event, v$session_envent, v$session_wait

SYS@orcl> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')

----------------------------------------------------------------------------------------------------

76

SYS@orcl> create table hr.t1 (a number);

SYS@orcl> insert into hr.t1 values(1);

SYS@orcl> commit;

SYS@orcl> select event, total_waits, time_waited

          from v$session_event

          where sid = 76

          and event ='log file sync'

SYS@orcl> @sess_event

EVENT                                                            TOTAL_WAITS TIME_WAITED

---------------------------------------------------------------- ----------- -----------

log file sync                                                              1           0