About DATABASE

◈ logfile 정보 보기

set line 150
col tname format a10
col file_name format a40
col error format a10
col first_change# format 999,999,999,999
col OFFLINE_CHANGE# format 999,999,999,999
col ONLINE_CHANGE# format 999,999,999,999
col change# format 999,999,999,999
COL MEMBER format a45

SELECT A1.GROUP#, MEMBER, A1.STATUS, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG A1, V$LOGFILE B2
WHERE A1.GROUP# = B2.GROUP# ;

◈ 복구가 필요한 datafile list 보기

col change# format 9999999999999999999999

select t.name tname, d.name file_name, r.change#,d.status, d.enabled, OFFLINE_CHANGE#,
ONLINE_CHANGE#, to_char(ONLINE_TIME,'RR/MM/DD:HH24:MI:SS') ONTIME
from v$tablespace t, v$datafile d, v$recover_file r
where t.ts#=d.ts# and d.file#=r.file#;

◈ tablespace 와 file의 status 보기

col fname format a50

select t.name TNAME, d.name FNAME, d.status, d.enabled, h.error header_err
from v$tablespace t, v$datafile d, v$datafile_header h
where t.ts#=d.ts# and h.file#=d.file#;

◈ 복구에 필요한 archive파일 리스트 보기

col name format a50

select a.name, a.sequence# , a.first_change#,
to_char(a.first_time,'YYYY/MM/DD:HH24:MI:SS') fst_time,
to_char(a.next_time,'YYYY/MM/DD:HH24:MI:SS') next_time
from v$archived_log a, v$recovery_log r
where a.sequence#=r.sequence#;

◈ recovery file status 보기

select * from V$RECOVERY_FILE_STATUS;

◈ recovery progress 보기

select * from v$recovery_progress;

◈ recovery status 보기

select * from v$recovery_status;

SHARED POOL SIZE의 계산방법
==============================
PURPOSE
---------
다음은 ORACLE DATABASE 의 SHARED POOL SIZE를 계산하는 방법에 대하여 알아보기로 한다.

Explanation
------------
오라클 instance를 background process + SGA라고 말할 때 처음 instance가 기동되면 SGA가 할당된다. 이 때 이 SGA를 구성하는 메모리 영역은 크게 3부분으로 구성된다고 할 수 있다.
- Shared pool, Redo log buffer, DB buffer cache.

여기서는 SGA를 구성하는 shared pool의 size를 시스템에 맞게 산출하는 방법에 대해서 알아본다.
Shared pool 영역 구성은 MTS 방식으로 접속된 session의 PGA, Shared SQL area 그리고 dynamic하게 할당되는 data structure로 구성된다.

1. Shared Pool
Shared pool은 ?/dbs/initSID.ora(parameter file)의 SHARED_POOL_SIZE라는 parameter로 그 크기를 지정하는데 default값은 3.5M를 갖게 된다.
일반적으로 shared pool이 얼마나 사용되는가 하는 문제는 application dependent하므로 각

application을 조사하는 게 필요하다.
시스템에서 필요로 하는 크기를 검사하기 위해 아주 큰 크기로 parameter file에 지정하여 dynamic하게 할당되는 SGA가 충분히 큰 값을 갖게 한 후, 검사가 끝난 다음 아래에서 계산된 size로
변경 해 주도록 한다.

2. 계산 공식
Session 당 최대 메모리 사용량(Max Session Memory)
* 동시 접속하는 User의 수
+ Shared SQL 영역으로 사용되는 메모리 양
+ Shared PLSQL을 위해 사용하는 메모리 영역
+ 최소 30%의 여유 공간
= Minimum Shared Pool

3. 계산 예제
(1) 적당한 user session에 대한 session id를 찾는다.

SVRMGR> select sid from v$process p, v$session s
where p.addr=s.paddr and s.username=''SCOTT'';
SID
----------
29
1 rows selected.

(2) 이 session id에 대한 maximum session memory를 찾는다.

SVRMGR> select value from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = ''session uga memory max''
and sid=29;
VALUE
-----------
273877
1 rows selected.

(3) Total shared SQL area를 구한다.

SVRMGR> select sum(sharable_mem) from v$sqlarea;
SUM(SHARAB
------------------
8936625
1 row selected.

(4) PLSQL sharable memory area를 구한다.

SVRMGR> select sum(sharable_mem) from v$db_object_cache;
SUM(SHARAB
------------------
4823537
1 row selected.

(5) Shared pool size를 계산한다.

274K shared memory * 400 users
+ 9M Shared SQL Area
+ 5M PLSQL Sharable Memory
+ 60M Free Space (30%)
= 184M Shared Pool

이 예제에서는 Shared pool의 size는 184M가 적당하다고 할 수 있다.

4. Shared Memory 부족 (ORA-4031)에 대한 대처 Ora-4031이 발생하는 원인은 2가지이다. 첫째 절대적으로 shared pool size가 작아서 나는 경우와, 둘째로 memory는 있으나 적재하려 하는

PL/SQL package가 너무커서 연속된 shared pool영역을 점유하지 못하는 경우가 있다.
만일 첫번의 경우라면 적당한 계산 과정으로 계산하여 parameter file에서 SHARED_POOL_SIZE를 늘려주고 ,두 번째 경우라면 다음과 같은 방법으로 에러를 피해 갈 수 있다.
- "Sys.dbms_shared_pool.keep" procedure사용.

[참고] 위 package를 사용하려면 ?/rdbms/admin/dbmspool.sql,
prvtpool.plb를 수행시켜 package를 create시킨 후 사용한다.

1. sga_target

이 parameter는 사용자가 원하는 크기만큼 sga를 지정하면 oracle kernel이 알아서(process수 etc)
shared_pool, db_cache_size등을 동적으로 할당하게 됩니다.
그냥 단순히 원하는 size만 지정하면 됩니다.
사용 예)
init.ora 에
sga_target = 300m
변경은 alter system set sga_target = 200m; 이런식으로 하면 됩니다만..
sga_target < sga_max_size입니다. sga_max_size는 oracle 10g에서도 변경 불가 입니다.

2. OLTP 속성에 관련된 parameter(shared_pool_size , db_cache_size)
sga_target , db_cache_size, shared_pool_size는 동적으로 크기를 변경할 수 있습니다.
단,db_cache_size + shared_pool_size <= sga_target 여야 합니다.
또한, sga_target < sga_max_size(변경불가) 입니다.
만약 sga_target = 300m로 지정하고

alter system set shared_pool_size = 100m;
alter system set db_cahce_size = 150m;

이렇게 하면 원하는 크기만큼 설정이 됩니다.

물론, shared_pool_size와 db_cache_size를 지정하지 않으면 오라클이 알아서(?)
내부적으로 shared_pool_size와 db_cache_size를 할당합니다.

가) sga_target만 설정한 경우
. sqlplus 에서 "show parameter sga_target" --- 지정된 값
. sqlplus 에서 "show parameter shared_pool_size" -- 0
. sqlplus 에서 "show parameter db_cache_size" -- 0

. alter system set sga_target = 200m; 으로 변경하면
. sqlplus 에서 "show parameter sga_target" --- 200m
. sqlplus 에서 "show parameter shared_pool_size" -- 0
. sqlplus 에서 "show parameter db_cache_size" -- 0

나) sga_targe=300m, shared_pool_size, db_cache_size를 지정한 경우
. sqlplus 에서 "show parameter sga_target" --- 지정된 값
alter system set shared_pool_size = 100m;
alter system set db_cahce_size = 150m;

. sqlplus 에서 "show parameter shared_pool_size" -- 100m
. sqlplus 에서 "show parameter db_cache_size" -- 150m

3. Batch 속성에 관련된 parameter(pga_aggregate_target)
Batch 속성을 갖는 DB는 주로 sorting 작업을 많이 하기 때문에 pga 영역의 확보가 절대적으로 필요합니다.
따라서 alter system set pga_aggregate_target = 1000m; 등등으로 설정하면 됩니다.

4. 그러면 얼만큼 메모리를 할당하면 될까요?
가) 일반적으로 OLTP인경우
SGA (65%) + PGA (15%) + O/S Overhead (20%)
예를 들어 전체 메모리가 10G 인경우
SGA ( 6G) , PGA( 2G ), O/S (2G)

나) 일반적으로 BATCH인경우
SGA (30%) + PGA (50%) + O/S Overhead (20%)
예를 들어 전체 메모리가 10G 인경우
SGA ( 3G) , PGA( 5G ), O/S (2G)

5. 그러면 4.에서 권고한 만큼 메모리를 할당/재할당 해야 하나요?
- 그건 그때 그때 달라요.. 경험이 필요하겠죠
원칙은 오라클에 가급적 많음 메모리를 할당하되, page나 swap이 빈번하게 발생하면 않되겠쬬!!!!!

OWI를 활용한 shared pool 진단 및 튜닝

아직도 Shared pool 설정에 어려움을 느끼십니까?

작성자 : 한민호(blubeard@nate.com)

필자가 Oracle Internal에 관심을 가지게 된 것은 얼마 되지 않지만 공부를 하면서 이렇게 자료를 만들어 정리를 해보는 것도 좋은 경험이 되고 여러 사람들과 지식을 공유하고 싶었기 때문에 OKM에 기고하게 되었다.

SGA에서 shared pool만큼 사이즈를 설정하기 어려운 Parameter가 없다. 그리고 이 shared pool의 크기는 너무 커도, 너무 작아도 문제가 된다. 즉, 얼마나 적절히 설정하느냐에 따라 DB의 성능을 좌우하게 된다. Parameter만 잘 설정해도 성능이 좋아진다니 이렇게 편하고 좋은 튜닝 방법이 어디 있을까 하는 생각도 든다. 하지만 shared pool의 크기를 아무 지식과 진단 없이 적절히 설정하기란 여간 까다로운 일이 아닐 수 없다. 특히 Row cache와 Library cache는 크기를 각각 설정하는 것이 불가능하기 때문에 초보자에겐 이런 것들이 어려움으로 다가올 수 있다. Shared pool을 자세히 알아 봄으로써 그러한 걱정들을 이번 기회에 덜 수 있다면 훌륭한 DBA가 되는데 도움이 되리라 생각된다. 이에 Shared Pool을 설정함에 있어 진단의 지표로 OWI를 사용할 것이다.

- Wait Event의 중요성

 Programming을 해본 사람이라면 동기화 문제에 대해 매우 잘 알고 있을 것이다. Oracle역시 수많은 Transaction에 의해 작업이 되기 때문에 이 때 발생하는 동기화 문제들을 해결하기 위해 수 많은 Latch, Lock, Pin을 사용하고 있다. 이 동기화란 것은 Serial한 작업이기 때문에 성능에 막대한 영향을 주게 된다. Wait Event는 이러한 동기화 작업에 있어서 Critical Section에 들어가지고 못하고 대기하는 작업들의 대기시간 동안 발생하는 이벤트이다. 이 때문에 Wait Event 발생을 줄이는 것은 중요한 일이고 이를 잘 분석하여 Tuning하는 것은 매우 효과적인 방법인 것이다. 그럼 이제 Shared Pool을 Wait Event의 관점에서 진단하고 분석해보기로 하겠다.

