About DATABASE

출처 :  http://blog.naver.com/bj2118/80069059099

shared pool 의 dump한결과

-Memory :공간 요청시 chunk 단위로 할당

-Freelist : Free chunk의 목록들

-LRU list : 현재 사용중이지 않는 재생성 가능한 chunk목록들

-Reserved Freelist : 최상위 Heap에만 존재, shared pool의 예약 영역에 대한 Freelist

-Freelist에서 원하는 크기의 chunk검색 -> LRU list 검색 -> 특정 크기 이상이면 Reserved list 검색

 ->모두 실패하면 ora-4031에러 발생

아래와같이 dump파일을 텍스트 문서로 하면 처음볼수있는건 시스템에 기본정보들을 볼수 있다.

/u01/app/oracle/admin/orcl/udump/orcl_ora_5836.trc                                                 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production                             
With the Partitioning                                                                              
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1                                                  
System name: Linux                                                                                 
Node name: localhost.localdomain                                                                   
Release: 2.6.9-42.0.0.0.1.EL                                                                       
Version: #1 Sun Oct 15 13:58:55 PDT 2006                                                           
Machine: i686                                                                                      
Instance name: orcl                                                                                
Redo thread mounted by this instance: 1                                                            
Oracle process number: 22                                                                          
Unix process pid: 5836

shared pool의 heap구조의 Top heap에 해당하는부분에 정보이다.

******************************************************                                             
HEAP DUMP heap name="sga heap"  desc=0x2000002c          <-----------  Top heap            

extent sz=0x32c8 alt=108 het=32767 rec=9 flg=-126 opc=0                                           
 parent=(nil) owner=(nil) nex=(nil) xsz=0xfd0                                                      
*****************************************************

HEAP DUMP heap name="sga heap(1                                                                    
 extent sz=0xfc4 alt=108 het=32767 rec=9 flg=-125 opc=0                                            
 parent=(nil) owner=(nil) nex=(nil) xsz=0x400000

EXTENT 상태를 확인할수 있는 자료

EXTENT 0 addr=0x26c00000         <------익스텐드                                                            
  Chunk 26c00038 sz=  4194248    perm      "perm           "  alo=134768   <---청크                        
EXTENT 1 addr=0x29000000                                                                           
  Chunk 29000038 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29000050 sz=   212888  R-free      "               "                                       
  Chunk 29033fe8 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29034000 sz=  3979072    perm      "perm           "  alo=3979072                          
  Chunk 293ff740 sz=     2240    free      "               "                                       
EXTENT 2 addr=0x29400000                                                                           
  Chunk 29400038 sz=       24  R-freeable  "reserved stoppe"                                        
  Chunk 29400050 sz=   212888  R-free      "               "                                       
  Chunk 29433fe8 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29434000 sz=  3214744    perm      "perm           "  alo=3214744                          
  Chunk 29744d98 sz=   765044    perm      "perm           "  alo=765044                           
  Chunk 297ffa0c sz=     1524    free      "               "                                       
EXTENT 3 addr=0x29800000                                                                           
  Chunk 29800038 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29800050 sz=   212888  R-free      "               "                                       
  Chunk 29833fe8 sz=       24  R-freeable  "reserved stoppe"                                       
  Chunk 29834000 sz=  3645256    perm      "perm           "  alo=3645256                          
  Chunk 29badf48 sz=   333196    perm      "perm           "  alo=333196                           
  Chunk 29bff4d4 sz=     2860    free      "               "              

freelist에 목록이 주소값으로 나와있으면 29bff4d4 이주소로 검색하면

FREE LISTS:Chunk 293ff740 sz=     2240    free 목록을 확인할수 있다.

FREE LISTS:                                                                                        
 Bucket 0 size=16           <----  버킷                                                                        
 Bucket 1 size=20                                                                                  
 Bucket 2 size=24                                                                                  
 Bucket 3 size=28                                                                                  
 Bucket 4 size=32                                                                                  
 Bucket 5 size=36                                                                                  
 Bucket 6 size=40                                                                                  
 Bucket 7 size=44                                                                                  
 Bucket 8 size=48                                                                                  
 Bucket 9 size=52                                                                                  
 Bucket 10 size=56                                                                                 
 Bucket 11 size=60                                                                                 
 Bucket 12 size=64                                                                                 
 Bucket 13 size=68                                                                                 
  Chunk 2a7fffbc sz=       68    free      "     <---- 프리 청크                        

..... 생략

Free lists는 프리chunk의 목록들이면 여기에 나오는 chunk 주소값은 heap구조상으로 익스텐드에

위치를 나타내준다.

UNPINNED RECREATABLE CHUNKS (lru first):                                                      
SEPARATOR                                                                                          
Unpinned space     =        0  rcr=0 trn=0                                                         
PERMANENT CHUNKS:                                                                                  
  Chunk 26c00038 sz=  4194248    perm      "perm           "  alo=134768                           
  Chunk 29badf48 sz=   333196    perm      "perm           "  alo=333196                           
  Chunk 29744d98 sz=   765044    perm      "perm           "  alo=765044                           
  Chunk 29034000 sz=  3979072    perm      "perm           "  alo=3979072                          
  Chunk 29434000 sz=  3214744    perm      "perm           "  alo=3214744                          
  Chunk 29834000 sz=  3645256    perm      "perm           "  alo=3645256                          
  Chunk 2b7ece44 sz=    76816    perm      "perm           "  alo=76816                            
  Chunk 2abff884 sz=     1040    perm      "perm           "  alo=1040                             
  Chunk 2afe0a1c sz=   128016    perm      "perm           "  alo=128016                           
  Chunk 2abe3c74 sz=   113680    perm      "perm           "  alo=113680                           
  Chunk 29fffa7c sz=     1040    perm      "perm           "  alo=1040                             
  Chunk 2a3fd14c sz=    11280    perm      "perm           "  alo=11280                            
  Chunk 29c34000 sz=  3979900    perm      "perm           "  alo=3979900                           
Permanent space    = 40015520 

lru list 정보를 확인할수 있으며 여기에 있는 목록들은 현재사용하지않은 재생성 가능한 chunk의 목록들이다.

RESERVED FREE LISTS:   <-----  예약 프리리스트                                     
 Reserved bucket 0 size=16                                                                         
 Reserved bucket 1 size=4400                                                                       
 Reserved bucket 2 size=8204                                                                       
 Reserved bucket 3 size=8460                                                                       
 Reserved bucket 4 size=8464                                                                       
 Reserved bucket 5 size=8468                                                                       
 Reserved bucket 6 size=8472                                                                       
 Reserved bucket 7 size=9296                                                                       
 Reserved bucket 8 size=9300                                                                       
 Reserved bucket 9 size=12320                                                                      
 Reserved bucket 10 size=12324                                                                     
 Reserved bucket 11 size=16396                                                                     
 Reserved bucket 12 size=32780                                                                     
 Reserved bucket 13 size=65548 

이상 대략적으로 재가 직접 Dump를 떠서 나름 공부해보면서 정리해본 자료입니다.

어차피 owi에 나와있는내용 한번 정리한 샘이라 책읽어 보시면 좀더 자세히 아실듯해요 ^_^