#40_140807_OWI_SHARED POOL에서 LATCH 경합

# SHARED POOL에서 LATCH 경합

l shared pool 의 역할

- parsing을 최소화 하기 위한 메모리 영역

- SQL과 실행계획, parse tree(실행코드)의 코드를 공유함으로써 파싱을 최소화 할 수 있다

l Shared pool의 구조

- chunck : 메모리 저장 최소단위

- soft parse : 똑 같은 SQL이 존재하면 parsing 생략한다

- hard parse : 똑 같은 문장이 없기 때문에 다시 parse한다

- soft parse를 위해서는 Library cache latch를 잡아야 한다

- hard parse를 위해서는 Shared pool latch가 필요하다

- Library cache latch : 똑 같은 SQL이 있는지 검색할 때 필요

- Shared pool latch : 똑 같은 SQL이 없어서 파싱한 결과물 3가지를(SQL, 실행계획, parse tree) shared pool에 올릴떄 필요하다

- parse를 수행하는 과정에서 같은 SQL이 존재한다면 parsing을 생략하는데 이것을 soft parse라고 한다. soft parse를 수행하기 위해서는 shared pool의 메모리를 검색해야 하는데 이 때 점유하고 하는 메모리에 library cache latch를 걸고 다른 session에서 접근하지 못하게 한다

- 만약에 같은 SQL이 없다면 parse를 수행해서 parse 결과물을(SQL, 실행계획, parse tree) 생성하는 hard parse를 수행한다. 이 때 필요한 latch는 shared pool latch를 잡아야 한다. 결국에 hard parse는 메모리에 같은 SQL이 있는지 유무 검사하고 parse의 결과물을 올리는 것이기 때문에 library cache latch와 shared pool latch 둘 다 필요하다

l Soft parse 수행과정

- 새로운 SQL이 생성되면 shared pool에 똑 같은 SQL이 있는지 확인하기 위해서library cache latch를 잡고 그 이후에 서버프로세서가 pool 검색을 한다

- 이 떄 PGA 영역에는 shared pool moery의 주소 정보가 들어있는데 이 주소 정보를 이용해서 빠르게 찾아서 검색을 한다

- 만약에 library cache latch 이벤트가 지속적으로 발생한다면 PGA 영역의 메모리 개수를 늘려줘야 한다(PGA 메모리 주소 개수 : session_cached_cursors)

n Library cache latch(ADDM report)

SQL> exec dbms_workload_repository.create_snapshot; SQL> @exec - library cache latch(120초) SQL> exec dbms_workload_repository.create_snapshot; SQL> @?/rdbms/admin/addmrpt.sql Action       Consider increasing the session cursor cache size by increasing the       value of parameter "session_cached_cursors".    Rationale       The value of parameter "session_cached_cursors" was "50" during the       analysis period. >> session_cached_cursor를 늘리고 다시 부하를 일으켜서 library cache latch 경합을 줄었는지 awr 비교 레포트 확인한다 SQL> alter system set session_cached_cursors=300 scope=spfile; SQL> shutdown immediate SQL> startup SQL> connect owi/owi SQL> exec dbms_workload_repository.create_snapshot; SQL> @exec - Library cache latch 60초 설정 SQL> exec dbms_workload_repository.create_snapshot; SQL> @?/rdbms/admin/awrddrpt.sql

l Hard parse 수행과정

- 만약에 새로운 SQL과 같은 SQL이 없다면 빈 메모리에 shred pool latch를 걸고hard parse를 수행한다

n Shared pool latch(Awr compare report)

>> addm report를 이용해서 shred pool latch의 발생의 해결책을 찾는다 SQL> exec dbms_workload_repository.create_snapshot; SQL> @exec - shared pool latch 60초 설정 SQL> exec dbms_workload_repository.create_snapshot; SQL> @?/rdbms/admin/addmrpt.sql Action       Consider increasing the session cursor cache size by increasing the       value of parameter "session_cached_cursors".    Rationale       The value of parameter "session_cached_cursors" was "300" during the       analysis period. ………………………………………………… Action       Alternatively, you may set the parameter "cursor_sharing" to "force".    Rationale       At least 28 SQL statements with FORCE_MATCHING_SIGNATURE       17109837705936771014 and PLAN_HASH_VALUE 3909286317 were found to be       using literals. Look in V$SQL for examples of such SQL statements. - session_cached_cursors의 공간을 늘리라는 결과가 나온다 - 또한 cursor_sharing의 값을 force로 바꾸라는 조언하는 것을 확인할 수 있다 >> addm report에서 확인한 결과를 이용해서 파리미터를 변경한다 # SYS SQL> alter system set cursor_sharing=force; SQL> alter system set session_cached_cursors=1200 scope=spfile; SQL> shutdown immediate SQL> startup # OWI SQL> @?/rdbms/admin/awrddrpt.sql - 결과 레포트를 확인해본 결과 첫번째와 두번째를 비교했을 때 파라미터를 변경했기 때문에 shared pool latch가 사라진 것을 확인할 수 있다