- Shared pool의 목적

Shared pool에 대해 간략히 설명을 해보자면 shared pool의 목적은 실행된 Cursor를 공유하여 CPU나 memory를 효율적으로 사용하는 데 있다. Cursor란 SQL의 경우 실행할 때 필요한 실행계획 같은 실행 정보를 담고 있는 SGA상에 할당된 Heap Memory를 말한다. 물론 공유 할 수 있는 것들은 다양하다. 공유할 수 있는 정보들을 나열하자면 SQL구문, 실행계획, PL/SQL소스와 그것들의 실행정보, table, view 같은 object 등이 있다. 이것들을 공유한다면 동일한 PL/SQL이나 SQL을 실행함에 있어 매번 실행계획을 만들며 hard parsing이 일어나는 부하를 예방할 수 있다.

- Shared pool의 구성요소

shared pool을 구성하고 있는 구성요소에 대해 알아보겠다. 우선 shared pool의 구성요소는 4가지로 나뉜다. Process목록, Session목록, Enqueue목록, Transaction목록 등이 할당된 Permanent Area와 SQL문을 수행하는데 필요한 모든 객체 정보를 관리하는 Library cache, dictionary 정보를 관리하는 Row Cache, 그리고 마지막으로 동적 메모리 할당을 위한 공간인 Reserved Area로 나눌 수 있다.

- Heap Manager를 통한 메모리 관리

메모리에 대한 할당 및 해제 작업은 Heap Manager를 통해 동적으로 관리가 된다. 이 Heap Manager에 대해 간략히 알아보면 Top-level의 Heap과 그 하위에 여러 개의 Sub-Heap을 포함하는 구조를 이루고 있다. 이 Heap은 또한 linked list구조의 Extent들로 구성이 되어 있으며 Extent는 여러 개의 chunk로 구성되어있다. 실제적으로 chunk의 사용 현황에 대해 알고 싶다면 X$KSMSP라는 View를 통해 관찰 할 수 있을 것이다.

- Chunk의 관리

Chunk는 4가지 상태로 관리가 된다. 그 4가지 상태는 Free, Recreatable, Freeable, Permanent다. 이러한 chunk들의 상태에 따라 linked list가 구성되는 것이다. 상태의 이름만으로도 그것이 어떤 상태인지 알 수 있을 것이다. 더 정확히 설명 하자면 Free는 즉시 사용 가능한 상태를 말한다. 이 Free 상태의 chunk들로 묶여 있는 linked list가 free list인 것이다. 구체적으로 설명하면 이것은 255개의 bucket이 있고 각 bucket당 free chunk들이 linked list구조로 연결되어있다. 이때 bucket은 각각의 정해진 기준의 크기 이하의 chunk들로만 구성되어 있다. 이러한 이유로 bucket이 아래로 갈수록 chunk들의 크기가 크다. Recreatable은 재생성 가능한 상태이다. 뒤에서 다시 설명하겠지만 이것은 unpinned(현재 사용되고 있지 않은)일 때 재사용이 가능하다. 쉽게 말하자면 이것은 사용이 되었었지만 다시 사용될 확률이 낮아져서 재사용이 가능한 상태가 된 것이며, 현재 사용 중이 아니라면 chunk를 재사용할 수 있도록 이러한 상태의 chunk를 묶어 LRU list로 관리한다. (관련 뷰 : X$KGHLU) 그리고 Freeable은 session이나 call 동안에만 필요한 객체를 저장하고 있는 상태이며 이는 session등이 금방 끊길 수도 있기 때문에 chunk가 필요할 때 할당의 대상이 되지는 못한다. Permanent는 말 그대로 영구적인 객체를 저장하고 있는 상태이며 이것 역시 사용할 수 없는 chunk다.

실제 Heap Dump를 이용하면 R이 앞에 붙어서 상태가 정의 되어 있는 것을 볼 수 있는데 이것은 SHARED_POOL_RESERVED_SIZE란 Parameter를 통해 발생한 chunk들이다. 이 chunk도 적절히 사용하면 매우 중요한 튜닝 요소가 될 수 있다. 이것에 대해 oracle 매뉴얼에서는 PL/SQL block이 많이 사용되는 경우를 예로 들고 있다. 즉, 이것은 large chunk를 위해 할당된 공간인 것이다. Parameter를 정해 주지 않는다면 설정된 shared_pool_size의 5%가 default value로 분류된다. Steve Adams의 Oracle Internal이나 매뉴얼에서도 5000byte 이상의 큰 object를 위한 공간이라고 설명한다. 이는 large chunk가 요구되지 않는다면 굳이 설정할 필요가 없다는 말도 되는 것이다. 이러한 튜닝 요소에 초점을 맞추어 설정하면 되는 parameter인 것이다. 이것들 역시 linked list로 관리되며 명칭은 Reserved Free list라고 부른다.

(아래 그림 1을 참조한다면 이해하는 데에 도움이 될 것이다.)

[그림 1] Shared Pool Heap 구조(출처 : Advanced OWI in Oracle 10g / ㈜엑셈)

정리를 해보자면 chunk는 사용할 수 있는 것과 없는 것으로 나눌 수 있다. 그 중에 실제 chunk 할당 과정에서 latch경합을 벌여 Wait Event가 발생하기 때문에 free list와 LRU list에서 관리되고 있는 chunk들에 주목을 할 필요가 있을 것이다. 왜냐하면 운영 시 peak time때의 할당된 chunk의 총 크기가 곧 shared pool의 size를 적절히 결정하는 데에 중요한 역할을 하기 때문이다. 이것에 대한 설명은 Wait Event의 발생과 연관 지어 이야기 해 보겠다.

- Shared Pool의 관리

Chunk를 할당하는 과정에서 반드시 필요한 것이 shared pool latch의 획득이다. 하지만 이러한 shared pool latch는 아쉽게도 shared pool당 단 1개 밖에 존재하지 않는다. 이것은 즉, chunk를 동시에 할당 받아야 할 상황이라면 이것을 획득하는 과정에서 경합을 벌이게 된다는 의미이다. 무엇 때문에 이렇게 shared pool latch 수를 적게 만들어 놓았는지 정확히 알 수는 없지만 여러 개를 만들어 놓았다면 역시나 동기화 문제를 관리하는데 있어 어려움이 있기 때문이 아닐까 싶다. 다행히도 Oracle 9i부터는 Hidden Parameter인 _KGHDSIDX_COUNT를 이용하여 하나의 shared Pool을 7개까지의 Sub-Pool로 나누어 관리하는 것이 가능해졌다. 그리고 더 반가운 소식은 각각의 Sub-pool당 독자적인 free list, LRU list, shared pool latch를 가짐으로 인해 부족했던 자원에 대한 경합을 그나마 감소시킬 수 있게 되었다. 그러나 경합은 감소했을지 몰라도 CPU의 개수나 SHARED_POOL_SIZE가 충분히 크지 않다면 ORA-4031에러의 발생위험이 1개의 shared pool로 관리됐을 때보다 더 높다는 것이다. 그래서 권장하는 방법이 CPU 4개에 SHARED_POOL_SIZE가 250m이상일 때 Sub-pool을 사용하는 것이다. 한 때는 하나의 Sub-pool에 할당 가능한 chunk공간이 없으면 다른 Sub-pool에 사용 가능한 free chunk가 있음에도 찾지 못했던 버그도 있었다. shared pool latch에 대해 좀 더 알아보자면 실제적으로 shared pool latch를 소유하는 시간이 shared pool latch를 대기하게 하는 중요한 이유이다. 때문에 latch를 획득한 후에 일어나는 작업들을 알면 경합의 포인트를 알 수 있을 것이다.

 - Chunk의 할당과정

Shared pool latch를 획득하게 되면 우선 free chunk를 찾기 위해 free list를 탐색한다. 그리고 적절한 free chunk가 있다면 할당을 받지만 없다면 LRU list를 탐색하게 된다. 이것마저도 찾지 못한다면 Reserved Free List를 탐색하고 이것 역시 실패하면 Spare Free Memory를 탐색하게 된다. 이 모든 과정이 실패가 되었을 때 ORA-4031에러가 발생하게 되는 것이다. 이 과정에서 할당을 받게 된다면 딱 필요한 크기만 할당을 받고(split) 나머지는 다시 free list에 등록이 되기 때문에 free list가 할당이 된다고 해서 반드시 짧아지는 것은 아니다. 그리고 적절한 chunk를 찾기 위해 위에서와 같이 여러 과정은 거치지만 이 과정은 생각보다 매우 빠른 시간 안에 이루어진다. 하지만 이것들이 다수의 작업이 된다면 경합에 대한 wait time은 피부로 느껴질 것이다. 여기서 이제껏 언급이 없었던 Spare Free Memory에 대해 궁금해하는 분이 많을 꺼 같아 간단하게 설명하자면(이 내용은 Steve Adams의 Oracle Internal에 내용이 있다) instance가 start up 되었을 때 shared pool size에 정해진 크기의 절반만이 shared pool에 할당된다. 이것은 성능을 극대화 하는데 에도 연관이 있으리란 생각이 든다. Chunk의 수가 줄면 그 만큼 free list가 짧아지기 때문에 그에 대한 탐색시간도 짧아지고 shared pool latch의 소유 시간 역시 짧아지기 때문에 메모리를 숨겨놓지 않았을까 생각된다. 위의 과정들을 미루어 짐작해볼 때 shared pool latch의 소유시간은 free list의 길이와 얼마나 빨리 적절한 chunk를 찾느냐에 따라서 결정된 다는 것을 알 수 있을 것이다. 그럼 free list의 길이가 길어지는 것은 어떠한 경우 일까? 바로 그것은 chunk split가 다량으로 발생하여 단편화(fragmentation) 되었을 때이다. 이러한 경우 free list의 길이가 길어지게 되는 것이다. 단편화는 hard parsing에서 일어나는 것인데 hard parsing에 대해 모르는 독자들을 위하여 간단히 설명하면 처음 실행하는 SQL문이 있다면 이것에 대한 실행정보를 저장하고 있는 Heap Memory 할당이 필요한데 이 Heap이 바로 chunk인 것이다. 이러한 실행계획을 만들고 chunk에 할당하는 과정은 매우 부하가 있기 때문에 hard parsing이라고 이름 붙여진 것이다. 이런 과도한 단편화로 인해 shared pool latch의 경합만 가중 시키는 것이 아니다. 큰 chunk 할당이 요구되는 hard parsing이 이루어 질 때 적절한 free chunk를 찾지 못하여 ORA-4031에러를 유발하게 된다.

