Notes on ‘Expert Oracle’ — No.4: Memory Areas

1.
SGA & PGA

  a. SGA: Shared Area for all the processes/threads

  b. PGA: The area for a single process/thread

  SGA ∩ PGA == Ø

2.
UGA: The area for the user 

  a. UGA ⊆ SGA if "Shared Server" is used.

  b. UGA ⊆ PGA if "Dedicated Server" is used.

3.Important components of SGA

  a.
Redo Buffer — extremely helpful for big/long transactions, since the redo log is involved for transactions.

  b.
Block Buffer Cache — Caching query results

  c.
Shared Pool

     i. Bascially for compiled SQLs. So, always use "Bind variables" in SQLs because this way there will be too much SQLs in this pool(Think otherwise).

     ii.LRU-Cache

  d.
Large Pool  — For large memory alloacation

4.
SQLs for SGA

  a. See pools

     select * from V$SGASTAT

  b. See pool size

     select sum(bytes)/1024/1024 mbytes from V$SGASTAT where pool = ‘shared pool’

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.