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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Find Oracle Database Design flaws

Oracle Tips by Burleson Consulting
July 17, 2003
Don Burleson


Once you have your database design arsenal in place, you can begin the work of building correct physical designs from scratch and managing the physical design lifecycle once a system goes into production.  But, how do you quickly spot physical design flaws in an up-and-running database? 

It definitely takes a trained eye to uncover the root cause of identified performance problems, but the table below will help get you started.  It lists just a few of the most common database performance problems and the possible physical design gremlins that could be the culprit in an Oracle database.


Performance Category

Performance Problem

Possible Design Cause


Poor Data Buffer Cache Hit Ratio

Too many long table scans ? invalid indexing scheme

Not enough RAM devoted to buffer cache memory area

Invalid object placement using Oracle?s KEEP and RECYCLE buffer caches

Not keeping small lookup tables in cache using CACHE table parameter


Poor Memory/Disk Sort Ratio

Not presorting data when possible





Redo log waits

Incorrect sizing of Oracle redo logs

Insufficient memory allocated to log buffer area


Free list waits

Not enough free lists assigned to tables

Not using Oracle?s auto segment management


Rollback waits

Insufficient number of rollback segments

Not using Oracle?s auto-UNDO management





Identified disk contention

Not separating tables and accompanying indexes into different tablespaces on different physical drives


Slow access to system information

Not placing SYSTEM tablespace on little accessed physical drive


Slow disk sorts

Placing tablespace used for disk sort activity on RAID5 drive or heavily accessed physical volume


Abnormally high physical I/O

Too many long table scans ? invalid indexing scheme

Not enough RAM devoted to buffer cache memory area

Invalid object placement using Oracle 8?s KEEP and RECYCLE buffer caches

Not keeping small lookup tables in cache using CACHE table parameter





Out of space conditions (storage structures)

Poorly forecasted data volumes in physical design


Tablespace fragmentation

Invalid settings for either object space sizes or tablespace object settings (PCTINCREASE, etc.)

Not using locally-managed tablespaces in Oracle8 and above





Large JOIN queries

Over normalized database design




Object activity

Chaining in tables

Incorrect amount of PCTFREE , PCTUSED  settings for objects

Too small database block size


Rollback extension

Incorrect sizing of rollback segments for given application transaction

Not using Oracle?s auto-UNDO management


Many large table scans

Incorrect indexing scheme


Object fragmentation

Incorrect initial sizing

Not using locally-managed tablespaces

 Table 3.1 ? Performance Problems and Possible Causes

Using a quality performance monitor, you can be quickly led to the performance headaches in your database, and then, using either your intelligent data-modeling tool or the combination of your database administration/change control product, you can remedy the situation. 

Fixing foundational flaws in a database is never easy, but perhaps one day the DBA community will be treated to software that gets things right, before the situation turns ugly. 




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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.