How is the SGA in an ASM instance made up

An ASM instance has its own SGA but it is not the same as the SGA in a database instance.

The 4 main sections of the SGA in an ASM instance are:

  • ASM Cache – this is used during rebalancing operations for reding and writing blocks.
  • Large Pool – Used for Parallel Operations.
  • Shared Pool – Stored Metadata information.
  • Free Memory – Memory that is not currently allocated.
Categories: Oracle Basics, Oracle RAC Tags: Tags: ,

Oracle Architecture Part 3

No Comments

[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

Oracle Architecture Part 2

No Comments

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

Check out part 1 first.

Notes:

Online Redo Logs

  • Contains Redo and Undo info
  • DML
  • Inserts/Deletes
  • DDL – Create alter drop
  • Commits
  • Need at least 2 files/groups
  • Should be multiplexed each on different devices and controlers
  • When each file fils, next one is started and old one is archived (if in Archivelogmode)

An Oracle database is comprised of datafiles, control files
and redo logs.

The second part is the memory – the SGA (system global area
or shared global memory)

Database Buffer Cache

Comprised of Oracle data blocks

There are 4 main types of data blocks, which are the same as the 4 types of data mentioned earlier:

  • Data
  • Index
  • Rollback
  • Temporary

Part 3…

Categories: Oracle Basics, Video Tags: Tags: , , , , , ,

Oracle Architecture Part 1

No Comments

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

There are 3 essential elements of any database system

  • Datafiles – storage
  • Memory – in oracle its the SGA
  • Processes – To operate on the data files and memory

Storage

  • Datafiles
  • Contains data
  • Tables
  • Indexes
  • Undo segments (used to be rollback segments)
  • Temporary files
  • Oracle is easy as DIRT – Data, Index, Rolback, Temp
  • In Oracle you can now have around 65000 datafiles

Important views for the DBA

  • dba_data_files
  • v$dbfile
  • v$datafile

Control files.

Should have 3 identical control files on different disks and controllers

They contain information about the database

  • db name
  • created date
  • path to data files
  • check point info

Important Views

  • v$controlfile

Watch Part 2…