• partial schema statistics (using dbms_stats)
• missing indexes
• optimizer_index_cost_adj=15        #10-15 for OLTP systems, 50 for DW  #This adjusts the optimizer to favor index access
  
• optimizer_index_caching=85 (depending on RAM for index caching, around 85)
• 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)

·        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:

As you can see we are looking at an example report from a RAW configuration using single disks. Notice how both read and write times exceed even the rather large good practice 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 AWRRPT 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.