Oracle Best Practices Guidelines
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose 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, hardware, operating system, legal jurisdictions, ...: Best practice isn't just about technology and it definitely isn't about selecting one practice and repeating it blindly everywhere. What is best in one situation, for example an init parameter value may be a horror story in another.

This page, and its links, should not be interpreted as being the "right" answer ... but rather as an 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 rather than practices that should be mandated.
 
Topic Discussion
Backup and Recover There is little question that the best tool for backup, restoration, and recovery is RMAN. But RMAN is only 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. Are they B*Tree because someone carefully considered all of Oracle's index types or just because somone typed "CREATE INDEX" and didn't apply any additional synaptic energy.
  • If your index is supporting a primary key and PCT_FREE is not set to 0 ... reconsider your design decisions. What do you know, or not know, about the index that makes your decision for a PCT_FREE value valid?
  • Have you used MONITORING to verify indexes are being used in development and pre-production testing?
  • 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? 
  • Have you considered ROWDEPENDENCIES? If not why not?
  • Have you enabled ROW MOVEMENT? If not why not?
  • Have you considered index and table COMPRESSION? If not why not?
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.
Array Processing
  • Does your FORALL statement contain a SAVE EXCEPTIONS clause? If not reconsider your decision or create an error table with DBMS_ERRLOG.
DML
  • If you are writing code that tests whether a record already exists, updates it if it does and inserts if it does not ... examine using MERGE instead. Also consider using DBMS_ERRLOG.
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 Oracle MultiMedia.
NOT NULL Constraints
  • If a column contains no NULLs then create a NOT NULL constraint in most cases doing so will improve the quality of information available to the Cost Based Optimizer. For example.
Number of columns in a table Oracle stores columns in variable length format
Each row is parsed in order to retrieve one or more columns
Each subsequently parsed column introduces a cost of 20 CPU cycles whether or not it is used

Never let the number of columns in a table exceed 255.
PL/SQL Code
  • 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:

    FUNCTION whoami RETURN VARCHAR2 IS
      RETURN 'My Package, Version 1.1.5, 29-Feb-2014';
    END whoami

     Thanks to Ed Prochak for the suggestion.

Predefined Inquiry Directives Because these are a free feature included in the database you've likely never heard of them. [Click here], run the demos and learn about these valuable parts of the product you've already paid for.
Replication
  • Enable Force Logging
  • Enable Supplemental Logging

Related Topics
DBA Best Practice Guidelines
Developer Best Practice Guidelines
Troubleshooting Best Practice Guidelines

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved