Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle DBA Worst Practices

Oracle Database Tips by Burleson Consulting
Updated April 12, 2015

 

There is a great deal of discussion about following Oracle best practices (e.g. using OFA, etc.), but little has been said about Oracle worst practices.  Contrary to an Oracle "best practice", an Oracle "worst practice" is an action (or non-action) that causes poor performance and creates excessive management overhead, essentially a non-standard approach to Oracle database management.   

Let's look at a few Oracle worst practices I have run across in my experience:

Oracle Worst Practices for the DBA

Many Oracle shops commission an Oracle Health Check to find these issues, but worst practices are alive-and-well in the Oracle community.

Suboptimal Database Design

Corporate America is where theory meets pragmatism.  Back in the 1980's when disk was $200k/gig, designing a database in third normal form was a perfect approach, since 3NF is non-redundant, and a DBA management goal was to save expensive disk space. 

Obviously, the rules of normalization are required to understand the relationships & functional dependencies, but BCNF (or 3NF for those non-purists) is just a starting point, not a completed model. 

No one can deny that legacy systems tended to be more highly normalized because of the high cost of disk in the 1990's.  Today, disk is incredibly cheap, and many Oracle professionals use  denormalization and pre-joins that our data can be retrieved with far less runtime overhead. 

In addition, Oracle offers several popular denormalization tools, some that create non first-normal form structures (0NF):

  • Object tables - Oracle has nested tables and varray table columns whereby repeating groups are stored within a row, violating 1NF.
     
  • Materialized Views - Tables are pre-joined together, queries are re-written to access the MV, and a method (Oracle snapshots) keeps the denormalization in-sync with the normalized representation of the data.

How and where do we introduce redundancy to remove table joins?  The answer depends on the "redundancy boundary", a function of the size and volatility of the redundant item. 

Today Oracle provides us with tools to manage denormalization (materialized views) and it is an Oracle worst practice to do an 8-way table join for every simple transaction fetch.

Inadequate Indexing

One of the top causes of excessive I/O during SQL execution is missing indexes, especially function-based indexes.  Failure to tune the instance according to the SQL load is a major worst practice.  It's no coincidence that the Oracle 10g SQLAccess advisor makes recommendations regarding missing indexes. 

See my notes:

Poor optimization of initialization parameters

The worst Oracle practice of all is undertaking to tune your SQL before these global parameters have been optimized to the workload.  Many shops fail to grasp the critical role of silver bullet parameters such as optimizer_mode, db_file_multiblock_read_count, pga_aggregate_target, optimizer_mode, and other critical parameters. 

As documented in the book "Oracle Silver Bullets", global, instance-wide changes can improve performance by orders of magnitude. 

Prior to Oracle 10g, adjusting powerful optimizer parameters such as optimizer_index_cost_adj was the only way to compensate for sample size issues with dbms_stats

But as of Oracle 10g, improvements in system statistics collection using dbms_stats.gather_system_stats, used to measure sequential vs. scattered disk I/O speed, plus improved sampling within dbms_stats had made adjustments to the optimizer parameters a worst practice exercise in most cases.  Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms.  For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference". 

For more on holistic instance tuning, see my notes:

Poor Schema Statistics Management

The Oracle worst practice (before 10g automatic statistics collection) was to re-analyze the schema on a schedule, forgetting that the purpose of re-analyzing schema statistics is to change your production execution plans.  This worst practice has become so commonplace that it has been dubbed the "Monday Morning Suprise".  Shops with strict production change control procedures forget that analyzing the production schema can effect the execution of thousands of SQL statements.

Just as you custom-set your pga_aggregate_target and db_cache_size according to your environment, you need to set your metadata and system stats (via the dbms_stats package), according to your specific environment.  It's not a one-size-fits-all utility and it must be set intelligently.  See my notes here:

Bad disk I/O sub-system

It's amazing how many shops fall into worst practices by following vendor advice and implementing an inappropriate disk layout for Oracle.  RAID5 for high-DML systems, failure to mirror disk are a major Oracle worst practice.

Using NOARCHIVELOG mode

While it is true that running in ARCHIVELOG mode adds some significant overhead to an Oracle database, especially those with high DML activity, it is likely a serious mistake to simply make the change to NOARCHIVLOG mode without carefully weighing the burned of that overhead against the potential for catastrophic loss of data that can occur in the event of an event like disk failure.

