Transcript SGA Internals

SGA
Internals
Julian Dyke
Independent Consultant
Web Version
1
© 2005 Julian Dyke
juliandyke.com
Agenda



2
Introduction
SGA
 Granules
 Fixed SGA
 Buffer Cache
 Shared Pool
 Permanent Areas
 Heaps
 Subheaps
 Library Cache
 Reserved Area
 Log Buffer
Conclusion
© 2005 Julian Dyke
juliandyke.com
Introduction
3

Examples based on
 32 bit architecture
 Red Hat 2.1 AS
 Oracle 9.2.0.4
 Single (non-RAC) instance

Examples affected by
 Oracle version
 Operating system
 Single instance / RAC
 Architecture (32/64)
 Parameters
© 2005 Julian Dyke
juliandyke.com
Word Length
4

Word length can be
 32 bit - 4 byte pointers
 64 bit - 8 byte pointers

Also affects
 size of some integer variables e.g. int
 word alignment

When moving from 32 bit to 64 bit Oracle recommends
doubling
 SHARED_POOL_SIZE
 SHARED_POOL_RESERVED_SIZE
 LARGE_POOL_SIZE
© 2005 Julian Dyke
juliandyke.com
Double Linked Lists - Insertion



Frequently used in SGA structures
Each element includes pointers to next and previous elements
Requires 8 bytes (32 bit systems) or 16 bytes (64 bit systems)
Head
Tail
5
STOP
5000F430
5000F430
5000F430
© 2005 Julian Dyke
55443080
55845C54
52175AC0
juliandyke.com
Double Linked Lists - Deletion

Double linked lists are more efficient for
 insertion
 deletion
Head
Tail
6
STOP
5000F430
55443080
52175AC0
© 2005 Julian Dyke
55443080
55845C54
5000F430
55845C54
52175AC0
55443080
52175AC0
5000F430
55845C54
juliandyke.com
Memory Areas

An Oracle process includes
the following memory areas
08048000
0AAA28C0





Executable
PGA/Session Heap
Shared Libraries
SGA
Stack
40000000
50000000
Executable
PGA
Session Heap
Shared
Libraries
SGA
584A4000
Stack
7
STOP
© 2005 Julian Dyke
juliandyke.com
Shared Memory

Shared memory can be dumped to trace file using
$ sqlplus /nolog
SQL> CONNECT SYS/<password> AS SYSDBA
Connected
SQL> ORADEBUG SETMYPID
ORADEBUG SETMYPID
Statement processed
ORADEBUG IPC
SQL> ORADEBUG IPC
Information written to trace file
SQL> ORADEBUG TRACEFILE_NAME
/var/opt/oracle/admin/LX092001/udump/lx092001_ora_9824.trc
8
© 2005 Julian Dyke
juliandyke.com
ORADEBUG IPC - Example
Area #0 `Fixed Size' containing Subareas 0-0
Total size 000000000006e5cc Minimum Subarea size 00000000
Area Subarea
Shmid
Stable Addr
Actual Addr
0
0
163840 0x00000050000000 0x00000050000000
Subarea size
Segment size
Fixed Area
000000000006f000 0000000008800000
Area #1 `Variable Size' containing Subareas 1-1
Total size 0000000008000000 Minimum Subarea size 00400000
Area Subarea
Shmid
Stable Addr
Actual Addr
1
1
163840 0x0000005006f000 0x0000005006f000
Subarea size
Segment size
Variable Area
0000000008391000 0000000008800000
Area #2 `Redo Buffers' containing Subareas 2-2
Total size 00000000000a3000 Minimum Subarea size 00000000
Area Subarea
Shmid
Stable Addr
Actual Addr
2
2
163840 0x00000058400000 0x00000058400000
Subarea size
Segment size
00000000000a3000 0000000008800000
Redo Buffers
Area #3 `skgm overhead' containing Subareas 3-3
Total size 0000000000001000 Minimum Subarea size 00000000
Area Subarea
Shmid
Stable Addr
Actual Addr
3
3
163840 0x000000584a3000 0x000000584a3000
Subarea size
Segment size
OS Specific
0000000000001000 0000000008800000
9
STOP
© 2005 Julian Dyke
juliandyke.com
V$SGA


Describes sizes of main components in SGA
Used by SHOW SGA command in SQL*Plus
SELECT name, value FROM v$sga;
Name
Fixed Size

