inside rac l.
Download
Skip this Video
Download Presentation
Inside RAC

Loading in 2 Seconds...

play fullscreen
1 / 106

Inside RAC - PowerPoint PPT Presentation


  • 263 Views
  • Uploaded on

Inside RAC. Julian Dyke Independent Consultant. Web Version. juliandyke.com. Agenda. Introduction to RAC Memory Structures The Buffer Cache Global Cache Services The Library Cache Global Enqueue Services. Introduction to RAC. Node 1. Node 2. Instance 1. Instance 2. Interconnect.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Inside RAC' - barbie


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
inside rac

InsideRAC

Julian Dyke

Independent Consultant

Web Version

juliandyke.com

agenda
Agenda
  • Introduction to RAC
  • Memory Structures
  • The Buffer Cache
  • Global Cache Services
  • The Library Cache
  • Global Enqueue Services
what is rac

Node 1

Node 2

Instance 1

Instance 2

Interconnect

LocalDisk

Shared Storage

LocalDisk

What is RAC?
  • Multiple instances running on separate servers (nodes)
  • Single database on shared storage accessible to all nodes
  • Instances exchange information over an interconnect network
architecture
Architecture

Public Network

Private Network(Interconnect)

Instance 1

Instance 2

Node 1

Node 2

Storage Network

SharedStorage

some definitions
Some Definitions
  • Resource
    • Object to which access must be controlled at instance level
  • Enqueue
    • Memory structure that serializes access to a resource
  • Global Resources
    • Object to which access must be controlled at cluster level
  • Global Enqueue
    • Locks and enqueues which need to be consistent between all instances
some definitions7
Some Definitions...
  • Global Resource Directory (GRD)
    • Records current state and owner of each resource
    • Contains convert and write queues
    • Distributed across all instances in cluster
    • Maintained by GCS and GES
  • Global Cache Services (GCS)
    • Implements cache coherency for database
    • Coordinates access to database blocks for instances
  • Global Enqueue Services (GES)
    • Controls access to other resources (locks) including library cache and dictionary cache
    • Performs deadlock detection
background processes

Instance 1

Instance 2

SGA

UGA

UGA

SGA

ARCH

CKPT

PMON

SMON

LMD

LMS

DIAG

LCK

LCK

LGWR

LMON

LMD

DIAG

CKPT

ARCH

LGWR

PMON

DBWR

SMON

DBWR

LMON

LMS

Background Processes

DatafilesControlfiles

Redo Logs

Redo Logs

background processes9
Background Processes
  • LMSn
    • Global Cache Service Process
    • Manage requests for data access across cluster
    • Up to 20 in Oracle 10.1
      • LMS0-LMS9 LMSa-LMSj
    • Up to 36 in Oracle 10.2
      • LMS0-LMS9 LMSa-LMSz
    • In Oracle 10.1 and above, number of GCS server processes can be configured using gcs_server_processes parameter
      • Default value is 1 (single CPU system)
background processes10
Background Processes
  • LCK0
    • Instance Enqueue Process
    • Manages
      • instance resource requests
      • cross-instance call operations
    • Assists LMS processes
    • Formerly known as lock process
    • In 9.0.1 and below, number of lock processes may be configurable using _gc_lck_procs parameter
background processes11
Background Processes
  • LMD0
    • Global Enqueue Service Daemon
    • Manages requests for global enqueues
      • Updates status of enqueues when granted to / revoked from an instance
    • One LMD0 process per instance
    • In 8.1.7 and below number of lock daemons may be configurable using _lm_dlmd_processes parameter
background processes12
Background Processes
  • LMON
    • Global Enqueue Service Monitor
    • One LMON process per instance
    • Monitors cluster to maintain global enqueues and resources
    • Manages
      • instance and process expirations
      • recovery processing for cluster enqueues
background processes13
Background Processes
  • DIAG - Diagnosibility Process
    • Collects diagnostic data in the event of a failure
    • Creates subdirectories in BACKGROUND_DUMP_DEST directory
    • In Oracle 9.0.1 and above can be disabled using _diag_daemon parameter
      • Do not try this on a production system
fixed tables
Fixed Tables
  • Memory structures externalized in X$ tables
  • Instance specific
  • Underlying structures for dynamic performance views
  • Can contain
    • Structures accessed directly from executable
      • X$KSLLD => V$LATCHNAME
      • X$KSUSD => V$STATNAME
    • Structures accessed directly from SGA
      • X$KSUSE => V$SESSION
      • X$KSUPR => V$PROCESS
    • Executable and/or SGA structures joined in PGA
      • X$KQLFXPL => V$SQL_PLAN
      • X$KGLOB => V$SQL, V$SQL_AREA
dynamic performance views
Dynamic Performance Views
  • In a RAC environment each V$ view has an equivalent GV$ view
  • GV$ view includesINST_ID column. For example
  • V$SGA
  • GV$SGA
  • In Oracle 9.2 and below PARALLEL_MIN_SERVERSmust be >= number of hosts to use GV$ views
  • In Oracle 10.1 and above PZnn background processes are used to return data on remote hosts e.g. PZ99
catclust sql
CATCLUST.SQL
  • Some additional views/synonyms are created for RAC databases using $ORACLE_HOME/rdbms/admin/catclust.sql
