Identify the root causes of Poor Oracle Performance!!

 Author: Burleson

In this article I will discuss some root-causes of poor Oracle performance that will be really beneficial for Oracle database administrators.

Over- Normalization :

Over-normalization of database is one of the major reasons for poor database performance. Over-normalized Oracle tables, excessive or unused indexes and 15-way table joins are not feasible as compared to a simple fetch.

Inappropriate Disk I/O configuration:

Many database administrators use inappropriate RAID5, disk channel bottlenecks and poor disk striping which badly affect the database performance.

Slow DML Performance:

Very slow DML performance occurs when database administrators fail to set ASSM, freelists or freelist_groups for DML-active tables.

Improper Server Settings :

Server setting plays an important role in Oracle Performance. Server kernel parameters and I/O configuration settings like direct I/O have a profound impact on Oracle performance. Inappropriate setting of these parameters can have adverse effects on your database.

Recursive SQL by SYS:

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.

Non-reentrant SQL:

Non-reentrant SQL is without bind variables. If bind variables are not used, then there is hard parsing of all SQL statements. All SQL should use host variables/cursor_sharing=force to make SQL reusable within the library cache.

Not Enough RAM:

If you do not allocate enough RAM for shared_pool_size, pga_aggregate_target and db_cache_size then it can cause an I/O-bound database.

In Disk Sorting:

In disk sorts for online operations could indicate poor transaction design, missing indexes, or poor SQL optimization.

Failing to set pga_aggregate_target:

We have gigabytes available for RAM sorts and hash joins in 64-bit Oracle systems. If database administrator fails to set pga_aggregate_target to allow the CBO to choose hash joins then it can result in very slow SQL performance.

Poor Optimizer Statistics:

Prior to Oracle 10g which supports automatic statistics, a common cause of poor SQL performance was missing/stale CBO statistics and missing histograms.

Long Full Table Scans:

Long full table scans for high-volume or interactive online operations can indicate poor transaction design, missing indexes, or poor SQL optimization.

Improper initialization parameters :

Many of the initialization parameters must be set by database administrators. Amongst them important parameters are db_file_multiblock_read_count and optimizer_index_caching parameter. If a database administrator fails to set these parameters properly then it results in the poorly optimized execution plans.

Security Flaws:

If your database is susceptible to security problems then your database will be at risk. If you're building a client-server application then build a three tier application, so the client is not connect to the database directly. Allow only 1 IP Address (as application server) to be connected to the oracle database. Never use Oracle user ID (especially DBA user) as your Application user ID, instead create a user table with user and encrypted password in it. Don't forget to give your Oracle Listener a password, so when someone unauthorized send a stop message to the listener it don't work out.

Failure to delete records:

Sometimes it happens that deleting records would be fail because the database table has so many records but there is no more space in the rollback segment. You can use the TRUNCATE command to bypass the rollback segments when deleting records. No triggers that may exist get fired. Also, do not do this to a table that is a master snapshot.

Others:

If a database administrator fails to monitor their database (STATSPACK/AWR), do not set-up exception reporting alerts or fails to adjust their instance to match changing workloads then it can act as a major cause of poor performance.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips for Verifying Oracle Data Replication!!
   Secrets of Resources with AWR Reports, Great!!
   How to create an ePub? - Simple Steps to follow!!
   The power of Oracle table index rebuilding!!