Oracle Architecture

An Oracle server: a DBMS that provides an open, comprehensive, integrated approach to information management, Consists of an Instance and a database.

An Oracle Instance: a means to access an Oracle database, always opens one and only one database and consists of memory structures and background process.

An Oracle database: a collection of data that is treated as a unit, Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log)

Instance memory Structures:

System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance. SGA Memory structures: Includes Shared Pool,  Database Buffer Cache, Redo Log Buffer among others.

Shared Pool : Consists of two key performance-related memory structures Library Cache and  Data Dictionary Cache.

Library Cache: Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.

Data Dictionary Cache : Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.

Database Buffer Cache: Stores copies of data blocks that have been retrieved from the datafiles. Everything done here.

Redo Log Buffer : Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.

User process: Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server.

Server process: Connects to the Oracle Instance and is Started when a user establishes a session. fulfills calls generated and returns results. Each server process has its own nonshared PGA when the process is started.

Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA and Return results in such a way that the application can process the information.

In some situations when the application and Oracle Database operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead.

Program Global Area (PGA):  Memory area used by a single Oracle server process. Allocated when the server process is started, deallocated when the process is terminated and used by only one process. Used to process SQL statements and to hold logon and other session information.

Background processes:

Started when an Oracle Instance is started. Maintains and enforces relationships between physical and memory structures. Mandatory background processes: SMON, PMON, DBWn, LGWR, CKPT.

DBWn: Writes when: Checkpoint occurs,  Dirty buffers reach threshold, There are no free buffers, Timeout occurs, TBS level alterations.

LGWR: Writes when: Commit, When one-third full, Every three seconds, Before DBWn writes, When there is 1 MB of redo.

SMON: Responsibilities: Opens database, Instance recovery, Rolls forward changes in redo logs, Rolls back uncommitted transactions, Coalesces free space, Deallocates temporary segments.

PMON: Cleans up after failed processes by: Rolling back the transaction, Releasing locks, Releasing other resources, Restarting dead dispatchers.

CKPT:  Responsiblities : Signaling DBWn at checkpoints, Updating datafile headers and control files with checkpoint information.

Recursive Subquery Factoring

Here is a 11g R1 hierarchical query:
col text format a40
col mgr format 9999
select rpad(' ',2*(level-1))||empno||': '||ename text,
mgr
  from scott.emp
       connect by prior empno=mgr
       start with job='PRESIDENT'
 order siblings by ename;
TEXT                       MGR
----------------------------- -----
7839: KING
  7698: BLAKE                 7839
    7499: ALLEN               7698
    7900: JAMES               7698
    7654: MARTIN              7698
    7844: TURNER              7698
    7521: WARD                7698
  7782: CLARK                 7839
    7934: MILLER              7782
  7566: JONES                 7839
    7902: FORD                7566
      7369: SMITH             7902
    7788: SCOTT               7566
      7876: ADAMS             7788
Execution Plan
----------------------------------------------------------
Plan hash value: 763482334
-----------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    14 |   4    (25)|
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |       |      |
|   2 |   TABLE ACCESS FULL            | EMP  |    14 |   3     (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("MGR"=PRIOR "EMPNO")
       filter("JOB"='PRESIDENT')

And here, we see the equivalent query using the 11g R1 with clause recursive subquery factoring syntax:
col text format a40
col mgr format 9999
with empl (empno, ename, xlevel, mgr) as
  (select empno, ename, 1, mgr
from scott.emp
where job='PRESIDENT'
   union all
   select e.empno, e.ename, empl.xlevel+1, e.mgr
from scott.emp e, empl
where e.mgr=empl.empno)
  search depth first by ename set ord
select rpad(' ',2*xlevel)||empno||': '||ename text,
mgr
  from empl;
TEXT                       MGR
------------------------- -----
  7839: KING
    7698: BLAKE            7839
      7499: ALLEN           7698
      7900: JAMES           7698
      7654: MARTIN        7698
      7844: TURNER        7698
      7521: WARD           7698
    7782: CLARK            7839
      7934: MILLER        7782
    7566: JONES            7839
      7902: FORD           7566
    7369: SMITH            7902
      7788: SCOTT           7566
    7876: ADAMS            7788
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3907725112
-----------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    25 |  8   (25)|
|   1 |  VIEW                    |      |    25 |    8   (25)|
|   2 |   UNION ALL (RECURSIVE WITH) DEPTH FIRST|      |       |            |
|*  3 |    TABLE ACCESS FULL            | EMP  |     3 |    3    (0)|
|*  4 |    HASH JOIN                |      |    22 |    4   (25)|
|   5 |     RECURSIVE WITH PUMP         |      |       |       |
|*  6 |     TABLE ACCESS FULL            | EMP  |    13 |    3    (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("JOB"='PRESIDENT')
   4 - access("E"."MGR"="EMPL"."EMPNO")
   6 - filter("E"."MGR" IS NOT NULL)

Recursive WITH clause

CONNECT BY is an Oracle oddity. But does recursive with performs as well as connect by?
CONNECT BY Clause
select
   empno,mgr
from
   big_emp
connect by
   mgr = prior empno
start with
   mgr is null;

E M
- -
1 - 
2 1
4 2
6 2
8 6
Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  185      78
CONNECT BY WITH FILTERING
TABLE ACCESS FULL         BIG_EMP      1    1   61      10
HASH JOIN                              2    2  122      46
CONNECT BY PUMP
TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000
Recursive WITH clause:
with
   e(empno,mgr) as (
     select
       empno,
       mgr
     from
       big_emp
     where
        mgr is null
     union all
     select
        f.empno,
        f.mgr
     from
         big_emp f, e
     where
       e.empno=f.mgr)
select
   empno,
   mgr
from e;
E M
- -
1 - 
2 1
3 1
4 2
5 3
...
Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  183      78
VIEW                                   3    3  183      78
UNION ALL (RECURSIVE WITH) BREADTH FIRST
TABLE ACCESS FULL BIG_EMP              1    1   61      10
HASH JOIN                              2    2  122      46
RECURSIVE WITH PUMP
TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000
In this particular simple case, it seems CONNECT BY has a 1% higher cost.