Tag: SGA

[embedplusvideo height=”356″ width=”584″ standard=”http://www.youtube.com/v/L_2TfSHotsY?fs=1″ vars=”ytid=L_2TfSHotsY&width=584&height=356&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=” id=”ep6822″ /]

Watch the rest of this series:

Part 1

Part 2

Part 4

Notes:

Initilisation params for the Buffer cache:

• db_cache_size
• db_block_size
• It is very helpfull if the buffer block size is a multiple of your operating system block size.

The Buffer cache is controled with a Least Recently Used (LRU) algorithm which determines what stays in and what gets removed from the SGA.

The goal being to have the data that is accesses most frequently kept in the SGA so that it can be accessed fast.

Shared pool.

The purpose of the Shared pool is to reduce parsing

The Shared pool consists of 2 parts:

• Library Cache
  • text of sql statements
  • meta code – compiled version of above text
  • execution plan that the optimiser will use
  • Views:
    • v$sqlarea
    • v$sql

• Dictionary Cache
  • names of objects that have been touched recently
  • privalages and roles – access information

The Shared Pool also usesa  LRU algorithm.

Initialization parameter – shared_pool_size controls size of shared pool.

 

Java Pool

• Initilization parameter – java_pool_size
• large pool – optional
• if using RMAN configure a large large pool
• shared server uses this

 

Watch Part 4