·        EXACT – The statements have to match exactly to be reusable

·        FORCE – Always replace literals

·        SIMILAR – Perform literal peeking and replace when it makes sense

We usually suggest the use of the SIMILAR option for CURSOR_SHARING

As you can see here there is no index on UPDATED_TS

Here there is no WHERE clause, hence a FTS is required.

Anything that goes to users or gets into the database must go through the database buffers.
Gone are the days of a single buffer area (the default) now we have 2, 4, 8,, 16, 32 K buffer areas, keep and recycle buffer pools on top of the default area. Within these areas we have the consistent read, current read, free, exclusive current, and many other types of blocks that are used in Oracle’s multi-block consistency model.
The V$BH view (and it’s parent the X$BH table) are the major tools used by the DBA to track block usage, however, you may find that the data in the V$BH view can be misleading unless you also tie in block size data.

As you can see, we will need to be SYS user to run it.  An example report would be:

This is an object statistic cross tab report based on the V$SEGMENT_STATISTICS view. The cross tab report generates a listing showing the statistics of concern as headers across the page rather than listings going down the page and summarizes them by object. This allows us to easily compare total buffer busy waits to the number of ITL or row lock waits. This ability to compare the ITL and row lock waits to buffer busy waits lets us see what objects may be experiencing contention for ITL lists, which may be experiencing excessive locking activity and through comparisons, which are highly contended for without the row lock or ITL waits. An example of the output of the report, edited for length, is shown here:

Date: 12/09/05                                     Page: 1
Time: 07:17 PM         Object Wait Statistics PERFSTAT
                whoville database
                                                                                                             
ITL Buffer Busy Row Lock Physical Logical
Object Waits       Waits    Waits     Reads Reads                     
-------------- ----- ----------- -------- ---------- -----------
BILLING 0 63636 38267 1316055 410219712
BILLING_INDX1 1 16510 55 151085 21776800
...
DELIVER_INDX1 1963 36096 32962 1952600 60809744
DELIVER_INDX2 88 16250 9029 18839481 342857488                       
DELIVER_PK 2676 99748 29293 15256214 416206384
DELIVER_INDX3 2856 104765 31710 8505812 467240320
...
All Objects 12613 20348859 1253057 1139977207 20947864752


In the above report the BILLING_INDX1 index has a large number of buffer busy waits but we can’t account for them from the ITL or Row lock waits, this indicates that the index is being constantly read and the blocks then aged out of memory forcing waits as they are re-read for the next process. On the other hand, almost all of the buffer busy waits for the DELIVER_INDX1 index can be attributed to ITL and Row Lock waits.
In situations where there are large numbers of ITL waits we need to consider the increase of the INITRANS setting for the table to remove this source of contention. If the predominant wait is row lock waits then we need to determine if we are properly using locking and cursors in our application (for example, we may be over using the SELECT…FOR UPDATE type code.) If, on the other hand all the waits are un-accounted for buffer busy waits, then we need to consider increasing the amount of database block buffers we have in our SGA.
As you can see, this object wait cross tab report can be a powerful addition to our tuning arsenal.
By knowing how our buffers are being used and seeing exactly what waits are causing our buffer wait indications we can quickly determine if we need to tune objects or add buffers, making sizing buffer areas fairly easy.
But what about the Automatic Memory Manager in 10g? It is a powerful tool for DBAs with systems that have a predictable load profile, however if your system has rapid changes in user and memory loads then AMM is playing catch up and may deliver poor performance as a result. In the case of memory it may be better to hand the system too much rather than just enough, just in time (JIT).
As many companies have found when trying the JIT methodology in their manufacturing environment it only works if things are easily predictable.
The AMM is utilized in 10g by setting two parameters, the SGA_MAX_SIZE and the SGA_TARGET. The Oracle memory manager will size the various buffer areas as needed within the range between base settings or SGA_TARGET and SGA_MAX_SIZE using the SGA_TARGET setting as an “optimal” and the SGA_MAX_SIZE as a maximum with the manual settings used in some cases as a minimum size for the specific memory component.

