Re: Slower db file parallel read waits after migrating to SAN

From: Adam Musch <ahmusch_at_gmail.com>
Date: Tue, 23 Feb 2010 09:00:13 -0600
Message-ID: <516d05a1002230700p1f734897l808adb5000153c85_at_mail.gmail.com>

When you refer to a change in storage, are you referring to the underlying storage, or did you move the tables and/or indexes yourself?

If you changed the underlying storage, how so?

If you moved/rebuilt indexes, how did the statistics (specifically the clustering factor of the indexes) change? Did you change anything else about the optimizer statistics, such as which columns histograms are calculated on and how?

Some other things to consider:

1. Partitioning. 422 million rows is a pretty sizeable chunk of table, and I suspect list partitioning may be useful if you have fewer clients with more skew in their volumes. If you have large numbers of clients with fewer records per clients, hash partitioning may be useful.
2. If you're going to redefine the index, I suspect the index should be (CLIENT_NUMBER, ABAWD_STATUS_CODE, BENEFIT_MONTH). Depending on the cardinalities of those columns (if the number of distinct values for is "small") consider index compression, either for 1, 2, or all 3 columns.

--

Adam Musch
ahmusch_at_gmail.com
--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 23 2010 - 09:00:13 CST