oradebug
ORADEBUG
  • ORADEBUG includes LKDEBUG
    • Must be run by user with SYSDBA privilege

SQL> ORADEBUG LKDEBUG HELP

Usage:lkdebug [options] -l [r|p] <enqueue pointer> Enqueue Object -r <resource pointer> Resource Object -b <gcs shadow pointer> GCS shadow Object -p <process id> client pid -P <process pointer> Process Object -O <i1> <i2> <types> Oracle Format resname -a <res/lock/proc> all <res/lock/proc> pointer -A <res/lock/proc> all <res/lock/proc> contexts -a <res> [<type>] all <res> pointers by an optional type -a convlock all converting enqueue (pointers) -A convlock all converting enqueue contexts -a convres all res ptr with converting enqueues -A convres all res contexts with converting enqueues

oradebug18
ORADEBUG
  • Continued...

-a name list all resource names -a hashcount list all resource hash bucket counts -t Traffic controller info -s summary of all enqueue types -k GES SGA summary info -m pkey <objectno> request for remastering this object at current instance -m dpkey <objectno> request for dissolving remastering of this object at current instance

memory areas

STOP

Memory Areas

00000000

  • An Oracle process includesthe following memory areas
    • Executable
    • SGA
    • Shared Libraries
    • PGA/Session Heap
    • Stack

Executable

20000000

SGA

SharedLibraries

40000000

PGASession Heap

Stack

FFFFFFFF

shared memory
Shared Memory
  • Shared memory areas can be dumped to trace file using

$ sqlplus /nolog

SQL> CONNECT SYS/<password> AS SYSDBAConnected

SQL> ORADEBUG SETMYPIDStatement processed

SQL> ORADEBUG IPCInformation written to trace file

ORADEBUG SETMYPIDORADEBUG IPC

oradebug ipc example

STOP

ORADEBUG IPC - Example

Area #0 `Fixed Size' containing Subareas 0-0 Total size 0000000000129968 Minimum Subarea size 00000000 Area Subarea Shmid Stable Addr Actual Addr 0 0 65537 0x00000020000000 0x00000020000000 Subarea size Segment size000000000012a000 0000000010800000 Area #1 `Variable Size' containing Subareas 2-2 Total size 0000000010000000 Minimum Subarea size 00400000 Area Subarea Shmid Stable Addr Actual Addr 1 2 65537 0x00000020400000 0x00000020400000 Subarea size Segment size0000000010000000 0000000010800000 Area #2 `Redo Buffers' containing Subareas 1-1 Total size 00000000002d6000 Minimum Subarea size 00000000 Area Subarea Shmid Stable Addr Actual Add 2 1 65537 0x0000002012a000 0x0000002012a000 Subarea size Segment size00000000002d6000 0000000010800000 Area #3 `skgm overhead' containing Subareas 3-3 Total size 0000000000001000 Minimum Subarea size 00000000 Area Subarea Shmid Stable Addr Actual Addr 3 3 65537 0x00000030400000 0x00000030400000 Subarea size Segment size0000000000001000 0000000010800000

Area #0 `Fixed Size' containing Subareas 0-0 Total size 0000000000129968 Minimum Subarea size 00000000 Area Subarea Shmid Stable Addr Actual Addr 0 0 65537 0x00000020000000 0x00000020000000 Subarea size Segment size 000000000012a000 0000000010800000 Area #1 `Variable Size' containing Subareas 2-2 Total size 0000000010000000 Minimum Subarea size 00400000 Area Subarea Shmid Stable Addr Actual Addr 1 2 65537 0x00000020400000 0x00000020400000 Subarea size Segment size 0000000010000000 0000000010800000 Area #2 `Redo Buffers' containing Subareas 1-1 Total size 00000000002d6000 Minimum Subarea size 00000000 Area Subarea Shmid Stable Addr Actual Add 2 1 65537 0x0000002012a000 0x0000002012a000 Subarea size Segment size 00000000002d6000 0000000010800000 Area #3 `skgm overhead' containing Subareas 3-3 Total size 0000000000001000 Minimum Subarea size 00000000 Area Subarea Shmid Stable Addr Actual Addr 3 3 65537 0x00000030400000 0x00000030400000 Subarea size Segment size 0000000000001000 0000000010800000

Fixed Area

Variable Area

Redo Buffers

OS Specific

shared global area
Shared Global Area
  • Contains
    • Fixed SGA
    • Buffer Pool
    • Shared Pool Oracle 7.0 and above
    • Large Pool Oracle 8.0 and above
    • Java Pool Oracle 8.1.5 and above
    • Streams Pool Oracle 10.1 and above
    • Redo buffers
  • Buffer Pool includes
    • Default cache
    • Keep and Recycle cache Oracle 8.0 and above
    • 2K, 4K, 8K, 16K and 32K cache Oracle 9.0.1 and above
v sgainfo
V$SGAINFO
  • Summarizes SGA pools

SELECT * FROM v$sgainfo;

NAME BYTES RESIZEABLE-------------------------------- ---------- ----------Fixed SGA Size 1218920 NoRedo Buffers 2973696 NoBuffer Cache Size 176160768 YesShared Pool Size 83886080 YesLarge Pool Size 4194304 YesJava Pool Size 4194304 YesStreams Pool Size 0 YesGranule Size 4194304 NoMaximum SGA Size 272629760 NoStartup overhead in Shared Pool 46137344 NoFree SGA Memory Available 0

