Oracle Database Performance Tuning - Basic - WH

 

Database tuning is a group of activities used to optimize the performance of a database.

Goal Of Database Tuning?

• To maximize use of system resources 
• To perform task as efficiently 
• To work  rapidly as possible

Why and when should one tune?

Slow Physical I/O

-caused by poorly-configured disks

-caused by unnecessary physical I/O 

-caused by poorly-tuned SQL.

Excessive CPU usage

-It means that there is little idle CPU on the system

-caused by an inadequately-sized system,

-caused by untuned SQLstatements

-caused inefficient application programs.

Latch Contention

Rarely is latch contention tunable by reconfiguring the instance. 

Rather, latch contention usually is resolved through application changes.

Causes for low Performance:-

•  Bad Connection Management
•  Bad Use of Cursors and the Shared Pool
•  Bad SQL
•  Use of Nonstandard Initialization Parameters
•  Getting Database I/O Wrong
•  Redo Log Setup Problems
•  Long Full Table Scans
•  High Amounts of Recursive (SYS) SQL

Where should we do the tuning?

Database Design

Poor system performance usually results from a poor database design. 

One should generally normalize to the 3NF. 

Selective denormalization can provide valuable performance improvements.. 

Application Tuning: 

Approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL.

Memory Tuning: 

 By Properly size your database buffers (shared pool, buffer cache, log buffer, etc) 

 By looking at your wait events, buffer hit ratios, system swapping and paging, etc. 

 

Disk I/O Tuning: 

Database files needs to be properly sized. 

Also look for frequent disk sorts, full table scans, data fragmentation, etc. 

Eliminate Database Contention: 

Study database locks, latches and wait events carefully and eliminate where possible.

 

Tune the Operating System: 

Monitor and tune operating system CPU, I/O and memory utilization

Top 10 Oracle SQL tuning tips

1. Design and develop with performance in mind
2. Establish a tuning environment
3. Index wisely
4. Reduce parsing
5. Take advantage of Cost Based Optimizer
6. Avoid accidental table scans
7. Optimize necessary table scans
8. Optimize joins
9. Use array processing
10. Consider PL/SQL for “tricky” SQL