l Shared pool의 부하가 있을때의 튜닝방법 정리

1.     SQL이 공유가 될 수 있도록 프로그래밍을 한다

- Literal SQL ~> bind 변수

2.     cursor_sharing을 force로 지정

- Shared pool latch 대기 이벤트가 줄어듦

3.     session_cached_cursors 의 값을 늘린다

- soft parse 시간을 줄일수 있다

- library cache latch 대기 이벤트를 줄일수 있다

4.     shared pool 사이즈를 늘린다

- SQL> show parameter shared_pool_size

- shared pool parameter 값이 0이면 자동관리 된다

- 11g : memory_target 파라미터로 shared pool 사이즈가 자동으로 관리된다

- SQL> alter system set shared_pool_size=136;

: 자동조절할 때 136 이하로는 조절안되도록 하는 명령어

5.     큰 SQL 소스나 PL/SQL을 위한 예약공간을 만든다

- 위와같이 오래된 SQL이 빠져나가고 연속된 공간이 없는 현상을 단편화(fragmentation) 라고 한다.

- 위와 같이 shared pool의 빈공간을 가지고 있지만 매우 큰 SQL이 들어오면서 공간을 확보하지 못하면 ORA-4131 Error 가 발생한다

- ORA - 4031 에러 해결방법

: shared pool flush

: 예약공간을 만든다

: 큰 object를 아예 shared pool 에 keep 시킨다(flush 해도 안내려감)

- SQL> alter system flush shared_pool;

: 빈 공간이 충분하지 않다고 shread pool을 flush 시키면 자주 사용하는 SQL까지 모두 flush 시키기 때문에 전체가 느려질 수 있다.

: 그렇기 때문에 사람들이 퇴근한 시간 이후에 수행하는 것이 좋다

- 큰 SQL이 연속된 공간을 확보하지 못하는 상황일 때 위 그림처럼 예약공간을 만들면 일정크기의 SQL은 예약공간으로 저장된다

n 메모리 사이즈 확인 SQL

- Shared pool 의 메모리를 변경하기전에 몇으로 설정되어 있는지 확인

SQL> ed sga.sql -- Oracle Database 10g: Administration Workshop II -- Oracle Server Technologies - Curriculum Development -- -- ***Training purposes only*** -- ***Not appropriate for production use*** -- PROMPT *** Current parameter settings *** col name format a12 col value format a8 show parameter sga_ PROMPT PROMPT *** SGA Dynamic Component Size Information*** col component format a22 col current_size format a15 col min_size format a15 SELECT component,current_size/1048576||'M' current_size,        min_size/1048576||'M' min_size FROM v$sga_dynamic_components WHERE component IN ('shared pool','large pool',                    'java pool','DEFAULT buffer cache'); col name format a20 col value format a20 PROMPT *** Current parameter settings in V$PARAMETER *** SELECT name, value, isdefault FROM v$parameter WHERE name IN ('shared_pool_size','large_pool_size',                'java_pool_size', 'db_cache_size');

SQL> @sga *** Current parameter settings *** NAME                                 TYPE                             VALUE ------------------------------------ -------------------------------- ------------------------------ sga_max_size                         big integer                      404M sga_target                           big integer                      0 *** SGA Dynamic Component Size Information*** COMPONENT              CURRENT_SIZE    MIN_SIZE ---------------------- --------------- --------------- shared pool            144M            144M large pool             4M              4M java pool              8M              8M DEFAULT buffer cache   64M             56M *** Current parameter settings in V$PARAMETER *** NAME                 VALUE                ISDEFAULT -------------------- -------------------- --------- shared_pool_size     0                    TRUE large_pool_size      0                    TRUE java_pool_size       0                    TRUE db_cache_size        0                    TRUE

n Keep 시키는 방법

- package 같은 경우에는 사이즈가 크기 때문에 단편화 현상이 일어날 확률이 높다

- 자주쓰는 package는 keep 이용하는 것도 좋다

# SYS SQL> @?/rdbms/admin/dbmspool.sql - dmbs_shared_pool 패키지 생성 SQL> desc dbms_shared_pool - shared pool 패키지 생성 확인 SQL> exec dbms_shared_pool.keep('DBFILE'); - keep 시키는 명령어 # SCOTT(package를 만든 session) SQL> SELECT * FROM v$db_object_cache WHERE TYPE='PACKAGE' AND kept !=  'NO'; - keep 확인 >> shared pool을 flush 시켜보고 keep 된 package가 사라지는지 확인 SQL> alter system flush shared_pool SQL> SELECT * FROM v$db_object_cache WHERE TYPE='PACKAGE' AND kept !=  'NO'; - flush 시킨 이후에도 keep 시킨 package가 남아있는 것을 확인할 수 있다 >> keep 된 package를 unkeep SQL> exec dbms_shared_pool.unkeep('DBFILE');