granules
Granules
  • Introduced in Oracle 9.0.1
  • SGA divided into granules
  • In Oracle 9.2 Unix 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
granules26
Granules
  • In Oracle 10.1 and above
    • SGA_MAX_SIZE dependent on SGA_TARGET
  • Granule size dependent on SGA_MAX_SIZE
    • 4 mb SGA_MAX_SIZE <= 256 mb??
    • 16 mb SGA_MAX_SIZE > 256 mb ??
  • Granule size can be controlled using _ksmg_granule_size unsupported parameter
x ksmge
X$KSMGE
  • Introduced in Oracle 9.2
  • Describes individual granules

Granule ID

0 Free1 Shared Pool2 Large Pool3 Java Pool6 Buffer Pool

INVALID (Free)ALLOCATED

Granule Address

Granule Size

Next granule ID

Previous granule ID

granules28

STOP

Granules

4M

Fixed SGA + Redo Buffers 4M

SGA_TARGET = 260M SGA_MAX_SIZE = 260M

GRANULE SIZE = 4M

Buffer Pool 172M

256M

Large Pool 4M

Java Pool 4M

This is an example of an SGA mapped using X$KSMGE

Shared Pool 76M

v sgastat
V$SGASTAT
  • Enhanced in Oracle 10.1 and above
  • In Oracle 10.2 describes around 700 memory areas
v sgastat30
V$SGASTAT
  • RAC-specific areas include
v sgastat31
V$SGASTAT
  • Significant RAC areas in Oracle 10.2
  • In Oracle 9.2 all five structures were stored in segmented arrays
permanent areas

2AC00000

2B800000

2BC00000

2C000000

2C400000

2C800000

2CC00000

2D000000

2D400000

2D800000

2DC00000

2E000000

2E400000

2E800000

2EF00000

2F000000

2F400000

2F800000

2FC00000

30000000

STOP

Permanent Areas
  • Allocated at instance startup
  • Contain structures such as
    • processes
    • sessions
    • segmented arrays

Heap

SELECT ksmchptr, ksmchsizFROM x$ksmspWHERE ksmchcls = 'perm';

PermanentArea

x ksmsp
X$KSMSP
  • Externalises chunks in the shared pool

Description of chunk type

Address of chunk

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

x ksmsp34
X$KSMSP
  • Some RAC components are stored in the shared pool heap

SELECT ksmchcom, SUM(ksmchsiz), COUNT(*)FROM x$ksmspGROUP BY ksmchcom;

segmented arrays
Segmented Arrays
  • 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
segmented arrays36

SegmentedArrays

Call

Enqueues

EnqueueResources

STOP

Segmented Arrays

SegmentedArray

SegmentedArray Header

SegmentedArray

SegmentedArray Header

x ksmdd
X$KSMDD
  • Externalises segmented array headers

Name of array

Number of chunks

Address of parent heap

segmented arrays38
Segmented Arrays
  • In Oracle 10.2 there are three RAC-specific segmented arrays:
    • GCS Resources
    • GCS Shadows
    • GES Enqueues
  • In previous releases there were five segmented arrays:
    • GCS Resources
    • GCS Shadows
    • GES Resources
    • GES Shadows
    • GES Messages
heaps

Chunks

STOP

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

Extent 1

Extent 2

Extent 0

Heap

Header

Free List

x ksmhp
X$KSMHP
  • Externalises chunks in the heap
  • Can only be accessed using KSMCHDS e.g.

SELECT * FROM x$ksmhpWHERE ksmchds = HEXTORAW ('2CA54040');

buffer headers

STOP

Buffer Headers
  • Each buffer has a buffer header
  • Buffer headers are stored in same granule as buffers
  • Buffer headers include
    • Replacement list
    • Hash list
    • Pointer to buffer
    • In RAC only pointer to Lock Element

Buffer Headers

Buffers

Granule

single block reads

45

33

42

11

52

72

71

66

34

66

42

49

92

87

45

45

52

71

71

42

72

33

33

87

42

33

11

42

52

71

11

11

45

42

71

42

52

92

34

34

72

45

92

11

72

34

2

1

1

1

1

2

1

1

0

1

2

0

0

3

4

2

1

1

2

1

0

2

0

4

1

1

1

2

2

4

4

0

1

4

1

2

0

1

0

1

2

4

0

3

1

2

Read Block 87

Read Block 34

Read Block 33

Read Block 42

Read Block 11

Update touch count for block 42

Update buffer contents

Get first available buffer from cold end

Update buffer contents

Insert buffer at head of cold end

Get first available buffer from cold end

Insert buffer at head of cold end

Insert buffer at head of coldend

Update buffer contents

Move block 71 to head of hot end

Set touch count on block 71 to zero

Get first available bufferfrom cold end

Update buffer contents

Set touch counton block 42 to zero

Insert buffer at head of coldend

Move block 42 to headof hot end

Update touch countfor block 34

Get first available bufferfrom cold end

STOP

Single-Block Reads

Block Number

Head of Hot End

