In most of he databases that suffer a poor performance ,the issue is often caused by the slowness of a single table. A table that lacks indexes or statistics can be responsible for 90% of the total physical or logical reads.
In the past it was hard to determine that table. From version 9 Oracle created the view sys.segment_stats. This is a very powerfull view that shows the used resources per segment.
Below an enhanced SQL based on v$segment_statistics to determine the segment reads compared with the total reads in the database.

When a single segment takes more as 20% of the total reads that segment is a candidate for further tuning. Further tuning can be adding or modifying an index, evaluate the statistics or check the table highwater mark size.

This way of working has proven to be very successfull.
Also databases that do not have a known performance issue can benefit from this method. At the end of the day more tuned databases can run on 1 server, important for reducing the costs.
Only if the performance remains poor after looking at v$segment_statistics more sophisticated ways of tuning have to be followed

This entry was posted on Monday, August 17th, 2009 at 8:45 pm and is filed under performance & tuning. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.