Oracle's ARCHIVELOG mode is a safety mechanism whereby Oracle can "roll forward", by applying the "after images" of all changed rows.  It is where the entries in redo logs are saved prior to the point in time where a new incarnation of the log overwrites the previous log.  Internally, redo is a critical recovery component, and its entries contain changes to data, indexes and undo segments (as well as commit markers and other 'housekeeping' items such as end-of-job and commit checkpoints).  Archiving makes sure that the redo component is protected. 

User Profiles

 It is a bad practice to use profiles.  All profiles do is limit the usage of a user, annoying them and possibly interrupting their work!


Oracle Environmental Worst Practices

No Instance Consolidation

The nightmarish approach of one Oracle instance per server was never a benefit.  It was a requirement of the limited computing power of the tiny minicomputers of the client server days of the 1990's.  Remember, the one-instance-per-server requirement was imposed by the tiny servers of the past, and now that we have large monolithic servers for UNIX/Linux, smart companies are returning to the time-tested approach of having a single server environment with many Oracle instances.  (See OTN "Strategy for Success: Consolidate!". 

There are many compelling benefits to having multiple instances on a large scalable server (Oracle instance consolidation), all part of the second age of mainframe computing:

  • Easier maintenance - The Oracle Optimal Flexible Architecture (OFA) is constructed so that dozens of instances can share the same binaries making patching non-repetitive. 

  • Better sharing of computing resources - In a Grid environment you can only add additional RAM of CPU resources by adding a new server blade.  In a large monolithic server, CPU resources are shared instantly between instances.

Poor Change Control Testing

This is the worst of the worst practices, where an Oracle shop relies on a "test case proof" to preview how a database change will effect production behavior.  Smart shops do a full TPC benchmark, using Oracle benchmarking tools like the Quest Benchmark Factory.

Inadequate Test Environments

One way to guarantee sub-optimal SQL in production is to provide an inadequate database for your developers.  Remember, you can export your production schema statistics to make your development system look like production. 

See my notes:

No Change Control Procedures

In highly active development shops, version control for schema objects, stored procedures and code chunks are absolute necessities.

Not Enough Testing Instances

Many Oracle shops keep fours environments: DEV for development; TEST for unit testing; QA for pre-production testing; and PROD for production.  Any less Oracle testing and you may risk unintended side effects.

No Performance Tracking

With STATSPACK (free) and AWR starting with Oracle10g, there is no excuse for not tracking your database performance.  STATSPACK and AWR provide a great historical performance record and set the foundation for DBA predictive modeling.  Also see Oracle tuning best practices and Oracle performance monitoring best practices.

Poor Security Management

Oracle offers a host of access control mechanisms (grants, roles, VPD) yet it is amazing how many shops have giant security holes.

No Alert Mechanisms

Oracle Enterprise Manager (OEM) has built-in alert capability and you can write your own alerts for many critical Oracle events (filesystem filling-up, object cannot extend, replication backlog, etc.).  The vast majority of Oracle outages are preventable and it is a worst practice to wait until Oracle is crippled before fixing an issue.

Non-standard External Environments

This is another of the worst of worst practices where every database uses different shells, different aliases and  non-standard file locations. 

See my notes:


References:

This article covers the top 20 most common errors made by Oracle professionals:

The author mentions many Oracle DBA "worst practices" including no standards, rushing to upgrade to the latest release of Oracle, poor Oracle security and poor Oracle tuning practices. 


PL/SQL Worst Practices

While not a DBA worst practice area, Steve Feuerstein has this list of PL/SQL worst practices:

Here are a few of the journeys we will take. 

The bad old days of cursor FOR loops
In the bad old days, we didn't think twice about using cursor FOR loops: quick and easy way to fetch a single row, simplest way to iterate through multiple rows. At back then (as in Oracle8!), this made perfect sense. But with FORALL and BULK COLLECT, along with SAVE EXCEPTIONS and INDICES OF clauses, our world and way we should write SQL inside PL/SQL has changed forever. 

Hard-coding? Let me count the ways....
Literal values, direct datatype declarations, SQL statements, -20NNN error numbers...the list of hard-codings inside PL/SQL is long, bewildering and intimidating. It's not easy to keep hard-codings out of your code, but once you acknowledge all the scenarios that constitute hard-coding you can then proceed to remove them.

Optimistic programming or just plain laziness?
We are generally challenged sufficiently to get our programs working properly. Thinking about and incorporating error handling into our programs often comes later or not at all. We will start with very poorly implemented error handling section and apply a variety of recent PL/SQL language enhancements to bring it up to 21st century standards.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational