MY DATABASE IS SLOW, WHAT SHOULD I DO

PURPOSE

This Article is designed to allow someone with little or no experience in database tuning to quickly tune an Oracle Database.

SCOPE & APPLICATION

The intended audience of this Article is anyone responsible for the tuning of an Oracle database. The intent of this article is to provide a step by step process for checking the most common performance problem areas and a way of correcting the problem

MY DATABASE IS SLOW, WHAT SHOULD I DO

The following steps will allow you to quickly analyze and/or fix most tuning problems associated with an Oracle Database.

The steps are ordered in such a way to find problems quickly.

Log Switches are costly

When Oracle performs a log switch a number actions are performed that are costly from a system resource standpoint. If Oracle is forced to switch between log files too frequently then the Oracle database is slowed down unnecessarily. The allocation of larger log files solves this problem.

SQL Statements must be tuned

Any SQL statement that is executed against the database has a cost in terms of resources used to complete the query. The most costly resource from a performance standpoint is disk i/o. Therefore any SQL statements that consume a large amount of disk i/o should be reviewed to insure that the i/o is necessary and that there is not another way to accomplish the same task with less i/o. For instance, an index may need to be defined on the table.

Sorts to disk are slow

All database systems seek to reduce disk i/o as much as possible by doing as much in memory as possible. Disk i/o's are significantly slower than memory access. Therefore if sorts are being done to disk they are not only much slower but are also competing with other oracle i/o operations.

The Cost Based Optimizer cannot optimize queries against tables that have not been analyzed.

If the blocks column of the dba_tables query is null or 0 then the table in question has not been analyzed. When this is the case the Cost Based Optimizer does not have any data in order to determine the best type of optimization for queries against the table. This can result in queries that consume much more system resource than is necessary, usually in the form of disk i/o.

Oracle can suffer from self inflicted memory starvation

When the Oracle software is executing and providing access to the data in the database it requires system resources. There are four types of system resources provided to Oracle by the hardware/operating system. Memory, CPU cycles, Disk Bandwidth and Network Bandwidth. DB_BLOCK_BUFFERS and SHARED_POOL_SIZE are examples of Oracle configuration parameters that may artificially starve Oracle for access to memory.

Oracle requires resources to perform a given workload

When the Oracle software is executing and providing access to the data in the database it requires system resources. There are four types of system resources provided to Oracle by the hardware/operating system. Memory, CPU cycles, Disk Bandwidth and Network Bandwidth. Because Oracle may be running on a system that has other software running on it, it is possible that the Oracle software is being starved for some type of system resource. Even if the Oracle software and database are tuned perfectly the database may still be slow. This can happen when the system is overloaded and one of the system resources is in short supply. Typically the system administrator finds these problems and deals with them but it is essential that the DBA is aware of the possibilities in the event that a performance problem is associated with system configuration.

Thanks

D.Sasi Kumar

 

0 comments: