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

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

On Tue, 2003-11-25 at 14:14, Boris Dali wrote:
> Thanks, Jared, Tanel.
>
> I was a little supprised to see a combination
> rlbk=1,rd_only=1. Why read-only bit is set here if it
> rolls back anyway?
> So I thought may be they mark their tx explicitly as
> read-only (aka "set transaction read-only"). Reveiwing
> OCI fine manual there seemed to be an option of doing
> just that with the OCITransStart() call and setting
> some flags...
>
> But than simple test-case of tracing commit and
> rollback in SQL*Plus disproved the theory about
> read-only tx, as "normal" commit/rollback produce this
> combination regardless of the tx type:
>
> commit - XCTEND rlbk=0, rd_only=1
> rollback - XCTEND rlbk=1, rd_only=1
>
> set transaction read only
>
> commit - XCTEND rlbk=0, rd_only=1
> rollback - XCTEND rlbk=1, rd_only=1
>
> So much for my theory :-(
>
>
> Another question I had here is whether or not having
> so many (implicit?) rollbacks on about every SELECT
> statement all over my trace file bears any overhead
> and proves deficiency of a stateless architecture used
> by this application - but again reviewing v$sesstat
> before and after I see user rollbacks count
> incremented, but no additional redo vectors generated.
> I guess I can try Tom Kyte's test harness, but it just
> doesn't sit well with me that 30 selects are as cheap
> as 30 selects with 30 rollbacks.
>
> Thanks,
> Boris Dali.
>
> ______________________________________________________________________
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

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).