Oracle-L: Re: sql trace - XCTEND rlbk=1, rd

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Wed, 26 Nov 2003 10:34:25 -0800
Message-ID: <F001.005D7E8C.20031126103425@fatcity.com>

Thanks, Jared.

Yes, running select vs select+rollback in a loop of 1000 iterations I got similar results (average over 3 runs):

STAT...user rollbacks      0  1,000  1,000
LATCH...enqueues         570  1,574  1,004
LATCH...shared pool    7,434  9,063  1,629
STAT...recursive       7,754 10,264  2,510
LATCH...library cache 13,401 17,429  4,028

So, every select w/ a rollback is at least 1 enqueue and 4 latches (1 shared pool + 3 library cache) more expensive than select wo/ a rollback.

Since latches are scalability inhibitors - would it be far away from the truth to say that an application that uses OCI session pooling (as opposed to connection pooling I suppose? - just reading chapter 9 of the OCI manual) doesn't scale well with respect to the number of concurrent users?

Thanks,
Boris Dali.

Jared Still <jkstill_at_cybcon.com> wrote: > Using a slightly modified version of run_stats to
> return
> timings in 1/10000 of a second, the timing of 1000
> iterations
> of a loop executing noop vs. 1000 doing rollback:
>
> .0005 secs
> .0354 secs
>
> Here are the stats that were different between the
> two,
> kind of what you would expect:
>
> LATCH.cache buffers chains 113
> 133 20
> STAT...redo size 27160
> 27648 488
> STAT...user rollbacks 0
> 1000 1000
> LATCH.enqueues 0
> 1001 1001
> LATCH.shared pool 3
> 1004 1001
> LATCH.session idle bit 0
> 1001 1001
> STAT...execute count 3
> 1004 1001
> LATCH.library cache pin 13
> 2014 2001
> LATCH.session allocation 0
> 2002 2002
> STAT...recursive calls 4
> 2008 2004
> LATCH.library cache 14
> 3018 3004

Post your free ad now! http://personals.yahoo.ca

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boris Dali
  INET: boris_dali_at_yahoo.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Nov 26 2003 - 12:34:25 CST

Re: sql trace - XCTEND rlbk=1, rd_only=1