10
452044
Variable Size
83886080
Database Buffers
50331648
Redo Buffers

Value
667648
Based from X$KSMSD
X$KSMSD derived from ksmstv_ (ksmst) in Fixed SGA
© 2005 Julian Dyke
juliandyke.com
Shared Global Area


11
Contains
 Fixed SGA
 Buffer Pool
 Shared Pool
 Large Pool
 Java Pool
 Streams Pool
 Redo buffers
Buffer Pool includes
 Default cache
 Keep and Recycle cache
 2K, 4K, 8K, 16K and 32K cache
© 2005 Julian Dyke
Oracle 7.0 and above
Oracle 8.0 and above
Oracle 8.1.5 and above
Oracle 10.1 and above
Oracle 8.0 and above
Oracle 9.0.1 and above
juliandyke.com
Granules
12

Introduced in Oracle 9.0.1

SGA divided into granules

In Oracle 9.2 granule size dependent on SGA_MAX_SIZE
 4 mb
SGA_MAX_SIZE <= 128 mb
 16 mb
SGA_MAX_SIZE > 128 mb

If SGA_MAX_SIZE not set explicitly then defaults to sum of
individual pool parameters

SGA_MAX_SIZE cannot be dynamically modified
© 2005 Julian Dyke
juliandyke.com
Granules
13

Pool parameters rounded up to nearest granule

Each pool occupies one or more granules

Different buffer caches held in separate granules

First "granule" contains
 Fixed SGA
 Granule directory
 Heap headers

Last "granule" contains
 Guard pages
 Redo buffer
 Operating system specific overhead
 Remaining space unused
© 2005 Julian Dyke
juliandyke.com
Granules
SGA_MAX_SIZE = 128M
DB_CACHE_SIZE = 48M
JAVA_POOL_SIZE = 20M
LARGE_POOL_SIZE = 12M
SHARED_POOL_SIZE = 20M
4M
Fixed SGA 4M
Free 16M
Buffer Pool 48M
128M
Java Pool 20M
Large Pool 12M
Shared Pool 32M
Note – shared pool size
automatically increased
from 20M to 32M
14
STOP
© 2005 Julian Dyke
Redo Buffer 4M
4M
juliandyke.com
Granule Operations


15
The following pools can be dynamically resized
Pool
Parameter
Version
Shared Pool
SHARED_POOL_SIZE
9.0.1 and above
Default
DB_CACHE_SIZE
9.0.1 and above
2K
DB_2K_CACHE_SIZE
9.0.1 and above
4K
DB_4K_CACHE_SIZE
9.0.1 and above
8K
DB_8K_CACHE_SIZE
9.0.1 and above
16K
DB_16K_CACHE_SIZE
9.0.1 and above
32K
DB_32K_CACHE_SIZE
9.0.1 and above
Keep
DB_KEEP_CACHE_SIZE
9.0.1 and above
Recycle
DB_RECYCLE_CACHE_SIZE
9.0.1 and above
Large Pool
LARGE_POOL_SIZE
9.2.0 and above
Java Pool
JAVA_POOL_SIZE
10.1 and above
Streams pool cannot be dynamically resized in Oracle 10.1
© 2005 Julian Dyke
juliandyke.com
Granule Views

The following views support dynamic memory management

Introduced in Oracle 9.2
 V$SGA_DYNAMIC_COMPONENTS
 V$SGA_DYNAMIC_FREE_MEMORY
 V$SGA_CURRENT_RESIZE_OPS
 V$SGA_RESIZE_OPS
Not always reliable


16
Introduced in Oracle 10.1
 V$SGAINFO
© 2005 Julian Dyke
juliandyke.com
X$KSMGE
Introduced
in Oracle 9.2
Describes individual granules
17
ADDR
RAW(4)
INDX
NUMBER
INST_ID
NUMBER
GRANNUM
NUMBER
GRANTYPE
NUMBER
GRANSTATE
VARCHAR2(16)
GRANFLAGS
NUMBER
LOCALITY
NUMBER
BASEADDR
RAW(4)
GRANSIZE
NUMBER
GRANNEXT
NUMBER
GRANPREV
NUMBER
© 2005 Julian Dyke
Granule ID
0
2
3
4
5
Free
Shared Pool
Large Pool
Java Pool
Buffer Pool
INVALID (Free)
ALLOCATED
Granule Address
Granule Size
Next granule ID
Previous granule ID
juliandyke.com
Granule Dumps

Granules can be dumped using
ALTER SESSION SET EVENTS
‘immediate trace name granulelist level <level>’

where <level> is
Heap
18
© 2005 Julian Dyke
Level
Null Granule
255
Home Granule
511
Shared Pool
767
Large Pool
1023
Java Pool
1279
Buffer Cache
1535
juliandyke.com
Fixed SGA




Area containing all fixed variables
Contains pointers to all other structures in SGA
Variable name, type, address and size
determined when Oracle executable is linked
Located at start of first granule of SGA
50000000
Fixed SGA
5001D9E8
Granule Directory
5001DF50
Heap Headers
19
STOP
© 2005 Julian Dyke
juliandyke.com
X$KSMFSV

Externalises name, type, address and size of all variables in
fixed SGA
Name

ADDR
RAW(4)
INDX
NUMBER
INST_ID
NUMBER
KSMFSNAM
VARCHAR2(64)
KSMFSTYP
VARCHAR2(64)
KSMFSADR
RAW(4)
KSMFSSIZ
NUMBER
Type
Address
Size
SGA can be dumped using
ORADEBUG FIXEDSGA
20
© 2005 Julian Dyke
juliandyke.com
Global Area Dumps

Global areas can be dumped using
ALTER SESSION SET EVENTS
‘immediate trace name global_area level <level>’


21
where <level> is
Heap
Level
PGA
1
SGA
2
PGA + SGA
3
UGA
4
PGA + UGA
5
SGA + UGA
6
PGA + SGA + UGA
7
More detail is included at levels 0x08 0x10 0x18 (OR)
© 2005 Julian Dyke
juliandyke.com
X$KSMMEM


22
Externalises contents of all locations in the SGA
ADDR
RAW(4)
INDX
NUMBER
INST_ID
NUMBER
KSMMMVAL
RAW(4)
Address
Value
Does not work on Windows platforms
© 2005 Julian Dyke
juliandyke.com
Buffer Cache
51400000
DB_CACHE_SIZE = 48M
DB_BLOCK_SIZE = 8192
51800000
51C00000

Buffer Cache includes 12 x 4M granules
52000000

Each granule contains 500 buffers
52400000

Buffer Cache contains 6000 buffers
52800000
52C00000
Block
Size
Buffers Per
Granule
53000000
53400000
23
STOP
4mb
16mb
2048
1875
7503
53800000
4096
979
3916
53C00000
8192
500
2002
54000000
16384
253
1012
32768
127
509
© 2005 Julian Dyke
juliandyke.com
Buffer Headers




Each buffer has a buffer header
In 9.2.0.4 buffer headers are 188 bytes
Buffer headers are stored in same granule as buffers
Buffer headers include
 Replacement list
 Hash list
 Pointer to buffer
Buffer
Headers
Buffers
Granule
24
STOP
© 2005 Julian Dyke
juliandyke.com
X$BH

25
Externalises buffer headers
ADDR
RAW(4)
INDX
NUMBER
INST_ID
NUMBER
HLADDR
RAW(4)
BLSIZ
NUMBER
NXT_HASH
RAW(4)
PRV_HASH
RAW(4)
NXT_REPL
RAW(4)
Tablespace#
PRV_REPL
RAW(4)
Absolute File Number
TS#
NUMBER
FILE#
NUMBER
Relative File Number
DBARFIL
NUMBER
Block Number
DBABLK
NUMBER
OBJ
NUMBER
BA
RAW(4)
CR_SCN_BAS
NUMBER
© 2005 Julian Dyke
Hash List Address
Block Size
Hash List
Replacement List
Object ID
Buffer Address
juliandyke.com
Shared Pool
56400000
Shared Pool includes

Permanent Area

Segmented Arrays
56C00000

Library Cache
57000000

Row Cache

Reserved Area
56800000
57400000
57800000
57C00000
Shared Pool occupies

8 x 4M granules = 32M

SHARED_POOL_SIZE = 20M
58000000
26
STOP
© 2005 Julian Dyke
juliandyke.com
V$SGASTAT

