Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why isn't Oracle Using My Index
The big problem is that Oracle v$ data doesn't attribute latch use by
session. So imagine that you're focused on measuring session A, and
imagine that session A doesn't use latches. But session B is running at
the same time as session A. Then it's going to look like session A is a
latch consumer, when it's not.
Cary Millsap
Hotsos Enterprises, Ltd.
Nullius in verba
Hotsos Symposium 2007 / March 4-8 / Dallas
Visit www.hotsos.com for curriculum and schedule details...
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
Joel.Patterson_at_crowley.com
Sent: Thursday, December 21, 2006 2:54 PM
To: jkstill_at_gmail.com
Cc: Thomas.Mercadante_at_labor.state.ny.us; wjwagman_at_ucdavis.edu;
oracle-l_at_freelists.org
Subject: RE: Why isn't Oracle Using My Index
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 environment'.
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
joel.patterson_at_crowley.com
x72546
904 727-2546
From: Jared Still [mailto:jkstill_at_gmail.com]
Sent: Thursday, December 21, 2006 3:09 PM
To: Patterson, Joel
Cc: Thomas.Mercadante_at_labor.state.ny.us; wjwagman_at_ucdavis.edu;
oracle-l_at_freelists.org
Subject: Re: Why isn't Oracle Using My Index
On 12/21/06, Joel.Patterson_at_crowley.com <Joel.Patterson_at_crowley.com > wrote:
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 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 21 2006 - 15:02:43 CST
![]() |
![]() |