• partial schema statistics (using dbms_stats)
• missing indexes
• optimizer_index_cost_adj=(between 20-60 for OLTP systems, I usually start with 20)
• optimizer_index_caching=xx (depending on RAM for index caching, around 65)
• optimizer_mode=first_rows (for OLTP)
• parallel_automatic_tuning=TRUE (parallelizes full-table scans, Because parallel full-table scans are very fast, the CBO will give a higher cost to index access and be friendlier to full-table scans)
  
• hash_area_size too small (too many nested loop joins)

Check for statements with a lot of executions. It is bad to have the PARSE_CALLS value in the above statement close to the EXECUTIONS value. The previous query will fire only for DML statements (to check on other types of statements use the appropriate command type number). Also ignore Recursive calls (dictionary access), as it is internal to Oracle

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       27      0.02       0.00          0          0          0           0
Execute     27      0.00       0.00          0          0          0           0
Fetch      108      0.03       0.00          0        189          0          81
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      162      0.05       0.00          2        189          0          81

NAME – Name of the buffer pool
PHYSICAL_READS – Number of physical reads
DB_BLOCK_GETS – Number of reads for INSERT, UPDATE and DELETE
CONSISTENT_GETS – Number of reads for SELECT
DB_BLOCK_GETS + CONSISTENT_GETS = Total Number of reads

Based on above statistics we can calculate the percentage of data blocks being accessed from the memory to that of the disk (block buffer hit ratio). The following SQL statement will return the block buffer hit ratio: 

SELECT NAME, 100 – round ((PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))*100,2) HitRatio
FROM V$BUFFER_POOL_STATISTICS;

NAME                   HITRATIO
-------------------- ----------
DEFAULT                   96.82

Before measuring the database buffer hit ratio, it is very important to check that the database is running in a steady state with normal workload and no unusual activity has taken place. For example, when you run a SQL statement just after database startup, no data blocks have been cached in the block buffers. At this point, Oracle reads the data blocks from the disk and will cache the blocks in the memory. If you run the same SQL statement again, then most likely the data blocks will still be present in the cache, and Oracle will not have to perform disk IO. If you run the same SQL statement multiple times you will get a higher buffer hit ratio. On the other hand, if you either run SQL statements that rarely query the same data, or run a select on a very large table, the data block may not be in the buffer cache and Oracle will have to perform disk IO, thereby lowering the buffer hit ratio.

A hit ratio of 95% or greater is considered to be a good hit ratio for OLTP systems. The hit ratio for DSS (Decision Support System) may vary depending on the database load. A lower hit ratio means Oracle is performing more disk IO on the server. In such a situation, you can increase the size of database block buffers to increase the database performance. You may have to increase the physical memory on the server if the server starts swapping after increasing block buffers.

If you have a low buffer hit ratio and you cannot increase the size of the database block buffers, you can still gain some performance advantage by tuning the block buffers and efficiently caching the data block that will provide maximum benefits. Ideally, we should cache data blocks that are either frequently used in SQL statements, or data blocks used by performance sensitive SQL statements (A SQL statement whose performance is critical to the system performance). An ad-hoc query that scans a large table can significantly degrade overall database performance. A SQL on a large table may flush out frequently used data blocks from the buffer cache to store data blocks from the large table. During the peak time, ad-hoc queries that select data from large tables or from tables that are rarely used should be avoided. If we cannot avoid such queries, we can limit the impact on the buffer cache by using RECYCLE buffer pool. 

A DBA can create multiple buffer pools in the SGA to store data blocks efficiently. For example, we can use RECYCLE pool to cache data blocks that are rarely used in the application. Typically, this will be a small area in the SGA to store data blocks for current SQL statement / transaction that we do not intend to hold in the memory after the transaction is completed. Similarly, we can use KEEP pool to cache data blocks that are frequently used by the application. Typically, this will be big enough to store data blocks that we want to always keep in memory. By storing data blocks in KEEP and RECYCLE pools you can store frequently used data blocks separately from the rarely used data blocks, and control which data blocks are flushed from the buffer cache. Using RECYCLE pool, we can also prevent a large table scan from flushing frequently used data blocks. You can create the RECYCLE and KEEP pools by specifying the following init.ora parameters:

DB_KEEP_CACHE_SIZE = <size of KEEP pool>
DB_RECYCLE_CACHE_SIZE = < size of RECYCLE pool>

When you use the above parameters, the total memory allocated to the block buffers is the sum of DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and DB_CACHE_SIZE.