Head of Cold End

Touch Count

Read Block 42

multi block reads

1

3

6

2

8

2

3

4

7

1

2

1

2

1

4

1

2

1

6

1

2

3

1

5

5

5

3

7

8

2

1

3

4

7

6

5

6

5

4

2

3

1

5

5

5

6

7

6

7

6

5

8

Read Block 7

Read Block 5

Read Block 2

Read Block 4

Read Block 8

Read Block 6

Read Block 1

Read Block 3

Move block 7 to cold end

Move block 5 to cold end

Move block 8 to cold end

Get first four available buffers from cold end

Read next four blocks into buffers

Insert buffers at head of cold end

Move block 1 to cold end

Move block 3 to cold end

Read next four blocks into buffers

Insert buffers at head of cold end

Move block 4 to cold end

Get next four available buffers from cold end

Move block 6 to cold end

Move block 2 to cold end

STOP

Multi-Block Reads

DB_FILE_MULTIBLOCK_READ_COUNT = 4

Head of Hot End

Head of Cold End

read with no transfer

3

2

1

4

STOP

Read with No Transfer

Request shared resource

N

S

ResourceMaster

Instance 2

Instance 3

Request granted

Read request

Block returned

Instance 4

Instance 1

Instance 2 requests current read on block

1318

1318

read to write transfer

4

3

2

1

STOP

Read to Write Transfer

Transfer block to Instance 1 for exclusiveaccess

ResourceMaster

N

S

N

Block and resource status

1318

Instance 2

Instance 3

Request exclusiveresource

N

X

1320

Resource status

Instance 1

Instance 4

Instance 1 requests exclusive read on block

1318

write to write transfer

4

3

2

1

STOP

Write to Write Transfer

Request block in exclusive mode

Transfer block to Instance 4 in exclusive mode

ResourceMaster

N

S

N

1318

Instance 2

Instance 3

Resource status

N

X

N

N

X

1320

1320

1323

Block and resource status

Instance 1

Instance 4

Instance 4 requests exclusive read on block

1318

Note that Instance 1 will create a past image (PI) of the dirty block

past images
Past Images
  • When an instance passes a dirty block to another instance it
    • Flushes redo buffer to redo log
    • Retains past image (PI) of block in buffer cache
      • PI is retained until another instance writes block to disk
      • Used to reduce recovery times
    • Recorded in V$BH.STATUS as PI
      • Based on X$BH.STATE (value 8 in Oracle 10.2)
past images51

1323

1324

1328

1329

1324

1325

1325

1326

1326

1327

1327

1328

STOP

Past Images

Buffer Cache

Buffer Cache

UPDATE t1SET c1 = 1329;COMMIT;

UPDATE t1SET c1 = 1325;COMMIT;

UPDATE t1SET c1 = 1324;COMMIT;

UPDATE t1SET c1 = 1328;COMMIT;

UPDATE t1SET c1 = 1327;COMMIT;

UPDATE t1SET c1 = 1326;COMMIT;

1323

1328

1324

1329

1325

1326

1327

1328

1328

1328

1329

1329

Instance 1

Instance 2

1329

1323

1323

1323

1329

Instance 1 must perform recovery for Instance 2

Instance 1 updates column to 1324

Undo/Redo written to Redo Log 1

Block 42 is updated in buffer cache

Instance 2 CrashesContents of buffer cache are lost

Instance 1 updates column to 1327

Undo/Redo written to Redo Log 1

DBWR has not written changes to block 42 back to disk yet

Undo/Redo written to Redo Log 1

Undo/Redo written to Redo Log 1

Block 42 is subsequently written back to disk by DBWR

Block 42 is read from disk

Undo/Redo written to Redo Log 1

Block 42 is updated in buffer cache

Instance 1 updates column to 1325

Block 42 is updated in buffer cache

Instance 1 updates column to 1326

Block 42 is updated in buffer cache

Block 42 is updated in buffer cache

Assume table t1 contains a single row in block 42

Block 42 is updated in buffer cache

Instance 1 makes block 42 a Past Image block

Undo/redo is applied from Redo Log 2

Undo/redo written toRedo Log 2

Block 42 needs recoveryInstance 1 uses Past Image

GCS transfers block from Instance 1 to Instance 2

Instance 2 updates column to 1329

Instance 1 updates column to 1328

Redo Log 1

Redo Log 2

write to read transfer

3

2

4

1

STOP

Write to Read Transfer

Request block in shared mode

Transferblock to Instance 1in sharedmode

ResourceMaster

N

S

N

S

1318

Resource status

Instance 2

Instance 3

N

X

N

N

S

X

1320

1320

Block and resource status

1323

Instance 1

Instance 4

Note that in recent versions _fairness_threshold is used to avoid unnecessary lock conversions

Instance 2 requests current read on block

1318

fairness threshold
Fairness Threshold
  • Intended to prevent unnecessary lock downgrades when other instances only require read-only copies
  • For write to read transfers
    • Writing instance retains X lock
    • Reading instance retains null lock
  • If _fairness_threshold reached then
    • Writing instance downgrades X lock to S lock
    • Reading instance receives S lock
  • _fairness_threshold default value is 4
fairness threshold54

STOP

Fairness Threshold

