Oracle-L: Re: Currval and buffer gets

From: Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
Date: Tue, 23 Apr 2002 10:00:09 -0800
Message-ID: <F001.0044CD32.20020423100009@fatcity.com>

Hi Dan,

I ran the query against x$dual multiple times and I am seeing a pattern. There are 2 fetches for each access, only the first one has a value for "r". The second fetch does not have any values for "r". I even tried with an arraysize to 5000, just to make sure. Comments??

So even if access to x$dual does in fact performs 1 "raw" I/O (which may or may not be an I/O to a database block), I think it is still better than dual which costs 5 LIOs (upto 8i) and 3 LIOs (upto 9i). This is especially relevant when we do have "PL/SQL loops" that perform an inordinate number of LIOs, especially to get values such as sysdate and such.

Cheers,

Gaja

---partial trace file output begins here ---

---first run---
FETCH
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=775551 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1111838976 p2=1 p3=0
FETCH
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=775551 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 41 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='FIXED TABLE FULL X$DUAL ' ---second run---
FETCH
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=775731 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1111838976 p2=1 p3=0
FETCH
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=775731 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 41 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='FIXED TABLE FULL X$DUAL ' ---third run---
FETCH
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=775773 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1111838976 p2=1 p3=0
FETCH
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=775774 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1111838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 41 p1=1111838976 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='FIXED TABLE FULL X$DUAL ' ---partial trace file output ends here ---

Danisment Gazi Unal <dunal_at_ubTools.com> wrote:
> Hello Gaja,
>
> Here is the line to talk about:
>
> FETCH
>
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5656332
>
> As we see, cu+cr=0 and returned raw is r=1.
> Normally, it's not possible to
> return a raw without touching a block. But there are
> some cases such as X$
> tables that these statistics are zero.
>
> I had tested V$SESSION, V$PROCESS and I had seen
> logical IO is 0 since
> they are based on X$ tables.
>
> I did not document it, but I think, Oracle doesn't
> account logical IO for
> X$ tables. Also, How can Oracle account it in blocks
> ? X$ tables are not
> organized in DB_BLOCK_SIZE.
>
> I may be wrong, I've not done detailed tests on this
> issue. I'm looking
> forward to hearing a confirmation on that.
>
> regards...
>
> Gaja Krishna Vaidyanatha wrote:
>
> > Hi Dan and list,
> >
> > Here is the output from a test after setting 10046
> at
> > level 12. Please tell me whether this is really 0
> or
> > not accounted for. If it is not accounted for,
> then I
> > stand corrected.
> >
> > Thanks,
> >
> > Gaja
> >
> > ----Trace file output starts here----
> > *** 2002-04-22 16:04:14.090
> > *** SESSION ID:(11.752) 2002-04-22 16:04:14.060
> > =====================
> > PARSING IN CURSOR #1 len=70 dep=0 uid=0 oct=42
> lid=0
> > tim=5655178 hv=347037164 ad='51d70a0'
> > alter session set events '10046 trace name context
> > forever, level 12'
> > END OF STMT
> > EXEC
> >
>
#1:c=2,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5655179
> > WAIT #1: nam='SQL*Net message to client' ela= 0
> > p1=1111838976 p2=1 p3=0
> > *** 2002-04-22 16:04:25.617
> > WAIT #1: nam='SQL*Net message from client' ela=
> 1152
> > p1=1111838976 p2=1 p3=0
> > =====================
> > PARSING IN CURSOR #1 len=21 dep=0 uid=0 oct=3
> lid=0
> > tim=5656331 hv=2119980703 ad='51d5564'
> > select * from x$dual
> > END OF STMT
> > PARSE
> >
>
#1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=5656331
> > BINDS #1:
> > EXEC
> >
>
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5656332
> > WAIT #1: nam='SQL*Net message to client' ela= 0
> > p1=1111838976 p2=1 p3=0
> > FETCH
> >
>
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5656332
> > WAIT #1: nam='SQL*Net message from client' ela= 2
> > p1=1111838976 p2=1 p3=0
> > FETCH
> >
>
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5656334
> > WAIT #1: nam='SQL*Net message to client' ela= 0
> > p1=1111838976 p2=1 p3=0
> > WAIT #1: nam='SQL*Net message from client' ela=
> 815
> > p1=1111838976 p2=1 p3=0
> > STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='FIXED
> TABLE
> > FULL X$DUAL '
> > =====================
> > ----Trace file output ends here----
> >
> > --- Danisment Gazi Unal <dunal_at_ubTools.com> wrote:
> > > Hello Gaja,
> > >
> > > am I missing something ?
> > >
> > > is it 0 logical IO indeed ? or it's not
> accounted
> > > for X$ tables ?
> > >
> > > regards...
> > >
> > >
> > >

<<stuff deleted>>

Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml

Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gaja Krishna Vaidyanatha
  INET: oraperfman_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Tue Apr 23 2002 - 13:00:09 CDT