Step 3: Assign tables to KEEP / RECYCLE pool. Identify buffer hit ratio for KEEP, RECYCLE, and DEFAULT pool. Adjust the initialization parameters for optimum performance.

By default, data blocks are cached in the DEFAULT pool. The DBA must configure the table to use the KEEP or the RECYCLE pool by specifying BUFFER_POOL keyword in the CREATE TABLE or the ALTER TABLE statement. For example, you can assign a table to the recycle pool by using the following ALTER TABLE SQL statement.

ALTER TABLE <TABLE NAME> STORAGE (BUFFER_POOL RECYCLE)

The DBA can take help from application designers in identifying tables that should use KEEP or RECYCLE pool. You can also query X$BH to examine the current block buffer usage by database objects (You must log in as SYS to query X$BH).

spool tables_to_RECYCLE_Pool.txt
--The following query returns a list of tables that are rarely used and can be assigned to the RECYCLE pool.
Col owner       format a14
Col object_name format a36
Col object_type format a15
SELECT o.owner, object_name, object_type, COUNT(1) buffers
  FROM SYS.x$bh, dba_objects o
  WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
    AND obj = o. object_id
    AND o.owner upper('&OWNER')
  GROUP BY o.owner, object_name, object_type
  ORDER BY buffers;
spool off

spool tables_to_KEEP_Pool.txt
--The following query will return a list of tables that are frequently
-- used by SQL statements and can be assigned to the KEEP pool.
Col owner       format a14
Col object_name format a36
Col object_type format a15
SELECT o.owner, object_name, object_type, COUNT(1) buffers
  FROM SYS.x$bh, dba_objects o
  WHERE tch > 10 
    AND lru_flag = 8
    AND obj = o.object_id
    AND o.owner = upper('&OWNER')
  GROUP BY o.owner, object_name, object_type
  ORDER BY buffers;
spool off

Once you have setup the database to use KEEP and RECYCLE pools, you can monitor the buffer hit ratio by querying V$BUFFER_POOL_STATISTICS and V$DB_CACHE_ADVICE to adjust the buffer pool initialization parameters.

Step 4: Identify the amount of memory needed to maintain required performance.

Oracle 9i maintains block buffer advisory information in V$DB_CACHE_ADVICE. This view contains simulated physical reads for a range of buffer cache sizes. The DBA can query this view to estimate buffer cache requirement for the database. The cache advisory can be activated by setting DB_CACHE_ADIVE initialization parameter. 

DB_CACHE_ADVICE = ON

There is a minor overhead associated with cache advisory collection. Hence, it is not advisable to collect these statistics in production databases until there is a need to tune the buffer cache. The DBA can turn on DB_CACHE_ADVISE dynamically for the duration of sample workload period and collect advisory statistics. 

Conclusion

Using this methodical approach, a DBA can easily identify the problem areas, and tune the database block buffers. The DBA can create the following buffer pool to efficiently cache data blocks in SGA:

1. KEEP: Cache tables that are very critical for system performance. Typically, lookup tables are very good candidates for the KEEP pool. The DBA should create the KEEP pool large enough to maintain 99% buffer hit ratio on this pool.
2. RECYCLE: Cache tables that are not critical for system performance. Typically, a table containing historical information that is either rarely queried or used by batch process is a good candidate for the RECYCLE pool. The DBA should create the RECYCLE pool large enough to finish the current transaction.
3. DEFAULT: Cache tables that do not belong to either KEEP or RECYCLE pool.

The DBA can setup OEM jobs, Oracle statspack, or custom monitoring scripts to monitor your production database block buffer efficiency, and to identify and tune the problem area.

If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can't be completely avoided; a "softer" soft parse is done that's faster and requires less CPU.)

To see if you've set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS.
select max(a.value) as highest_open_cur, p.value as max_open_cur
  from v$sesstat a, v$statname b, v$parameter p
  where a.statistic# = b.statistic#
    and b.name = 'opened cursors current'
    and p.name= 'open_cursors'
  group by p.value;
HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------
            1953         2500

--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' ;

You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.

select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id
and c.sid=&sid;

select cach.value cache_hits, prs.value all_parses, prs.value-cach.value sess_cur_cache_not_used
   from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
   where cach.statistic# = nm1.statistic#  
     and nm1.name = 'session cursor cache hits' 
     and prs.statistic#=nm2.statistic#
     and nm2.name= 'parse count (total)'
     and cach.sid= &sid and prs.sid= cach.sid ;

