Oracle Schema Documentation Oracle Database Tips by Donald Burleson

This document outlines who to document details on a schema, specifically the DBA features for these areas:

• Schema documentation - (tables and indexes)

• Data structures (data files & tablespaces documentation)

• Server documentation - (installation, file locations & permissions, kernel settings)

• Oracle instance documentation - (initialization non-default parameters, kernel parm settings). 

It's important to document all schema non-default details and this document justifies all of our choices as supplemental documentation to the file comments on the database server.

The Schema (tables and indexes)

Non-default table parameters

Many shops are using "max extents unlimited" for tables & indexes to reduce the risk of Oracle :hanging" because an object has reached max extents. . . .

For large time-series tables, we are partitioning by month, and the following were identified as high-growth time-series tables:

• Clearance history table
• Call_log table

PCTUSED

We are using PCTUSED = 10 on all pages because we do not want to intermix unrelated data (which happens when we release a page back into the freelist).

PCTFREE

We are using PCTFREE = 60 to allow room for row expansion.  Because some general tables store frequently-changing information (such as a person's address history), the following tables were identified as volatile, and required a lower PCTFREE:

• Address_history table
• Transient_logs table

Indexing strategy

The Oracle data structures
(data files & tablespaces)

Tablespace details

Here are our choices for tablespace definition:

• Block management - The tablespaces are defined as locally managed tablespaces. 

• Freelists - We chose to use automatic segment space management "segment space management auto", which creates a bitmap to manage free blocks within the tablespace (as opposed to the older structure of one-way linked-lists).  The exception is imposed by Oracle (not for large objects, like photographs and scan documents). The following tables are defined as non-ASSM because they contain large objects:
   

  • Photo_table table

  • Image_scan table

The Server
(file permissions, kernel parm settings).

The server has the following characteristics:

• CPU Name:
• Number of CPU's:
• Dual core/multithreaded?
• T1 RAM amount (from chip specs):
• T2 RAM amount:

The operating system details are:

• OS:
• Release version:
• Patch level:

File installation details include (in addition to full compliance with OFA standards):

• Location of $ORACLE_HOME
• Default settings for umask
• Location, name and file permissions for all mount points
• Crontab listings for all defined users
• Details on archive log directories (sizing)

The Oracle instance
(initialization non-default parameters, kernel parm settings).

This documents both system-wide setting and non-default choices for specific parameters.

System-wide parameters

All standard default init.ora parameter settings were used except for the user-configured section on the startup file (located in $ORACLE_HOME/admin/$ORACLE_SID/pfile) and the choices include the partitioning on the server RAM for use by Oracle.

Pool Segregation

• KEEP POOL - We chose to implement the KEEP Pool for all lookup tables and any tables used as foreign keys, lookup tables, and any tables that v$bh show that are more than 70% cached.  The following tables are assigned to the KEEP pool.
   

  • Customer_salutation_lookup table
  • State_name_lookup table
     

• Multiple blocksizes - We chose to utilize large blocksizes for large objects and objects that are accessed in a sequential prefetch (as shown by a history of multi-block reads in AWR).
   

  • Tables with large objects (for a 32k blocksize tablespace):

  • Photo table
  • Image_scan table

Tables and indexes with small rows that are randomly accessed will waste less RA space in placed in a smaller blocksize.  If a table has 80-byte rows and there is never multi-block I/O (random fetches) then placing this tables in a smaller blocksize tablespace (the size dependent on the OS fetch blocksize) will result in a read of, say 4k, using –up less buffer than if the table was on a 16k blocksize.  For indexes, AWR can display the usage history of indexes with high historical values for "UNIQUE" (as opposed to RANGE SCAN) access.

Tables/Indexes with small objects/random access (for an 8k blocksize tablespace):

• Prod_lin table
• Cust_onon_idx5 index

Buffer Sizes:

• db_cache_size (db_block_buffers):
• KEEP POOL:
• RECYCLE POOL:
• db_xx_cache_size(s):

Other area sizes:

• Sort_area_size
• Pga_aggregate_target
• Hash_area_size
• Method for allocating super-large sorting or hashing areas for evening batch jobs:

Optimizer parameter settings should be set according the needs of the application, and some like optimizer_index_caching allow the DBA to tell the optimizer how effectively their index buffer cache is operating.  If there is enough room in the cache for all of the indexes, re-setting optimizer_index_caching = 100 will tell the optimizer that index access will be very fast.   

• Optimizer_mode:
• Optimizer_index_caching:
• Optimizer_index_cost_adj:
• Cursor_sharing setting
• List all other non-default parameters

 For more information on Oracle best practices and schema documentation, see the book "Oracle Best Practices: Practical Standards for Success" for 30%-off at the link below:

Oracle Best Practices: Practical Standards for Success Kent Crotty Only $19.95

Related Schema Articles:

Oracle extract schema tips
Get Oracle schema DDL syntax with dbms_metadata
Oracle Schema Components
Tracking Oracle schema changes
Oracle schema change control
Oracle Schema Statistics Management