_fairness_threshold = 4

3

2

4

N

S

S

X

0

1

1323

1323

1323

1323

1323

Instance 1

Instance 2

Instance 2 receives block with Null lock

Instance 2 requests consistent read

Instance 1 sends block to instance 2

Instance 1 sets counter to 3

Instance 1 downgrades lock from X to S

Instance 1 sets counter to 4

Instance 2 receives block with Null lock

Instance 2 requests consistent read

Instance 1 sets counter to 2

Instance 2 receives block with Shared lock

Assume instance 1 holds exclusive lock on block

Instance 2 receives block with Null lock

Instance 2 requests consistent read

Instance 1 sets counter to 1

Instance 1 sends block to Instance 2

Instance 1 sends block to Instance 2

Instance 1 sends block to Instance 2

Instance 2 requests consistent read

lock elements

LockElement

LockElement

LockElement

BufferHeader

BufferHeader

BufferHeader

GCSClient

GCSClient

GCSClient

Lock Elements
  • Contain embedded GCS Client structures (KJBL)
v lock element
V$LOCK_ELEMENT
  • Based on X$LE
slide57

LockElement

BufferHeader

GCSClient

STOP

X$LE
global cache services

KJBR

KJBR

KJBL

BH

BH

KJBL

KJBL

Global Cache Services

GCSClient

GCSShadow

LE

LE

gcs parameters
GCS Parameters
  • GCS Resources
    • Number of GCS resource structures determined by
      • _gcs_resources parameter
    • Stored in segmented array
    • Externalized in X$KJBR
    • Number of free GCS resource structures in X$KJBRFX
  • GCS Enqueues (Shadows/Clients)
    • Number of GCS enqueue structures determined by
      • _gcs_shadow_locks parameter
    • Stored in segmented array
    • Externalized in X$KJBL
    • Number of free GCS enqueue structures in X$KJBLFX
x kjbr
X$KJBR
  • Externalizes GCS Resources
x kjbl
X$KJBL
  • Externalizes GCS Enqueues
global cache dumps
Global Cache Dumps
  • To dump the contents of the global cache use:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME GC_ELEMENTS LEVEL 1';

GLOBAL CACHE ELEMENT DUMP (address: 0x21fecd18): id1: 0x3591 id2: 0x10000 obj: 181 block: (1/13713) lock: SL rls: 0x0000 acq: 0x0000 latch: 0 flags: 0x41 fair: 0 recovery: 0 fpin: 'kdswh05: kdsgrp' bscn: 0x0.18a9c bctx: (nil) write: 0 scan: 0x0 xflg: 0 xid: 0x0.0.0

GCS CLIENT 0x21fecd60,1 sq[(nil),(nil)] resp[(nil),0x3591.10000] pkey 181 grant 1 cvt 0 mdrole 0x21 st 0x20 GRANTQ rl LOCAL master 1 owner 0 sid 0 remote[(nil),0] hist 0x7c history 0x3c.0x1.0x0.0x0.0x0.0x0. cflag 0x0 sender 2 flags 0x0 replay# 0 disk: 0x0000.00000000 write request: 0x0000.00000000 pi scn: 0x0000.00000000 msgseq 0x1 updseq 0x0 reqids[1,0,0] infop 0x0 pkey 181 hv 107 [stat 0x0, 1->1, wm 32767, RMno 0, reminc 6, dom 0] kjga st 0x4, step 0.0.0, cinc 8, rmno 10, flags 0x0 lb 0, hb 0, myb 178, drmb 178, apifrz 0

global cache dumps63
Global Cache Dumps
  • Continued

GLOBAL CACHE ELEMENT DUMP (address: 0x237f4358): id1: 0x6a39 id2: 0x10000 obj: 74 block: (1/27193) lock: SL rls: 0x0000 acq: 0x0000 latch: 0 flags: 0x41 fair: 0 recovery: 0 fpin: 'kdswh05: kdsgrp' bscn: 0x0.26992 bctx: (nil) write: 0 scan: 0x0 xflg: 0 xid: 0x0.0.0

GCS SHADOW 0x237f43a0,1 sq[0x2ee64e8c,0x2eff3858] resp[0x2ee64e74,0x6a39.10000] pkey 74 grant 1 cvt 0 mdrole 0x21 st 0x40 GRANTQ rl LOCAL master 0 owner 0 sid 0 remote[(nil),0] hist 0x12a5 .....

GCS RESOURCE 0x2ee64e74 hashq [0x2ee61894,0x2ff57390] name[0x6a39.10000] pkey 74 grant 0x2eff3858 cvt (nil) send (nil),0 write (nil),0@65535 flag 0x0 mdrole 0x1 mode 1 scan 0 role LOCAL .....GCS SHADOW 0x2eff3858,1 sq[0x237f43a0,0x2ee64e8c] resp[0x2ee64e74,0x6a39.10000] pkey 74 grant 1 cvt 0 mdrole 0x21 st 0x40 GRANTQ rl LOCAL master 0 owner 1 sid 0 remote[0x23fea160,1] hist 0x65f .....

GCS SHADOW 0x237f43a0,1 sq[0x2ee64e8c,0x2eff3858] resp[0x2ee64e74,0x6a39.10000] pkey 74 grant 1 cvt 0 mdrole 0x21 st 0x40 GRANTQ rl LOCAL master 0 owner 0 sid 0 remote[(nil),0] hist 0x12a5 .....