Describes sizes of individual components in SGA
SELECT pool, name, value FROM v$sgastat;
Pool
Name
fixed sga
buffer cache
27
Value
452044
50331648
log buffer
656384
shared pool
enqueues
51680
shared pool
sessions
91656
shared pool
library cache
1480240
shared pool
dictionary cache
1614976
shared pool
free memory
19746760
large pool
free memory
12582912
java pool
free memory
20971520
© 2005 Julian Dyke
juliandyke.com
Permanent Areas


Allocated at instance startup
Contain structures such as
 processes
 sessions
 segmented arrays
SELECT ksmchptr, ksmchsiz
FROM x$ksmsp
WHERE ksmchcls = 'perm';
Address
28
STOP
3967868
5681F000
2718736
56C00000
57000000
57400000
57800000
57C00000
905116
56C1F00
4063188
5701F000
4067328
© 2005 Julian Dyke
56800000
Size
5641F000
56AB6C10
56400000
58000000
juliandyke.com
Sessions





29
Array of sessions stored in shared pool permanent area
Allocated at instance startup
Number of elements specified by SESSIONS parameter
In Oracle 9.2 each element is 2412 bytes
Session array externalised by
 X$KSUSE
 General information
 Used by V$SESSION
 X$KSUSESTA
 Statistics
 Used by V$SESSTAT
 X$KSUSECST
 Current Waits
 Used by V$SESSION_WAIT
© 2005 Julian Dyke
juliandyke.com
Sessions
ksusga_
struct ksusg
Array of
Pointers
30
STOP
© 2005 Julian Dyke
X$KSUSE
V$SESSION
X$KSUSESTA
V$SESSTAT
X$KSUSECST
V$SESSION_WAIT
juliandyke.com
Segmented Arrays
31

Used to store arrays of objects
 Array too large to fit in granule
 Array may grow dynamically

Objects using segmented arrays include
 enqueues (locks)
 enqueue resources (resources)
 transactions
 transaction branches

Segmented arrays managed recursively by segmented array

Segmented arrays externalised in X$KSMDD
© 2005 Julian Dyke
juliandyke.com
Segmented Arrays
Segmented
Arrays
Call
Segmented
Array
Segmented
Array
Segmented
Header
Array
Segmented
Array
Header
32
STOP
© 2005 Julian Dyke
Enqueues
Enqueue
Resources
juliandyke.com
X$KSMDD

33
Externalises segmented array headers
ADDR
RAW(4)
INDX
NUMBER
INST_ID
NUMBER
NAME
VARCHAR2(30)
ELEMENTS_CHUNK
NUMBER
ITEMS_PT
NUMBER
INITENTRIES
NUMBER
NUMENTRIES
NUMBER
CURENTRIES
NUMBER
NUMCHUNKS
NUMBER
ELEMSIZE
NUMBER
FLAGS
NUMBER
HEAP
RAW(4)
SECONDARY
RAW(4)
© 2005 Julian Dyke
Name of array
Number of
chunks
Address of parent
heap
juliandyke.com
Heaps




A heap consists of one or more extents
Each heap extent occupies a single granule
Each extent contains one or more chunks
Each heap has a header containing
 list of used chunks
 list of free chunks
Heap
Extent 0
Extent 1
Extent 2
Header
Free List
34
STOP
© 2005 Julian Dyke
Chunks
juliandyke.com
Chunks

35
Chunks contain
 header
 body

Size of chunk header
dependent on chunk type
 16 bytes <= size <= 32 bytes

Chunks chained through extent

Chunks can be
 permanent
 recreatable
 freeable
 free
STOP
© 2005 Julian Dyke
Extent
Extent
Header
Chunk
Header
Chunk
Body
juliandyke.com
X$KSMSP

Externalises chunks in the shared pool
Description of chunk type
Address of chunk
ADDR
RAW(4)
INDX
NUMBER
INST_ID
NUMBER
KSMCHIDX
NUMBER
KSMCHDUR
NUMBER
KSMCHCOM
VARCHAR2(16)
KSMCHPTR
RAW(4)
KSMCHSIZ
NUMBER
KSMCHCLS
VARCHAR2(8)
KSMCHTYP
NUMBER
KSMCHPAR
RAW(4)
Size including header
Class
●Perm
●Recreate
●Freeable
●Free
●R-Free
●R-Freeable
Type (recreate only)
●0x18 - KGL Handle
●0x24 - Fixed Allocation
●0x74 - KQR PO
●0x80 - KQR SO
●0xFFF - Extended header
Address of parent
36
© 2005 Julian Dyke
juliandyke.com
Heap Dumps

