Big Tables--slowed response

Here's the setting: Oracle 7.2.3 running on HP-UX 9000 series. 1 gig of RAM and 32 gigs of storage. Storage technology is RAID5 with 16 gigs on each of 2 LUN controllers. (Each LUN controls 5 drives of 4 gigs apiece. Each set of 5 drives appears as one hard drive.) The database has a few large tables (over 10,000,000 rows), and several medium to smaller tables. Database is query only with updates occuring in batch jobs on a monthly basis. Each table has multiple indexes to speed retrieval.

The background: We have have a series of scripts to test performance. We just moved to the HP and upgraded from 7.2.2 to 7.2.3. We kicked off Test 1 after initial load of tables onto the HP. All tables were indexes and analyzed prior to the test. (We're using COST optimizer.) Some times were not as fast as we wanted, so we added a indexes to a couple of tables, analyzed those tables, and kicked of Test 2. The new indexes sped up their designated queries quite nicely. But...

The problem: We happened to notice in Test 2's results that queries not affected by the new indexes had actually slowed down--considerably. Absolutely no changes had been made to these other tables or to their indexes. One key query went from 2 minutes, 27 seconds to over 26 minutes.

After beating our heads against the wall for awhile, I began reanalyzing tables in the problem queries, starting with the smaller tables and working up. In Test 3, low and behold, when I reanalyzed the large tables, the queries resumed their original efficiency. Please note: statistics for the large tables were always estimated at 1 percent; Explain Plans were developed for all queries prior to each test. Explain Plans in Tests 1 & 3 where the same. Explain Plans for the problem queries in Test 2 were different _and_ had lower costs. Again, same data and same indexes.

In addition, no users had access to any parts of the system. No other queries to contend with and no other demands on the dual CPU's. In addition, the test scripts were kicked off from SQLPLUS running to the HP, so there was no slow down because of traffic on the LAN lines.

So why did this happen? Why did I have to reanalyze only the larger tables? Is there something that confuses the optimizer? Does the optimizer not work well with RAID5? Did the statistics from the original ANALYZE not get committe somehow??? I'm really lost here, but I would like to make sure this doesn't happen again. I would really appreciate any insight or direction anyone could give me.

TIA. Received on Mon Jun 03 1996 - 00:00:00 CEST