block mastering
Block Mastering
  • Each block is mastered on one instance
    • Block DBA is reported by X$KJBR.KJBRNAME
      • Names for have the format:
        • [<block_number>][<file_number>],[BL]
      • Ordering by X$KJBR.KJBRNAME is difficult because the resource names do not collate e.g.
        • [0x900][0x70000],[BL]
        • [0x90][0x70000],[BL]
    • Current master reported by X$KJBR.KJBRMASTER
block mastering65
Block Mastering
  • In Oracle 10.2 block mastering is determined by
    • _lm_contiguous_res_count
  • Specifies number of contiguous blocks that will hash to the same HV bucket
  • Defaults to 128
  • For example

Instance 1

Instance 0

block mastering66
Block Mastering
  • In Oracle 9.2 (and probably 10.1) block mastering determined by hash function
    • Algorithm applied to groups of 1289 contiguous blocks
    • In two node cluster
      • instance 0 has 645 blocks
      • instance 1 has 644 blocks
    • In three node cluster
      • instance 0 has 430 blocks
      • instance 2 has 215 blocks
      • instance 1 has 430 blocks
      • instance 2 has 214 blocks
    • Beware of small hot tables and indexes....
dynamic remastering
Dynamic Remastering
  • In Oracle 9.2
    • documentation describes dynamic remastering
    • not implemented in code
  • In Oracle 10.1
    • work at data file level
    • very high threshold so difficult to test
    • does occur on some customer sites
    • may cause LMON process to crash in 10.1.0.4
      • bug 3659289 - patch available
      • fixed in 10.1.0.5/10.2.0.1
  • In Oracle 10.2
    • works at object level
    • thresholds are relatively low
dynamic remastering68
Dynamic Remastering
  • Example

SELECT data_object_id FROM dba_objectsWHERE owner = 'US01'AND object_name = 'T1';

OBJECT_ID---------52084

  • To remaster object at current instance use:

ORADEBUG LKDEBUG -m pkey 52084

  • All blocks now mastered by the current instance
  • To redistribute masters to all available instances use:

ORADEBUG LKDEBUG -m dpkey 52084

  • Blocks mastered by both (all) instances again
v gcspfmaster info
V$GCSPFMASTER_INFO
  • Object re mastering is recorded in V$GCSPFMASTER_INFO
dynamic remastering70
Dynamic Remastering
  • Object remastering recorded in V$GCSPFMASTER_INFO
  • Instances are internally numbered 0, 1 etc
  • Initially contains no rows
  • After remastering object 52084 to instance 0

SELECT object_id, current_master, previous_master FROM v$gcspfmaster_info;

  • After remastering object 52084 to instance 1
dynamic remastering71
Dynamic Remastering
  • Information about Dynamic Remastering operations is also recorded in the following fixed views
    • X$KJDRMREQ
      • Dynamic Remastering Requests
    • X$KJDRMAFNSTATS
      • File Remastering Statistics
    • X$KJDRMHVSTATS
      • Hash Value Statistics
library cache object parent

KGLNA

ParentName

SELECT SUM (c2) FROM t1WHERE c3 = 42 AND c4 < 2004

Heap 0

Child 1

Child 2

STOP

Library Cache Object - Parent

KGLHD

ParentObject

KGLOB

ParentHandle

ChildHandle

X$KGLOB

KGLHD

16 x 1 wordpointers

KGLHD

library cache object child

KGLHD

SubheapHeader

SubheapHeader

KGLOB

Heap 6

Heap 0

SubqueryHeader

STOP

Library Cache Object - Child

Statistics,Optimizer Environment,BindVariables

ChildObject

ChildHandle

X$KGLOB

SELECTStatement

library cache
Library Cache
  • In general
    • Locks are required for parsing
      • Externalized in X$KGLLK
    • Pins are required for execution
      • Externalized in X$KGLPN
  • Each KGLHD structure has a set of double linked lists including;
    • Locks
    • Pins
library cache object locks pins

X$KGLLK

KGLHD

Lock

Lock

Pin

Lock

Pin

Pin

KGLOB

X$KGLPN

STOP

Library Cache Object - Locks & Pins

ChildObject

ChildHandle

X$KGLOB

namespaces
Namespaces
  • In Oracle 10.2 there are 64 library cache namespaces
  • Externalized by KGLHDNSP in X$KGLOB
namespaces78
Namespaces
  • Library cache statistics can be dumped using:

ALTER SESSION SET EVENTS'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL 1';

  • For example:

LIBRARY CACHE STATISTICS:namespace gets hit ratio pins hit ratio reloads invalids-------------- --------- --------- --------- --------- ---------- ----------CRSR 2403 0.086 33698 0.933 435 0TABL 4368 0.578 10032 0.657 961 0BODY 127 0.717 211 0.801 4 0TRGR 4 0.000 45 0.911 0 0INDX 81 0.272 83 0.036 21 0CLST 101 0.921 264 0.958 3 0KGLT 0 0.000 0 0.000 0 0PIPE 0 0.000 0 0.000 0 0LOB 0 0.000 0 0.000 0 0DIR 0 0.000 0 0.000 0 0QUEU 156 0.904 5886 0.997 0 0....