- Shared Pool Size 설정

지금까지 설명했던 것들을 가지고 shared pool size에 대해 결론을 내 보면, 첫째로, memory가 무조건 크다는 생각으로 shared pool을 늘리면 안 된다고 볼 수 있다. 이것은 오히려 free list의 길이만 늘어나게 되기 때문이다. 그리고 V$SGASTAT를 통해 확인한 shared pool의 free memory가 작다고 해서 SHARED_POOL_SIZE를 늘려서는 안 된다. Free memory는 단지 free chunk의 합이기 때문이다. 이는 즉 LRU list, reserved list, spare memory도 있기 때문에 크게 문제가 되는 것은 아니라는 말이다. 지금까지 설명한 것을 고려해 본다면 적절한 크기를 정하는 데는 매우 도움이 될 것이다. 만약 초보 DBA라면 Oracle에서 제공하는 advice를 이용하는 것도 괜찮은 방법일 듯싶다. OEM구성을 하여 Enterprise Manager를 보면 memory tab에서 shared pool 부분의 advice버튼만 클릭하면 적절한 shared pool 크기에 대한 지침을 제공하고 있다.(그림 2를 참조하시오) 이것이 아주 정확한 척도가 되지는 못할지라도 초보 DBA에게는 매우 매력 있는 기능임에는 틀림이 없다. 이 지침은 되도록이면 peak time 이후에 이용하는 것을 권장한다.

\

[그림 2] Enterprise Manager의 Shared pool advice

Shared Pool Size를 적절히 줄이게 되면 free list 탐색시간의 감소로 인해 hard parsing에 의한shared pool latch의 경합을 줄이는 효과를 볼 수 있지만 ORA-4031에러의 위험이나 상주할 수 있는 공유 객체의 수가 줄어들어 LRU list를 자주 이용하기 때문에 오히려 부가적인 hard parsing을 발생시킬 수 있음에 유의해야 한다. 이때 오르내리는 객체가 프로시저나 패키지라면 그 부하는 상당할 것이다. 이에 대비하여 DBMS_SHARED_POOL.KEEP을 이용하여 메모리에 고정시키는 방법도 매우 유용하다. Shared pool을 flush해도 내려가지 않기 때문이다. Shared pool latch가 발생하는 것을 가장 줄일 수 있는 방법은 bind변수의 사용이나 CURSOR_SHARING Parameter를 설정하는 것이다. CURSOR_SHARING parameter는 SQL문장을 자동으로 bind변수치환을 해주는 변수이다. 위에서 언급했던 Flush Shared pool에 대하여 잠시 설명을 하고 가면 alter system flush shared_pool;이란 명령을 통해 shared pool을 flush 시킬 수 있다. 이 작업은 단편화된 free chunk에 대해 coalesce 작업이 이루어 지기 때문에 유용하지만 NOCACHE 옵션이 없는 sequence가 있다면 예상치 못한 gap이 생길 수도 있기 때문에 유의해야 한다.

 - Library Cache Latch와 Shared Pool Latch의 관계

Shared pool을 튜닝 하는데 있어 반드시 shared pool latch 획득만이 문제가 되는 것은 아니다.  바로 shared pool latch 획득 이전에 library cache latch의 획득이 먼저 있기 때문이다. 이것을 비롯한 parsing에 대해 좀더 이해를 돕기 위해 아래의 그림 3을 참조하기 바란다.

[그림 3]  parsing시 shared pool latch와 library cache latch

 그림 3를 보면 알 수 있듯이 hard parsing이 soft parsing 보단 부하가 큰 작업 임을 알 수 있다. 또한 library cache latch와 shared pool latch 획득 시점을 미루어 보아 동시에 많은 세션이 library cache latch를 획득하려고 하게 되면 이것에 대한 병목 현상으로 shared pool latch에 대한 경합은 상대적으로 줄어들 수 있을 것이란 예상도 가능하다. 그렇다면 이렇게 shared pool latch에 영향을 주는 library cache latch에 대해서도 자세히 알아볼 것이다.

- Library Cache의 구조와 관리

Library cache에 할당 받는 Heap memory는 shared pool latch를 걸고 할당 받은 free chunk이다. 이때 Library Cache Manager(KGL)에 의해 관리되는데 이는 내부적으로 Heap Manager(KGH)를 이용하는 것이다. 이때 할당된 free chunk는 LCO(Library Cache Object)와 handle을 저장하는데 사용된다. Library Cache Memory는 크게 hash function, bucket, handle list, LCO로 구성되어 있다. 하나씩 설명을 해보면 hash function은 bucket을 결정하기 위한 연산을 수행하는 함수로 보면 된다. 객체에 따라 SQL의 경우 SQL TEXT를 그대로 numeric 값으로 변환하여 bucket을 결정하고 SQL외의 객체들은 schema, object name, DB link를 numeric 값으로 변환하여 bucket을 결정한다. Bucket의 성장은 LCO의 크기가 매우 많아져서 성장이 불가피할 때 성장하게 되는데 이때 대략 2배 크기의 소수로 확장하게 된다. 그리고 bucket의 초기 값은 _KGL_BUCKET_COUNT로 설정이 가능하다.

아래 그림 4, 5는 필자가 그린 handle과 LCO의 구조, 그리고 그것에 대한 간략한 설명이다.

[그림 4] Handle의 구조

[그림 5] LCO의 구조

[그림 6] Library cache 구조 (출처 : Advanced OWI in Oracle 10g / ㈜엑셈)

위의 그림 4, 5를 숙지하였다면 handle이 무엇이고 LCO가 무엇인지, 또 이것이 저장하는 정보에 대하여 알 수 있었을 것이라고 예상 된다. 이제 그림 6을 보면 대략적인 구조가 머리 속에 들어 올 것이다. 여기서 특징적인 것은 SQL의 경우 child table을 갖는 다는 점이고 그 child table이 저장하고 있는 실제적 자식 handle과 LCO는 익명 리스트로 관리되고 있다는 점이다. 물론 PL/SQL의 경우는 조금 다르다. 이것의 구조는 그림 7,8을 보면 좀더 명확히 알 수 있다.

[그림 7] SQL의 LCO구조

[그림 8] PL/SQL의 LCO구조

PL/SQL과 일반 SQL은 Heap Memory를 사용하는데 있어서도 차이점이 있다는 것을 금방 알 수 있을 것이다.

- Oracle의 놀라운 메커니즘 Invalidation 자동화

Oracle의 장점 중 하나인 invalidation 자동화에 대해 잠시 설명을 해 보겠다. 이에 대한 내용이 자세히 언급된 곳은 많지 않았을 것이다. 우선 그림 7을 보면 SQL LCO의 구조에 대해 잘 보여주고 있다. Parent LCO와 두 개의 child LCO가 보일 것이다. 보통 이런 방식으로 저장되는 경우는 각각 다른 schema에 같은 이름의 table을 가지고 있을 때 동일한 SQL을 query할 수 있을 것이다. 쉽게 예를 들면 scott1과 scott2란 두 user가 있는데 이들이 각각 emp라는 table을 가지고 있고 select * from emp;라는 동일한 query를 두 user가 전송했을 때 이러한 구조로 LCO가 생성되게 되는 것이다.

이때 child LCO의 Dependency Table에는 scott1의 경우 scott1.emp table의 LCO를 참조하고 cott2의 경우 scott2.emp table의 LCO를 참조하게 되는 것이다.

이 참조 과정에서 handle의 lock hold/wait list, pin hold/wait list를 이용하게 된다. 이때 중요한 것이 바로 lock이다. 참조하는 LCO의 lock을 shared mode로 잡아 사용하고 해제하는 것이 아니라 null mode로 유지하는 것이다. 이것이 바로 나중에 참조하는 table이 변경되었을 때 이 lock list를 없애 버림으로 인해 SQL Cursor(select * from emp;)를 따로 어떤 프로세스를 통해 invalidation한 상태로 만들지 않고 자동으로 invalidation하게 하는 것이다.

- Library cache에서 발생하는 Wait Event 소개

그럼 이제는 library cache에서 발생하는 주요 Wait Event들에 대해 알아보자. library cache에서 일어나는 자주 발생하는 Wait Event에는 3가지가 있다. 그것은 latch:library cache, library cache lock event, library cache pin event이다. 이것은 명칭에서도 알 수 있듯이 latch, lock, pin을 소유하기 위해 대기하는 event 이다. 이 동기화 자원들에 대하여 자세히 알아보자.

- Library Cache Latch

 우선 library cache latch에 대해 알아보면 이 latch는 library cache 영역을 탐색하고 관리하는 모든 작업을 보호하는 데에 그 목적이 있다. Latch의 수는 일반적으로 shared pool latch의 수 보다는 많다. 왜냐하면 CPU개수보다 큰 소수 중 가장 작은 소수로 설정되어 있기 때문이다. 이 때문에 library cache latch를 획득하려는 프로세스가 CPU개수 보다 적다면 library cache latch 자원은 손쉽게 획득하는 대신에 shared pool latch를 가지고 경합할 확률이 높을 것이고 library cache latch의 개수보다 많은 프로세스가 획득하려 한다면 library cache latch를 가지고 경합을 하느라 shared pool latch의 경합은 상대적으로 줄어들 수 있다. 그렇다면 이러한 library cache latch 경합을 가중시키는 작업엔 어떤 것이 있을까? 바로 hard parsing이나 soft parsing이 과다한 경우와 자식 LCO가 많아 anonymous list의 탐색시간이 증가하는 경우이다. 그리고 SGA영역이 page out되는 극히 드문 경우를 예로 들어 볼 수 있다. 이에 대한 해결 책으로 PL/SQL block 내에서 자주 실행되는 SQL에 대해서는 Static SQL을 사용하면 된다.(Dynamic SQL은 안됨) LCO를 pin하여 soft parsing 없이도 cursor를 계속 재사용 할 수 있는 효과를 볼 수 있다.

그리고 SESSION_CACHED_CURSORS Parameter를 이용하여 3회 이상 수행된 SQL에 대해서는 PGA영역에 cursor의 주소 값과 SQL text를 저장하여 cursor 탐색 시 성능향상을 기대할 수 있다.(library cache latch도 획득해야 하고 soft parsing도 발생하지만 library 탐색시간이 매우 짧기 때문에 성능향상이 된다.) 하지만 application에서 SQL 수행 시 마다 log on/off하는 경우 이 parameter는 세션이 끊어지면 소용이 없기 때문에 성능 향상을 기대하기 어렵다. 때문에 connection pool을 함께 이용하는 것이 현명한 방법이다. 그리고 마지막으로 SGA영역의 page out의 경우는 잘 발생하지 않지만 만약을 대비해서 LOCK_SGA값을 TRUE로 하여 고정시켜 놓는 것이 좋다.

