Oracle Physical/Logical Structures

As a review, the following list is the physical and logical data structures that Oracle uses. This is taken out of the Oracle 8i DBA Handbook

1. Internal database structures - a very brief overview of all Oracle internal structures:
   1. Tables, columns, constraints, datatypes
   2. Partitions & subpartitions
      1. Breaking up larger tables into smaller ones based on particular value
      2. Helps performance because optimizer will know where to look for particular entries
      3. Large partitions can be further subpartitioned.
      4. Index partitions – same concept
         1. Local: partitions match the table partitions
         2. Global: partitions don’t match the table partitions.
   3. Users
   4. Indexes, clusters, & hash clusters
   5. Views
   6. Sequences
   7. Procedures, functions, packages, and triggers.
   8. Synonyms
   9. Privileges and roles
   10. Database links
   11. Segments, extents, and blocks
   12. Rollback segments
   13. Snapshots and materialized views.
       1. Snapshot provides data replication from a remote database based on a query. Can be scheduled, read-only or updateable.
       2. Materialized view provides the same functionality for local databases. Apparently, the optimizer decides when to use a materialized view vs running the query as is.
   
   
2. Internal memory structures
   1. System global area: Primarily composed of:
      1. Data block buffers: Holds the data. This is the cache hit/miss thing you keep hearing about.
      2. Redo log buffer: Redo log buffer: Tracks changes to the database.
      3. Shared sql pool: Important parts of which:
         1. Dictionary cache: Holds the dictionary. If the cache is too small, data dictionary will be queried, resulting in a recursive call.
         2. Library cache (sql & pl/sql parsed statements)
   2. The large pool: Optional memory area used to store large sql statements commonly used in backups, etc.
   3. The Java pool
   4. Multiple buffer pools
   5. Context areas
   6. Program global area (PGA): Used to store real values in place of bind variables for executing SQL code.
   
   
3. Background processes:
   1. smon: System monitor
      1. Responsible for database recovery
      2. Coalesces contiguous extents (pctincrease = 0 in tables)
   2. pmon: User process monitor – cleans up failed user processes.
   3. dbwr: Database writer – manages the data block buffer cache.
   4. lgwr: Log writer – writes the redo log buffer cache to the redo logs.
   5. chkp: Checkpoints:
      1. Causes dbwr to write all dirty data blocks to disk
      2. Update the data file headers & control file with the timestamp of the checkpoint.
   6. arch: Archives redo logs before lgwr overwrites them.
   7. reco: Attempts to resolve problems with distributed databases.
   8. snp#: Snapshot refreshes
   9. lck# Lock processes for interinstance locking.
   10. S### Server processes: Executes data reads from disk on behalf of user processes in an MTS environment.
   11. D### Dispatcher processes: Connects user processes from SQL*Net listender via dispatcher to server (S###) process.
   12. P##: Parallel Query server processes:
   
   
4. External structures:
   1. Files: Tablespace files are considered both internal, since they’re used for tablespaces, and external, since they’re OS dependent.
   2. Redo logs:
      1. A log of everything that happens on the database.
      2. Used in the event of a database crash
   3. Control files
      1. Contains the entire architecture of the database. Very important
      2. Maintained on separate disks (hopefully)
   4. Trace files – all background processes create trace files; used for indepth troubleshooting.
   5. Alert log: Alert log keeps track of major database events. Should be monitored daily