Check Disks I/O
Disk stress will show up on the Oracle side as excessive read or write times. Filesystem stress is shown by calculating the IO timings as shown here:

Usually  you want to see limits of 10-20 milliseconds for a disk read. However in my experience for reads you should not exceed 5 milliseconds and usually with modern buffered reads, 1-2 milliseconds. Oracle is more tolerant for write delays since it uses a delayed write mechanism, so 10-20 milliseconds on writes will normally not cause significant Oracle waits, however, the smaller you can get read and write times, the better!

For the money, I would suggest RAID0/1 or RAID1/0, that is, striped and mirrored. It provides nearly all of the dependability of RAID5 and gives much better write performance. You will usually take at least a 20 percent write performance hit using RAID5. For read-only applications RAID5 is a good choice, but in high-transaction/high-performance environments the write penalties may be too high.

Table 1 shows how Oracle suggests RAID should be used with Oracle database files.

Table 1: RAID Recommendations (From Metalink NOTE: 45635.1)

Improper PGA setup
Oracle provides AWR or statspack reports to track and show the number of sorts. Unfortunately hashes are not so easily tracked. Oracle tracks disk and memory sorts, number of sort rows and other sort related statistics. Hashes on the other hand only can be tracked usually by the execution plans for cumulative values, and by various views for live values. After 9i the parameter PGA_AGGREGATE_TARGET was provided to allow automated setting of the sort and hash areas. For currently active sorts or hashes the following script can be used to watch the growth of temporary areas.

As you can see the whoville database had no hashes, at the time the report was run, going to disk. We can also look at the cumulative statistics in the v$sysstat view for cumulative sort data.

 In this case we are seeing 1-pass executions indicating disk sorts are occurring with the maximum size being in the 4m to 8m range. For an 8m sort area the PGA_AGGREGATE_TARGET should be set at 320 megabytes (sorts get 0.5*(.05*PGA_AGGREGATE_TARGET)). For this system the setting was at 160 so 4 megabytes was the maximum sort size, as you can see we were seeing 1-pass sorts in the 2-4m range as well even at 160m.
By monitoring the realtime or live hashes and sorts and looking at the sort histograms from the AWRRPT reports you can get a very good idea of the needed PGA_AGGREGATE_TARGET setting. If you need larger than 200 megabyte sort areas you may need to get approval from Oracle support through the i-tar process to set the _pga_max_size parameter to greater than 200 megabytes.

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

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.

Wed Oct 23                                          page    1

                   Contents of Data Buffers

                                                  Percentage
                                                   of object  
Object                      Object     Number of data blocks  
Name                        Type          Blocks   in Buffer  
--------------------------- ------- ------------ -----------  
MTL_DEMAND_INTERFACE        TABLE         38,745         100  
FND_CONCURRENT_REQUESTS     TABLE         16,636          88  
WIP_TRANSACTIONS            TABLE         14,777         100  
WIP_TRANSACTION_ACCOUNTS    TABLE         13,390          33  
CRP_RESOURCE_HOURS          TABLE          7,806         100  
SO_LINES_ALL                TABLE          7,576         100  
ABC_EDI_LINES               TABLE          7,041         100  
BOM_INVENTORY_COMPONENTS    TABLE          6,882          46  
MTL_SYSTEM_ITEMS            TABLE          4,747          63  
WIP_TRANSACTION_ACCOUNTS_N1 INDEX          3,996          38  
MTL_ITEM_CATEGORIES         TABLE          3,390         100  
RA_CUSTOMER_TRX_LINES_ALL   TABLE          3,264         100  
MRP_FORECAST_DATES          TABLE          3,082          99  
RA_CUSTOMER_TRX_ALL         TABLE          2,739          97  
WIP_OPERATIONS              TABLE          2,311          34  
SO_PICKING_LINES_ALL        TABLE          2,006         100  
MTL_DEMAND_INTERFACE_N10    INDEX          1,482          76  
BOM_OPERATION_RESOURCES     TABLE          1,456          45  
ABC_EDI_ERRORS              TABLE          1,427         100  
ABC_EDI_HEADERS             TABLE          1,188         100