v librarycache
V$LIBRARYCACHE
  • Subset of rows from X$KGLST
  • Contains 11 rows in Oracle 10.2

RAC SpecificStatistics

v librarycache80
V$LIBRARYCACHE
  • Based on X$KGLST

SELECT inst_id, DECODE (indx, 0,'SQL AREA', 1,'TABLE/PROCEDURE', 2,'BODY', 3,'TRIGGER', 4,'INDEX', 5,'CLUSTER', 6,'OBJECT', 7,'PIPE', 13,'JAVA SOURCE', 14,'JAVA RESOURCE', 32,'JAVA DATA',' ?'), kglstget,kglstght, DECODE (kglstget,0,1,kglstght/kglstget),kglstpin,kglstpht, DECODE (kglstpin,0,1,kglstpht/kglstpin),kglstrld,kglstinv, kglstlrq,kglstprq,kglstprl,kglstirq,kglstmiv FROM x$kglst WHERE indx<8 OR indx=13 OR indx=14 OR indx=32

Names are generated in dynamic performance view

Only selected rows from X$KGLST

x kglst
X$KGLST
  • Contains one row for each namespace (59 rows in 10.2)

RAC SpecificStatistics

v lock type
V$LOCK_TYPE
  • Introduced in Oracle 10.1
  • Lists all lock types
  • Wildcards for
    • Library Cache Locks and Pins
    • Row Cache Locks
library cache objects
Library Cache Objects
  • Library Cache Objects can be dumped using:

ALTER SESSION SET EVENTS'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL 4';

  • For example:

BUCKET 127469: LIBRARY OBJECT HANDLE: handle=2bb8dfbc mutex=0x2bb8e070(0) name=US01.T1 hash=b2f454b86387761e02fc7e686e37f1ed timestamp=01-14-2006 22:04:06 namespace=TABL flags=KGHP/TIM/MED/[40000000] kkkk-dddd-llll=0000-0701-0701 lock=0 pin=0 latch#=1 hpc=0002 hlc=0002 lwt=0x2bb8e018[0x2bb8e018,0x2bb8e018] ltm=0x2bb8e020[0x2bb8e020,0x2bb8e020] pwt=0x2bb8dffc[0x2bb8dffc,0x2bb8dffc] ptm=0x2bb8e004[0x2bb8e004,0x2bb8e004] ref=0x2bb8e038[0x2bb8e038,0x2bb8e038] lnd=0x2bb8e044[0x2bb7a7ac,0x2bb8e410] LOCK INSTANCE LOCK: id=LBb2f454b86387761e PIN INSTANCE LOCK: id=NBb2f454b86387761e mode=S release=F flags=[00 INVALIDATION INSTANCE LOCK: id=IV0000c9890e170507 mode=S LIBRARY OBJECT: object=2caede30 type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0 BUCKET 127469 total object count=1

library cache instance locks
Library Cache Instance Locks
  • Instance Locks created for
    • Library Cache Locks
    • Library Cache Pins
  • Lock name based on object hash value (KGLNAHSH)
  • For example:

BUCKET 127469: LIBRARY OBJECT HANDLE: handle=2bb8dfbc mutex=0x2bb8e070(0) name=US01.T1 hash=b2f454b86387761e02fc7e686e37f1ed timestamp=01-14-2006 22:04:06namespace=TABL flags=KGHP/TIM/MED/[40000000] kkkk-dddd-llll=0000-0701-0701 lock=0 pin=0 latch#=1 hpc=0002 hlc=0002 lwt=0x2bb8e018[0x2bb8e018,0x2bb8e018] ltm=0x2bb8e020[0x2bb8e020,0x2bb8e020] pwt=0x2bb8dffc[0x2bb8dffc,0x2bb8dffc] ptm=0x2bb8e004[0x2bb8e004,0x2bb8e004] ref=0x2bb8e038[0x2bb8e038,0x2bb8e038] lnd=0x2bb8e044[0x2bb7a7ac,0x2bb8e410] LOCK INSTANCE LOCK: id=LBb2f454b86387761e PIN INSTANCE LOCK: id=NBb2f454b86387761e mode=S release=F flags=[00 INVALIDATION INSTANCE LOCK: id=IV0000c9890e170507 mode=S LIBRARY OBJECT: object=2caede30 type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0 BUCKET 127469 total object count=1

library cache instance locks86
Library Cache Instance Locks
  • For example in library cache dump
    • Library cache pin instance lock is
      • id=NBb2f454b86387761e
    • In X$KJIRFT this KJIRFTRN column
      • [0xb2f454b8][0x6387761e],[NB]
v rowcache
V$ROWCACHE

RAC SpecificStatistics

v rowcache91
V$ROWCACHE
  • Based on X$KQRST
  • Contains 42 rows in Oracle 10.2
    • 34 Parent Cache
    • 8 Subordinate Caches
row caches instance locks93
Row Caches->Instance Locks
  • Slide 2 of 2
  • Cache# = KQRSTCID
row caches
Row Caches
  • Row cache statistics can be dumped using:

ALTER SESSION SET EVENTS'IMMEDIATE TRACE NAME ROW_CACHE LEVEL 1';

  • For example:

ROW CACHE STATISTICS:cache size gets misses hit ratio DLM req-------------------------- ------- ------- ------ --------- -------dc_tablespaces 496 8909 7 0.999 7dc_free_extents 388 0 0 0.000 0dc_segments 444 4191 569 0.880 709dc_rollback_segments 452 4210 31 0.993 74dc_used_extents 400 0 0 0.000 0dc_tablespace_quotas 396 0 0 0.000 0dc_files 412 0 6 0.000 6dc_users 520 9042 23 0.997 23 dc_users 260 0 0 0.000 0 dc_user_grants 108 24 17 0.585 0 dc_app_role 100 0 0 0.000 0....

row caches95
Row Caches
  • Row cache statistics can be dumped using:

ALTER SESSION SET EVENTS'IMMEDIATE TRACE NAME ROW_CACHE LEVEL 8';

  • For example:

Cache ID 8dc_objects

BUCKET 48205: row cache parent object: address=0x2bb8dcd8 cid=8(dc_objects) hash=313bbc4c typ=9 transaction=(nil) flags=00000002 own=0x2bb8dd44[0x2bb8dd44,0x2bb8dd44] wat=0x2bb8dd4c[0x2bb8dd4c,0x2bb8dd4c] mode=S status=VALID/-/-/-/-/-/-/-/- request=N release=FALSE flags=0 instance lock id=QI f611ffad e31d1de3 set=0, complete=FALSE data= 00000037 31540002 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000001 00000000 00000000 00000000 00000000 00000000 00000000 ...... 00000000 00000000 00000000 0000c989 00000001 0000c989 016a7802 0705170e 0e016a78 78070517 170e016a 00010705 00000000 00000000 00000000 00000000 00000000 00000006 BUCKET 48205 total object count=1

Instance LockQI F611FFAD E31D1DE3

T1

global enqueue services

KJIRFT

KJILKFT

KJILKFT

KJILKFT

KJILKFT

KJILKFT

KJILKFT

Global Enqueue Services

Holders

Converters

Waiters

GESEnqueue

GESResource

resource names
Resource Names
  • Contain
    • 1 x two byte name
    • 2 x 32 bit integer tag fields
  • Used with
    • Resources
    • Enqueues
    • Locks
    • Global Enqueue Services
  • Global Cache Services
    • Block are resources with resource name BL
resource names98
Resource Names
  • Externalized inconsistently throughout Oracle in
    • V$/GV$ dynamic performance views
    • X$ fixed tables
    • Dumps and trace files
  • Stored consistently in internal C structures including
    • KJBR
    • KJIRFT
  • Other structures reference the resource names in these structures including
    • KJBL
    • KJILFKT
ges parameters
GES Parameters
  • GES Resources
    • Number of GES resource structures probably determined by
      • _lm_ress parameter
    • Stored in heap
    • Externalized in X$KJIRFT
  • GES Enqueues
    • Number of GES enqueue structures probably determined by
      • _lm_locks parameter
    • Stored in segmented array
    • Externalized in X$KJILKFT
x kjirft
X$KJIRFT
  • Externals global enqueue resources
  • Does NOT include global cache resources
v ges resource
V$GES_RESOURCE
  • Contains rows from both KJIRFT and KJBR
  • Synonym for V$DLM_RESS
v ges resource103
V$GES_RESOURCE
  • Contains rows from both KJIRFT and KJBR
  • Synonym for V$DLM_RESS

SELECT inst_id, kjirftrp, kjirftrn, kjirftcq, kjirftgq, kjirftpr, kjirftmn, kjirftncl, kjirftvs, kjirftvb FROM x$kjirft UNION ALL SELECT inst_id, kjbrresp, kjbrname, DECODE (kjbrcvtq, '00', 0, 1), DECODE (kjbrgrantq, '00', 0, 1), 1, kjbrmaster, kjbrncvl, 'KJUSERVS_NOVALUE', '0x0' FROM x$kjbr

v ges enqueue
V$GES_ENQUEUE
  • Contains rows from both KJILKFT and KJBL
v ges enqueue105
V$GES_ENQUEUE
  • Contains rows from both KJILKFT and KJBL

SELECT inst_id, kjilkftlkp, kjilkftgl, kjilkftrl, kjilkftrn1, kjilkftrn2, kjilkftpid, kjilkftxid0, kjilkftxid1, kjilkftgid, kjilkftoodd, kjilkftoopt, kjilkftoopo, kjilkftoonxid, kjilkftcogv, kjilkftcopv, kjilkftconv, kjilkftcodv, kjilkftconq, kjilkftcoep, kjilkftconddw, kjilkftconddb, kjilkftwq, kjilkftls, kjilkftaste0, kjilkfton, kjilkftblked, kjilkftblker FROM x$kjilkft UNION ALL SELECT inst_id, kjbllockp, kjblgrant, kjblrequest, kjblname, kjblname2, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, kjblqueue, kjbllockst, 0, kjblowner, kjblblocked, kjblblocker FROM x$kjbl

thank you for your interest
Thank you for your interest

For more information and to provide feedback please contact me

My e-mail address is:

info@juliandyke.com

My website address is:

www.juliandyke.com