RE: Currval and buffer gets

Can somebody check how many rows we have in sys.x$dual in an Oracle database running in OPS mode?

Thanks

Waleed

-----Original Message-----
Sent: Tuesday, April 23, 2002 2:56 PM
To: Multiple recipients of list ORACLE-L

Just a clarification (thanks Gaja!)

With Oracle 9i, calls to SYSDATE in PL/SQL or in-line in your DML statements do not get converted to "SELECT SYSDATE FROM DUAL".

Oracle 9i does not speed up explicit selects from dual.

So, if you have an application where the developers coded "SELECT ... FROM DUAL" all over the place, Oracle 9i won't help. You'll still want to use the x$dual method.

Kevin

-----Original Message-----
Sent: Tuesday, April 23, 2002 2:00 PM
To: Multiple recipients of list ORACLE-L

Speaking of Oracle 9i and dual...

But first, some background. In Oracle 8i and earlier, if you refrenced SYSDATE, USER or some number of other built-in functions, Oracle automatically converted that into a SELECT ... FROM dual call. Very expensive (hence this talk about x$dual!)

Enter Oracle 9i. In Oracle 9i references to SYSDATE et al do _NOT_ get converted into such calls! Some quick benchmarks showed that 9i takes between 98 and 99% less time to get SYSDATE!

Caver

-----Original Message-----
Sent: Tuesday, April 23, 2002 12:58 PM
To: Multiple recipients of list ORACLE-L

Redefine the view to work on dual and pay the price !!! Checked it on oracle 8.1.6 and 9.0.1.
I will take the risk that maybe in some future date this will not work if it can save a lot of resources now.

Yechiel Adar
Mehish

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Tuesday, April 23, 2002 3:13 PM

> what would you do if Oracle removed this x$dual or made it multiple row
> table in future upgrades?
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> Sent: 4/23/02 4:08 AM
>
> Hello Gaja
>
> Thanks for the detailed info.
> I created a view and did grant to public.
> I did 10 selects and they did only 2 buffers get.
> BTW - describe on x$dual does not work but select * works.
>
> Yechiel Adar
> Mehish
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Monday, April 22, 2002 8:15 PM
>
>
> > Hello Yechiel,
> >
> > X$DUAL is an Oracle-internal table "in the SGA" and
> > will not be shown in an ALL_OBJECTS listing.
> > Obviously, you need to be SYS to see this. You can do
> > a describe as SYS and you will see it. Which is the
> > reason why I recommended creating a view and a public
> > synonym on the view, so that the application may
> > reference it without any issues.
> >
> > Cheers,
> >
> > Gaja
> >
> > --- Yechiel Adar <adaryechiel_at_hotmail.com> wrote:
> > > Hello Gaja
> > >
> > > I could not find x$dual. Did select on all_objects
> > > got zip.
> > > Oracle 8.1.6.3.4 on NT.
> > >
> > > Yechiel Adar
> > > Mehish
> > >
> > > ----- Original Message -----
> > > To: Multiple recipients of list ORACLE-L
> > > <ORACLE-L_at_fatcity.com>
> > > Sent: Sunday, April 21, 2002 8:28 PM
> > >
> > >
> > > > Hi Yechiel,
> > > >
> > > > Any full-table-scan in Oracle 8i (or below)
> > > consumes 4
> > > > LIOs to the segment header. This number has
> > > reduced to
> > > > 2 in 9i. Given that the 1 row that you are going
> > > after
> > > > is in 1 data block, there is 1 LIO for the data
> > > block
> > > > itself, given you a total of 5 LIOs. You can
> > > verify
> > > > this by setting 10046 for the session and looking
> > > at
> > > > the trace output.
> > > >
> > > > The workaround is to reference x$dual in your
> > > > application. Alternatively, you can create a view
> > > on
> > > > x$dual, create a synonym for it and then go from
> > > > there. You will incur some I/O for the first
> > > access of
> > > > the query (with the synonym), but subsequent
> > > accesses
> > > > will incur 0 LIOs against x$dual.
> > > >
> > > > Cheers,
> > > >
> > > > Gaja
> > > > --- Yechiel Adar <adaryechiel_at_hotmail.com> wrote:
> > > > > I did two statspack snapshots, one hour and
> > > forty
> > > > > minutes apart.
> > > > > Then I generated a report and loaded it into
> > > > > oraperf.com.
> > > > > In the report I saw that the two SQL statements
> > > that
> > > > > where executed the most
> > > > > times where:
> > > > >
> > > > > Select xxxx.currval from dual;
> > > > >
> > > > > Select xxxx.nextval from dual;.
> > > > >
> > > > > Each one was executed about 90,000 times with 5
> > > > > buffer gets per execution.
> > > > > The net result was about 950,000 buffer get for
> > > > > nextval and currval.
> > > > >
> > > > > My question is:
> > > > > Why should there be about 5 buffer gets per
> > > > > execution?
> > > > >
> > > > > Yechiel Adar
> > > > > Mehish
> > > > > --
> > > > > Please see the official ORACLE-L FAQ:
> > > > > http://www.orafaq.com
> > > > > --
> > > > > Author: Yechiel Adar
> > > > > INET: adaryechiel_at_hotmail.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).
> > > >
> > > >
> > > > =====
> > > > 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).
> > > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Yechiel Adar
> > > INET: adaryechiel_at_hotmail.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).
> >
> >
> > =====
> > 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).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Yechiel Adar
> INET: adaryechiel_at_hotmail.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yechiel Adar
  INET: adaryechiel_at_hotmail.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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).