- Library Cache Lock

 Library cache lock(관련 뷰 : X$KGLLK, 10g-DBA_DDL_LOCKS, DBA_KGLLOCK)에 대해 설명하면 이것은 handle에 대해 획득하는 lock이라 볼 수 있다. 이것의 목적은 동일 object의 접근 및 수정에 대해 다른 client들로부터 예방하는 것이다. Lock은 세가지 모드를 갖게 되는데 shared, exclusive, null mode가 있다. Shared로 획득하는 경우는 parsing과 실행단계이고 exclusive는 procedure 생성이나 변경의 경우, recompile시와 table 변경의 경우가 있다. 보통 참조하는 LCO에 대해 exclusive모드와 shared모드로 각각 획득하려는 경합으로 인해 waiting이 발생하게 된다. null mode는 보통 실행 후에 참조하는 객체에 대해 null mode로 lock을 소유하게 된다. 

- Library Cache Pin

 마지막으로 library cache pin(관련 뷰 : X$KGLPN, 10g-DBA_KGLLOCK)에 대해 설명하면 Heap data에 pin을 꽂아 변경되지 않도록 보장하는데 그 목적이 있다. 이것은 반드시 library cache lock을 획득한 후에 획득해야 한다. 이것은 shared와 exclusive 두 mode가 지원되며 이렇게 획득하는 경우를 살펴보면, shared mode로 획득하는 경우는 Heap Data를 읽을 때 pin을 걸어 object들의 변경을 예방하며 exclusive mode로 획득하는 경우는 Heap Data를 수정할 때이다. Heap data를 수정하는 경우는 procedure recompile이나 hard parsing 발생 시 execution plan을 세우는 과정에서 참조하는 LCO가 변경되면 안되기 때문에 pin을 걸어 보호한다.

 이때 발생하는 library cache lock이나 library cache pin은 V$SESSION_WAIT의 P1, P2, P3 column과 X$KGLOB View를 이용하여 object 정보를 구할 수 있다. P1=handle address, P2=lock address, P3=mode*100+namespace (lock mode : 1=null, 2=shared, 3=exclusive)이기 때문에 V$SESSION_WAIT을 조회하여 P1값을 구한 후 P1과 X$KGLOB의 kglhdadr column과 비교하여 kglnaobj column을 query해서 object의 이름을 구할 수 있다.

- 맺음말

위의 library caches에 관한 내용들을 종합적으로 정리해서 결론을 내려보면 Wait Event를 통해 그 Event가 왜 발생하였는가를 인지할 수 있다면 정확한 진단 역시 가능함을 알 수 있다. 그리고 덧붙여 내부적인 Event의 발생과정을 아는 것이 튜닝을 하게 될 때 넓은 시야를 가질 수 있도록 도와주고 좀더 효율적이고 정확한 튜닝을 할 수 있는 계기가 되리라 확신한다.

참고자료

OWI를 활용한 오라클 진단 & 튜닝 / ㈜엑셈 역

Advanced Oracle Wait Interface in 10g / 저자 조동욱 / ㈜엑셈

Oracle 8i Internal Services for Waits, Latches, Locks, and Memory / Steve Adams / O’Reilly

Manual

Oracle Database Concepts 10g R2

Oracle Database Reference 10g R2

=============================================================
DB shared pool flush시키는 명령어
alter system flush shared_pool;

(Oracle 10g) MMAN 백그라운드 프로세스를 통한 자동 공유 메모리 관리
========================================

PURPOSE

Explanation

자동 공유 메모리 관리 기능이 갖는 장점은 다음과 같은 몇 가지가 있다.
첫째, workload가 변함에 따라 자동으로 공유 메모리가 적용이 된다.
둘째, 메모리의 활용률을 극대화한다.
세째, out-of-memory라는 메모리 부족 발생으로 인한 에러를 예방할 수 있다.

즉, 오라클의 공유 메모리 영역 중 Shared pool size, Buffer cache size,
Large pool size, Java pool size를 매뉴얼하게 셋팅할 필요가 없다.
가용한 메모리의 사용을 보다 효과적으로 해주는 것 뿐만 아니라,
메모리 자원을 얻는 데 필요한 비용을 줄여줄 수 있다.
무엇보다 dynamic하고 flexible한 메모리 관리 구조를 통하여
오라클 데이타베이스 관리를 단순화시켜 준다.

2. MMAN 백그라운드 프로세스

공유 메모리의 자동 튜닝을 위하여 MMAN이라는 백그라운드 프로세스가
새로이 등장하였다.
MMAN이라는 백그라운드 프로세스가 5분 마다 주기적으로 수집한
작업 부하(Workload) 정보를 바탕으로 동적으로 구성이 된다.
메모리는 가장 필요한 곳으로 동적으로 할당이 된다.

SPFILE을 사용하면 MMAN이 변경한 파라미터들의 정보가 자동으로
SPFILE에 저장이 된다.
그러므로, 가능한 Oracle 9i 이상부터는 SPFILE 의 사용을 권장한다.
왜냐 하면 다음과 같은 세 가지 장점이 있기 때문이다.

첫째, 각 부분 크기의 권장안을 인스턴스 종료 후에도 보관할 수 있다.
둘째, 저장되어진 각 파라미터들의 사이즈는 데이타베이스 기동 시 할당이 된다.
세째, 각 파라미터들의 최적의 값을 찾는 데 드는 비용을 줄일 수 있다.

이렇게 MMAN 이라는 프로세스에 의해서 자동 공유 메모리 관리 기능이
구현이 되는 것이다.

3. SGA_TARGET 파라미터를 통한 자동 공유 메모리 관리

자동 공유 메모리 관리 기능을 사용하게 되면 오라클 SGA 관련 네 가지
파라미터들을 DBA가 일일이 셋팅할 필요가 없다.
오라클의 공유 메모리 크기는 SGA_TARGET 이라는 파라미터 하나로 다 조절이 된다.
SHARED_POOL_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE 라는
파라미터들을 구성하는 데 관여하지 않아도 된다는 뜻이다.
과거에는 이러한 파라미터들을 너무 낮게 잡게 되면 성능도 저하될 뿐만 아니라
out-of-memory error인 ORA-4031 ERROR를 자주 만나게 되었고 메모리 낭비도 있었다.

그러나, Oracle database 10g의 New Feature는 SGA_TARGET이라는 새로운
파라미터만 셋팅하여도 되게 설계되었다.
SGA_TARGET이라는 파라미터는 해당 인스턴스에 필요한 SGA의 최대 크기를 나타낸다.
이 파라미터는 SGA 내의 모든 메모리들을 포함한다.
즉, Automatic하게 사이즈가 결정되는 파라미터, 매뉴얼하게 결정되는 파라미터,
startup 시에 할당되는 internal metadata 할당을 다 포함한다.
10g 이전 버젼처럼 SGA의 TOTAL 크기를 정확히 컨트롤하는 것이 어렵지가 않다는 뜻이다.

SGA_TARGET 파라미터를 셋팅할 때에는 다음을 염두에 두어야 한다.

1) SGA 영역 중 자동 구성으로 조절되지 않는 영역이 있는데
Redo Log buffer 와 Fixed SGA 영역, BUFFER KEEP, RECYCLE과 관련된 파라미터,
STREAMS POOL 관련 파라미터들이다.

2) SGA_TARGET 파라미터가 0으로 셋팅되면 자동 공유 메모리 기능은 DISABLE된다.
SGA_TARGET 파라미터의 DEFAULT 값은 0이다.
SGA_TARGET 파라미터가 0이 아닌 어떤 값으로 셋팅이 되어 있어도
SHARED_POOL_SIZE 또는 DB_BLOCK_BUFFERS 와 같은 파라미터들은 여전히
셋팅할 수는 있다.

3) SGA_TARGET 파라미터가 설정되어 있지 않거나 0 으로 셋팅되어 있을 경우,
자동 튜닝 파라미터들은 10g 이전 버젼에서와 같이 관리된다.
단, 예외는 있다. SHARED_POOL_SIZE가 그 경우인데, 내부적인 STARTUP 시
소모되는 오버헤드는 포함이 된다. 이 부분이 SHARED_POOL_SIZE에 포함이 된다.
Oracle 10g 이전 버젼에서는 이 부분이 SHARED_POOL_SIZE에 포함되지 않았으나
10g부터는 포함되게 되어 SHARED_POOL_SIZE를 좀 더 크게 잡아야 한다.
구체적으로 10g에서는 SHARED_POOL_SIZE를 32m 정도 더 크게 잡아야 한다.
예를 들어, Oracle 9i에서 SHARED_POOL_SIZE를 256M를 사용했었다면,
Oracle 10g에서는 같은 효과를 얻으려면 288M 정도로 잡아야 한다.

4) SGA_TARGET 파라미터가 0이 아닌 값으로 셋팅이 되어 있는 경우
자동으로 튜닝되는 SGA 파라미터들은 모두 기본적으로 0으로 셋팅이 된다.
이러한 파라미터들은 자동 공유 메모리 관리 알고리즘에 의하여 자동으로
사이즈가 결정이 된다.
그러나, 만약 이러한 자동 튜닝 파라미터들이 0이 아닌 어떤 값으로
셋팅이 되어 있다면 명시한 값은 자동 튜닝 알고리즘에 의해 최저값을
나타낸다.
예를 들어, SGA_TARGET 파라미터가 8G로 셋팅되어 있고, SHARED_POOL_SIZE가
1G로 셋팅되어 있다면 SHARED_POOL_SIZE는 절대 1G 아래로 떨어지지
않음을 뜻한다.

V$SGA_Dynamic_components 뷰를 조회하면 자동 튜닝 component들의
실제 사이즈를 확인할 수 있다.

SELECT component, current_size/1024/1024
FROM v$sga_dynamic_components;

4. 수동으로 튜닝되는 SGA 파라미터 설정

SGA의 몇몇 구성 파라미터들은 자동으로 튜닝되지 않아서 매뉴얼하게 튜닝을 해야 한다.
다음은 수동으로 튜닝되는 SGA 파라미터들이다.

- KEEP 및 RECYCLE 버퍼 캐시
- 멀티 블록 사이즈 캐시(DB_nK_cache_size)
- 로그 버퍼
- 스트림즈 POOL

수동으로 튜닝되는 파라미터들은 반드시 사용자에 의해 명시되어져야 한다.
이런 파라미터들은 10g 이전 버젼에서와 같이 정밀하게 크기가 제어되어야 한다.
매뉴얼하게 튜닝되는 이러한 파라미터들은 SGA_TARGET에는 포함되지만,
자동으로 튜닝되지는 않는다.
예를 들어, SGA_TARGET 이 8G이면 MANUAL하게 수동으로 조절되는 파라미터들의
사이즈의 합을 1G로 잡으면 7G는 자동으로 오라클이 알아서 설정한다.

5. SGA_TARGET 파라미터의 설정 변경

SGA_TARGET 파라미터의 RESIZING이란 SGA_TARGET 파라미터의
설정 변경을 의미한다.
SGA_TARGET 초기화 파라미터의 특징은 다음과 같다.

