7장 파싱과 최적화 (by feelie) [2015.11.26]
SQL 의 이해
UPDATE T1
SET SMALL_NO = SMALL_NO + 0.1
WHERE ID = 3
AND SMALL_NO = 1
- Parsing - 문장의 의미를 이해하기 위해 오라클이 수행해야 하는 일은 얼마나 될까?
- Optimizing - 문장을 실행하기 위한 최선의 방법을 알아내기 위해 오라클이 수행해야 하는 일은 얼마나 될까?
- 파싱 = Syntactic(구문) 분석 + Semantic(의미) 분석
- Syntactic(구문) 분석
- SQL 문이 유효한지 결정
- Semantic(의미) 분석
- T1 의 오브젝트유형은 무엇인가? 테이블, 뷰, 시노님 등
- T1 오브젝트에 ID 와 SMALL_NO 칼럼이 존재 하는가 ?
- SMALL_NO 칼럼에 UPDATE 시 체크해야 할 제약조건이 존재 하는가 ?
- 해당 오브젝트에 UPDATE 시 수행되야 할 트리거가존재하는가 ?
- 오브젝트가 UPDATE 될 때 관리돼야 할 인덱스가 존재하는가?
- 해당 유저가 오브젝트를 UPDATE 할 권한을 가지고 있는가 ?
- Syntactic(구문) 분석
- ID 또는 SMALL_NO(또는 양쪽) 컬럼에 인덱스가 존재하고 , 도움이 되는가?
- 테이블, 인덱스 및 칼럼에 대한 통계정보가 존재하는가 ?
이후 옵티마이저는 시스템에 대한 몇가지 정보(파라미터, 시스템통계정보)를 수집하고, 실행계획을 수립
- 11g에서 문장을 파싱하기 위해 수행되는 전체적인 일을 증명
SQL> create table t1 ( id number, small_no number ) ;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool ;
System altered.
-- 이전 SQL 문장의 라이브러리 캐시로부터 제거
-- 오브젝트에 대한 권한 및 종속성, 오브젝트 정의를 딕셔네리 캐시로부터 제거
SQL> alter session set sql_trace = true ;
Session altered.
SQL> update t1 set small_no = small_no + 0.1
2 where id = 3 and small_no = 1 ;
0 rows updated.
1 session in tracefile.
2 user SQL statements in trace file.
-- ( 유저가 수행한 1) alter session... 2) update 문장 )
15 internal SQL statements in trace file.
-- ( sys recursive 문장 : DBMS 가 Update 문을 이해(Parse) 및 최적화하기 위한 정보를 수집)
17 SQL statements in trace file.
17 unique SQL statements in trace file.
737 lines in trace file.
- 1차 테스트 TRACE 내용
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15 0.01 0.00 0 0 0 0
Execute 99 0.06 0.05 0 0 0 0
Fetch 189 0.00 0.01 0 338 0 633
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Total 303 0.10 0.07 0 338 0 633
-- 15개의 SQL문에 대한 parsel call 15회 수행
-- execute call 99 회 수행,
-- fetch call 189 회 수행을 통해서 633 Row 추출
Misses in library cache during parse: 15
-- Miss 발생은 자식 커서(실행계획)가 라이브러리 캐시 내에 존재 하지 않음 의미
-- Recursive SQL 문의 첫 번째 parse call 시에 최적화 필요함
- 2차 테스트 TRACE 내용(테이블을 동일하게 생성 후 반복 수행 )
SQL> create table t1a ( id number, small_no number ) ;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T1');
-- PL/SQL procedure successfully completed.
-- 이후 동일한 테스트 수행 (Shared Pool Flush 하지 않는다.)
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15 0.00 0.00 0 0 0 0
Execute 43 0.01 0.01 0 0 0 0
Fetch 51 0.00 0.00 0 135 0 526
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Total 109 0.00 0.00 0 133 0 526
Misses in library cache during parse: 0
-- 1차 테스트와 동일한 Parse call= 15 발생
-- 그러나, execute 와 fetch call 횟수가 다름
-- Miss 0 발생은 이미 적절한 커서가 라이브러리 캐시 내에 존재
-- SQL 문장이 참조하는 Object 정의 찾는 Recursivce SQL
selectobj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
andlinkname is null and subname is null
-- SQL 문장이 참조하는 테이블 정의 찾는 Recursivce SQL
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,
t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),
nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
ts.logicalread
from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
-- SQL 문장이 참조하는 인덱스 정의 찾는 Recursivce SQL
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i. obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
-- SQL 문장이 참조하는 칼럼 정의 찾는 Recursivce SQL
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from col$ where obj#=:1 order by intcol#
-- 첫번째 테스트는 다양한 "bootstrap" 활동
-- ( 오라클은 테이블과 인텍스에 대한 정보를 확인하기 위해서， internal 테이블과 인텍스에
-- 대한 몇 가지 정보를 더 확인할 필요가 있다. )
-- 두번째 테스트는 첫번째 테스트에서 캐시된 정보를 이용 (메타데이터와 코드의 캐싱을 의미함)
-- 메타데이타 - row cache : 테이블, 인덱스, 칼럼, 히스토그램, 트리거 등과 같은 오브젝트 정보
-- 코드 - 라이브러리 캐시 : 명령어(SQL문과 PL/SQL), 커서 및 오브젝트 종속성과 권한 정보
- 사용자 프로그램 관점
- 커서는 일반적으로 결과 집합 의미, 라이브러리 캐시 내에 저장된 "실행 가능한" 명령어를 참조하는 배열의 인덱스이며, 사용자 프로그램 데이터와 라이브러리 캐시 정보를 연결하는 역할
- 인스턴스 관점
- 명령어가 수행될 때 세션의 프로세스 메모리 내에 저장되는 일시적인 데이터 의미
( 상태에 대한 정보와 결과집합의 일부 포함) - 라이브러리 캐시 내에 저장된 SQL 문의 실행 가능한 버전 의미
- 명령어가 수행될 때 세션의 프로세스 메모리 내에 저장되는 일시적인 데이터 의미
- 하나의 명령어(SQL)가 여러 개의 실행 계획 가질 때 발생 ( v$sql_shared_cursor )
- Place-holder : parent cursor, 개별 실행 계획 - child cursor
**[참고 Cursor 정보]
▶ Heap 0 ( Cursor 기본정보 )
▶ Heap 6 ( Cursor 실행계획정보 )
- Place-holder : parent cursor, 개별 실행 계획 - child cursor
딕셔너리 캐시( 로우캐시 )
- 오브젝트 정의를 저장 하는 곳
- 관련 뷰 :v$rowcache =v$rowcache_parent + v$rowcache_subordinate
SQL> select cache#, parameter, type, subordinate#, count, usage, fixed, gets ,getmisses
from v$rowcache order by cache#, type, subordinate# ;
CACHE# PARAMETER TYPE SUBORDINATE# COUNT USAGE FIXED GETS GETMISSES
---------- -------------------------------- ----------- ------------ ---------- ---------- ---------- ---------- ----------
0 dc_tablespaces PARENT 10 10 0 17216006 87
1 dc_free_extents PARENT 0 0 0 0 0
2 dc_segments PARENT 926 926 0 2761891 47480
3 dc_rollback_segments PARENT 25 25 1 3051290 24
4 dc_used_extents PARENT 0 0 0 0 0
5 dc_tablespace_quotas PARENT 0 0 0 220 32
6 dc_files PARENT 11 11 0 43505 44
7 dc_users SUBORDINATE 0 5 5 0 243337 145
7 dc_users SUBORDINATE 1 33 33 0 1509468 431
7 dc_users SUBORDINATE 2 0 0 0 0 0
8 dc_objects PARENT 1849 1849 116 13042484 168133
8 dc_object_grants SUBORDINATE 0 10 10 0 34906 8087
- COUNT : Cache Entry 총수 ( 1849 )
- USAGE : 유효한 데이터를 가지고 있는 캐시 Entry 수 ( 1849 )
- GET : 항목을 찾기 위해 리스트를 검색한 횟수 ( 13042484 )
- GETMISSES : Cache Miss를 발생시키는 데이터 요구 횟수 ( 168133 )
- 딕셔너리 캐시의 로우들은 Parent 와 Subordinate(종속된) 두 개의 그룹으로 구분
예) dc_objects(테이블 정보), dc_object_grants(grant 정보)
SQL 수행을 위해 필요한 OBJECT 정보
Select count(*) from all_objects ;
- 쿼리 수행 시, 필요한 세 개의 all_objects 항목
*1. Sys의 view
*2. Public synonym
*3. 필자의 스키마에 의해 소유된 non-existence
non-existence : 데이터 딕셔너리에는 존재하지 않지만 딕셔너리 캐시에는 존재
Non-existent 오브젝트
SQL> create or replace view my_objects as select * from user_objects ;
View created.
SQL> select referenced_owner, referenced_name, referenced_type
2 from user_dependencies
3 where name ='MY_OBJECTS';
REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE
------------------------------ ------------------------------ -----------------
PUBLIC USER_OBJECTS SYNONYM
OPS$ORACLE USER_OBJECTS NON-EXISTENT
# 현재 스키마에 속하지 않는 user_objects 라고 불리는 실제 오브젝트에 좌우됨
# 만일 user_objects 라는 실제 오브젝트를 생성하면,
오라클은 non-existent 에 대한 종속성 제거(이로써 my_objects뷰 정의 무효화)
11g)
select referenced_owner, referenced_name, referenced_type
from user_dependencies
where name = 'MY_OBJECTS'
/
REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE
-------------------- -------------------- ------------------------------
PUBLIC USER_OBJECTS SYNONYM
오라클이 메모리 내의 오브젝트 리스트 유지 방법 :
- 버퍼 캐시, 라이브러리 캐시 및 euqueue 와 같은 방법
- 해시 체인 래치들의 집합, 해시 체인 및 링크드 리스트를 이용해 관리
추론 근거 : V$ROWCACHE_PARENT 와 V$ROWCACHE_SUBORDINATE 뷰에 HASH 칼럼
딕셔너리 캐시 구조
: v$rowcache뷰의 한 로우와 v$rowcache_parent 및 v$rowcache_subordinate뷰 관계로 이해
[그림 7-1] 딕셔너리 캐시의 subset
- 각 래치는 하나의 부모(parent)유형과 관련된 모든 종속(subordinate)유형을 관리
- 부모 유형과 종속 유형은 별로 관리(x$kqrpd, x$kqrsd)
- 버킷 내의 부모 항목 간에, 그리고 부모 항목과 종속 항목간에 이중 링크드 리스트연결
딕셔너리 캐시 메모리 크기 확인
SQL> select * from v$sgastat where name like '%KQR%' order by name
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KQR ENQ 90816
shared pool KQR L PO 694960
shared pool KQR L SO 125992
shared pool KQR M PO 2199792
shared pool KQR M SO 549968
shared pool KQR S SO 26128
shared pool KQR X PO 30840
7 rows selected.
- KQR :rowcache 와 관련
- ENQ : row cache enqueues 일 것이다.
- X/L/M/S 는 e(X)tra large / (L)arge / (M)edium / (S)mall
- PO/SO 는 부모 오브젝트(x$kqrpd), 종속 오브젝트(x$kqrsd)를 의미
딕셔너리 캐시 활동성
딕셔너리 캐시 내의 항목을 찾기 위해 해시 체인을 탐색하는 동안에 해시 체인이 변경되지 않도록 보호해야 한다.
딕셔너리 캐시 활동의 핵심은 해시 버킷과 해시 체인에 근거하며, 해시 체인은 읽기/쓰기 충돌로부터 보호되어야 함
딕셔너리 캐시 및 이와 과련된 래치 활동성을 관찰
테스트 주요 SP 설명
Snap_latch_child – v$latch_children뷰의 스냅샷 제공
Snap_rowcache -- v$rowcache뷰의 스냅샷 제공
CASE1) Literal SQL 에 1000번 수행
결과 
- 딕셔너리 캐시를 한 번 획득하면 이와 관련된 래치는 세 번 획득 해야 함 ( dc_segments : 2000번, 1FE4846C : 6000번)
- 테이블과 인덱스 액세스 하므로 dc_segments를 두번 액세스
- 인증을 위해서 쿼리 마다 dc_users 한번 액세스
- dc_objects 와 dc_objects_ids 는 총 4000번 액세스(테이블(1000번), 인덱스(3000)번)
- dc_histogram_defs를 2000번 액세스(쿼리내에서 참조하는 칼럼에 액세스만큼, n1,t1)
=> 불필요한 컬럼을 조회 시 dc_histogram_defs를 액세스하기 위한 불필요한 비용 발생
CASE2) bind변수 SQL 에 1000번 수행
CURSOR_SHARING
- CURSOR_SHARING : exact(기본설정값), force, similar 로 설정
- force : 해당 문장을 최적화하기 전에 상수를 바인드 변수로 자동 치환
- similar : force와 동일하게 수행, 다시 최적화를 수행할 수도 있다
- 범위 조건인 경우
- 조건 컬럼에 히스토그램이 존재하는 경우
- 조건 컬럼이 파티션 테이블의 파티션 키인 경우
- adaptive cursor sharing (11g)
Parse Call 이란
Parsing은 Parsing,Parse call로 분리
이를 분리해야 하는 이유는
- Parse call 은 항상 발생 하지 않는다.
- PL/SQL cursor cache 효과
- 최적화를 위해서 반드시 parse call 이 필요한 것은 아니다.
- Parsing 을 위해서 반드시 parse call 이 필요한 것은 아니다.
- Parsing 과 최적화는 parse call 뿐만 아니라 execute call 단계에서도 발생
Name (literal string test) value
-
parse count (total) 1,004
parse count (hard) 1,001
Name (bind variable test) value
-
parse count (total) 5
parse count (hard) 2
- Parse count(total) : 오라클커널에 의해 수행된 parse call 횟수 = SQL 수행 횟수
Bind variable test 시에는 PL/SQL cursor cache 효과(11g 이상 시만 )에 의해서 parse call 획기적으로 줄어듦
Name (bind variable test 9i) value
-
parse count (total) 1,004
parse count (hard) 2
- Parse count(Hard) : 오라클이 문장을 최적화 한 횟수 = 최적화 횟수
- 문장에 대한 최적화(Parse count(hard)) 없이 parse call(parse count(total) 수행 가능
- 루프 내의 첫 번째 실행 시에, 오라클은 해당 문장에 대한 파싱 및 최적화 수행 후, 라이브러리 캐시 적재
- 루프 내의 두 번째 실행 시에, 오라클은파싱 및 최적화를 시작하기 전에 라이브러리 캐시를 검색하고, 동일 문장 존재 시, 최적화를 수행 하지 않고 재사용
cursor authentication
: 자식 커서의 내용을 비교 (동일 오브젝트여부/동일 권한여부/옵티마이져 환경)
커서 캐싱
: 커서 캐싱을 디폴트(11g : 50)과 0으로 설정 후, dbms_sql 캐시지를 이용한 명시적 parse 와 execute call 수행
fori in 1..1000 loop
m_cursor := dbms_sql.open_cursor;
dbms_sql.parse(
m_cursor,
'select n1 from t1 where id = :n',
dbms_sql.native
);
dbms_sql.define_column(m_cursor,1,m_n);
dbms_sql.bind_variable(m_cursor, ':n', i);
m_rows_processed := dbms_sql.execute(m_cursor);
ifdbms_sql.fetch_rows(m_cursor) > 0 then
dbms_sql.column_value(m_cursor, 1, m_n);
end if;
dbms_sql.close_cursor(m_cursor);
end loop;
- Parse count(hard) 수치(=최적화 횟수)는 작다 ==> 루프 내 문장 재사용
- 세션 캐시에 따른 커서에 액세스 방법 차이, 경합 차이 발생 시킴 ( 래치 gets 수치 차이 유발 )
세션 커서 캐시 : SQL 자주 수행 = 문장 커서에 KGL lock 설정, 세션 메모리 내에 state object 를 생성 후, 해당 커서와 연결
라이브러리 검색 없이 문장 사용
커서 캐싱 동작 방식
- 첫 번째 실행 시 최적화 수행
- (첫 수행 세션과 다른 세션도 가능함)두 번째 실행 시 인증 수행 ( cursor authentication = child cursor 비교작업 )
- 세 번째 실행이 완료된 이후에 캐싱
- 이후부터 캐싱된 정보 사용
세션 커서 캐시에 존재한다는 것은 parse call 시에(문장 execute 시에) 어떠한 Parsing (최적화)도 수행 하지 않는다는 것 의미
- adaptive cursor sharing
- 세션 커서 캐시에 유지된 퀴리는 재 최적화가 발생할 수 있다
- PL/SQL 커서는 adaptive cursor sharing 기능을 사용하지 않는다
커서 홀딩
문장을 자주 사용한다면, 커서 변수 선언하고, 원하는 만큼 오랫동안 커서를 open 하는것이 바람직
m_cursor := dbms_sql.open_cursor;
dbms_sql.parse(
m_cursor,
'select n1 from t1 where id = :n',
dbms_sql.native
);
dbms_sql.define_column(m_cursor,1,m_n);
fori in 1..1000 loop
dbms_sql.bind_variable(m_cursor, ':n', i);
m_rows_processed := dbms_sql.execute(m_cursor);
ifdbms_sql.fetch_rows(m_cursor) > 0 then
dbms_sql.column_value(m_cursor, 1, m_n);
end if;
end loop;
dbms_sql.close_cursor(m_cursor);
- 래치 획득 수치가 수백번 정도 ( VS 이전 수천번 )
execute call 시 파싱과 최적화 발생 할 경우
: PL/SQL 최적화로 인한 홀딩(Holding) 커서의 부작용
- 홀딩 커서는 내부적으로 라이브러리 캐시 내의 자식 커서와 부모 커서와 연결되어 있다.
- 다른 세션에서 라이브러리 캐시 메모리를 요구하면, 자식 커서의 대부분을 메모리에서 제거(세션이 커서 홀딩하여도 )
- 이 시점에 문장을 재수행 하면, parse count(total) (ql 수행 횟수 통계 수치)는 증가하지 않음에도 불구하고 Miss in library cache during exeucte 가 기록되고, parse count(hard)=최적화 통계가 증가한다.
결과적으로 parse count(total) < parse count(hard )
라이브러리 캐시
: 캐시 버퍼 체인과 라이브러리 캐시 체인은 근본적으로 동일한 목적 제공
즉, 특정한 항목의 위치를 매우 빠르게 찾는 방안 제공
- 버퍼 캐시
- repl_main = 현재 사용중인 버퍼들의 집합
- repl_aux = 디스크로부터 블록을 읽거나 CR복제본 생성하려고 할 때, 재사용할 수 있는 버퍼
- (Shared Pool) LRU 리스트,메모리 Free List과 Shared Pool 간의 유사성
- 버퍼 캐시는 working data set 으로 나눠지고,
Shared Pool 은 다수의 서브 힙(Sub Heap)으로 나뉠수 있음 - Work data set 은 x$kcbwds 구조의 한 로우로 정의 되고
각 서브 힙은x$kghlu 구조의 한 로우로 정의 - 버퍼 캐시는 Main 과 AUX 리스트로
Shared Pool 은 LRU 리스트와 Free 리스트로 나눠진다. - 버퍼 캐시(MAIN)는 LRU(Least recently used)리스트를 소유하고 있으며
새로운 항목을 추가 할 때 midpoint insertion 알고리즘 사용
Shared Pool도 LRU 리스트를 소유하고 있으면 새로운 항목의 입력 위치는
리스트 내의 reccrrent 와 transient 부분의 경계지점 - 버퍼 캐시는 keep, recycle, default 세 개의 영역
Shared Pool 은 11g 부터"지속기간" 에 따라 네 영역으로 나뉨
- (Shared Pool) LRU 리스트,메모리 Free List과 Shared Pool 간의 차이점
- 버퍼 캐시는 동일한 크기의 청크로 구성
Shared Pool은 서로 다른 크기의 청크로 구성
!많은 lru리스트.png!
Shared Pool의 구조
- 여러 개의 그래뉼(granule)로 구성
- SGA가 충분히 크다면 Shared Pool은 서브-풀(Sub-Pool)로 나뉠수 있음
- 각 서브-풀을 서로 다른 크래뉼로구성
- 11g 에서는 각 서브-풀은 "지속시간"에 따라 네 개의"sub-sub-pool"로 나뉨
- oradebug dump heapdump 2; 명령어 수행 결과
- 11g 에서는 각 서브-풀은 "지속시간"에 따라 네 개의"sub-sub-pool"로 나뉨
Shared Pool(sub Pool이나 지속기간에 따라) 나누는 이유
- 초장기 하나의 프로세스가 하나의 서브-풀만 이용하는 부작용으로 인해
다른 서브 풀에 충분한 메모리가 존재하여도 ORA-4031: unable to allocate %n bytes of memory 발생 - ORA-4031 발생 최소화
- 예약풀(reserved pool): 큰 메모리 청크를 필요로 하면 먼저 여유 공간 확인 후 바로 예약 공간을 확인
- 표준화(Standadization): 딕셔너리 캐시 데이터를 위한 메모리 청크 사이즈를 표준화 시도 ( 잘게 쪼개짐 방지 = Fragmentation 방지 )
- 지속기간(Duration) : 업무 성격에 따른 Shared Pool 나눔, 단편화를 유발할 가능성이 높은 업무를 분리할 가능성도 높아짐
Shared Pool 구조의 우수성
익스텐트 리스트
- 익스텐트 덤프 결과는 각 인스텐드를 순서대로 나열하고, 라인당 하나의 메모리 청크를 주소 순으로 출력
- 클래스가 free 인 청크는 shared pool 프리 리스트에 존재
- 클래스가 R-Free 인 청크는 Shared Pool 예약 프리 리스트에 존재
- 클래스가 R-Freeable청크는"Stoppers" 용도로 사용 R-free 와 인접한 다른 청크와 구별
- 클래스가 freeable 은 free 상태로 전환될수 있는 메모리
- 클래스가 recreate 는 free 상태로 전환될수 있는 메모리 단, Destory call 수행 필요
- freeable 청크는 recreatable 청크에 연결되어 있음
- recreatable 청크의 owner는 recreatable 청크를 free 상태로 전환시킬때, 해당 청크와 연결된 freeable 청크도 free 상태로 전환
프리 리스트
- 프리 리스트 개수가 많은 이유
- 다양한 크기의 메모리 청크가 존재
- 짧은 링크드 리스트 검색 위해서
- 적절한 청크 크기를 검색 하기 위해서
- shared pool은 best 청크를 제공한다
- 80 bytes의 청크가 필요하고 free 리스트에 80 bytes의 bucket 존재시 해당 bucket 사용
- 184 bytes의 청크가 필요하지만, free 리스트에는 정확히 일치하는 bucket이 미존재할 경우
184 bytes의 청크를 수용할 수 있는 가장 근접한 bucket 를 사용 - 가장 근접한 bucket 사용시 여분의 사이즈가 16 bytes 이상이면 잘라서 사용하고, 나머지는 프리리스트로 등록
- 모든 청크에 대한 map 관리
1. 맵을 이용하여 인접한 메모리 청ㅋ가 이미 프리 상태인지 체크
2. 프리 상태라면, 인접한 메모리 청크를 프리 리스트에서 제거
3. 프리 상태로 전환된 청크와 인접한 메모리 청크를 병합
4. 병합된 청크를 프리 리스트에 등록
LRU 리스트(들)
Shared Pool 내에는 permanent, recreatable 및 freeable 청크가 존재 하지만
LRU 리스트(들)에 존재하는 것은 recreatable 청크 뿐이다.
리스트 하단부를 보면 recreatable 청크의 개수는 12,085 개이고, 이 중 3,921 개는 recurrent(rcr)이고 8,164 개는 transient(trn)라는 것을 알 수 있다.
Tansient 는 separator 앞부분에 위치, recurrent 는 separator 뒷 부분에 위치
새로운 항목이 LRU 리스트에 추가되면 Transient 리스트의 헤드에 등록
해당 항목이 한번 이상 사용되면 recurrent 리스트의 헤드로 이동
결과적으로 자주 사용하지 않는 오브젝트들은 transient 리스트에 위치, 자주 사용하는 오브젝트들은 recurrent 리스트에 위치
Shared Pool 메모리 획득에 대한 시나리오
(시나리오1) 운이 좋은 경우
세션은 shared pool 래치를 획득한 후 버킷을 검색 하여 원하는 크기의 청크를 찾는다면,
해당 청크를 프리 리스트에서 분리한 후, transient 리스트 헤더에 등록 그리고 청크에 핀(pin)을 설정하고,
shared pool 래치를 릴리즈, 만일 적당한 크기의 프리 청크를 못 찾는다면,
더 큰 크기의 청크 검색 하여 필요한 만큼만 자르고 나머지를 프리 리스트에 등록
만일 해당 버킷에서 적절한 청크를 찾지 못한다면,적절한청크를 찾을 때까지 다음 버킷으로 이동하여 청크를 검색 반복
(시나리오2) 운이 없는 경우
적당한 프리 청크를 찾지 못한다면
LRU 리스트의 꼬리(tail)의 끝으로 이동한 후 몇 개의 청크를 리스트로부터 분리한 후 프리(free)상태로 표시
그리고 이들 청크를 적절한 프리 리스트에 등록, ( 만일 해당 청크가 기존의 프리청크에 인접한 경우라면, 프리 리스트에 등록하기 전에 병합(Coalesce) 수행 )
그런 후에, 적절한 프리청크를 찾기 위해 다시 프리 리스트 버킷을 검색
(시나리오4) 큰 청크 할당
_shared_pool_reserved_min_alloc 값(기본 설정 4,400bytes)보다 큰 청크 요청 경우
운이 좋은 경우와 동일하게 작동하고, 프리청크를 찾지 못한다면, 예약된 프리 리스트 검색하고, 적절한 프리 청크를 찾지 못하면 LRU 리스트에서 몇 개의 청크를 분리한 후 프리청크를 병합
ORA-4031
시나리오 2와 3에서 본 것과 같이 ORA-4031 에러는 프리 리스트에서 적절한 프리청크를 찾지 못하고,
LRU 리스트를 검색하여(오라클 노트에 따르면 5회까지 수행) 기존의 청크를 분리한 후에도 적절한 프리청크를 생성하지 못할 때 발생
예방 : bind 변수 사용, dbms_pool.keep(인스턴스 시작시 라이브러시 캐시에 고정)
파싱과 최적화
- 동일한 문장에 대한 최적화는 단 하나의 세션에서만 수행되도록 보장
- Shared Pool의 프리 리스트로 부터 메모리를 획득하고, LRU 리스트에 부모(Parent)커서와 자식(child)커서 정보를 포함한 청크들을 등록
최적화 완료 전까지 , 부모커서와 자식커서에 exclusive 모드로 Pin 설정
( 이 때 최신 버전 DB 는 cursor : pin S wait on X 대기, 이전 버전 DB는 library cache pin 대기 )
- 자식 커서에 바인드 변수, 옵티마이저 환경, 종속성 리스트를 가짐(분기 사유)
- 부모 커서를 통해서 자식 커서에 접근 ( cursor_sharing=similar 위험성 )
EXECUTING, LOCKING, PINNNG
- Executing
- 자식커서가 존재하고, 라이브러리 캐시 해시 버킷ㅇㄹ 찾고, 체인을 탐색중에 실행계획이 메모리에서 밀려날 수 있다
- 문장에 대한 최적화를 다시 수행
- parse count(hard) 통계수치가 증가
- Misses in library cache during execute 증가
- v$librarycache의 reloads 값을 통해 확인 가능
- Locking
- 쿼리를 실행하는 동안에는 메모리에서 밀려나지 않도록 힙 0에 대하여 library cache lock을 설정
- Pinnging
- 힙 6에 대하여 library cache pin을 설정
*문장 탐색, 실행 비용을 최소화하는 오라클 전략
1. 세션이 KGL pin을 캐시 할 수 있도록 허용 (10.2)
2. KGL pin, lock을 페이지 단위로 할당 (11g)
- 뮤텍스
래치- 자세한 통계(정보) 제공
뮤텍스- 단순 통계(정보) 제공, 그만큼 빠르다.