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.

��