첫째, 운영 중에 동적으로 변경이 가능하다.
둘째, SGA_MAX_SIZE 내에서 크기를 증가시킬 수 있다.
세째, 모든 구성 요소의 최저값의 합까지 크기를 감소시키는 것이 가능하다.
또한, SGA_TARGET 파라미터의 설정은 오직 자동으로 튜닝할 수 있는
파라미터들에만 영향을 준다.

예를 들어, SGA_MAX_SIZE가 10G이고, SGA_TARGET이 8G로 가정을 한다.
만약, DB_KEEP_CACHE_SIZE가 1G로 셋팅이 된다고 하면 SGA_TARGET 을
최대 9G까지 늘릴 수 있다.
추가적인 1G는 SGA_TARGET에 의해 조절되는 자동 튜닝 파라미터들에게 분배가 된다.
DB_KEEP_CACHE_SIZE 는 영향을 받지 않는다는 뜻이다.
이것은 SGA_TARGET 파라미터를 줄일 때에도 해당된다.

6. 자동 공유 메모리 관리 비활성화

이 기능을 비활성화하려면 SGA_TARGET 파라미터를 0으로 설정하면 된다.
이렇게 SGA_TARGET 파라미터를 0으로 설정하게 되면 자동 튜닝 파라미터들은
그 당시의 값으로 설정된다.
그리고, 전체 SGA 크기에는 영향을 미치지 않는다.

예) 변경 전 값
SGA_TARGET=8G
SHARED_POOL_SIZE=1G

변경 후 값
SGA_TARGET=0
SHARED_POOL_SIZE=2G
DB_CACHE_SIZE=4G
LARGE_POOL_SIZE=512M
JAVA_POOL_SIZE=512M

SGA_TARGET이 8G로 셋팅되어 있고, SHARED_POOL_SIZE 파라미터가 1G로
설정되어 있다가 SGA_TARGET을 0으로 변경하면 자동 튜닝 파라미터들은
그 당시의 값으로 셋팅되고 전체 SGA 크기는 이전 SGA 크기를 초과하지 않는다.
SHARED_POOL_SIZE가 2G로 셋팅이 된 것은 내부적으로 측정하여 결정된 수치이다.

7. 동적 SGA 파라미터의 수동 변경

자동 튜닝되는 파라미터를 수동으로 변경할 경우 다음과 같은 영향을 가진다.

1) 만약 파라미터의 새로이 반영되는 값이 현재의 값보다 클 경우에는 즉시 반영이 된다.
그러나, 새로이 반영되는 값이 현재의 값보다 작을 경우에는 현재의 값에는
즉시 변화가 없고 최저값으로 셋팅이 된다.

2) 자동으로 튜닝되는 파라미터들을 수동으로 변경하였을 경우에는
SGA의 자동 튜닝 부분에 영향을 준다.
RESIZE 수행 시에 사용되는 메모리는 자동 튜닝 파라미터들로부터
더해지거나 감해질 뿐 매뉴얼하게 조절되는 파라미터들에는 영향을 주지 않는다.

Example

만약 SGA_TARGET 파라미터가 0이 아닌 값으로 셋팅이 되어 있는 경우
자동 튜닝되는 파라미터들은 값을 명시하지 말라는 뜻이다.
V$PARAMETER 뷰를 조회하였을 때 자동으로 튜닝되는 이러한 SGA 파라미터들의
값은 모두 0이다.
이것이 정상이고, isdefault 컬럼 값은 TRUE 이다.

즉, 자동 공유 메모리 관리 기능을 구현할 때에는 이 파라미터들은
매뉴얼하게 설정을 하지 않으면 된다.

Reference Documents

ORA-04031: unable to allocate … shared memory

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

Symptom:
The Oracle error:

ORA-04031: unable to allocate nnn bytes of shared memory

Cause:
More shared memory is needed than was allocated. SGA private memory has been exhausted.

Fragmentation of shared pool memory is a common problem and ORA-04031 is commonly a result of such fragmentation. Application programmers usually get this error while attempting to load a big package or while executing a very large procedure and there is not sufficient contiguous free memory available in the shared pool. This may be due to fragmentation of the shared pool memory or insufficient memory in the shared pool.

Possible remedies:
Use the dbms_shared_pool package to pin large packages.

Attempt to reduce the use of shared memory.

Increase the initialisation parameter ‘SHARED_POOL_SIZE’.

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

Alternate symptom
An error of the form (Oracle 8.1.5):

ORA-04031: unable to allocate NNNNN bytes of shared memory ("large pool","unknown object","large pool hea","PX msg pool")

Cause:
This error indicates that Oracle is unable to allocate memory from the ‘large’ pool.

To determine the number of free bytes in the ‘large’ pool execute the following SQL:

SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME);

Possible Remedy:
To resolve the problem, consider increasing the value for ‘LARGE_POOL_SIZE’.

ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","TRIGGER$SYS","sga heap","state objects")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","unknown object","sga heap","state objects")

DB를 관리하면서 다음과 같은 에러를 직면할때가 있느데 이럴 경우에는 신속하게
대처할 수 있는 방법
  1. 오래된 세션을 끊어서 shared pool 에 잡고 있는 메모리를 해제 시켜준다.
  2. alter system flush shared_pool; 명령어를 통해 메모리 조각모임을 해준다.
장기적인 대책은
  단편화 시키는 SQL를 찾아내고 바인딩 변수화 시킨다.

ORA-04031 의 경우 shared pool 내에 메모리 조각화에 따라서 연속된 parsing 공간을 제공하지 못하기 때문에 발생하는 에러입니다.
다시 말해서 parsing 에러가 발생하는 것입니다.

이를 해소 해주기 위한 방법으로
개체가 큰 자주 사용되는 프로시져등을 메모리에 pined 해주면 되며,
shared pool 사이즈를 늘려 주는것이 가장 좋으며,
상황이 여의치 않는 경우 단편화된 shared pool 의 조각화을 다시 flush 해주는 방법
등이 있습니다.

SQL> alter system flush shared_pool;

shared pool 의 hit 율을 잘 분석해 보시고 오라클의 권장사항에 따라서 튜닝 가이드 라인을 정하는게 중요합니다.

너무 크게 줘서 free size 가 너무 많이 남는 경우 즉 hit 율은 좋은데, free size 가 너무 큰 경우는 메모리 낭비를 하게 되며, 각각의 o/s 에 따라서  paging 이나 swap 이 발생할 가능성이 있으니, 튜닝후 적적할 모니터를 통해서 사이즈를 잡아 가는 것이 좋습니다.

SQL> select name, bytes/1024/1024 "Size in MB" from v$sgastat where name='free memory';

NAME                       Size in MB
-------------------------- ----------
free memory                451.496498
free memory                  .5859375
free memory                    .03125

즉 오라클의 동적 뷰들의 대부분은 current 한 내용이 아닌 축적용으로 평균치를 나타 내기 때문에 의미가 없습니다.

======================================================================================================================

Ora-00604 또는 Ora-04031 에러 메세지 발생했을 때 아래 내용에 따라 오류를 조치하기 바랍니다.
 
1. 발생 원인
  Shared Pool의 사용 가능한 Memory 가 시간이 흐름에 따라 작은 조각으로 분할되어 진다는 것이다.
  그래서 큰 부분의 Memory 를 할당하려 한다면 Shared Memory가 부족하다는 ORA-4031 Error가 발생한다.
  즉, 전체적으로는 많은 양의 사용 가능한 Space가 있다 하더라도 충분한 양의 연속적인 공간이 없으면 이 Error가 발생한다.
 
2. 조치 방법
   DB를 관리하면서 다음과 같은 에러를 직면할때가 있느데 이럴 경우에는 신속하게 대처할 수 있는 방법
 
  1. 오래된 세션을 끊어서 shared pool 에 잡고 있는 메모리를 해제 시켜준다.
  2. alter system flush shared_pool; 명령어를 통해 메모리 조각모임을 해준다.
  3. Shared_Pool에 크기가 큰 프로그램을 Keep 을 시켜준다.
 
장기적인 대책은 단편화 시키는 SQL를 찾아내고 바인딩 변수화 시킨다.
   이를 해소 해주기 위한 방법으로 개체가 큰 자주 사용되는 프로시져등을 메모리에 pined 해주면 되며,
shared pool 사이즈를 늘려 주는것이 가장 좋으며, 상황이 여의치 않는 경우 단편화된 shared pool 의 조각화을
다시 flush 해주는 방법과 인스턴스를 내렸다 올리는 방법도 있습니다.

  SQL> alter system flush shared_pool;

======================================================================================================================

ORA-4031의 솔루션은 그 원인에 따라 다양한 방법이 있습니다.

먼저 ORA-4031가 발생하는 원인은, SHARED_POOL을 관리하는 과정에, 많은 조각화(Fragment)가 발생하고 Free Memory가 아주 적은 상태에서, 커다란 SQL(PL/SQL)이 Memory로 Load 될 때 공간이 부족해서 발생할 수 있습니다.

이 ora-4031 Error가 발생하게 되면, Shared pool의 관리가 원활히 되지 않아, 이후에 수행되는 모든 SQL이 error가 발생합니다. 그러므로 이는
반드시 예방되어야 합니다.

 이러한 Memory관리상의 문제를 해결하기 위해 조치 할 수 있는 것은 아래의 것들이 있습니다.

1.      v$sql 내의 Literal SQL이 많은지 확인한다.

많은 경우 Literal SQL을 사용하는 SQL을    찾아서 공유 할수 있도록 Bind Variable을 사용토록 하면 됩니다.

   => Literal SQL을 찾는 방법.

select substr(sql_text, 1, 40) "SQL",
       count(*) cnt,
       sum(executions) "TotExecs",
       sum(sharable_mem) mem,
       min(first_load_time) start_time,
       max(first_load_time) end_time,
       max(hash_value) hash        
from v$sqlarea
where executions < 5    --> 수행 횟수가 5번 이하인 것.
group by substr(sql_text, 1, 40)
having count(*) > 30    --> 비슷한 문장이 30개 이상.
order by 2 desc;

2. v$sql 내의 sharable Memory가 큰것들을 확인 한다.

   1M byte이상의 SQL이 있다면 확인 후 SQL의 복잡도를 줄인다(recursive call을 많이 한다든지..). 대부분의 경우 크기가 큰 것들은 일반 SQL이 아니라 PL/SQL이므로 이러한 것들은 Memory에서 내려오지 않도록 Pin을 시키는 방법도 있습니다. (그렇다고 memory에서 완전히 안내려 오는 것은 아닙니다.)

   => PL/SQL을 Memory에 Pin시키는 방법.

   execute dbms_shared_pool.keep('SCOTT.HELLO_WORLD'); 

