- Bad Connection Management
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
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
space and not I/O bandwidth.
- Redo Log Setup Problems
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
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
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
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
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
- Use of Nonstandard Initialization Parameters