Re: Big Tables--slowed response

In <NEWTNews.19958.833835619.Postmaster_at_freh25-97.adpc.purdue.edu>, bwskiles_at_adpc.purdue.edu writes:
>
>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,

Oracle may have chosen a bad 1% example rows to base its estimate on.. Analyzing a table will also re-analyze ALL of the indexes, therefore possibly messing up the previously OK indexes

> 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.
>

if explain plan gave a different path then the optimizer must have got it wrong this time - i don't know if there is a good minimum % for estimates, but 1% seems a bit low to me

>
>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.
The problem may not be with the fact that they were big tables - it was just a bit more noticable !!

Bye,
Steve

opinions expressed are mine and do not
necessarily represent those of my employer Received on Tue Jun 04 1996 - 00:00:00 CEST