3. SHARED_POOL_SIZE와 SHARED_POOL_RESERVED_SIZE의 크기를 늘린다.

   항상 Shared pool의 Free가 여유가 있도록 shared_pool_size를 크기를 좀 늘리시고

   특히 Shared_pool_reserved_size의 크기를 100M정도 되도록 지정하세요. 경험적으로 shared_pool_reserved_size가 100M정도 지정하면 ora-4031가 많이 발생하지는 않더군요.

   Free공간 확인 .

   SELECT free_space, avg_free_size, used_space,
          avg_used_size, request_failures, last_failure_size
   FROM    v$shared_pool_reserved;

4. 이것이 진짜 마약처럼 잘 듯는 방법인데, 9i부터는 Shared_pool의 관리를 좀더 효율적으로 하고 System의    CPU를 효과적으로 사용하기 위해 하나의 heap memory를 사용하던 것을 subheap으로 나누어 관리를 하고 있지요. 이렇게 sub-heap으로 나누어 관리하다 보니 작은 공간이 sub heap에 동시에 있더라도 이를 잘 활용하지 못해서 발생하는 경우가 있습니다. 이러한 이유로 ORA-4031 Error의 원인이 되는 경우가 종종 있습니다.

   현재 시스템이 Multi CPU인 경우에는 아마도 1보다 큰 값으로 되어 있을 겁니다.

   그래서 아래의 Query로 조회해 본 후 그 값이 1보다 큰 값이라면 init.ora에서

   _kghdsidx_count=1로 지정한 후 restart해서 사용해 보세요. 어지간해서 ORA-4031가발생 하지 않을 겁니다.

          select x.ksppinm, y.ksppstvl
            from x$ksppi x , x$ksppcv y
             where x.indx = y.indx
             and x.ksppinm like '_kghdsidx_count%' escape ''
             order by x.ksppinm;
 

==========================================
 결과 수치 값 예
==========================================
  select x.ksppinm, y.ksppstvl
                from x$ksppi x , x$ksppcv y
                where x.indx = y.indx
               and x.ksppinm like '_kghdsidx_count%'
               order by x.ksppinm

KSPPINM   KSPPSTVL   _kghdsidx_count
--------------------------------------------------------------------------------
4

shared pool flush 시키기

SQL> alter system flush shared_pool;

출처 : OTN - Technical Bulletins

No. 10095

ORA-4031 조치 방법 과 DBMS_SHARED_POOL STORED PROCEDURE 사용법
==============================================================

Purpose
-------

  다음과 같은 작업 수행 시 Oracle 이 Shared pool 에서 연속적인
  메모리 부분을 찾지 못해 ORA-4031 에러를 발생시키는 것을 볼 수 있다.

   .  PL/SQL Routine
   .  Procedure 수행 시  
   .  Compile 시
   .  Forms Generate 또는 Running 시
   .  Object 생성하기 위해 Installer 사용 시

  본 자료에서는 이러한 에러에 대한 대처 방안을 설명 하고자 한다.

Problem Description
-------------------

  Error 발생의 주된 원인은 Shared Pool의 사용 가능한 Memory 가 시간이
 흐름에 따라 작은 조각으로 분할되어 진다는 것이다. 그래서 큰 부분의
 Memory 를 할당하려 한다면 Shared Memory가 부족하다는 ORA-4031 Error가
 발생한다. 즉, 전체적으로는 많은 양의 사용 가능한 Space가 있다 하더라도
 충분한 양의 연속적인 공간이 없으면 이 Error가 발생한다.

1. Shared Pool과 관련된 인스턴스 파라미터
  다음 3가지 파라미터는 본 자료를 이해 하는데 매우 중요하다.

 * SHARED_POOL_SIZE - Shared Pool 의 크기를 지정 한다. 정수를 사용하며
    "K" 나 "M" 을 덧붙일 수 있다. 
           
 * SHARED_POOL_RESERVED_SIZE - 공유 풀 메모리에 대한 대량의 연속 공간
    요청에 대비해서 예약하는 영역의 크기를 지정한다. 이 영역을 사용하기
    위해서는 SHARED_POOL_RESERVED_MIN_ALLOC 보다 큰 영역 할당 요청이어야
    한다. 일반적으로 SHARED_POOL_SIZE 의 10% 정도를 지정한다.
           
 * SHARED_POOL_RESERVED_MIN_ALLOC - 예약 메모리 영역의 할당을 통제한다.
  이 값보다 큰 메모리 값이 할당 요청되었을 때 공유 풀의 free list 에
  적합한 메모리 공간이 없으면 예약된 메모리 공간의 리스트에서 메모리를
  할당해 준다. 이 값은 8i부터는 내부적으로만 사용된다.

Workaround
-----------
Re-start the instance 

Solution Description:
---------------------
이 Error 해결방안을 살펴 보면 다음과 같다.

 1. 혹시 알려진 제품 문제에 해당 되지 않는지 확인 한다.
  
 * BUG 1397603: ORA-4031 / SGA memory leak of PERMANENT memory occurs
 for buffer handles. (Workaround: _db_handles_cached=0, Fixed: 8172,
 901 )
 * BUG 1640583: ORA-4031 due to leak / cache buffer chain contention
from AND-EQUAL access. (Fixed: 8171,901 )
 * BUG 1318267: INSERT AS SELECT statements may not be shared when they
 should be if TIMED_STATISTICS. It can lead to ORA-4031. (Workaround:
_SQLEXEC_PROGRESSION_COST=0, Fixed: 8171, 8200)
 * BUG 1193003: Cursors may not be shared in 8.1 when they should be
   (Fixed: 8162, 8170, 901)

 2. Object를 Shared Pool에 맞추어 Fragmentation을 줄인다.
    (Dbms_Shared_Pool Procedure 이용) 
    
     다음은 크기가 크고 빈번히 access되는 package들임.

 standard packages
 dbms_standard
 diutil
 diana
 dbms_sys_sql
 dbms_sql
 dbms_utility
 dbms_describe
 pidl
 dbms_output
 dbms_job

 3. Shared Pool 을 효율적으로 사용하도록 Application Program을 조절한다.

 4. 메모리 할당을 조정한다.

 우선 다음 쿼리로 library cache 문제인지 shared pool reserved space  문제인지 진단한다.

          SELECT free_space, avg_free_size, used_space,
          avg_used_size, request_failures, last_failure_size
          FROM    v$shared_pool_reserved;

  만일    REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is >
          SHARED_POOL_RESERVED_MIN_ALLOC
  이면 ORA-4031 은 Shared Pool 의 연속 공간 부족의 결과이다. 

  해결책: SHARED_POOL_RESERVED_MIN_ALLOC 값을 증가 시켜서 shared pool
          reserved space 에 올라가는 오브젝트의 수를 줄인다. 그리고
          SHARED_POOL_RESERVED_SIZE 와 SHARED_POOL_SIZE 를 충분히 확보
          해 준다.

  만일    REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is <
          SHARED_POOL_RESERVED_MIN_ALLOC
  이거나
          REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is <
          SHARED_POOL_RESERVED_MIN_ALLOC
  이면 ORA-4031 은 library cache 내의 연속된 공간 부족의 결과 이다.

  해결책: SHARED_POOL_RESERVED_MIN_ALLOC 을 줄여서 shared pool reserved
          space 를 보다 쉽게 사용할 수 있도록 해준다. 그리고 가능하면
          SHARED_POOL_SIZE 를 증가시킨다.
   

  5. DBMS_SHARED_POOL STORED PROCEDURE 사용법

이 stored package는 dbmspool.sql을 포함하며 7.0.13 이상 version에서 사용
 가능하다. 이는 다음과 같이 3가지 부분으로 나누어 진다.
 
 Procedure sizes(minsize number):
 -> Shared_Pool_size 안에서 정해진 Size 보다 큰 Object를 보여준다.

 Procedure keep(name varchar2, flag char Default  'P'):
 -> Object (Only Package)를 Shared Pool 에 유지한다. 또한 일단 Keep한
      Object는 LRU Algorithm에 영향을 받지 않으며
   "Alter System Flush Shared_Pool" Command 에 의해 Package 의 Compiled
      Version 이 Shared Pool에서 Clear되지 않는다.

 Procedure unkeep(name varchar2):
   -> keep() 의 반대 기능이다

  이 Procedure들과 사용법에 대해 보다 더 자세한 정보를 위해서는  $ORACLE_HOME/rdbms/admin/dbmspool.sql script 또는 오라클 레퍼런스매뉴얼을 참조하기 바람.

Reference Documents
-------------------
<NOTE:146599.1> Diagnosing and Resolving Error ORA-04031.

No. 19876

(V7.X ~ V9.2)예제를 통한 ORA-4031 ERROR 실제 사례의 분석(SHARED POOL)
=====================================================================

Purpose
-------

   이 자료는 ORA-4031 에러가 발생하는 여러가지 case 가운데 사이즈가   큰 PL/SQL Routine 또는 Procedure가 메모리에 로드되기 위하여
   주로 발생하는 ORA-4031 사례에 대한 예제와 분석을 소개하는 자료이다.

Problem Description
-------------------

   Procedure, function, package 등의 library가 shared pool 영역에   할당되려고 할 때 ORA-4031 에러가 발생하는 경우가 있다. 이 때
   shared memory를 많이 차지하는 query를 어떻게 추적하는지 사례를   통해 알아보기로 한다.

Workaround
----------
   restart instance or flush shared pool
   keep large objects in memory

Solution Description
--------------------

   SYS.X$KSMLRU 와 SYS.X$KSMSP 는 shared pool memory의 사용 현황을
   보여주는 오라클의 base table들이다.

   1. SYS.X$KSMLRU

   SYS.X$KSMLRU 를 보면
   이 fixed table은 shared pool 영역에 cache되기 위해 다른 object를   밀어낸(aged out) allocation들에 대한 정보를 담고 있다.

   이 table을 통해 어떤 object가 많은 공간을 메모리에 차지하면서  할당되었는지 알 수 있는데, 한 번 조회하고 나면 조회된 정보는  테이블에서 remove된다.

   KSMLRCOM  부분이 'MPCODE'나 'PLSQL%' 로 시작한다면,  큰 사이즈의 PL/SQL object가 shared pool 영역에 load된 것이므로,
   이 procedure는 memory에 keep되어지면 좋다는 결론이 나오는데,  SYS.X$KSMLRU 를 조회한 결과를 보아야 한다.

   SYS.X$KSMLRU에 만약 아무것도 조회되지 않는다면, 그러니까, 큰 object가 memory에 load되기 위해 다른 object가  aged out된 것은 없다는 것을 의미한다.

   이 fixed table의 column에는 다음과 같은 것이 있다.
   =================================================================
   KSMLRSIZ :  allocate된 연속된 memory size.
               이 크기가 5K가 넘으면 문제될 소지가 있다고 보고,
               10K가 넘으면 심각한 문제가, 20K가 넘으면 매우 심각한
               문제를 야기할 수 있으므로 주의가 필요하다.
   KSMLRNUM :  이 object의 할당으로 인하여 flush되었던 object의 갯수.
   KSMLRHON :  load되고 있는 object의 이름.(PL/SQL or a cursor)
   KSMLROHV :  load되고 있는 object의 hash value.
   KSMLRSES :  이 object를 load한 session의 SADDR 값.
   =================================================================

   2. SYS.X$KSMSP

   SYS.X$KSMSP 뷰를 조회하면 shared pool 영역의 free space와 flush할 수 있는 freeable space에 대한 조각이 얼마인지 확인할 수 있다.

   ORA-4031 오류가 발생했을 때, V$SGASTAT 뷰를 통해서는 shared   pool 영역의 전체 free space만 확인 가능하지만, 이 뷰를 조회하면
   memory space 조각에 대한 정보도 볼 수 있다.

   이 fixed table의 column에는 다음과 같은 것이 있다.
   (Allocate된 chunk 하나 당 하나의 row가 생성된다.)
   =================================================================
   KSMCHCLS  :  CHUNK의 CLASS
      (free : free, freeable : freeable, perm : permanent,
       recr : recreatable)
   KSMCHSIZ  :  CHUNK의 사이즈
   KSMCHCOM  :  CHUNK에 대한 속성을 나타내는 간단한 text comment
   KSMCHPTR  :  메모리 상에서 LOCATION에 대한 HEX value
   =================================================================

   SQL> select ksmchcls, MAX(ksmchsiz), SUM(ksmchsiz)
        from sys.x$ksmsp
        group by ksmchcls;

   KSMCHCLS MAX(KSMCHSIZ) SUM(KSMCHSIZ)
   -------- ------------- -------------
   R-free          671600       6044400
   R-freea             40           720
   free          16105472     106453784
   freeabl         124176       5391136
   perm          15650000      31052280
   recr              6496       2052048

   6 rows selected.

Example
-------

   SQL> select ksmchcls, MAX(ksmchsiz), SUM(ksmchsiz) 
        from sys.x$ksmsp
        group by ksmchcls;

   KSMCHCLS MAX(KSMCHSIZ) SUM(KSMCHSIZ)
   -------- ------------- -------------
   R-free       138716800     139574016
   R-freea           8152        236200
   free             21712        987208
   freeabl          50680     234508992
   perm          47020752      53054992
   recr             12168      30352488

   6 rows selected.                                     
 

SQL> select * from X$KSMLRU
     where KSMLRSIZ > 0;