Enter value for sid: 947
old   8: and cach.sid= &sid and prs.sid= cach.sid
new   8: and cach.sid= 947 and prs.sid= cach.sid

CACHE_HITS ALL_PARSES SESS_CUR_CACHE_NOT_USED
---------- ---------- -----------------------
       106        210                     104

Monitor this in concurrence with the session cursor cache count.

--session cached cursors, for a given SID, compared to max
select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
  from v$sesstat a, v$statname b, v$session s, v$parameter2 p
  where a.statistic# = b.statistic#  and s.sid=a.sid and a.sid=&sid
    and p.name='session_cached_cursors'
    and b.name = 'session cursor cache count' ;

Top 5 Timed Events
                                                          % Total
 Event                         Waits        Time (s)     Ela Time
 --------------------------- ------------ ----------- -----------
 db file sequential read       2,598        7,146           48.54
 db file scattered read       25,519        3,246           22.04
 library cache load lock         673        1,363            9.26
 CPU time                      2,154          934            7.83
 log file parallel write      19,157          837            5.68

The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won't help.
In order to look at system-wide wait events, we can query the v$system_event performance view. This view, shown below, provides the name of the wait event, the total number of waits and timeouts, the total time waited, and the average wait time per event.

EVENT                       TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT

--------------------------- ----------- -------------- ----------- ------------

buffer busy waits                636528           1557      549700   .863591232

write complete waits               1193              0       14799   12.4048617

free buffer waits                  1601              0         622   .388507183

CLASS                   COUNT       TIME
 ------------------ ---------- ----------
 data block            1961113    1870278
 segment header          34535     159082
 undo header            233632      86239
 undo block               1886       1706

• The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.  
• Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.

Because buffer busy waits are due to contention between particular blocks, there's nothing you can do until you know which blocks are in conflict and why the conflicts are occurring. Tuning therefore involves identifying and eliminating the cause of the block contention.
The v$session_wait performance view, shown below, can give some insight into what is being waited for and why the wait is occurring.

The columns of the v$session_wait view that are of particular interest for a buffer busy wait event are:

• P1—The absolute file number for the data file involved in the wait.  
• P2—The block number within the data file referenced in P1 that is being waited upon.  
• P3—The reason code describing why the wait is occurring.
  

Here's an Oracle data dictionary query for these values:

select p1 "File #", p2 "Block #", p3 "Reason Code"
from v$session_wait
where event = 'buffer busy waits';

If the output from repeatedly running the above query shows that a block or range of blocks is experiencing waits, the following query should show the name and type of the segment:

select owner, segment_name, segment_type
from dba_extents
where file_id = &P1
  and &P2 between block_id and block_id + blocks -1;

Once the segment is identified, the v$segment_statistics performance view facilitates real-time monitoring of segment-level statistics. This enables a DBA to identify performance problems associated with individual tables or indexes, as shown below.

We can also query the dba_data_files to determine the file_name for the file involved in the wait by using the P1 value from v$session_wait for the file_id.

Interrogating the P3 (reason code) value from v$session_wait for a buffer busy wait event will tell us why the session is waiting. The reason codes range from 0 to 300 and can be decoded, as shown in Table A.

Table A

Reason codes
As I mentioned at the beginning of this article, buffer busy waits are prevalent in I/O-bound systems. I/O contention, resulting in waits for data blocks, is often due to numerous sessions repeatedly reading the same blocks, as when many sessions scan the same index. In this scenario, session one scans the blocks in the buffer cache quickly, but then a block has to be read from disk. While session one awaits the disk read to complete, other sessions scanning the same index soon catch up to session one and want the same block currently being read from disk. This is where the buffer busy wait occurs—waiting for the buffer blocks that are being read from disk. The following rules of thumb may be useful for resolving each of the noted contention situations:

• Data block contention—Identify and eliminate HOT blocks from the application via changing PCTFREE and or PCTUSED values to reduce the number of rows per data block. Check for repeatedly scanned indexes. Since each transaction updating a block requires a transaction entry, increase the INITRANS value.  
• Freelist block contention—Increase the FREELISTS value. Also, when using Parallel Server, be certain that each instance has its own FREELIST GROUPs.  
• Segment header contention—Again, increase the number of FREELISTs and use FREELIST GROUPs, which can make a difference even within a single instance.  
• Undo header contention—Increase the number of rollback segments.