Common causes of Oracle performance problems

• Bad Connection Management

The application connects and disconnects for each database interaction. This is a common problem with stateless middle-ware in application servers. This
mistake has over two orders of magnitude impact on performance, and it is totally unscalable. A possible solution here is to have a three-tier architecture
where users or clients connect to the middle tier that has permanent connections to the database. In this way one permanent connection to the database can
be used by different users.

• Bad Cursors Sharing

Not sharing cursors results in repeated parses. If bind variables are not used, then hard parsing occurs for all SQL statements. This has a significant negative
performance impact. Cursors with bind variables that open the cursor and re-execute it many times should be used. For large applications that don't use bind
variables, cursor sharing can be enforced by setting the initialization parameter CURSOR_SHARING=FORCE.

• Getting Database I/O Wrong

Many sites lay out their databases poorly over the available disks. Other sites specify the number of disks incorrectly, because they configure disks by disk
space and not I/O bandwidth.

• Redo Log Setup Problems

Many sites run with too few redo logs that are too small. Small redo logs cause frequent log switches which can put a high load on the buffer cache and I/O
system. If there are too few redo logs, then the archive process cannot keep up, and the database stalls. As general practice, databases should have at least 3
redo log groups with two members in each group.

• Lack of Free Lists, Free List Groups, and Rollback Segments

Serialization of data blocks in the buffer cache can occur due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback
segments. This is particularly common on INSERT-heavy applications with large database block sizes (8K to 16K). This problem can easily be avoided by
using the Automatic Segment Space Management feature along with Automatic Undo Management.

• Long Full Table Scans

Long full table scans for high volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long
table scans, by nature, are I/O intensive and do not scale for high number of users. OEM tuning pack offers a powerful tool, SQL Analyze, for identifying and
tuning resource intensive SQL. This is a good way of addressing SQL optimization issues.

• On Disk Sorting

Disk sorts, as opposed to memory sorts, for online operations could indicate poor transaction design, missing indexes, sub-optimal PGA configuration, or poor
SQL optimization. On disk sorts, by nature, are I/O intensive and unscalable. By allocating sufficient PGA memory and using Automatic PGA Tuning feature of Oracle9i, this problem can be eliminated.

• High Amounts of Recursive SQL

Large amounts of recursive SQL executed by SYS could indicate space management activities, such as extent allocations, taking place. This is unscalable and
impacts user response time. This generally occurs when using dictionary managed tablespaces. Locally managed tablespaces can help improve performance in such situations by significantly reducing recursive SQL.

• Schema Errors and Optimizer

In many cases, the application uses excessive resources because the schema owning the tables has not been correctly migrated from the development environment or from an older implementation. Examples of this are missing indexes or incorrect statistics. These errors can lead to sub-optimal execution plans and poor interactive user performance. When migrating applications of known performance, export!the schema statistics to maintain plan stability using the DBMS_STATS package. Likewise, optimizer parameters set in the initialization parameter file can override proven optimal execution plans. For these reasons schema statistics and optimizer settings should be managed together as a group to ensure consistency of performance.

• Use of Nonstandard Initialization Parameters

These might have been implemented based on poor advice, incorrect assumption or prior need. In particular, parameters associated with spin_count on latches and undocumented optimizer features can cause a great deal of problems that can require considerable investigation.