ADDR                  INDX   INST_ID KSMLRCOM              KSMLRSIZ  KSMLRNUM KSMLRHON                          KSMLROHV KSMLRSES                                                
---------------- --------- --------- -------------------- --------- --------- -------------------------------- --------- ----------------                                        
C0000000472FA428         0         1 PAR.C:parchk:page         2120         8 BEGIN         PRD_WS_NEXT2( ...  3.325E+09 C00000002B8DA980                                        
                                                                             
C0000000472FA470         1         1 KQLS MEM BLOCK            2288         8 WGQCT                            4.034E+09 C00000002B867680                                        
                                                                             
C0000000472FA4B8         2         1 seldef : kkmset           2552         8 SELECT MJCD,fun_aa_nm1(mjcd)...  850201559 C00000002B915F80                                        
                                                                             
C0000000472FA500         3         1 lazdef : kkmset           2568         8 SELECT MJCD,fun_aa_nm1(mjcd)...  265721063 C00000002B8DA980                                        
                                                                             
C0000000472FA548         4         1 lazdef : kkmset           2664         8 select mjnm,mjcd,sum(jg1) j...   1.594E+09 C00000002B85FB00                                        
                                                                             
C0000000472FA590         5         1 idndef : prsexl           3112         3 SELECT /*+ rule */ * FROM sy...  4.285E+09 C00000002B8FF680                                        
                                                                             
C0000000472FA5D8         6         1 BAMIMA: Bam Buffer        3896         8 YYCAT_T1                         1.175E+09 C00000002B857600                                        
                                                                             
C0000000472FA620         7         1 state objects             4080         8                                          0 C00000002B8C9600                                        
                                                                             
C0000000472FA668         8         1 BAMIMA: Bam Buffer        4168       168 BEGIN  PRD_WS_NEXT2( ...         4.036E+09 C00000002B8BE180                                        
                                                                             
C0000000472FA6B0         9         1 library cache             4232        40 SELECT /*+NESTED_TABLE_GET_R...  2.607E+09 C00000002B856300                                        
                                                                                                   
                                                              
10 rows selected.

위의 결과에서 주목해야 할 부분은 KSMLRNUM 값이 큰 수치(168, 40)를 보이는
Procedure(168)와 SQL(40)이다.
KSMLRNUM 값이 높다는 것은 이 object의 할당으로 인하여 flush되었던 object
의 갯수가 그 만큼 많다는 것이므로, 이 Procedure 또는 Function은 메모리
에 keep되어질 필요가 있음을 의미한다.
Shared_pool에 Procedure를 Keep하는 방법과 여러 사용자 간에 주로 사용하
는 SQL 문을 공유하기에 관한 자료는 <Bulletin:11776>을 참조하도록 한다.

Reference Documents
-------------------
<Note:61623.1>
<Note:146599.1>
<Note:62143.1>

No. 19876

(V7.X ~ V9.2)예제를 통한 ORA-4031 ERROR 실제 사례의 분석(SHARED POOL)
=====================================================================

Purpose
-------

   이 자료는 ORA-4031 에러가 발생하는 여러가지 case 가운데 사이즈가
   큰 PL/SQL Routine 또는 Procedure가 메모리에 로드되기 위하여
   주로 발생하는 ORA-4031 사례에 대한 예제와 분석을 소개하는 자료이다.

Problem Description
-------------------

   Procedure, function, package 등의 library가 shared pool 영역에
   할당되려고 할 때 ORA-4031 에러가 발생하는 경우가 있다. 이 때
   shared memory를 많이 차지하는 query를 어떻게 추적하는지 사례를
   통해 알아보기로 한다.

Workaround
----------
   restart instance or flush shared pool
   keep large objects in memory

Solution Description
--------------------

   SYS.X$KSMLRU 와 SYS.X$KSMSP 는 shared pool memory의 사용 현황을
   보여주는 오라클의 base table들이다.

   1. SYS.X$KSMLRU

   SYS.X$KSMLRU 를 보면
   이 fixed table은 shared pool 영역에 cache되기 위해 다른 object를
   밀어낸(aged out) allocation들에 대한 정보를 담고 있다.

   이 table을 통해 어떤 object가 많은 공간을 메모리에 차지하면서
   할당되었는지 알 수 있는데, 한 번 조회하고 나면 조회된 정보는
   테이블에서 remove된다.

   KSMLRCOM  부분이 'MPCODE'나 'PLSQL%' 로 시작한다면,
   큰 사이즈의 PL/SQL object가 shared pool 영역에 load된 것이므로,
   이 procedure는 memory에 keep되어지면 좋다는 결론이 나오는데,
   SYS.X$KSMLRU 를 조회한 결과를 보아야 한다.

   SYS.X$KSMLRU에 만약 아무것도 조회되지 않는다면,
   그러니까, 큰 object가 memory에 load되기 위해 다른 object가
   aged out된 것은 없다는 것을 의미한다.

   이 fixed table의 column에는 다음과 같은 것이 있다.
   =================================================================
   KSMLRSIZ :  allocate된 연속된 memory size.
               이 크기가 5K가 넘으면 문제될 소지가 있다고 보고,
               10K가 넘으면 심각한 문제가, 20K가 넘으면 매우 심각한
               문제를 야기할 수 있으므로 주의가 필요하다.
   KSMLRNUM :  이 object의 할당으로 인하여 flush되었던 object의 갯수.
   KSMLRHON :  load되고 있는 object의 이름.(PL/SQL or a cursor)
   KSMLROHV :  load되고 있는 object의 hash value.
   KSMLRSES :  이 object를 load한 session의 SADDR 값.
   =================================================================

   2. SYS.X$KSMSP

   SYS.X$KSMSP 뷰를 조회하면 shared pool 영역의 free space와 flush할
   수 있는 freeable space에 대한 조각이 얼마인지 확인할 수 있다.

   ORA-4031 오류가 발생했을 때, V$SGASTAT 뷰를 통해서는 shared
   pool 영역의 전체 free space만 확인 가능하지만, 이 뷰를 조회하면
   memory space 조각에 대한 정보도 볼 수 있다.

   이 fixed table의 column에는 다음과 같은 것이 있다.
   (Allocate된 chunk 하나 당 하나의 row가 생성된다.)
   =================================================================
   KSMCHCLS  :  CHUNK의 CLASS
      (free : free, freeable : freeable, perm : permanent,
       recr : recreatable)
   KSMCHSIZ  :  CHUNK의 사이즈
   KSMCHCOM  :  CHUNK에 대한 속성을 나타내는 간단한 text comment
   KSMCHPTR  :  메모리 상에서 LOCATION에 대한 HEX value
   =================================================================

   SQL> select ksmchcls, MAX(ksmchsiz), SUM(ksmchsiz)
        from sys.x$ksmsp
        group by ksmchcls;

   KSMCHCLS MAX(KSMCHSIZ) SUM(KSMCHSIZ)
   -------- ------------- -------------
   R-free          671600       6044400
   R-freea             40           720
   free          16105472     106453784
   freeabl         124176       5391136
   perm          15650000      31052280
   recr              6496       2052048

   6 rows selected.

Example
-------

   SQL> select ksmchcls, MAX(ksmchsiz), SUM(ksmchsiz) 
        from sys.x$ksmsp
        group by ksmchcls;

   KSMCHCLS MAX(KSMCHSIZ) SUM(KSMCHSIZ)
   -------- ------------- -------------
   R-free       138716800     139574016
   R-freea           8152        236200
   free             21712        987208
   freeabl          50680     234508992
   perm          47020752      53054992
   recr             12168      30352488

   6 rows selected.                                     
 

SQL> select * from X$KSMLRU
     where KSMLRSIZ > 0;

ADDR                  INDX   INST_ID KSMLRCOM              KSMLRSIZ  KSMLRNUM KSMLRHON                          KSMLROHV KSMLRSES 
---------------- --------- --------- -------------------- --------- --------- -------------------------------- --------- ----------------
C0000000472FA428         0         1 PAR.C:parchk:page         2120         8 BEGIN         PRD_WS_NEXT2( ...  3.325E+09 C00000002B8DA980
C0000000472FA470         1         1 KQLS MEM BLOCK            2288         8 WGQCT                            4.034E+09 C00000002B867680       
C0000000472FA4B8         2         1 seldef : kkmset           2552         8 SELECT MJCD,fun_aa_nm1(mjcd)...  850201559 C00000002B915F80
C0000000472FA500         3         1 lazdef : kkmset           2568         8 SELECT MJCD,fun_aa_nm1(mjcd)...  265721063 C00000002B8DA980
C0000000472FA548         4         1 lazdef : kkmset           2664         8 select mjnm,mjcd,sum(jg1) j...   1.594E+09 C00000002B85FB00
C0000000472FA590         5         1 idndef : prsexl           3112         3 SELECT /*+ rule */ * FROM sy...  4.285E+09 C00000002B8FF680
C0000000472FA5D8         6         1 BAMIMA: Bam Buffer        3896         8 YYCAT_T1                         1.175E+09 C00000002B857600 
C0000000472FA620         7         1 state objects             4080         8                                          0 C00000002B8C9600
C0000000472FA668         8         1 BAMIMA: Bam Buffer        4168       168 BEGIN  PRD_WS_NEXT2( ...         4.036E+09 C00000002B8BE180   
C0000000472FA6B0         9         1 library cache             4232        40 SELECT /*+NESTED_TABLE_GET_R...  2.607E+09 C00000002B856300  

10 rows selected.

위의 결과에서 주목해야 할 부분은 KSMLRNUM 값이 큰 수치(168, 40)를 보이는
Procedure(168)와 SQL(40)이다.
KSMLRNUM 값이 높다는 것은 이 object의 할당으로 인하여 flush되었던 object
의 갯수가 그 만큼 많다는 것이므로, 이 Procedure 또는 Function은 메모리
에 keep되어질 필요가 있음을 의미한다.
Shared_pool에 Procedure를 Keep하는 방법과 여러 사용자 간에 주로 사용하
는 SQL 문을 공유하기에 관한 자료는 <Bulletin:11776>을 참조하도록 한다.

Reference Documents
-------------------
<Note:61623.1>
<Note:146599.1>
<Note:62143.1>

출처 : Tong - exospace님의 Oracle통

- shared pool size를 늘리기 전에 다음과 같은 사항을 먼저 체크한다

  ○ shared sql을 이용하는 application 튜닝
  ○ shared_pool_size, shared_pool_reserved_size 튜닝
  ○ shared_pool_reserved_min_alloc 튜닝

1. ora-4031 에러가 library cache나 shared_pool reserved space내의 fragmentation으로 발생했는지 다음 명령어로 확인한다.

select free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size
from v$shared_pool_reserved;

=> shred pool reserved space 내에 연속된 공간이 부족해서 ora-4031 에러가 발생했다면 위 쿼리의 결과 값은 다음과 같은 조건을 만족해야 한다

request_failures > 0
and
last_failure_size > shared_pool_reserved_min_alloc

shared pool reserved space 내에 연속된 공간이 부족한 문제를 해결하려면, shared pool reserved space 내에 캐시될 objects의 개수를 맞추기 위해서 shared_ool_reserved_min_alloc parameter 값을 늘려준다.  또한 shard pool reserved space 내에서 이용 가능한 메모리를 증가시키려면 shared_pool_reserved_size와 shared_pool_size parameter 값을 증가시킨다.

2. library cache 내에 존재하는 sapce 중에서 연속된 space의 부족으로 ora-4031 에러가 발생했다면, 앞의 sql 문의 결과 값은 다음과 같은 조건을 만족해야 한다.

request_failures > 0
and
last_failure_size < shared_pool_reserved_min_alloc

- or -

request_failures = 0
and
last_failure_size < shared_pool_reserved_min_alloc

library cache 내에 존재하는 space 중에서 연속된 space의 부족으로 발생한 문제를 제거하려면 shared pool reserved space 내에 더 많은 objects 들을 두기 위해 shared_pool_reserved_min_alloc parameter 값을 낮춰주고, shared_pool_size parameter 값을 증가 시킨다.

3. 진단 후의 솔루션

- 최신 patchset 적용
- shared pool 이 조각나면 실제 운용 환경에서는 사용자들이 느낄 수 있을 정도의 성능 저하 현상이 발생한다.
  wait_event 상으로는 shard pool latch가 발생한다. 그리고 연속된 조각을 발견할수 없다는 ora-04031 에러를 발생시킨다.

-literal values 와 bind variable을 포함하는 candidates들을 볼 수 있는 쿼리

select substr(sql_text,1,40) "SQL",count(*),sum(executions) "TotExecs"
from v$sqlarea
where executions < 5
group by substr(sql_text,1,40)
having count(*) > 30
order by 2;

=> having 부분의 30이라는 숫자는 사용자 환경에 맞춰 변경해 주면 된다.

- X$KSMLRU view
: shared pool 내에 있는 다른 objects들을 내보낸(age out)것을 추적하는데 이용된다. 이것은 large allocation을 유발시킨 것을 찾아낼 수 있게 해준다. 많은 object들이 지속적으로 shared pool 에서 빠져나간다면 응답 시간이 느려지는 문제가 발생할 수 있고, flush된 objects들이 shared pool 내로 재진입 할때는 library cache latch 이벤트를 유발하는 원인이 될 수도 있다.

select * from X$KSMLRU where ksmlrsiz > 0;

- miss ratio 결과 값이 1% 이상이라면 shared pool size를 증가시켜 library cache miss ratio를 줄이도록 하자.

4. 최후 수단

event = "4031 trace name errorstack level 3"

sqlplus를 이용하여 sysdba 계정에서 아래의 파일을 실행합니다.

SQL> @dbmsutil.sql <== ORACLE_HOME\rdbms\admin 에 파일이 존재함
SQL> @prvtutil.plb <== ORACLE_HOME\rdbms\admin 에 파일이 존재함

SQL> grant execute on dbms_system to system;

- DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, TRUE)
지정된 session에 대해 SQL trace를 실행하여 user_dump_dest parameter에 지정된 위치에 trace file이 생성된다.

- DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, FALSE)
지정된 session에 대한 SQL trace를 해제한다.

[예제] system user에서 scott user를 지정하여 SQL trace 생성하기.

sqlplus system/manager

SQL> select sid, serial#
from v$session
where username = 'SCOTT';

SID SERIAL#
--------- ---------
8 12

SQL> exec sys.dbms_system.set_sql_trace_in_session(8, 12, TRUE);

-> scott user에 대한 SQL trace가 실행된다.

SQL> exec sys.dbms_system.set_sql_trace_in_session(8, 12, FALSE);

-> scott user에 걸린 SQL trace가 해제된다.

==> 위와 같이 trace 처리된 내역은 DBMS가 설치된 server의 user_dump_dest 경로에 trc 파일로 생성됩니다.

이렇게 해서 생성된 trace 파일을 TKPROF를 이용하여 가독이 용이한 형태로 내용을 변환하여 확인하면 됩니다.

ex) tkprof '원본파일' '변환파일' [option] sys=no aggregate=no explain=계정ID/계정PWD
--> tkprof C:\oracle\admin\orcl\udump\orcl_ora_3012.trc c:\test.text sys=no aggregate=no explain=scott/tiger

SQL 튜닝, 대기현상(내부 매커니즘)을 분석 할때 사용하는 툴

즉, 개발환경에서 문제없이 수행 되는 SQL 문장이 운영환경에서 느린 성능을 보이는 경우 SQL Trace 를 이용하면 원인 분석이 가능하다.

10046 이벤트를 사용하며 4가지 레벨로 제어 한다

LEVEL 1 : SQL 문장의 실행정보(Parse, Execute, Fetch 단계와 Row source operation 결과)만을 제공한다

LEVEL 4 : LEVEL 1 + 바인드 변수값을 제공한다

LEVEL 8 : LEVEL 1 + 대기정보를 제공한다

LEVEL 12 : LEVEL 4 + LEVEL 8, 즉 SQL 문장의 실행정보와 바인드 변수 값, 대기정보를 제공한다

현재 세션 Trace

SQL> ALTER SESSION SET sql_trace=TRUE; -- trace file 시작
SQL> ALTER SESSION SET sql_trace=FALSE; -- trace file 종료

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

다른 세션 Trace

- dbms_system 사용

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE); --sql trace 입장
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' '); -- 이벤트 입장

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' '); -- nm은 name이다.

--SQL> exec dbms_system.set_ev(7, 10, 65535, 10, 'controlf');
--SQL> exec dbms_system.set_ev(8,1056,65535,10,'PROCESSSTATE');

- oradebug 사용

SQL> CONN sys/password AS SYSDBA; --반드시 sysdba 권한 유저로 실행
(SQL> ORADEBUG SETMYPID; -- Debug current session.)

or
SQL> ORADEBUG SETOSPID 1234; -- Debug session with the specified OS process.

or
SQL> ORADEBUG SETORAPID 123456; -- Debug session with the specified Oracle process ID.

SQL> ORADEBUG unlimit; -- Trace file 크기제한 無

SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME; -- Display the current trace file.

/u01/app/oracle/product/10.2.0/db_1/rdbms/log/dhdb_ora_10803.trc
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

10g 부터 SQL Trace 로 캡쳐 불가능한 작업을 dbms_monitor 패키지를 사용하여 Client ID, Service/Module/Action 등을 Trace 할 수 있다

이때, 여러 개의 세션에 대한 Trace 파일이 생성되며, trcsess 툴을 이용해 하나의 Trace 파일로 만들 수 있다

exec dbms_monitor.client_id_trace_enable(client_id=>'12345', waites=>true, binds=>true);

exec dbms_monitor.serv_mod_act_trace_enable(123456);

exec dbms_monitor.session_trace_enable(1234567);

또한, dbms_monitor 패키지를 이용하면 세션이 아닌 Client ID 나 Service/Module/Action 명을 기준으로 통계정보(Statistics)를 수집 할 수 있다

client_id_stat_enable, serv_mod_act_stat_enable 을 통해 수집된 정보는 V$CLIENT_STATS, V$SERV_MOD_ACT_STATS 뷰를 통해 관찰 가능하다

cf.alter session set events 'immediate trace name controlf level 10; --> 이 경우는 event 번호가 없으므로 name으로 trace를 생성한다.

=====================================================

출처 및 참고자료 : Advanced Oracle Wait Interface in 10g

=====================================================

special thank's to eddy

In this Document
  Symptoms
  Cause
  Solution

Applies to:

Symptoms

Comments
--------
=== ODM Issue Clarification ===

Traces are written regularly in the user_dump_dest containing just the error 'opiino: Attach failed! error=-1 ifvp=0000000000000000'.

The application running against this database is EBS 11.5.1.0.2 and, when EBS is stopped, the traces do not appear. SQL*Net tracing shows that the connections producing the traces are JDBC connections.

Cause

Solution

The traces can be stopped by setting the following parameters:

listener.ora:
INBOUND_CONNECT_TIMEOUT_PROD = 120

sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT = 120
SQLNET.SEND_TIMEOUT = 120
SQLNET.RECV_TIMEOUT = 120

However setting these can cause some EBS connections to fail with TNS-12537, TNS-12560, TNS-507.

The traces were finally stopped by applying ATG.6 to EBS.