Heaps can be dumped using
ALTER SESSION SET EVENTS
‘immediate trace name heapdump level <level>’;

37
where <level> is
Heap
Summary
Detail
PGA
1
1025
SGA
2
2050
UGA
4
4100
CGA – Current Call
8
8200
CGA – User Call
16
16400
LGA – Large Pool
32
32800
© 2005 Julian Dyke
juliandyke.com
Free List
38

Heap free lists contain 255 buckets

Each bucket contains double linked list to free chunks

Bucket sizes increase in
 increments of 4 bytes - 16, 20, 24, 28, 32 ... 808, 812
 increments of 64 bytes - 876, 940, 1004 ... 3948, 4012
 then 4108, 8204, 16396, 32780, 65548

Reduces fragmentation
© 2005 Julian Dyke
juliandyke.com
Free List

Example
Heap Header
Buckets
39
© 2005 Julian Dyke
16
20
24
28
32
36
40
44
48
52
56
60
64
24 byte chunks
36 byte chunks
52 byte chunks
juliandyke.com
Subheaps




Heaps can contain subheaps
Objects can have up to 10 subheaps
 SQL statements include subheaps 0 and 6
Subheaps consist of one or more extents
Subheaps have heap headers
 Subheap header format similar to heap header format
 Only one freelist containing all free space
Extent 0
Extent 1
Extent 2
Subheap
Header
Free
List
40
© 2005 Julian Dyke
Chunk
juliandyke.com
Subheap Dumps

In Oracle 9.0.1 and below to dump a subheap, first obtain the
address of the subheap and convert to decimal
0x56b95c0c = 1454988300

For a summary dump use
ALTER SESSION SET EVENTS
‘immediate trace name heapdump_addr level 1454988300’;

For a detailed dump add 1 to the address e.g.
ALTER SESSION SET EVENTS
‘immediate trace name heapdump_addr level 1454988301’;

In Oracle 9.2 and above to dump the subheap summary use
ALTER SESSION SET EVENTS
‘immediate trace name heapdump_addr level 1, addr 0x56b95c0c’;

For a detailed dump use
ALTER SESSION SET EVENTS
‘immediate trace name heapdump_addr level 2, addr 0x56b95c0c’;
41
© 2005 Julian Dyke
juliandyke.com
Subheap Dumps

Example - dump the SGA using
ALTER SESSION SET EVENTS ‘immediate trace name heapdump level 2’;
Chunk
Chunk
Chunk
Chunk
Chunk
Chunk
Chunk
ds
56b94dc8
56b94ef8
56b95128
56b95344
56b95560
56b959bc
56b959d0
56b95c0c
56b94ef8
Chunk 56b95c00

sz= 304
sz= 560
sz= 540
sz= 540
sz=1116
sz= 20
sz= 560
sz=1120
sz= 560
sz= 96
recreate
freeable
recreate
recreate
freeable
free
recreate
ct=
"KGL handles
"library cache
"KQR PO
"KQR PO
"KGLS heap
"
"library cache
2
"
"
"
"
"
"
"
freeable
"library cache
"
latch=0x56efd918
ds=0x56b95c0c
latch=(nil)
latch=0x56d719f4
ds=0x56b96d1c
latch=(nil)
Dump subheap using
ALTER SESSION SET EVENTS
‘immediate trace name heapdump_addr level 1, addr 0x56b95c0c’;
42
© 2005 Julian Dyke
juliandyke.com
Subheap Dumps
Extent 0
Extent 1
Extent 2
Free List
HEAP DUMP heap name="library cache" desc=0x576193c4
extent sz=0x224 alt=32767 het=16 rec=9 flg=2 opc=2
parent=0x5000002c owner=0x5761826c nex=(nil) xsz=0x224
EXTENT 0 addr=0x57615130
Chunk 57615138 sz=
312
perm
"perm
Chunk 57615270 sz=
76
freeable "kgltbtab
Chunk 576152bc sz=
76
freeable "kgltbtab
Chunk 57615308 sz=
76
freeable "kgltbtab
EXTENT 1 addr=0x57616a88
Chunk 57616a90 sz=
512
perm
"perm
Chunk 57616c90 sz=
28
free
"
EXTENT 2 addr=0x57618254
Chunk 5761825c sz=
172
perm
"perm
Chunk 57618308 sz=
36
free
"
Total heap size
=
1288
FREE LISTS:
Bucket 0 size=0
Chunk 57616c90 sz=
28
Chunk 57618308 sz=
36
Total free space
=
64
UNPINNED RECREATABLE CHUNKS (lru
PERMANENT CHUNKS:
Chunk 57615138 sz=
312
Chunk 57616a90 sz=
512
Chunk 5761825c sz=
172
Permanent space
=
996
43
© 2005 Julian Dyke
free
free
"
"
"
"
alo=260
"
"
alo=512
"
"
alo=172
"
"
"
"
"perm
"perm
"perm
"
"
"
first):
perm
perm
perm
alo=260
alo=512
alo=172
juliandyke.com
X$KSMHP

