DBMentors is a solution oriented group, started by a team of qualified and committed professionals with vast experience in IT industry. The team has in-depth technical and design expertise with highest standards of programming quality.

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.

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.

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.

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.

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.