RE: Why isn't Oracle Using My Index

  • From: <Joel.Patterson@xxxxxxxxxxx>
  • To: <jkstill@xxxxxxxxx>
  • Date: Thu, 21 Dec 2006 15:53:36 -0500

Because it is Tom Kytes script, and it's explanation is given in his
book Expert Oracle Database Architecture, page xxxi of 'setting up your


I will pull out a couple sentences, and paraphrase.   Runstats measures
three key things, Elapsed time, system statistics, latching - the key
output of the report.


Later he elaborates on latches, but continues on that page describing
latches as lightweight locks - serialization devices, which inhibit
concurrency, which if inhibited makes the code less scalable....   He
goes on.


"Runstats is best used in isolation - that is, on a single-user
database.  We will be measuring statistics and latching (locking)
activity that result from our approaches.  We do not want other sessions
to contribute to the system's load or latching while this is going on.
A small test database is perfect for these sorts of tests...."




Joel Patterson 
Database Administrator 
904  727-2546 


From: Jared Still [mailto:jkstill@xxxxxxxxx] 
Sent: Thursday, December 21, 2006 3:09 PM
To: Patterson, Joel
Cc: Thomas.Mercadante@xxxxxxxxxxxxxxxxx; wjwagman@xxxxxxxxxxx;
Subject: Re: Why isn't Oracle Using My Index



On 12/21/06, Joel.Patterson@xxxxxxxxxxx <Joel.Patterson@xxxxxxxxxxx >

Runstats is great, and is especially geared towards latches, (is query
scalable).    However, you need a database without anyone else on it to
get accurate results.

I'll play Devil's advocate and say "Why?"

It seems to me that running a problem query on a production system 
with runstats would be a very good idea. 
eg. it will allow you to see how the SQL statements will compare
when competing for resources with a real workload.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist 

Other related posts: