RE: Why isn't Oracle Using My Index

Right, I was just going to elaborate when you responded.  

I'm not saying you can't get some information from the script, but the real jist of it is to measure latches, and as far as I know you cannot isolate the latches per user. The emphasis is on knowing that your test script produces X amount of latches, but your rewrite produces X-1000. (even though the rewrite initially takes longer). The rewrite will be much more scalable.... Because latches are serialized... and everyone has to take there turn.  

Furthermore, If I didn't get mixed up with enqueues, they are not queued, they are like a bunch of people outside a bar where the bouncer only allows some in when room is made, and who gets in is arbitrary.    

Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546

From: Cary Millsap [mailto:cary.millsap_at_hotsos.com] Sent: Thursday, December 21, 2006 4:03 PM To: Patterson, Joel; _oracle_L_list
Subject: 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