44
Externalises chunks in a subheap
ADDR
RAW(4)
INDX
NUMBER
INST_ID
NUMBER
KSMCHDS
RAW(4)
KSMCHCOM
VARCHAR2(16)
KSMCHPTR
RAW(4)
KSMCHSIZ
NUMBER
KSMCHCLS
VARCHAR2(8)
KSMCHTYP
NUMBER
KSMCHPAR
RAW(4)
KSMCHOWN
RAW(4)
© 2005 Julian Dyke
Address of data segment
Description of chunk type
Address of chunk
Size including header
Class
●Perm
●Free
Address of parent heap
juliandyke.com
X$KSMHP
SQL> SELECT COUNT(*) FROM x$ksmhp;
COUNT(*)
-------SQL> SELECT
COUNT(*) FROM
0
x$ksmhp;
SQL>
SELECT kglobhd6 FROM x$kglob
0 rows
WHERE kglnaobj = 'SELECT SUM(c1) FROM t1'
AND kglhdadr != kglhdpar;
KGLOBHD6
-------5755E798
SQL> SELECT COUNT(*) FROM x$ksmhp
WHERE ksmchds = HEXTORAW ('5755E798');
COUNT(*)
-------1
45
STOP
© 2005 Julian Dyke
juliandyke.com
Library Cache
46

Database objects
 Tables
 Views
 Packages
 Functions

Shared cursors
 SQL statements
 Anonymous PL/SQL block
 Stored PL/SQL procedures and functions
 Java stored procedures
 Methods
© 2005 Julian Dyke
juliandyke.com
Library Cache

For example
CREATE TABLE t1
(
c1 NUMBER,
c2 NUMBER,
c3 NUMBER,
c4 NUMBER
);
CREATE INDEX i1 ON t1 (c1);
CREATE INDEX i2 ON t1 (c3);
SELECT SUM(c2)
FROM t1
WHERE c3 = 42
AND c4 < 2004;
0
1
2
3
47
STOP
0
1
2
SELECT STATEMENT
SORT (AGGREGATE)
TABLE ACCESS (BY INDEX ROWID) OF 'T1'
INDEX (RANGE SCAN) OF 'I2'
© 2005 Julian Dyke
juliandyke.com
Library Cache
512 x 1 word
pointers
Library Cache
Object Handle
256 x DLL
headers
Heap
ksmgsg_
struct kgsms
KGLHD
Library
Cache
KGLHD
KGLHD
KGLHD
512 x 256 =
131072 buckets
Anonymous
List
KGLHD
48
STOP
© 2005 Julian Dyke
KGLHD
KGLHD
KGLHD
juliandyke.com
Library Cache Object - Parent
KGLNA
KGLHD
SELECT SUM (c2) FROM t1
WHERE c3 = 42 AND c4 < 2004
KGLOB
Parent
Handle
Parent
Name
Parent
Object
Heap 0
X$KGLOB
Child
Handle
KGLHD
Child 1
16 x 1 word
pointers
49
STOP
© 2005 Julian Dyke
KGLHD
Child 2
juliandyke.com
Library Cache Object - Child
KGLHD
Child
Object
Subheap
Header
Statistics
etc
KGLOB
Heap 0
Heap 6
Child
Handle
Subquery
Header
X$KGLOB
50
STOP
© 2005 Julian Dyke
Subheap
Header
SELECT
Statement
juliandyke.com
Library Cache - Statement
Subquery
Header
51
STOP
Explain
Plan
Operation
SORT
(AGGREGATE)
Explain
Plan
Operation
TABLE ACCESS (BY
INDEX ROWID)
Explain
Plan
Operation
INDEX
(RANGE SCAN)
© 2005 Julian Dyke
juliandyke.com
Sort (Aggregate)
Output
Columns
SORT (AGGREGATE)
Expression
Function Expression
Op=SUM
Expression
Column Expression
Variable
Column Definition
Obj =1234
Col=2
52
STOP
© 2005 Julian Dyke
Column Alias
Alias=C2
juliandyke.com
Table Access (By Index Rowid)
Output
Columns
TABLE ACCESS (BY INDEX ROWID)
ROWID
Expression Expression
Variable
Column Definition
Obj =1234
Col=2
Column Expression
Condition
Expression
Variable
Column Definition
Obj =1234
Col=ROWID
Predicate
c4 < 2004
Function Expression
Op=LT
Table Alias
Alias=T1
Expression
Column Expression
Expression
Constant Expression
Variable
Column Definition
Obj =1234
Col=4
53
STOP
© 2005 Julian Dyke
Constant
Value=2004
Column Alias
Alias=C4
juliandyke.com
Index (Range Scan)
Output
Columns
Predicate
c3 = 42
INDEX (RANGE SCAN)
Variable
Expression Array
Column Definition
Obj =1234
Col=ROWID
Expression
Function Expression
Op=EQ
Expression
Column Expression
Expression
Constant Expression
Variable
Column Definition
Obj =1234
Col=3
54
STOP
© 2005 Julian Dyke
Constant
Value=42
Column Alias
Alias=C3
juliandyke.com
Shared Pool Reserved Area




