Oracle database Performance Tuning

The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. ne should do performance tuning for the following reasons:

• The speed of computing might be wasting valuable human time (users waiting for response);
• Enable your system to keep-up with the speed business is conducted; and
• Optimize hardware usage to save money (companies are spending millions on hardware). 

Areas for tuning
The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.

• Database Design (if it's not too late):

Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

• Application Tuning:

Experience shows that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

• Memory Tuning:

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. You may also want to pin large objects into memory to prevent frequent reloads.

• Disk I/O Tuning:

Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, 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. For more information, read the related Oracle FAQ dealing with your specific operating system.

What tools/utilities does Oracle provide to assist with performance tuning?

Oracle provides the following tools/ utilities to assist with performance monitoring and tuning:

• ADDM (Automated Database Diagnostics Monitor) introduced in Oracle 10g
• TKProf
• Statspack
• Oracle Enterprise Manager - Tuning Pack (cost option)
• Old UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring 

My query was fine last week and now it is slow. Why?

The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:

• Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
• Has OPTIMIZER_MODE been changed in INIT.ORA?
• Has the DEGREE of parallelism been defined/changed on any table?
• Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
• Have the statistics changed?
• Has the SPFILE/ INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
• Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
• Have any other INIT.ORA parameters been changed?

What do you think the plan should be? Run the query with hints to see if this produces the required performance.
It can also happen because of a very high high water mark. Typically when a table was big, but now only contains a couple of records. Oracle still needs to scan through all the blocks to see if they contain data.

Oracle Wait eventThere are some of the wait events from V$SESSION_WAIT and V$SYSTEM_EVENT views:

• db file sequential read: Tune SQL to do less I/O. Make sure all objects are analyzed. Redistribute I/O across disks.
• buffer busy waits: Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i)/ Analyze contention from SYS.V$BH ("buffer busy waits" was replaced with "read by other session" Oracle 10g).
• log buffer space: Increase LOG_BUFFER parameter or move log files to faster disks
• log file sync: If this event is in the top 5, you are committing too often (talk to your developers)
• log file parallel write: deals with flushing out the redo log buffer to disk. Your disks may be too slow or you have an I/O bottleneck.

Difference between DBFile Sequential and Scattered Reads

Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.
db file sequential read:
A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.
db file scattered read:
Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.

How does one tune the Redo Log Buffer?

The size of the Redo log buffer is determined by the LOG_BUFFER parameter in your SPFILE/INIT.ORA file. The default setting is normally 512 KB or (128 KB * CPU_COUNT), whichever is greater. This is a static parameter and its size cannot be modified after instance startup.

SQL> show parameters log_buffer
NAME                                 TYPE        value
------------------------------------ ----------- ------------------------------
log_buffer                           integer     262144

When a transaction is committed, info in the redo log buffer is written to a Redo Log File. In addition to this, the following conditions will trigger LGWR to write the contents of the log buffer to disk:

• Whenever the log buffer is MIN(1/3 full, 1 MB) full; or
• Every 3 seconds; or
• When a DBWn process writes modified buffers to disk (checkpoint).

Larger LOG_BUFFER values reduce log file I/O, but may increase the time OLTP users have to wait for write operations to complete. In general, values between the default and 1 to 3MB are optimal. However, you may want to make it bigger to accommodate bulk data loading, or to accommodate a system with fast CPUs and slow disks. Nevertheless, if you set this parameter to a value beyond 10M, you should think twice about what you are doing.

SQL> SELECT name, value
  2    FROM SYS.v_$sysstat
  3   WHERE NAME in ('redo buffer allocation retries',
  4                  'redo log space wait time');
NAME                                                                  value
---------------------------------------------------------------- ----------
redo buffer allocation retries                                            3
redo log space wait time                                                  0

Statistic "REDO BUFFER ALLOCATION RETRIES" shows the number of times a user process waited for space in the redo log buffer. This value is cumulative, so monitor it over a period of time while your application is running. If this value is continuously increasing, consider increasing your LOG_BUFFER (but only if you do not see checkpointing and archiving problems).
"REDO LOG SPACE WAIT TIME" shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer. If this value is low, your log buffer size is most likely adequate.