On Wed, Nov 25, 2009 at 2:36 PM, Paul Drake <bdbafh@xxxxxxxxx> wrote: > Hello Neil. > > I'm not familiar with the hardware configuration referenced in this thread. > > If the SysAdmins have enabled read-ahead behavior (or adaptive > read-ahead) in the RAID controllers or in the management software of > the storage units, one would expect to see that the multiblock read > time is low as the data has been pre-fetched into the (storage > controller) cache. Not entirely sure about this one. We're using Veritas File Systems on our Oracle Volumes if that helps. Solaris 10. > > Perhaps you can design a test that would cause scans to be executed > against many different tables at the same time which might defeat the > read-ahead behavior? > > I'm guessing that if the queue length is greater than a threshold > value that the read-ahead is not going to be active. This of course > would depend upon the vendor implementation. > > hth. > > Paul > > > On Wed, Nov 25, 2009 at 2:12 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote: > > As far as things that might have changed statistics-wise, I'm not too > sure. > > I can ask around, there are other DBAs in the org, but I dont think > anyone > > would have changed the stats gathering methods. the gather_stats_job > runs > > but it hasn't touched any of the tables in this specific schema. > > One other thing that Randolf touched upon was the single block read time > > taking longer than multi-block. I calculated workload statistics again > and > > received a similar response. Is this worth bringing up to the Sysadmins? > > I've since deleted the system stats and computed noworkload statistics > > instead. > > > > On Wed, Nov 25, 2009 at 11:51 AM, Randolf Geist < > info@xxxxxxxxxxxxxxxxxxxxx> > > wrote: > >> > >> > Well look what I found while poking around sys.wri$_optstat_histhead_ > >> > history > >> > > >> > http://bit.ly/75oNst (screenshot) > >> > > >> > Could this have been a function of sample size changing? Could > >> > parallel have anything to do with it? > >> > >> Since the density itself was so far not used for the cardinality > estimate > >> calculation this is merely an indicator that "something" has changed - > the > >> old density is calculated based on the histogram generated. So a > significant > >> change in the density might indicate that the underlying histogram has > >> changed. > >> > >> That was one of the questions I haven't raised yet - what could have > >> changed, since I already had the impression that there was a time when > the > >> batch job performed better from your comments. > >> > >> You can try to restore the old statistics from the statistics history to > >> see if there were significant differences in the histogram generated and > the > >> sample size used to gather the statistics along with the number of > distinct > >> values gathered. This way you could also check what NewDensity got > >> calculated with the old statistics and what the estimates looked like > for > >> your example queries. > >> > >> The slowly increasing density suggests that the underlying data might > >> slowly have changed to something now that might have changed the shape > of > >> the height-balanced histogram so that the density calculation was > >> significantly affected. > >> > >> Regards, > >> Randolf > >> > >> Oracle related stuff blog: > >> http://oracle-randolf.blogspot.com/ > >> > >> Co-author of the forthcoming "OakTable Expert Oracle Practices" book: > >> http://www.apress.com/book/view/1430226684 > >> > >> > http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 > >> ___________________________________________________________ > >> Preisknaller: WEB.DE DSL Flatrate für nur 16,99 Euro/mtl.! > >> http://produkte.web.de/go/02/ > >> > > > > > > > > -- > http://www.completestreets.org/faq.html > http://safety.fhwa.dot.gov/ped_bike/docs/pamanual.pdf >