PeopleSoft Learning

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.

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.

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.

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.

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.

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.