Oracle Code Library
Find Or Post Oracle Jobs
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 Search the Reference Library pages:  

Oracle Magazine
and Oracle White Papers

Oracle Best Practices
Version 11.1
Why a best practices page?

The more you know about Oracle the easier it is to say that what constitutes "best practice" changes depending upon many factors including SLA, business rules, product version: Best practice isn't just about technology.

This page, and its links, should not be interpreted as being the "right" answer ... but rather a best attempt to point to factors that should be considered in putting together your internal guidelines. Think of it as a list of practices that should be considered.

Topic Discussion
Backup and Recover There is little question that the best tool for backup, restoration, and recovery is RMAN. But RMAN is but one piece in the puzzle that constitutes best practice. Here are some other topics you might wish to explore:
Create Index
  • If every index is a B*Tree index ... reconsider the design decisions
  • If your index is supporting a primary key and PCT_FREE is not set to 0 ... reconsider your design decisions
  • Have you used MONITORING to verify indexes are being used?
  • Consider using Function Based Indexes. Especially to enforce business rules and in cases where the cardinality is skewed and a B*Tree index might not be used most of the time.
  • Will you be running queries where you are looking for NULL? If so consider the use of function based indexes with an expression that substitutes a value for NULL.
Create Table
  • If every table is a heap table ... reconsider the design decisions
  • If you are using the default values for PCT_FREE and PCT_USED ... reconsider your design decisions
  • Have you considered index-organized tables for lookup tables and other purposes? 
Cursor Loops
  • There is essentially no valid reason to use a cursor loop in any version of Oracle since 9.0.1. If you are about to create a cursor loop ... stop ... and write the code using BULK COLLECT with the LIMIT clause and FORALL to perform the insert, update, and/or delete.
  • If you are writing code that tests whether a record already exists, updates it if it does and inserts if it does not ... use a MERGE instead.
Loading Data
  • The best solution is not always SQL*Loader. Look at external tables, look at using the UTL_FILE built-in package. If the data includes BLOBs consider UTL_FILE, DBMS_LOB, and Intermedia.
NOT NULL Constraints
  • If a column contains no NULLs then create a NOT NULL constraint in cases where it will improve the quality of information available to the Cost Based Optimizer. For example.
  • Put all PL/SQL procedures and functions into packages
  • In each package put a WHOAMI function that returns the package's version information.
    For example:

      RETURN 'My Package, Version 1.1.5, 29-Feb-2008';
    END whoami;

     Thanks to Ed Prochak for the suggestion.

  • Enable Force Logging
  • Enable Supplemental Logging
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [68 users online]    © 2010