Architeture Topics

Oracle Architecture

Start with Oracle 9i PDF’s from Oracle Corporation for understanding of Architecture

SGA & PGA&UGA : 

1. Understanding of what exactly is SGA, PGA, UGA

2. Importance of SGA & PGA

3. Components of SGA & PGA

4. Memory calculation for SGA and PGA

5. Oracle 10g allowed automating SGA management using the SGA_TARGET parameter.

6. SGA_TARGET & SGA_MAX_SIZE parameters

7. PGA – aggregate & PGA_AGGREGATE_TARGET parameter - Oracle 9i automated PGA    management by introducing PGA_AGGREGATE_TARGET parameter

8. Dedicated Server Mode and Shared Server Mode

9. Dispatcher background process in shared server mode and in dedicated server mode

10. Physical IO and Logical IO

11. SGA –

       a. Database Buffer Cache –

        ·  LRU and MRU,

        · Buffers – pinned, free, dirty, clean or used,

        · Checkpoint Queue

    b. The database buffer cache has three pools:

         · Default sized by DEFAULT_CACHE_SIZE and is required,

         · Keep pool sized by DB_KEEP_CACHE_SIZE and used to keep buffers in memory mainly the  most used buffers.

         ·  Recycle pool sized by DB_RECYCLE_CACHE_SIZE used when you know that buffers  will not be used again so best to get them flushed and get the space back.

         ·  DB_nK_CACHE_SIZE - You can use non-standard block size pools but this is only used when you want to change block sizes within a tablespace. It can also be used when you might be storing multimedia items that require large block sizes. It is only supported by Oracle for transporting tablespaces not performance



12.   Result Cache Feature (11g)

13.   PGA-  parameter cursor_sharing

       
Caching and Pooling – 11g

1. Enhancing performance by using SQL Result Cache, PL/SQL Function Cache and Client Side Caches,

2. Database Resident Connection Pooling.


BG Processes

1.      How Background processes works

2.      when are they get initialized

3.      what other things are dependent on BG processes

4.      Functions of each BG Processes

5.      Background processes which are introduced in Oracle 10g – like psp

6.      Types of checkpoint

7.      ckpt background process and the content it writes to datafile header and inside control file



Server Process, User Process, SQL Statement Processing

1.       Server Process & User Process

2.        Cursor allocation

3.       Major components involved in the process of Statement processing

4.        Types of statements fired by user and accordingly how the SQL Statement gets processed

a.       DML

b.      DDL-TCL or DCL-

c.       Query


Physical Files (including pfile, spfile and network files)

1.       Oracle Architecture – Important files in the architecture

2.       Their  locations, importance of each file and related scenarios

3.       Password file, recreation of password file, deleting password file,

4.       Intro to tnsnames.ora, listener.ora and sqlnet.ora


Start and shut down of a database

1.       Different ways of shutting down as well as different ways to make the database start

2.       Startup phases

3.       Shutdown phases

4.       Types of startup


spfile ,pfile, init parameter file

1.       Dynamic Parameters of spfile

2.       Difference between dynamic parameter and static parameter

3.       Difference between spfile and pfile

4.       Default location of  spfile as well as pfile

5.       Default parameter file used to start the instance and sequence of parameter files searched

6.       Data dictionary views to see parameters  as well as show parameter

5.       Pfile/spfile – advance – hidden parameters or otherwise known as underscore parameters

6.       V$parameter and v$parameter2  - dynamice view to see the parameters and their values

7.       Another way of looking at parameter and its respective value  (Show parameter parameter      name)

Oracle RDBMS

1.       Database Server

2.       Instance

3.       Database

Networking files, password file and pfile/spfile

Memory

a.       Granules

b.      View/data dictionary to see the occupied blocks / granules

c.       Output of show SGA

Data Dictionary Views

1.       Different types of Data Dictionary Views – dba_ & all_ & user_

2.       Which views are accessed at what stage

3.       Which views gives us information about SGA memory blocks.

Connecting Database

1.       Connection

2.       Session

Instance Caging to Manage CPU Usage Features

Oracle Database 11g Release 2

1.       Enabling Resource Manager

2.       Setting CPU_COUNT

3.       Monitoring Instance Caging

Automatic Memory Management - AMM – Oracle 11g release 1

1.        Automatic Memory Tuning

2.        Considerations Before Using AMM