1 / 52

Make a Real DBA

Make a Real DBA. 인스턴스 튜닝. ㈜ 신한시스템즈. 김 종 근. 인스턴스 튜닝. 메모리 히트율과 SGA 사이즈 결정 대기 이벤트 모니터 Latch 와 Enqueue Q & A. 메모리 히트율과 SGA 사이즈 결정. SGA 의 구조 히트율 측정 SGA 사이즈 결정. System Global Area. Database Buffer Cache. Redo Log Buffer. Shared Pool. SHARED_POOL_SIZE. DB_CACHE_SIZE.

wittc
Download Presentation

Make a Real DBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Make a Real DBA 인스턴스 튜닝 ㈜ 신한시스템즈 김 종 근

  2. 인스턴스 튜닝 • 메모리 히트율과 SGA 사이즈 결정 • 대기 이벤트 모니터 • Latch와 Enqueue • Q & A

  3. 메모리 히트율과 SGA 사이즈 결정 • SGA의 구조 • 히트율 측정 • SGA 사이즈 결정

  4. System Global Area Database Buffer Cache Redo LogBuffer Shared Pool SHARED_POOL_SIZE DB_CACHE_SIZE DB_KEEP_CACHE_SIZE SHARED_POOL_RESERVED_SIZE DB_KEEP_RECYCLE_SIZE System Global Area(SGA) LOG_BUFFER

  5. Shared Pool System Global Area Library Cache Database Buffer Cache Redo LogBuffer Shared Pool Dictionary Cache User Global Area • Library Cache: 공유 SQL 및 PL/SQL 코드 저장 • Dictionary Cache: 데이터 딕셔너리 객체 정의 보유 • User Global Area: 공유 서버 모드에서 세션 정보 저장

  6. DB Buffer Cache System Global Area Database Buffer Cache Redo LogBuffer Shared Pool • DB Buffer Cache: 데이터 파일의 데이터 블록의 복사본 보유 • LRU 알고리즘에 의해 관리

  7. Redo Log Buffer System Global Area Database Buffer Cache Redo LogBuffer Shared Pool • Redo Log Buffer: 데이터베이스에 적용된 변경사항(리두항목)이 기록되는 영역

  8. Library Cache Dictionary Cache Shared Pool • 다른 메모리 영역에서의 캐시 실패보다 공유 풀에서 캐시 실패가 비용이 높다. • 라이브러리 캐시의 튜닝에 초점을 맞춘다. • 사이즈가 작은 경우 CPU 소모가 많고, 경합이 많이 발생할 수 있다. • 사이즈가 큰 경우 단편화 관리를 위한 오버헤드가 발생

  9. Library Cache Tuning Point • 구문 분석을 최소화하여 실패(miss)를 줄인다. • 공유할 수 있도록 문장을 작성한다. • 충분한 공간 할당을 통해 문장이 삭제(age out)되지 않도록 한다. • 무효화가 일어나지 않도록 한다. • 메모리 단편화를 제거한다. - 대용량 메모리 요구 사항에 대비한 공간 예약 - 자주 사용되는 대형 객체 고정 - 익명의 대형 PL/SQL 블록 제거

  10. Library Cache Hit Ratio • Hit Ratio = Get Hits / Gets │ │ │ └ 요청에 대해 캐시에서 찾은 총 회수 │ └ 해당 항목을 요청한 총 회수 SQL> select gethitratio 2 from v$librarycache 3 where namespace = 'SQL AREA'; GETHITRATIO ----------- .841509596

  11. Finding Sharable SQL SQL> select users_executing, executions, loads, substr(sql_text, 1, 200) 2 from v$sqlarea where rownum < 100 3 order by upper(sql_text); USERS_EXECUTING EXECUTIONS LOADS --------------- ---------- ---------- SUBSTR(SQL_TEXT,1,80) -------------------------------------------------------------------------------- 0 62 1 SELECT * FROM webmgr.webyaa WHERE idno = '' 0 2 4 SELECT * FROM webmgr.webyaa WHERE idno = 'DTSWCY32' 0 1 1 SELECT * FROM webmgr.webyaa WHERE idno = 'NAJICY' 0 8 3 select 'X' from BIDMGR.BIDUSR where (CPCD=trim(:b0) and USID=trim(:b1)) 0 3006 2 select 'X' from ICPMGR.ICPNCH where (CPCD=trim(:b0) and CNNO=trim(:b1)) 0 1 2 select 'x' from dual : :

  12. Library Cache Reloads • Reload: 실행단계에서의 캐시 실패로 인한 재구문 분석 • Reloads Ratio = Reloads / Executions │ │ │ └ 실행 단계에서의 캐시 실패가 발생한 총 회수 │ └ 객체를 읽거나 실행한 총 회수 SQL> select sum(pins) "Executions", sum(reloads) "Cache misses", sum(reloads)/sum(pins) 2 from v$librarycache; Executions Cache misses SUM(RELOADS)/SUM(PINS) ---------- ------------ ---------------------- 8662143 19285 .002226354

  13. Large Memory Reserve • 사이즈가 큰 오브젝트를 위한 공간을 마련한다. • SHARED_POOL_RESERVED_SIZE 파라미터 사용 • Default: Shared_Pool_Size의 5% SQL> SELECT free_space, avg_free_size, used_space, 2 avg_used_size, request_failures, last_failure_size 3 FROM v$shared_pool_reserved; FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE ---------- ------------- ---------- ------------- ---------------- ----------------- 105334360 43383.1796 11096240 4570.11532 0 0 - request_misses가 0으로 유지되도록 하는게 목표 - REQUEST_FAILURES>0 면 사이즈가 작은 것 - FREE_SPACE=>SHARED_POOL_RESERVED_SIZE/2 이면 큰 것

  14. Large Object Pinning • 자주 사용되는 큰 객체는 캐시 내에 고정 SQL> select * from v$db_object_cache 2 where sharable_mem > 10000 3 and type in ('PACKAGE','PACKAGE BODY', 'FUNCTION', 'PROCEDURE') 4 and kept = 'NO'; OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS … ------- --------------- --------------- ---------- ------------ ----- ---------- SYS DBMS_OUTPUT TABLE/PROCEDURE PACKAGE 14023 7 0 … SALMGR SF_IOIL TABLE/PROCEDURE FUNCTION 12794 7 0 SYS DBMS_STANDARD TABLE/PROCEDURE PACKAGE 27737 3 8 SALMGR SF_TARF_ARN3 TABLE/PROCEDURE FUNCTION 11219 5 421 LIAMGR LIASTAYPERIOD TABLE/PROCEDURE FUNCTION 21868 1 9 SALMGR SF_TARF_ARN4 TABLE/PROCEDURE FUNCTION 11591 5 421 SQL> @?/rdbms/admin/dbmspool.sql SQL> execute dbms_shared_pool.keep(‘dbms_standard’);

  15. Dictionary Cache Tuning • 캐시 실패(miss)를 줄인다. - 실패율이 15% 이내에 있도록 한다. - 라이브러리 캐시의 튜닝 여부를 점검한다. SQL> SELECT SUM(gets), SUM(getmisses), 2 SUM(getmisses)*100/SUM(gets) "GetMissRatio" 3 FROM v$rowcache; SUM(GETS) SUM(GETMISSES) GetMissRatio ---------- -------------- ------------ 35926 328 .912987808

  16. Shared Pool Sizing • SHARED_POOL_SIZE = ? (Obj Mem + SQL Mem + Cursors) * 1.2 공유 SQL의 사이즈 오버헤더 객체 정의에 할당된 메모리 오픈된 커서에 할당된 메모리

  17. Shared Pool Sizing • 패키지, 뷰과 같은 내장 객체: SQL> select sum(sharable_mem) 2 from v$db_object_cache; SUM(SHARABLE_MEM) ----------------- 924308904 • SQL 문: SQL> select sum(sharable_mem) 2 from v$sqlarea where executions > 5; SUM(SHARABLE_MEM) ----------------- 59959149

  18. Shared Pool Sizing • 사용자당 열린 커서별로 약 250Bytes 필요: SQL> select sum(250*users_opening) 2 from v$sqlarea; SUM(250*USERS_OPENING) ---------------------- 50120300 SHARED_POOL_SIZE = (924308904+59959149+50120300)*1.2 = 1241266023

  19. DB Buffer Cache • SQL 튜닝을 통해 액세스 블록 수를 줄인다. • 액세스 특성을 구분한다. • 메모리에 캐시할 테이블을 구분한다.

  20. DB Buffer Cache Hit Ratio • Hit Ratio = Physical Reads / All Reads │ └ 전체 블록 읽기 회수 │ │ │ └ 물리적 디스크 읽기 회수 SQL> select 1-(phy.value-lob.value-dir.value)/ses.value "CacheHitRatio" 2 from v$sysstat ses, v$sysstat lob, v$sysstat dir, v$sysstat phy 3 where ses.name = 'session logical reads' 4 and dir.name = 'physical reads direct' 5 and lob.name = 'physical reads direct (lob)' 6 and phy.name = 'physical reads’; CacheHitRatio ------------- .982277269

  21. Buffer Cache Tuning Point • 히트율 99%는 잘 못 튜닝된 데이터베이스에서도 나올수 있다. • 대기 이벤트를 통하여 병목의 원인을 파악한다. • SQL 문장을 튜닝한다. • 버퍼 캐시 사이즈를 늘린다.

  22. 다중 버퍼 풀 • 오브젝트의 특성에 따라 캐시 영역을 분류 캐시에서 가능한 빨리 제거될 오브젝트 RECYCLE POOL 캐시에서 가능한 오래 유지될 오브젝트 KEEP POOL 일반적인 오브젝트 DEFAULT POOL

  23. 다중 버퍼 풀 사용 • 오브젝트의 특성에 따라 캐시 영역을 분류 CREATE TABLE a … STORAGE( BUFFER_POOL recycle … ); DB_RECYCLE_CACHE_SIZE ALTER TABLE b … STORAGE( BUFFER_POOL keep … ); DB_KEEP_CACHE_SIZE ALTER TABLE b … STORAGE( BUFFER_POOL default … ); DB_CACHE_SIZE

  24. Redo Log Buffer • 서버 프로세스가 필요로 하는 공간 확보 Redo LogBuffer • 지나치게 큰 것은 자원 낭비 • 생성되는 리두의 양을 조절

  25. Redo Log Buffer Monitor • 리두 로그 버퍼에서 발생한 대기 시간: SQL> select sid, event, seconds_in_wait, state 2 from v$session_wait 3 where event = 'log buffer space%'; SID EVENT SECONDS_IN_WAIT STATE ----- --------------------------- --------------- -------- 13 log buffer space 110 WAITING • 리두 로그 버퍼에 발생한 대기 이벤트: SQL> select r.value "Retries", e.value "Entries", r.value/e.value*100 "Percentage" 2 from v$sysstat r, v$sysstat e 3 where r.name = 'redo buffer allocation retries' 4 and e.name = 'redo entries‘; Retries Entries Percentage ---------- ---------- ---------- 35 8637338 .000405217

  26. 대기 이벤트 모니터 • 대기 이벤트 • 대기 이벤트의 종류 • 대기 이벤트를 통한 병목 지점 확인

  27. 대기 이벤트 • 공유 오브젝트에 대한 경합으로 인해 대기 현상이 발생 • 다음 작업을 하기 전에 이전 작업이 끝나기를 기다림으로 인해 발생

  28. 대기 이벤트 종류 SQL> select * from v$event_name; EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3 ------ ---------------------------------------- ----------- ---------- ----------- 0 null event 3 latch free address number tries 15 enqueue name|mode id1 id2 137 free buffer waits file# block# set-id# 139 checkpoint completed 145 buffer busy waits file# block# id 146 buffer busy global cache file# block# id 147 buffer busy global CR file# block# id 179 log file switch (checkpoint incomplete) 180 log file switch (archiving needed) 187 db file sequential read file# block# blocks 188 db file scattered read file# block# blocks 253 index block split rootdba level childdba 259 row cache read cache id address times 348 PL/SQL lock timer duration 363 rows selected

  29. 병목 지점 확인을 위한 뷰 • V$EVENT_NAME: 모든 이벤트 명 • V$SYSTEM_EVENT: 모든 세션을 합한 이벤트에 대한 전체 대기 수 • V$SESSION_EVENT: 대기해야 했던 각 세션에 대한 이벤트 대기 수 • V$SESSION_WAIT: 대기 중인 현재 활성 세션에 대한 이벤트 대기 수

  30. 병목 지점 확인(시스템) SQL> select * from v$system_event; EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -------------------------------------- ----------- -------------- ----------- ------------ Null event 1 1 410 410 latch free 2 2 4 2 pmon timer 214428 214426 66043383 307.997943 enqueue 68 0 1396 20.5294118 control file parallel write 214184 0 93727 .437600381 checkpoint completed 1 0 8 8 buffer busy waits 1 0 11 11 log file sequential read 10 0 13 1.3 log file single write 12 0 6 .5 log file parallel write 1571 0 956 .608529599 log file switch completion 3 0 107 35.6666667 log file sync 1486 0 868 .584118439 db file sequential read 1800 0 828 .46 db file scattered read 431 0 190 .440835267 db file single write 12 0 1 .083333333 db file parallel write 771 0 1 .001297017 db file parallel read 1 0 10 10 SQL*Net message to client 179024 0 58 .000323979 SQL*Net more data to client 61 0 1 .016393443 SQL*Net message from client 179022 0 9669480 54.0128029

  31. 병목 지점 확인(세션) SQL> select * from v$session_event; SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT --- ----------------------------- ----------- -------------- ----------- ------------ ---------- 1 pmon timer 214596 214594 66095127 307.997945 320 5 rdbms ipc reply 1 0 1 1 1 2 rdbms ipc message 214601 214591 66094208 307.986487 314 3 rdbms ipc message 216084 214574 66094213 305.872776 316 6 rdbms ipc message 362 361 65990193 182293.351 184327 4 rdbms ipc message 214298 214290 66001759 307.990551 322 2 control file sequential read 46 0 15 .326086957 15 4 control file sequential read 28 0 0 0 0 3 control file sequential read 60 0 11 .183333333 3 3 control file parallel write 34 0 115 3.38235294 61 4 control file parallel write 214304 0 93595 .436739398 104 3 log file sequential read 7 0 7 1 2 3 log file single write 11 0 6 .545454545 3 3 log file parallel write 1571 0 956 .608529599 36 5 db file sequential read 974 0 357 .366529774 24 9 db file sequential read 37 0 28 .756756757 2 6 db file sequential read 3 0 0 0 0 5 db file scattered read 241 0 107 .443983402 10 2 db file parallel write 771 0 1 .001297017 1 2 direct path read 24 0 0 0 0 4 direct path read 36 0 0 0 0 3 direct path read 12 0 0 0 0 3 direct path write 12 0 0 0 0

  32. 주요 대기 이벤트

  33. Latch와 Enqueue • Latch란? • Latch의 종류 • Latch 모니터 • Enqueue란? • Enqueue의 종류 • Enqueue 모니터

  34. Latch란? • SGA에 있는 공유 데이터구조를 보호하는 직렬화 방식 System Global Area Server Process1 LRU List Database Buffer Cache Server Process2 LRU Latch Server Process3

  35. Latch 대기 • 자원이 사용중이면 요청한 프로세스는 대기한다. Spin or sleep System Global Area Server Process1 LRU List Database Buffer Cache Server Process2 LRU Latch Spin or sleep Server Process3

  36. Latch 요청 방식 • Willing-to-wait • 요청 프로세스가 잠시 대기 후 다시 래치 요청 • 프로세스가 래치를 사용할 수 있을 때가지 계속 대기하면서 요청 • Immediate - 요청 프로세스가 대기하지 않고 다른 명령의 처리 진행 두 모드의 차이는 요청된 래치를 사용할 수 없을 때의 진행 방식만 틀림

  37. Latch의 종류 SQL> select * from v$latchname; LATCH# NAME ---------- ---------------------------------------------------------------- 0 latch wait list 1 event range base latch 2 post/wait queue 3 process allocation 4 session allocation : 93 cache buffers lru chain 94 buffer pool 95 multiple dbwriter suspend 96 active checkpoint queue latch 97 checkpoint queue latch : 107 archive control 108 archive process latch 112 alert log latch 113 redo writing 114 redo copy : 239 xssinfo freelist 240 AW SGA latch 241 rows selected.

  38. Latch 경합 모니터 • 대기 이벤트에서「latch free」의 비중을 확인 SQL> SELECT * 2 FROM v$system_event 3 WHERE event NOT IN ('pmon timer', 'smon timer', 'rdbms ipc message', 4 'SQL*Net message from client') 5 ORDER BY time_waited DESC; EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -------------------------- ----------- -------------- ----------- ------------ db file sequential read 478770 0 7 .000014621 log file sync 113274 47 1 8.8282E-06 Null event 1 1 0 0 latch free 4833 4262 0 0 process startup 15 0 0 0

  39. Latch 경합 모니터 • V$LATCH에서 경합 부위 확인 SQL> select name, gets, misses, sleeps, immediate_gets, immediate_misses 2 from v$latch 3 where misses > 0 or immediate_misses >0; NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES ------------------------- ---------- ---------- ------- -------------- ---------------- enqueues 13928533 3105 0 0 0 enqueue hash chains 17243788 1048 0 0 0 cache buffers lru chain 5101150 4719 12 40659842 17200 checkpoint queue latch 38148780 1851 0 3253596 134 cache buffers chains 2508578251 573034 11972 68638909 1548 cache buffer handles 2452968 61 0 0 0

  40. 주요 Latch

  41. 40 최경식 부산시 남구 대연… 부산시 해운대구 우 50 이동원 부산시 중구 중앙동 70 80 60 90 이정현 김일동 홍민우 김진명 Enqueue란? • 데이터베이스 자원을 직렬화하기 위한 공유 오브젝트 System Global Area Server Process1 Server Process2 Enqueue Server Process3

  42. 40 최경식 부산시 남구 대연… 부산시 해운대구 우 50 이동원 부산시 중구 중앙동 60 70 80 90 이정현 김일동 김진명 홍민우 Enqueue 대기 • 자원이 사용 중이면 대기한다. System Global Area Server Process1 Hold Server Process2 Wait Enqueue R E Q 3 R E Q 2 R E Q 1 Server Process3 Wait

  43. Enqueue 요청 레벨

  44. Enqueue의 종류

  45. 대표적 Enqueue

  46. Enqueue 모니터 SQL> select * from v$lock; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---- -- ------- ----- ----- ------- ------ ----- 0000000381912138 0000000381912158 2 MR 6 0 4 0 665125 0 00000003819120B0 00000003819120D0 2 MR 5 0 4 0 665125 0 0000000381912028 0000000381912048 2 MR 4 0 4 0 665125 0 0000000381911FA0 0000000381911FC0 2 MR 3 0 4 0 665125 0 0000000381911F18 0000000381911F38 2 MR 2 0 4 0 665125 0 0000000381911E90 0000000381911EB0 2 MR 1 0 4 0 665125 0 0000000381911CF8 0000000381911D18 3 RT 1 0 6 0 665126 0 0000000381911AD8 0000000381911AF8 4 XR 4 0 1 0 665130 0 0000000381911D80 0000000381911DA0 5 TS 2 1 3 0 665121 0 000000038126EDD0 000000038126EF48 10 TX 524295 175 6 0 10 0 0000000381255550 0000000381255578 10 TM 5978 0 3 0 10 0 11 rows selected.

  47. Enqueue 모니터 SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from v$lock where sid > 9; no rows selected.

  48. Enqueue 모니터 SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> update dept 2 set loc='a' 3 where deptno = 10; 1 row updated. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from v$lock where sid > 9; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---- -- ------- ----- ----- ------- ------ ----- 000000038126EDD0 000000038126EF48 10 TX 524295 175 6 0 10 0 0000000381255550 0000000381255578 10 TM 5978 0 3 0 10 0 2 rows selected.

  49. Enqueue 모니터 SQL> update dept 2 set loc='a' 3 where deptno = 10; 1 row updated. SQL> insert into dept 2 values ( 50, 'a', 'b'); 1 row created. SQL> select * from v$lock where sid > 9; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---- -- ------- ----- ----- ------- ------ ----- 000000038126EDD0 000000038126EF48 10 TX 524295 175 6 0 120 0 0000000381255550 0000000381255578 10 TM 5978 0 3 0 120 0 0000000381265B08 0000000381265C80 14 TX 327681 178 6 0 12 0 0000000381255610 0000000381255638 14 TM 5978 0 3 0 12 0 4 rows selected.

  50. Enqueue 모니터 SQL> update dept 2 set loc='a' 3 where deptno = 10; 1 row updated. SQL> insert into dept 2 values ( 50, 'a', 'b'); 1 row created. SQL> update dept 2 set dname = 'a' 3 where deptno = 10; SQL> select * from v$lock where sid > 9; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---- -- ------- ----- ----- ------- ------ ----- 000000038126EDD0 000000038126EF48 10 TX 524295 175 6 0 167 1 0000000381255550 0000000381255578 10 TM 5978 0 3 0 167 0 0000000381265B08 0000000381265C80 14 TX 327681 178 6 0 59 0 0000000381255610 0000000381255638 14 TM 5978 0 3 0 59 0 0000000381911BE8 0000000381911C08 14 TX 524295 175 0 6 1 0 5 rows selected.

More Related