55
Area of memory reserved for large contiguous requests for
memory
Reduces fragmentation in shared pool
Only used if no space available in shared pool
Summarised in V$SHARED_POOL_RESERVED

SHARED_POOL_RESERVED_SIZE
 specifies size of shared pool in bytes
 defaults to 5% of SHARED_POOL_SIZE

_SHARED_POOL_RESERVED_PCT
 specifies percentage of shared pool to reserve
 defaults to 5%

_SHARED_POOL_RESERVED_MIN_ALLOC
 specifies minimum chunk size e.g. 4400
© 2005 Julian Dyke
juliandyke.com
X$KSMSPR

56
Externalises address, size and class of all chunks in the
shared pool
Comment
ADDR
RAW(4)
INDX
NUMBER
INST_ID
NUMBER
KSMCHCOM
VARCHAR2(16)
KSMCHPTR
RAW(4)
KSMCHSIZ
NUMBER
KSMCHCLS
VARCHAR2(8)
KSMCHTYP
NUMBER
KSMCHPAR
RAW(4)
© 2005 Julian Dyke
● free memory
● reserved_stopper
Address
Size in bytes
Class
●R-free
●R-freeable
juliandyke.com
Shared Pool Reserved Area
SELECT ksmchcom,ksmchptr,ksmchsiz,ksmchcls
FROM x$ksmspr
ORDER BY ksmchptr;
56400000
56800000
56C00000
57
KSMCHCOM
KSMCHPTR
reserved_stopper
56400024
free memory
56400038
reserved_stopper
5641EFEC
20 R-freeable
reserved_stopper
56800024
20 R-freeable
free memory
56800038
reserved_stopper
5681EFEC
20 R-freeable
reserved_stopper
56C00024
20 R-freeable
free memory
56C00038
reserved_stopper
56C1EFEC
20 R-freeable
reserved_stopper
57000024
20 R-freeable
STOP
© 2005 Julian Dyke
KSMCHSIZ KSMCHCLS
20 R-freeable
57000000
126900 R-free
126900 R-free
126900 R-free
57400000
57800000
57C00000
58000000
juliandyke.com
Log Buffer
Guard Page
58400000
LOG_BUFFER = 524288

2 x 4K guard pages (DEADFACE)

1288 x 512 byte blocks for redo

Each 512 byte includes 16 byte header

Usable redo = 1288 x 496 = 638848 bytes
Header - 16 bytes
Body - 496 bytes
584A2000
Guard Page
58
STOP
© 2005 Julian Dyke
juliandyke.com
Thank you for your interest
For more information and to provide feedback
please contact me
My e-mail address is:
[email protected]
My website address is:
www.juliandyke.com
59
© 2005 Julian Dyke
juliandyke.com