You should begin with a global sweep of access pattern activity.  A query such as the globaccpatt.sql  script can be used for that:

The complete listing of the globaccpatt.sql  script can be obtained from the online Code Depot at http://www.dba-oracle.com/bp/bp_book5_perf.htm. The script queries the sys.v_$sysstat view and the results from such a query might look like this:

NAME                             VALUE

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

table fetch by rowid             146540

table fetch continued row          698

table scans (cache partitions)        0

table scans (direct read)             0

table scans (long tables)             0

table scans (rowid ranges)            0

table scans (short tables)          262

When reviewing the output from the above query, focus on these things:

• Long table scans are typically an activity to avoid, as they have the capability to cause needless physical and logical I/O, as well as flood the buffer cache with seldom-referenced blocks of data.  We will find out in a subsequent chapter exactly how to find the large tables that are being scanned, and we will show a quick script that will uncover them.

• The table fetch continued row  statistic is indicative of chained/migrated row I/O.  Such activity is not desired because chained/migrated row access can cause twice the I/O needed to access a table.  This is because Oracle must do two or more I/O's to read a chained/migrated row in a table.  If high numbers in the table fetch continued row statistic are present, then you should determine the percentage of such activity in the overall I/O of the database.  This can be obtained from the following chainpct.sql  script:

The complete listing of the chainpct.sql  script can be obtained from the online Code Depot at http://www.dba-oracle.com/bp/bp_book5_perf.htm. The script also queries the sys.v_$sysstat view.

Should the query above return anything over 25%, then your database is likely suffering from a bad case of chained/migrated rows  (or perhaps a very hot chained/migrated row table is being accessed repeatedly).  You can get a quick idea of how many tables in your database suffer from such a condition by issuing the chaincnt.sql  query:

The complete listing of the chaincnt.sql  query can be obtained from the online Code Depot at http://www.dba-oracle.com/bp/bp_book5_perf.htm. The script queries the sys.tab$ view. 

The actual tables that contain chained rows can be found by using the chaintables.sql  query:

The complete listing of the chaintables.sql  query can be obtained from the online Code Depot at http://www.dba-oracle.com/bp/bp_book5_perf.htm. The script queries the sys.dba_tables view.

Once you get a general feel for the I/O methods that Oracle uses to access the database objects, you can begin to locate the areas of the database that are most affected.

If you're a DBA who's looking for real world Oracle tuning techniques, Oracle scripts, and advice on how to get to the heart of critical Oracle performance problems, then you've come to the right place. Oracle Performance Troubleshooting: With Dictionary Internals SQL & Tuning Scripts was written by one the world's most widely-read DBAs and Oracle internals experts. Robin Schumacher focuses his incredible knowledge of the Oracle data dictionary into a superb book that shows how to quickly troubleshoot and correct Oracle performance problems. 

Plus! The online code depot is available immediately!

http://www.dba-oracle.com/bp/bp_book5_perf.htm