86페이지

아래 5번과 관련해서 참고해서 볼 사항 

http://m.dbguide.net/knowledge.db?cmd=view&boardConfigUid=21&boardUid=168439

원본출처 : 

http://www.adminschool.net/wiki/doku.php?id=study:oracle:adv_owi_10g:oracle_internal_owi:buffer_cache_owi

Buffer Cache 란

1. 오라클은 물리적인 I/O를 최소화 하기 위해 최근에 사용된 블럭에 대한 정보를 메모리의 일정 부분에 보관하려 한다 ⇒ 이 영역을 Buffer Cache 라고함.

Buffer Cache 구조

1. Hash Chain 구조의 시작점은 Hash Table인데, Hash Table은 Hash Bucket으로 구성되어져 있다
2. 오라클 블럭(DBA: Data Block Address File# , Block# )과 블럭 클래스 에 대한 Hash 함수 결과 값으로 해당 Hash Bucket을 찾아가서 해당 데이터 블럭이 메모리에 존재 하는지를 확인 할 수 있는 것 이다
3. Hash Bucket에 딸린 Buffer Header는 Buffer에 대한 메타 정보만 가지고 있고 Buffer 메모리 영역의 실제 버퍼에 대한 포인터 값을 가지고 있다.
4. Hash Chain 구조는 Shared Pool 영역에 존재 하며 실제 버퍼에 대한 정보들은 버퍼 캐쉬 영역에 존재 한다
5. 특정 블럭에 대한 Scan을 할려고 하면 Cache buffer chains latch를 획득 해야만 한다. 
   ※ 9i부터 읽기 작업에 한하여  cache buffer chains latch를 shared 모드로 획득 한다
6. 다음과 같은 쿼리를 이용하여 cache buffer chains latch의 갯수를 확인가능 하다 
   

   SQL> SELECT COUNT(*) FROM v$latch_children WHERE name = 'cache buffers chains'
    
     COUNT(*)
   ----------
        65536

• cache buffer chains latch ⇒ hash 함수로 얻은 값으로 해당 데이터가 어떠한 bucket에 있는가를 확인할때 발생되는 latch

Working Set

• 오라클은 Buffer Cache에 대하여 효율적으로 사용하기 위해 두 종류의 LRU리스트를 사용한다.

1. LRU List : 가장 최근에 사용되거나 미사용된 버퍼들의 List
   1. Free(미사용) , 사용중이거나 사용된 버퍼 아직 LRUW로 옮겨지지 않은 Dirty 버퍼
   2. 메인 리스트 : 사용된 버퍼들의 리스트 , 핫 영역과 콜드 영역으로 구분 관리 됨
   3. 보조 리스트 : 프리 버퍼들의 리스트 , 미사용 된 버퍼나 DBWn에 의해 기록 된 버퍼 관리
2. LRUW List : 아직 디스크에 기록 되지 않은 Dirty한 버퍼들의 List
   1. 메인 리스트 : 변경된 버퍼들의 리스트
   2. 보조 리스트 : 현재 DBWR에 의해 기록중인 버퍼 리스트

• 다음 그림과 같이 LRU , LRUW를 합쳐서 하나의 Working Set 이라고 부른다.

• Working Set 개수는 Cache buffer lru latch 개수에 의해 결정( 하나의 working set을 하나의 lru latch가 관리함 )되며 X$KCBWDS( Kerner Cache Buffer Working sets Descriptors )를 조회 하면 확인 가능 하다
• LRU , LRUW List를 탐색 하고자 하는 프로세스는 반드시 cache buffer lru chain latch를 획득 해야 함.
• cache buffer chains lru latch ⇒ bucket의 header chain을 검색하여 블록이 버퍼캐시안에 없을때 lru를 뒤져 free buffer을 찾기위해 발생되는 latch

Touch Count

• 오라클은 개별 버퍼 마다 Touch Count를 관리 하며 , 프로세스에 의해서 스캔이 이루어 질때 마다 Touch Count를 1씩 증가시킴

1. Cold 영역의 꼬리에 있으면서 Touch Count가 1이하인 것이 Free Buffer로 사용된다.
2. Cold 영역의 꼬리에 있으면서 Touch Count가 2 이상인 블럭을 만나면 Hot영역의 head 부분으로 옮기고 Touch count를 0 으로 초기화 한다.
3. 핫 영역으로 옮기는 기준은 _DB_AGING_HOT_CRITERIA 파라메터 이며 기본이 2 이다.
4. 만약 Single블럭 I/O에 의해 읽혀진 블럭은 Mid-Point에 삽입되며 Touch Count는 1 이다.
5. Full table scan 이나 인덱스 Full Scan으로 읽혀진 데이터 들은 Mid-Point에 삽입되었다 바로 Cold 영역의 꼬리로 옮겨져서 버퍼 캐쉬에 머무를 확률이 작아 진다.

Buffer lock

• buffer 자체 보호 . 즉 , 버퍼의 내용을 변경하거나 읽으려고 하는 프로세스는 변경 작업이나 읽기 작업을 완료할때 까지 해당 버퍼 (정확하게 말하면 Buffer header)에 대해서 buffer lock을 exclusive 또는 shared 하게획득 해야 한다.

Buffer Cache 블록을 올리거나 버퍼캐시에 올라와있는 블록을 사용하고자 할때

1. sql문에 의해서 조건에 만족하는 데이터가 버퍼캐시에 존재 여부 확인하기 위해 DBA(data block address) + class(data, sort, undo)를 조합하고 > hash function을 돌려 해당 데이터가 어떠한 bucket에 있는가 확인(= DBA + class를 hash 함수로 돌린뒤 그값을통해 hash bucket를 찾음)
2. cache buffer chains latch를 획득(읽기는 shared, 변경은 exclusive)
3. hash bucket에서 관리하는 buffer header들(chain)을 검색하여 블록이 버퍼캐시안에 존재 하는지 확인
4. 버퍼캐시안에 없을경우 cache buffer lru chain latch를 획득함
5. LRU 보조 리스트를 통해 free buffer를 검색
6. LRU 메인리스트에서 free buffer을 검색
   1. 검색중 dirty블럭을 만나면 LRUW 리스트의 메인 리스트에 추가
   2. 검색중 touch count가 2이상인 것을 만나면 hot 영역의 머리부분으로 옮기고 touch count를 0으로 세팅
   3. 검색중 LRU 리스트를 40% 스캔을 했는데도 free block을 찾지 못하면 LRU 스캔을 멈추고 DBWR에게 dirty 버퍼를 파일에 기록할것을 요구(_DB_BLOCK_SCAN_MAX_PCT 파라미터에 의해 %를 정함)
   4. DBWn에 의해서 프리버퍼가 확보 될때까지 free buffer waits 이벤트 대기
   5. 요청받은 DBWn은 cache buffer lru chain latch를 획득 후 LRUW 리스트의 cold 영역 꼬리부터 탐색하여 기록함
   6. 기록된 블럭은 LRU 보조 리스트로 옮겨짐
7. free block를 찾으면 buffer lock을 exclusive하게 획득 후 데이터 파일로 부터 block를 load

Buffer Cache Dump

1. 버퍼의 구조를 가장 명확하게 알 수 있는 방법은 버퍼 캐시 덤프를 이용하는것 
   

   SQL> ALTER SESSION SET evnets 'immediate trace name buffers level 1';
   OR
   SQL> oradebug dump buffers 1

• 참고로 oradebug 를 추천한다 
  

1. X$BH 뷰에서 확인가능하다

• 눈여겨 봐야될 컬럼은 다음과 같다

컬럼	내용
HLADDR	해당 버퍼를 관장하는 래치의 주소 V$LATCH_CHILDREN.ADDR 과 조인해서 원하는 래치정보를 얻을수 있다
TS#	블록이 속한 테이블스페이스 번호 V$TABLESPACE.TS# 과 조인
DBARFIL	DBA 중 파일번호
DBABLK	DBA 중 블록번호
CLASS	블록 클래스
STATE	버퍼의 상태
0	FREE no valid block image
1	XCUR a current mode block, exclusive to this instance
2	SCUR a current mode block, shared with other instances(RAC환경에서만 쓰임)
3	CR a consistent read (stale) block image
4	READ buffer is reserved for a block being read from disk
5	MREC a block in media recovery mode
6	IREC a block in instance (crash) recovery mode
8	PI past image(RAC환경에서만 쓰임)
TCH	Touch Count

plustrace 권한 생성

conn / as sysdba

@?/sqlplus/admin/plustrce.sql

해당 사용자에게 plustrace 권한 부여

conn / as sysdba

grant plustrace to scott;

애앙 사용자로 로그인 후 plan table 생성

@?/rdbms/admin/utlxplan.sql

autotrace 모드 설정

set autotrace on

set autotrace traceonly

set autotrace traceonly explain

set autotrace traceonly statistics

SQL> desc v$session

 Name                                      Null?    Type

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

 SADDR                                              RAW(4)

 SID                                                NUMBER

 SERIAL#                                            NUMBER

 AUDSID                                             NUMBER

 PADDR                                              RAW(4)

 USER#                                              NUMBER

 USERNAME                                           VARCHAR2(30)

 COMMAND                                            NUMBER

 OWNERID                                            NUMBER

 TADDR                                              VARCHAR2(8)

 LOCKWAIT                                           VARCHAR2(8)

 STATUS                                             VARCHAR2(8)

 SERVER                                             VARCHAR2(9)

 SCHEMA#                                            NUMBER

 SCHEMANAME                                         VARCHAR2(30)

 OSUSER                                             VARCHAR2(30)

 PROCESS                                            VARCHAR2(24)

 MACHINE                                            VARCHAR2(64)

 PORT                                               NUMBER

 TERMINAL                                           VARCHAR2(30)

 PROGRAM                                            VARCHAR2(48)

 TYPE                                               VARCHAR2(10)

 SQL_ADDRESS                                        RAW(4)

 SQL_HASH_VALUE                                     NUMBER

 SQL_ID                                             VARCHAR2(13)

 SQL_CHILD_NUMBER                                   NUMBER

 SQL_EXEC_START                                     DATE

 SQL_EXEC_ID                                        NUMBER

 PREV_SQL_ADDR                                      RAW(4)

 PREV_HASH_VALUE                                    NUMBER

 PREV_SQL_ID                                        VARCHAR2(13)

 PREV_CHILD_NUMBER                                  NUMBER

 PREV_EXEC_START                                    DATE

 PREV_EXEC_ID                                       NUMBER

 PLSQL_ENTRY_OBJECT_ID                              NUMBER

 PLSQL_ENTRY_SUBPROGRAM_ID                          NUMBER

 PLSQL_OBJECT_ID                                    NUMBER

 PLSQL_SUBPROGRAM_ID                                NUMBER

 MODULE                                             VARCHAR2(64)

 MODULE_HASH                                        NUMBER

 ACTION                                             VARCHAR2(64)

 ACTION_HASH                                        NUMBER

 CLIENT_INFO                                        VARCHAR2(64)

 FIXED_TABLE_SEQUENCE                               NUMBER

 ROW_WAIT_OBJ#                                      NUMBER

 ROW_WAIT_FILE#                                     NUMBER

 ROW_WAIT_BLOCK#                                    NUMBER

 ROW_WAIT_ROW#                                      NUMBER

 TOP_LEVEL_CALL#                                    NUMBER

 LOGON_TIME                                         DATE

 LAST_CALL_ET                                       NUMBER

 PDML_ENABLED                                       VARCHAR2(3)

 FAILOVER_TYPE                                      VARCHAR2(13)

 FAILOVER_METHOD                                    VARCHAR2(10)

 FAILED_OVER                                        VARCHAR2(3)

 RESOURCE_CONSUMER_GROUP                            VARCHAR2(32)

 PDML_STATUS                                        VARCHAR2(8)

 PDDL_STATUS                                        VARCHAR2(8)

 PQ_STATUS                                          VARCHAR2(8)

 CURRENT_QUEUE_DURATION                             NUMBER

 CLIENT_IDENTIFIER                                  VARCHAR2(64)

 BLOCKING_SESSION_STATUS                            VARCHAR2(11)

 BLOCKING_INSTANCE                                  NUMBER

 BLOCKING_SESSION                                   NUMBER

 FINAL_BLOCKING_SESSION_STATUS                      VARCHAR2(11)

 FINAL_BLOCKING_INSTANCE                            NUMBER

 FINAL_BLOCKING_SESSION                             NUMBER

 SEQ#                                               NUMBER

 EVENT#                                             NUMBER

 EVENT                                              VARCHAR2(64)

 P1TEXT                                             VARCHAR2(64)

 P1                                                 NUMBER

 P1RAW                                              RAW(8)

 P2TEXT                                             VARCHAR2(64)

 P2                                                 NUMBER

 P2RAW                                              RAW(8)

 P3TEXT                                             VARCHAR2(64)

 P3                                                 NUMBER

 P3RAW                                              RAW(8)

 WAIT_CLASS_ID                                      NUMBER

 WAIT_CLASS#                                        NUMBER

 WAIT_CLASS                                         VARCHAR2(64)

 WAIT_TIME                                          NUMBER

 SECONDS_IN_WAIT                                    NUMBER

 STATE                                              VARCHAR2(19)

 WAIT_TIME_MICRO                                    NUMBER

 TIME_REMAINING_MICRO                               NUMBER

 TIME_SINCE_LAST_WAIT_MICRO                         NUMBER

 SERVICE_NAME                                       VARCHAR2(64)

 SQL_TRACE                                          VARCHAR2(8)

 SQL_TRACE_WAITS                                    VARCHAR2(5)

 SQL_TRACE_BINDS                                    VARCHAR2(5)

 SQL_TRACE_PLAN_STATS                               VARCHAR2(10)

 SESSION_EDITION_ID                                 NUMBER

 CREATOR_ADDR                                       RAW(4)

 CREATOR_SERIAL#                                    NUMBER

 ECID                                               VARCHAR2(64)

임의의 세션 추적

v$session을 통해 SID와 SERIAL#을 구함

SQL> select username, sid, serial# from v$session;

USERNAME                              SID    SERIAL#

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

                                        2          1

                                        3          1

                                        4          1

                                        5          1

                                        6          1

                                        7          1

                                        8          1

                                        9          1

                                       10          1

                                       11          1

                                       12          1

USERNAME                              SID    SERIAL#

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

                                       13          1

                                       14          1

                                       15          1

                                       16          1

                                       17         13

                                       18          5

                                       20          3

                                       21          1

                                       23          1

                                       25          9

                                       28         21

USERNAME                              SID    SERIAL#

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

                                       34          3

                                       35          3

SCOTT                                  41         51

SYS                                    43        147

                                       53         29

27 rows selected.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE

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

user_dump_dest                       string      /app/oracle/diag/rdbms/testdb/

                                                 testdb/trace