For an Oracle DBA, here are some reasons that was consider as the most common root-causes of poor Oracle performance:
- Bad Design – The number one offender to poor performance is over-normalization of Oracle tables, excessive (unused indexes) and 15-way table joins for what should be a simple fetch.
- Bad disk I/O configuration – Inappropriate use of RAID5, disk channel bottlenecks and poor disk striping.
- Object contention – Failing to set ASSM, freelists or freelist_groups for DML-active tables and indexes can cause very slow DML performance.
- Non-reentrant SQL – All SQL should use host variables/cursor_sharing=force to make SQL reusable within the library cache.
- Excessive nested loop joins – In 64-bit Oracle systems we have gigabytes available for RAM sorts and hash joins. Failing to set pga_aggregate_target to allow the CBO to choose hash joins can result in very slow SQL performance.
- Poor server optimization – Setting the server kernel parameters and I/O configuration (e.g. direct I/O) has a profound impact on Oracle performance.
- Poor Optimizer Statistics – Prior to Oracle 10g (automatic statistics), a common cause of poor SQL performance was missing/stale CBO statistics and missing histograms.
- Under-allocated RAM regions – Not allocating enough RAM for shared_pool_size, pga_aggregate_target and db_cache_size can cause an I/O-bound database.
- Un-set initialization parameters – Many of the initialization parameters are made to be set by the DBA (db_file_multiblock_read_count, optimizer_index_caching) and failing to set these parameters properly results in poorly optimized execution plans.
- Human Misfeasance – The DBA’s failure to monitor their database (STATSPACK/AWR), set-up exception reporting alerts (OEM) and adjusting their instance to match changing workloads is a major cause of poor performance.
