■ Share pool과 성능문제
xx지원 오라클 장애처리
========================
작성자 :
1. 장애처리
장애 시간 : 2006-07-21 11:34
장애 서버 : xx지원 서버2의 오라클 데이터베이스
장애 유형 : 오라클 데이터베이스 인스턴스 다운
장애 원인 : 오라클의 LMD(LOCK MONITOR DEADON) 다운으로 오라클 인스턴스 다운이 발생함
(장애분석 참조)
장애 조치 : 오라클 인스턴스 재시작함
복구 시간 : 2006-07-21 11:53
장애 예방방안 : 장애대처 예방 참조
2. 장애분석
[ alert! 로그 파일]
Fri Jul 21 11:34:26 2006 ==> 장애 시간
LMD0: terminating instance due to error 4031 ==> 장애 메시지
Instance terminated by LMD0, pid = 19848 ==> 데이터베이스 다운
[ trace 파일 분석 ]
파일명 : llmd0_19848_hiraops2.trc
*** 2006-07-21 11:34:26.346
*** SESSION ID:(3.1) 2006-07-21 11:34:26.306
error 4031 detected in background process
[ 장애원인 ]
ORA-4031 : shared pool memory 분석으로 LMD 프로세스 다운으로 발생함
Shared Pool의 정확인 영역은 나타나지 않음
<참조 : 메시지 >
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
*Cause: More shared memory is needed than was allocated in the shared pool.
*Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin
large packages,reduce your use of shared memory, or increase the amount of available shared
memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and
"shared_pool_size".
If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".
< Ora-4031 란 ? >
이 에러는 사용자가 특정 Object를 사용하고자 할 때 해당 Object를 위해 Shared_Pool에 공간을 할당하려 할 때 충분한
공간을 얻지 못하는 경우이다
Shared_Pool 영역에 차지하는 크기가 큰 PL/SQL 사용시 오라클은 공간 확보를 위해 현재 사용되지 않는 Object를 Flush시킨다.
기존 V7.2. 이하에서는 특정 프로시저를 Shared_Pool에 올리기 위해 연속된 공간을 필요로 하였으나, 이후 버전에
서는 꼭 연속되지 않아도 사용이 가능하여 ORA-4031 에러가 발생하는 경우가 많이줄어들었다.
이의 해결을 위해서는 Shared_Pool을 늘려주도록 한다 .
이 에러를 유발시키는 문제인 메모리의 단편화 (fragmentation)를 줄이기 위해서, 또는 일반적인 경우 자주 사용하는 Object가
메모리에서 밀려 내려가는 경우를 없애기 위해서 Object를 미리 Shared_Pool에 Keep 한다.
3.장애 예방 방안
[ 목적 ]
- Shared Pool Memory 부족(ORA-4031) 장애발생 예방
[ 예방 방법 ]
- shared pool size , shared pool reserved size , large pool size를 증가 한다.
[ Shared Pool Memory 할당 값]
#shared_pool_size = 471859200 # 450MB
#shared_pool_reserved_size = 47185920 # shared pool * 10%
#shared_pool_size = 512000000 # 500MB 2005.07.21
#shared_pool_reserved_size = 51200000 # shared pool * 10% 2005.07.21
shared_pool_size = 629145600 # 600MB 2005.08.08
shared_pool_reserved_size = 62914560 # shared pool * 10% 2005.08.08
java_pool_size = 2097152
large_pool_size = 10M # 2004/10/30
[ 메모리 사용률 ]
전체 메모리 : 19.9G
사용률 : Sys Mem : 4.68GB User Mem: 2.71GB Buf Cache: 1.99GB Free Mem: 10.5GB
[ shared pool free memory 확인 QUERY 및 flush 명령어]
SQL> select v$sgastat.pool, to_number(v$parameter.value) value, v$sgastat.bytes,
(v$sgastat.bytes/v$parameter.value)*100 "Percent Free"
from v$sgastat , v$parameter
where v$sgastat.name = 'free memory'
and v$parameter.name = 'shared_pool_size';
POOL VALUE BYTES Percent Free
---------------------------------------------------------------
shared pool 629145600 140415416 22.3184293111165 <-- free space 확인
large pool 629145600 299984 0.0476811726888021
java pool 629145600 2097152 0.333333333333333
SQL> alter system flush shared_pool; <-- 단편화된 조각화를 flush 해주는 방법(메모리 조각모음) (업무외시간에 수행)
[ Library Cache Fragmentation 확인 Query]
select
decode(sign(ksmchsiz - 812), -1, (ksmchsiz - 16) / 4,
decode(sign(ksmchsiz - 4012), -1, trunc((ksmchsiz + 11924) / 64),
decode(sign(ksmchsiz - 65548), -1, trunc(1/log(ksmchsiz - 11, 2)) + 238, 254 ))) bucket,
sum(ksmchsiz) free_space, count(*) free_chunks, trunc(avg(ksmchsiz)) average_size,
max(ksmchsiz) biggest
from
sys.xm$ksmsp
where
inst_id = userenv('Instance') and ksmchcls = 'free'
group by
decode(sign(ksmchsiz - 812), -1, (ksmchsiz - 16) / 4,
decode(sign(ksmchsiz - 4012),-1, trunc((ksmchsiz + 11924) / 64),
decode(sign(ksmchsiz - 65548),-1, trunc(1/log(ksmchsiz - 11, 2)) + 238, 254)))
BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST
---------- ---------- ----------- ------------ -------------------------------------------------------
6 92000 2300 40 40
8 1392 29 48 48
10 1456 26 56 56
……
위의 결과처럼 shared pool 영역에 작은 chunk 들의 다수 존재하는 경우에는 free list 검색시 shared pool latch를 장시간 holding하게 되며,
이는 shared pool latch free 대기 현상이 발생할 가능성을 높여주게 된다. 일반적으로 hard parsing에 의해 이러한 현상들이 발생하게 됨.
[ Shared Pool size 적절성 확인 Query ]
column kghlurcr heading "RECURRENT|CHUNKS"
column kghlutrn heading "TRANSIENT|CHUNKS"
column kghlufsh heading "FLUSHED|CHUNKS"
column kghluops heading "PINS AND|RELEASES"
column kghlunfu heading "ORA-4031|ERRORS"
column kghlunfs heading "LAST ERROR|SIZE"
select
kghlurcr "RECURRENT|CHUNKS" ,
kghlurcr *3,
kghlutrn "TRANSIENT|CHUNKS" ,
kghlufsh "FLUSHED|CHUNKS" ,
kghluops "PINS AND|RELEASES" ,
(kghlufsh/ kghluops) *100,
kghlunfu "ORA-4031|ERRORS" ,
kghlunfs "LAST ERROR|SIZE"
from
sys.x_$kghlu
where
inst_id = userenv('Instance');
RECURRENT TRANSIENT FLUSHED PINS AND ORA-4031 LAST ERROR
CHUNKS CHUNKS CHUNKS RELEASES ERRORS SIZE
---------- ---------- --------------- ---------- -----------------------------------------------------------------
587 1687 8946 28416904 0 0
-- 일반적으로 Transient list가 Recurrent List 의 3배 이상이면 shared pool 이 oversize 된 것이며, chunk flush 수치가
-- pins and released 수치의 5% 이상이면 shared pool 이 작게 설정된 것이라고 볼 수 있다.
shared pool 의 free 공간 확인
select to_number(v$parameter.value) value, v$sgastat.bytes,
(v$sgastat.bytes/v$parameter.value)*100 "Percent Free"
from v$sgastat, v$parameter
where v$sgastat.name = 'free memory'
and v$parameter.name = 'shared_pool_size';
■ Shared Pool 관련 wait
|
Latch |
Lock |
|
latch free ( library cache )
latch free ( library cache load lock) |
library cache lock, library cache pin
library cache load lock |
|
latch free ( row cache objects ) |
row cache lock |
|
latch free ( shared pool ) |
|
☞ 4-6 library cache 알고리즘
라이브러리 캐쉬는 lru 알고리즘에의해 관리가 되며
문장이 이미 캐쉬에 저장되어 있는가를 찾기 위하여, 오라클 서버는 다음을 수행합니다:
1. ASCII 텍스트의 수치로 문장을 줄임
2. 이 수치에 해시 함수 사용
☞ 4-7 shared pool latch
SQL 이 공유되지 못하고 파싱해달라고 하는 SQL 이 많아지면 공간을 할당 받아야하는데 이때
공간을 할당 받을때 메모리에 락(latch) 를 건다. latch 를 사용하는 이유는 파싱해달라고 요청하는
sql 에 대하여 순서를 정하는 것인데 먼저 요청한 sql 에 대하여 작업을 원할히 수행하기 위해 메모리에 락을 거는것이다.즉 동시 엑세스를 제어하기 위해 latch 를 사용한다.
그런데 조각화(단편화) 가 심해지면 free list 가 그만큼 길어지므로 공간 할당받을때 시간이 많이 소유될것이고 이로 인해 발생하는 wait event ( latch free(library cache) ) 가 다량으로 발생할것이다.
이를 해결하기 위해서는
1. 바인드변수 사용
2. cursor_sharing 사용
3. shared pool size 늘린다.
4. large pool 을 구성한다.
☞ 4-8 shared pool 에 공유되지 않은 SQL 에 대한 정보 보기
select *
from v$sqlarea
where executions < 5
order by upper(sql_Text)
select sql_Text, parse_calls, executions
from v$sqlarea
order by parse_calls;
☞ 4-12 ~ 실습
v$sqlarea
(라이브러리 캐쉬: 문장저장)
동일한 문장
1. 대소문자
2. 스키마(schema)
3. 공백
4. 주석
5. bind 변수의 type(아래설명)
*****************************************
scott1
실행
SQL> select empno,ename from emp where deptno=10;
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER
internal에서 실행
SQL> ed
Wrote file afiedt.buf
select sql_text,SHARABLE_MEM,EXECUTIONS
from v$sqlarea
where sql_text like 'select empno,ename from emp where%';
SQL_TEXT SHARABLE_MEM EXECUTIONS
-------------------------------------------------------------- ----------
select empno,ename from emp where deptno=10 5234 1
사용했던 문장이 나옴
scott1
변수 선언
SQL> variable v1 varchar2(10)
SQL> declare
2 begin
3 :v1 := '10';
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select empno, ename from emp where deptno=:v1;
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER
internal
SQL> /
SQL_TEXT SHARABLE_MEM EXECUTIONS
-------------------------------------------------- ------------ ----------
select empno,ename from emp where deptno=10 5234 1
select empno,ename from emp where deptno=:v1 5067 1
scott1
변수값을 바꾸어줌
SQL> declare
2 begin
3 :v1 := '20';
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select empno,ename from emp where deptno=:v1;
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
internal
scott의 결과는 달라도 새로 파싱을 안함
SQL> /
SQL_TEXT SHARABLE_MEM EXECUTIONS
-------------------------------------------------- ------------ ----------
select empno,ename from emp where deptno=10 5234 1
select empno,ename from emp where deptno=:v1 5067 2
문제) select empno,ename from emp where deptno=30 을 하고 출력해보아라 !!!
scott2
변수형을 넘버로 선언
SQL> variable v1 number
SQL> declare
2 begin
3 :v1 :=20;
4 end;
PL/SQL procedure successfully completed.
SQL> select empno,ename from emp where deptno=:v1;
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
internal
scott2에 실행한 문장후 크기가 늘어남 (파싱계획변경)
SQL> /
SQL_TEXT SHARABLE_MEM EXECUTIONS
-------------------------------------------------- ------------ ----------
select empno,ename from emp where deptno=10 5234 1
select empno,ename from emp where deptno=:v1 8939 3
********** bind type에 따라 파싱이 다르다 ****************
☞ 4-13 실습
1) v$librarycache 를 통해 hit율(gethitratio)를 분석
- gets : 사용자가 실행한 SQL문이 구문 분석되어 라이브러리 캐시 영역에 로드되려 했던 수.
- gethits : 그 중 로드되었던 수
- hit율(gethitratio)이 90% 이상일때 좋은 성능 기대
select namespace , gets , gethits,gethitratio
from v$librarycache
where namespace = 'SQL AREA' ;
NAMESPACE GETS GETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA 141767 141498 .99810252
<= GETHITRATIO 컬럼 값이 90%이상이면 라이브러리 캐시 영역이 개발자들의 SQL 파싱정보를
저장하기에 충분한 메모리 공간을 확보하고 있슴을 의미하며, 만약 90% 이하라면 성능이 저하
될 수 있다는 것을 의미.
물론 90% 이하라도 사용자들이 성능에 만족한다면 튜닝 대상이 안될 수도 있으며 반드시 튜닝
을 해야 할 필요는 없다.
▷ 유의사항
- 히트율 분석은 기업에서 가장 일을 바쁘게 진행하고 있는 시간대의 분석 결과를 기준으로
하여야 한다.
▷ 초치사항
- HIT율이 90% 이하일 경우 SHARED_POOL_SIZE 파라메터 값을 높게 설정.
ex) initSID.ora
......
SHARED_POOL_SIZE = 32000000 <= 이전 보다 큰 값으로 변경
.....
2) v$library 자료사전을 통해 RELOAD 비율 분석.
- RELOADS 비율 = ( Reloads / Pins ) * 100
- Library Cache Area 의 크기가 너무 작아서 사용자의 SQL 구문분석 정보가 로드되지 못하고
가장 오래된 SQL문 정보를 삭제 후 사용자의 SQL 문장이 다시 실행될 때 Reloads 증가.
- 구분분석된 SQL문에서 사용된 객체가 다른 사용자에 의해 삭제된 상태에서 다시 SQL문이
재실행될 때에는 RELOADS 컬럼값이 증가.
- PINS : 구문분석되어 Library Cache Area 에 저장될 수 있었던 SQL 정보.
- PINS 컬럼에 대한 Realods 컬럼의 백분율이 1% 미만일 때 좋은 성능 기대.
select sum(pins),sum(reloads),sum(reloads)/sum(pins)
from v$librarycache
where namespace = 'SQL AREA' ;
SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)
---------- ------------ ----------------------
320823 10 .00003117
<= PINS에 대한 RELOADS의 비율이 1% 미만일 때 라이브러리 캐시 영역의 크기가 AQL 파싱정보
를 저장하기에 충분하다는 것을 의미. 1% 이상이라면 성능이 저하될 수도 있다는 것을 의미.
3) v$librarycache 를 통해 SQL문에서 사용된 객체가 다른 사용자들에 의해 얼마나 자주
삭제,변경되었는 지를 분석하는 방법.
주로 ANALYZE ,ALTER , DROP명령어에 의해 테이블 구조가 변경되는 경우에 발생.
select namespace, invalidations
from v$librarycache
where namespace = 'SQL AREA' ;
NAMESPACE INVALIDATIONS
--------------- -------------
SQL AREA 0
<= INVALIDATIONS 컬럼의 값이 높게 출력 되거나 계속적으로 증가 값을 보인다면 공유 풀 영역
에 작아서 성능이 저하되도 있음을 의미. 즉, 불필요한 재파싱. 재로딩 작업이 발생할 가능성이 높
아지는 것이다.
☞ 4-14 ~ 실습
SQL> alter system set cursor_sharing=force scope=both;
시스템이 변경되었습니다.
SQL>
SQL>
SQL> select sql_text,SHARABLE_MEM,EXECUTIONS
from v$sqlarea
where sql_text like 'select empno,ename from emp where%';
SQL_TEXT
--------------------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=10
6693 1
select empno,ename from emp where deptno=30
6725 1
select empno,ename from emp where deptno=:v1
6546 1
SQL> alter system flush shared_pool;
시스템이 변경되었습니다.
SQL> select sql_text,SHARABLE_MEM,EXECUTIONS
2 from v$sqlarea
3 where sql_text like 'select empno,ename from emp where%';
SQL_TEXT
--------------------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=30
1461 0
scott 창에서
SQL> select empno,ename from emp where deptno=10;
SQL> /
SQL_TEXT
--------------------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=30
1461 0
select empno,ename from emp where deptno=:"SYS_B_0"
11873 1
scott 창에서
SQL> select empno,ename from emp where deptno=20;
SQL> /
SQL_TEXT
--------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=30
1461 0
select empno,ename from emp where deptno=:"SYS_B_0"
11873 1
문제) 다시 exact 로 바꾸고 실습하여라 !!!
SQL> alter system set cursor_Sharing=exact;
시스템이 변경되었습니다.
SQL> select sql_text,SHARABLE_MEM,EXECUTIONS
2 from v$sqlarea
3 where sql_text like 'select empno,ename from emp wh
SQL_TEXT
------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=10
6705 1
select empno,ename from emp where deptno=30
1461 0
select empno,ename from emp where deptno=:"SYS_B_0"
11873 2
SQL>
SQL> /
SQL_TEXT
------------------------------------------------------------------
SHARABLE_MEM EXECUTIONS
------------ ----------
select empno,ename from emp where deptno=10
6705 1
select empno,ename from emp where deptno=20
6725 1
select empno,ename from emp where deptno=30
1461 0
select empno,ename from emp where deptno=:"SYS_B_0"
11873 2
☞ 4-17 실습
**************** 테이블과 뷰만들어서 파싱확인하기 ****************
scott1
테이블 생성 및 뷰 생성
SQL> create table abc
2 (a1 number, a2 number);
Table created.
SQL> insert into abc values(111,111);
1 row created.
SQL> commit;
Commit complete.
SQL> create view abcvw
2 as
3 select a1+a2 summun
4 from abc;
View created.
scott2
테이블 뷰 실행
SQL> select * from abcvw;
SUMMUN
----------
222
scott1
테이블 드랍
SQL> drop table abc;
Table dropped.
internal
SQL> ed
Wrote file afiedt.buf
1 select sql_text,SHARABLE_MEM,EXECUTIONS, INVALIDATIONS
2 from v$sqlarea
3* where sql_text like 'select * from abcvw%'
SQL> /
SQL_TEXT SHARABLE_MEM EXECUTIONS INVALIDATIONS
----------------------------------------------- -----------------------
select * from abcvw 866 1 1
scott2
테이블 뷰 쿼리
SQL> select * from abcvw;
select * from abcvw
*
ERROR at line 1:
ORA-04063: view "SCOTT.ABCVW" has errors
scott1
다시 테이블 생성
SQL> create table abc
2 (a1 number, a2 number);
Table created.
SQL> insert into abc values(111,111);
1 row created.
SQL> commit;
Commit complete.
scott2
뷰 실행(다시 실행이 됨)
SQL> select * from abcvw;
SUMMUN
----------
222
internal
SQL> /
SQL_TEXT SHARABLE_MEM EXECUTIONS INVALIDATIONS
----------------------------------- ------------ ---------- -------------
select * from abcvw 5718 2 1
☞ 4-20 실습
select shared_pool_size_for_estimate as pool_size, estd_lc_size, estd_lc_time_saved
from v$shared_pool_advice;
SHARED_POOL_SIZE_FOR_ESTIMATE NUMBER : Shared pool size for the estimate (in megabytes)
ESTD_LC_SIZE NUMBER: Estimated memory in use by the library cache (in megabytes)
ESTD_LC_TIME_SAVED NUMBER
Estimated elapsed parse time saved (in seconds), owing to library cache memory objects being found in a shared pool of the specified size. This is the time that would have been spent in reloading the required objects in the shared pool had they been aged out due to insufficient amount of available free memory
☞ 4-25 실습
■ V$SQL_PLAN
- Oracle9i 부터 새롭게 추가된 기능
- SQL문이 실행되면서 작성된 실행계획이 Shared Pool Area에 저장됨.
SQL>connect system/manager
SQL>grant dba to scott ;
SQL>connect scott/tiger
SQL>select * from dept ;
SQL>select sql_text , hash_value , address
from v$sqlarea ;
SQL_TEXT HASH_VALUE ADDRESS
------------------- ---------- ----------
select * from dept 3015709834 04165C60
SQL>select id, lpad(' ',depth)||operation operation , options , object_name,optimizer,cost
from v$sql_plan
where hash_value = &1 and address='&2'
start with id=0
connect by ( prior id = parent_id and prior hash_value = hash_value
and prior_child_number = child_number)
order siblings by id, position;
&1 : 3015709834 (HASH_VALUE )
&2 : 04165C60 (ADDRESS)
ID OPERATION OPTIONS OBJECT_NAME OPT COST
---- ---------------------- ----------- --------------- ------ ------
0 SELECT STATEMENT CHO
1 TABLE ACCESS FULL DEPT
<= 또는, set autotrace on 사용으로 SQL문 실행시 파싱단계에서 옵티마이저가 작성한 실행계획
을 참조할 수 있다.
☞ 4- 26 실습
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 50331648
SQL> select sum(sharable_mem)
from v$db_object_cache;
SUM(SHARABLE_MEM)
-----------------
331406
☞ 4-30 ~ 4-32 실습
show parameter shared
select p.value/r.value * 100 "reserved radit"
from v$parameter p, v$parameter r
where p.name = 'shared_pool_reserved_size'
and r.name = 'shared_pool_size';
select * from v$db_object_cache
where sharable_mem > 10000
and ( type='PACKAGE' or type='PACKAGE BODY' or
type='FUNCTION' or type='PROCEDURE' )
and kept='NO';
@d:\oracle\ora92\rdbms\admin\dbmspool.sql
desc dbms_shared_pool
execute dbms_shared_pool.keep('dbms_sql');
select * from v$db_object_cache
where sharable_mem > 10000
and ( type='PACKAGE' or type='PACKAGE BODY' or
type='FUNCTION' or type='PROCEDURE' )
☞ 4-30 ~ 4-32 실습
SQL> declare x number;
begin x :=5;
end;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SQL> declare /* keep_me */ x number;
begin x := 5;
end;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select address, hash_value
from v$sqlarea
where command_type=47
and sql_Text like '%keep_me%';
ADDRESS HASH_VALUE
-------- ----------
669841E4 4215866029
execute dbms_shared_pool.keep('669841E4,4215866029','c');
select distinct name, sharable_mem, loads
from v$db_object_cache
where name like '%keep_me%';
DBMS_SHARED_POOL을 이용한 KEEP
Shared Poll에 크기가 큰 프로그램을 KEEP하기 위해서는 아래에 있는 것처럼 DBMS_SHARED_POOL Package를 이용 할 수 있습니다.
SQL> @C:\oracle\ora92\rdbms\admin\dbmspool.sql
패키지가 생성되었습니다.
권한이 부여되었습니다.
뷰가 생성되었습니다.
패키지 본문이 생성되었습니다.
SQL> @C:\oracle\ora92\rdbms\admin\prvtpool.plb
뷰가 생성되었습니다.
패키지 본문이 생성되었습니다.
SQL> grant execute on dbms_shared_pool to scott;
권한이 부여되었습니다.
Object를 KEEP하는 방법은 다음과 같습니다.
Procedure,Function,Package : exec dbms_shared_pool.keep(‘pname’,’p’)
Trigger : exec dbms_shared_pool.keep(‘tr_emp’,’r’)
Sequence : exec dbms_shared_pool.keep(‘seq_empno,’q’)
SQL문은 아래와 같은 방법으로 KEEP 합니다.
예를들어 select empno, ename, sal from emp where deptno = ‘20’ 라는 SQL문장을 Library Cache안의 Shared Cursor 부분에 KEEP하기 위해서는 아래처럼 하면 됩니다…
SQL> conn scott/tiger
연결되었습니다.
SQL> select empno, ename, sal from emp where deptno = 20;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
SQL> conn / as sysdba
연결되었습니다.
SQL> select address, hash_value from v$sqlarea
2 where sql_text = 'select empno, ename, sal from emp where deptno = 20';
ADDRESS HASH_VALUE
-------- ----------
7856AC4C 1137127237 <- 원하는 SQL문장에 대한 주소와 해시 값
아래 명령으로 KEEP 합니다.
SQL> exec dbms_shared_pool.keep('7856AC4C, 1137127237','c');
PL/SQL 처리가 정상적으로 완료되었습니다.
Object의 KEEP 상태는 다음으로 체크 가능 합니다.
SQL> select distinct name, sharable_mem, loads
from v$db_object_cache
where name like '%emp%'
and kept = 'YES';
NAME SHARABLE_MEM LOADS
------------ ----------------------------------------------
select empno, ename, sal from emp where deptno = 20 1469 1
또는 exec dbms_shared_pool.sizes(0)로 확인 가능 합니다. 이 sizes라는 procedure는 제한된 사이크 이상의 keep된 Object를 나타내 줍니다.
SQL> set serveroutput on size 2000
SQL> exec dbms_shared_pool.sizes(0) -> buffer overflow가 나더라도 pin시킬(KEEP할) SQL문장을 찾을 수는 있습니다.
각 Object를 Shared Pool에 유지하던 것을 해제 할 때는 아래의 unkeep 프로시저를 이용 합니다.
SQL> exec dbms_shared_pool.unkeep('7856AC4C, 1137127237','c');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select distinct name, sharable_mem, loads
2 from v$db_object_cache
3 where name like '%emp%'
4 and kept = 'YES';
결과가 없겠죠…
☞ 자주 사용되는 PL/SQL 블록을 캐싱한다.
- PL/SQL(프로시저,함수,패키지,트리거) 블록들은 너무 커서 실행시 마다 라이브러리 캐시영역
에 로드되었다가 다시 제거되는 현상이 반복 --> 라이브러리 캐시영역의 단편화 현상 발생
- PL/SQL 블록 , SEQUENCE 등을 라이브러리 영역에 캐싱할 수 있도록 DBMS_SHARED_POOL
패키지 제공.
SQL> select name,type,kept
from v$db_object_cache
where type in ('PACKAGE','PROCEDURE','TRIGGER','PACKAGEBODY');
NAME TYPE KEPT
------------------- --------- ------
<= 캐싱된 PL/SQL 블록 정보가 없다
SQL>connect scott/tiger
SQL>create or replace procedure check_swan <= 샘플 Procedure 생성
( v_emp_no in emp.empno%type )
is
begin
delete from emp where empno = v_emp_no ;
end check_swan ;
/
SQL>execute DBMS_SHARED_POOL.KEEP('CHECK_SWAN')
<= 해당 프로시저를 공유 풀 영역에 상주.
SQL> select name,type,kept
from v$db_object_cache
where type in ('PACKAGE','PROCEDURE','TRIGGER','PACKAGEBODY');
NAME TYPE KEPT
------------------- ------------- ------
CHECK_SWAN PROCEDURE Y <= 해당 프로지셔 캐싱
SQL>execute DBMS_SHARED_POOL.UNKEEP('CHECK_SWAN')
SQL> select name,type,kept
from v$db_object_cache
where type in ('PACKAGE','PROCEDURE','TRIGGER','PACKAGEBODY');
NAME TYPE KEPT
------------------- ------------- ------
CHECK_SWAN PROCEDURE N <= 해당 프로지셔 캐싱 해제
☞ 4-41 실습
아래의 쿼리를 이용하여 가장 빈번하게 사용되는 오브젝트를 찾을 수 있다.
select cache#, type, parameter, gets, getmisses, modifications mod
from v$rowcache
where gets > 0
order by gets;
CACHE# TYPE PARAMETER GETS GETMISSES MOD
------ ----------- ------------------ ---------- ---------- ------
7 SUBORDINATE dc_user_grants 1615488 75 0
2 PARENT dc_sequences 2119254 189754 100
15 PARENT dc_database_links 2268663 2 0
10 PARENT dc_usernames 7702353 46 0
8 PARENT dc_objects 11280602 12719 400
7 PARENT dc_users 81128420 78 0
16 PARENT dc_histogram_defs 182648396 51537 0
11 PARENT dc_object_ids 250841842 3939 75
row cache 튜닝은 매우 제한적이다. 최상의 솔루션은 V$ROWCACHE 의 결과를 기반으로 딕셔너리 접근을 줄이는 것이다. 예를 들어, 만일 시퀀스가 문제라면, 시퀀스를 캐슁하는 것을 고려할 수 있다. 여러 개의 테이블 조인을 포함하는 뷰 및 중첩된 뷰는 래치 경합을 증가시키게 된다. 일반적인 대안은 단순히 SHARED_POOL_SIZE를 증가하는 것이다.
기타>
LIBRARY CACHE LOCK WAIT EVENT가 나타날 때의 해결방법
====================================================
PURPOSE
-------
v$session_wait event 에 'library cache lock'이 발생하면서 session이
waiting 되는 경우가 있다. 오랫동안 이 현상이 지속될 경우 어떤 session이
이 library cache lock 을 갖고 있는지 확인해 볼 수 있다.
Explanation
-----------
v$session_wait view에 'library cache lock'이 나타날 수 있는 경우가
어떠한 것이 있는 지 알아보자.
예를 들어 다음과 같은 alter table 문장의 경우를 살펴보자.
ALTER TABLE x MODIFY (col1 CHAR(200));
X 라는 Table의 row가 많다면 위의 문장으로 인해서 모든 row의 값이
200 bytes로 update되어야 하므로 굉장히 오랜 시간이 걸리게 된다.
이 작업 중에는 Table에 dml이 실행되어도 waiting되는 데, 이런 때에
'library cache lock' 이라고 나오게 된다.
또 package가 compile 되는 중에는 다른 user가 같은 package 내의
procedure나 function 등을 실행시켜도 waiting이 걸리면서 library cache
lock 이나 library cache pin event가 나타난다.
이 library cache lock 을 잡고 있는 session을 확인해 보자.
1. waiting session의 session address 확인
library cache lock으로 waiting하고 있는 session의 sid를 찾아서
그 session address - v$session의 saddr column 을 확인한다.
2. 다음과 같은 sql로 해당 library cache lock을 잡고 있는 session을
확인할 수 있다.
아래의 SQL 중에서 'saddr_from_v$session' 부분에 위의 1번에서 찾은
Waiting session의 saddr 값을 입력한다.
단, 아래의 sql은 반드시 sys 또는 internal user에서 실행해야 한다.
SELECT SID,SERIAL#,USERNAME,TERMINAL,PROGRAM
FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);
3. 2번에서 확인한 blocking session에 의해 waiting하고 있는 session들도
확인할 수 있다. 2번에서 찾은 session의 saddr 값을 다음 sql에 대입하여
찾을 수 있다.
SELECT SID,USERNAME,TERMINAL,PROGRAM
FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);
4. blocking session이 왜 오래 걸리는 것인지, v$session_wait 를 다시
확인하거나 실행하고 있는 sql 문장이나 object 등을 v$sql view 등을 확인
해 보아야 한다. 작업이라면 종료될 때까지 기다릴 수 있겠지만, 비정상적인
경우 또는 waiting session을 위해서는 다음과 같이 kill 할 수도 있다.
alter system kill session 'SID, SERIAL#';
관련 자료
ora-4031_and_shared_pool_duration.pdf
관련 자료
첨부 파일
관련 자료
ORA-04031
(1) 에러 메시지
[ora11@localhost ~]$ oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
(2) 원인
새로운 SQL에 대해서 파스를 수행하려면 힙 영역에 새로운 빈 공간을 할당해야 하며 빈 공간을 할당하기 위해서는 프리 리스트로부터 필요한 크기의 프리 청크를 찾아야 한다. 그런데 프리 리스트를 검색하고도 프리 청크를 찾지 못한다면 Shared pool LRU 리스트로부터 사용 가능한 청크를 찾아서 사용하게 된다.
만약 LRU 리스트에서도 필요한 크기의 사용 가능한 청크를 찾지 못하면 ORA-04031 에러를 발생시키고 SQL 파스는 실패한다.
예) 새로운 SQL 파스를 위해서 256바이트의 공간이 필요하다고 가정
① Shared pool 래치를 획득하고 프리 리스트로부터 256바이트의 프리 청크를 검색한다. 이 과정에서 래치를 획득하지 못하면 latch: shared pool 대기 이벤트를 발생시키며 획득 가능할 때까지 대기한다.
② 256바이트의 프리 청크를 찾았다면 해당 청크를 익스텐트에 할당한다. 만약 프리 리스트에 256바이트 크기의 프리 청크가 없어서 찾지 못했다면 더 큰 크기의 프리 청크를 검색한다.
③ 만약 256바이트보다 더 큰 400바이트의 프리 청크를 찾았다면 400바이트 프리 청크를 필요한 크기의 256바이트와 나머지 144바이트 크기로 쪼갠다.
④ 필요한 크기로 쪼개진 256바이트 청크는 익스텐트에 할당하고 나머지 144바이트는 다시 프리 리스트에 등록되어서 관리된다.
⑤ 2번 단계에서 모든 프리 리스트를 검색하고도 256바이트보다 큰 프리 청크를 찾지 못했다면 Shared pool LRU 리스트로부터 핀이 해제된(재사용 가능한) 청크 중에서 256바이트 이상의 크기를 갖는 청크를 찾아서 프리 리스트로 등록하고 3번 단계부터 진행한다.
⑥ 5번 단계에서 Shared pool LRU 리스트를 검색하고도 필요한 크기의 청크를 찾지 못하면 "ORA-4031 unable to allocate %s bytes of shared memory" 에러를 발생시키고 SQL 파스는 실패한다.
프리 리스트로부터 프리 청크를 검색하고 할당받기까지의 모든 단계에서 shared pool 래치를 획득해야 한다. 그런데 청크 할당과 해제가 빈번하게 반복되면 청크는 더욱 더 작게 쪼개져서 관리되어야 할 청크 수가 증가한다. 이것은 프리 리스트를 검색하는 시간을 증가시키고 shared pool 래치에 대한 경합을 증가시켜서 성능 저하를 유발하거나 ORA-4031 에러를 발생시키게 된다.
(3) 임시 조치 방법
1) Shared pool 초기화 : 거의 해결 되는 경우가 없습니다!!!!!!
SQL> alter system flush shared_pool;
=> Shared pool 내의 연속된 메모리 조각들을 하나의 조각으로 합쳐주는 역할.
=> 다른 SQL 정보 또한 Shared pool에서 제거하므로 해당 명령어를 수행한 후에 모든 SQL이 하드파싱을 수행하게 되어 성능 저하가 발생할 수 있음.
2) 패치 등을 고려
ORA-4031 에러는 오라클 버그로 등록된 부분이 있으므로 해당 오라클 버전을 확인하여 오라클 패치 적용 및 업그레이드 등을 고려.
3) 파라미터 설정
SHARED_POOL_RESERVED_SIZE 파라미터 설정을 통해 에러 감소.
SQL> SELECT owner, name, type FROM v$db_object_cache
2 WHERE sharable_mem > 10000
3 AND (type=‘PACKAGE’ or type=‘PACKAGE BODY’ or
4 type=‘FUNCTION’ or type=‘PROCEDURE’)
5 AND KEPT=‘NO’;
OWNER NAME TYPE
----- -------- ------------
SYS STANDARD PACAKGE
SYS STANDARD PACAKGE BODY
SYS DBMS_UTILITY PACAKGE BODY
Keeping Large Objects
------------------------
Pin large packages in the library cache:
SQL> EXECUTE dbms_shared_pool.keep(‘package_name’);
SQL> SELECT owner, name, type FROM v$db_object_cache
2 WHERE sharable_mem > 10000
3 AND (type=‘PACKAGE’ or type=‘PACKAGE BODY’ or
4 type=‘FUNCTION’ or type=‘PROCEDURE’)
5 AND KEPT=‘NO’;
OWNER NAME TYPE
----- -------- ------------
SYS DBMS_UTILITY PACAKGE BODY
4) Large pool 설정
만약 병렬 프로세싱을 사용한다면 Large pool 설정으로 에러를 감소.
5) 커서 공유
.. OPEN_CURSORS
.. CURSOR_SPACE_FOR_TIME
.. SESSION_CACHED_CURSORS
과도한 설정시 4031 에러 발생하는 경우 발생
.. CURSOR_SHARING
CURSOR_SHARING 파라미터를 설정함으로써 커서를 공유하게 함.
- EXACT : 정확히 동일한 SQL문장인 경우만 커서를 공유. 기본값.
- FORCE : SQL 문장은 같으며 리터럴 변수 값만 틀린 SQL에 대해 커서를 공유.
- SIMILAR : 오라클이 실행계획을 판단해서 성능이 저하되지 않는다고 판단될 때 FORCE 설정과 동일하게 동작.
그러나 FORCE나 SIMILAR로 설정한 경우 원하지 않는 실행계획 변화로 인한 성능 저하를 유발시킬 수 있으므로 운영 단계에서는 적용이 어려움.
(4) 근본적인 조치 방법
1) 하드 파싱을 많이 발생시키는 원인이 되는 리터럴SQL을 찾아서 바인드 변수를 사용하도록 변경해야 함.
이미 개발이 완료되어 운영되고 있는 단계에서는 프로그램에서 수행되고 있는 SQL을 수정하기가 쉽지 않기 때문에, 미리 앞전에 개발 단계에서 하드 파스를 유발하는 SQL들을 찾아서 수정하는 것이 중요.
또한 개발자들에 대한 교육을 통해서 개발 시 바인드 변수를 사용하도록 해야 함.
2) Prepared Statement의 사용을 통해 JDBC 프로그램 내의 리터럴 SQL을 제거.
3) 실제 shared pool size 가 작아서 발생하는 경우도 있음
4) 운영중 발생시 db restart 하는게 대부분 임