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 


 

 

 


 

 

 
 

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

Memory

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

 

 

 

Contention

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

 

 

 

I/O

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

 

 

 

Space

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

 

 

 

SQL

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

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational