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.

http://www.hotsos.com

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