RE: Currval and buffer gets

[Sakthi , Raj]

On ORACLE 8163/Win2K , I got some interesting results
though...

ONE SESSION ONLY

SYS@ZETA@RSAKTHI>
run time using table dual in centiseconds=400

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.75
SYS@ZETA@RSAKTHI>

SYS@ZETA@RSAKTHI>

TWO CONCURRENT SESSIONS

SYS@ZETA@RSAKTHI>/
run time using table dual in centiseconds=500

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.17

THREE CONCURRENT SESSION

SYS@ZETA@RSAKTHI>/

run time using table dual in centiseconds=700

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.59


Notice 100 centiseconds increase in each concurrent
session..:)

HTH
RS
--- Lyubomir Petrov <[EMAIL PROTECTED]> wrote:
> No, I couldn't duplicate this effect
> (8.1.7.0.0/Linux
> and 9.0.1.1.1/Win2k) - 1) first test 2 simultaneous
> sessions and then 2) second test 3 simultaneous
> sessions running.
> 
> Regards
> Lyubomir Petrov
> 
> --- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote:
> > Never mess up with Oracle's memory:
> > 
> > It seems that x$dual is not designed for
> concurrent
> > access. When I try to
> > run the code below in two simultaneous windows the
> > first one comes back and
> > the second crash (8173):
> > 
> > declare
> > nn number;
> > ss1 date;
> > ss2 date;
> > begin
> > ---
> > ss1 := sysdate;
> > for i in 1..10 loop
> > select 2 into nn from sys.x_$dual;
> > end loop;
> > ss2 := sysdate;
> > dbms_output.put_line('run time using table dual in
> > centiseconds='||(ss2 -
> > ss1 ) * 24 * 60 * 60 * 100);
> > end;
> > 
> > 
> > If the x$dual gets replaced by dual it works fine.
> > 
> > Can any one duplicate this?
> > 
> > 
> > Thanks
> > 
> > 
> > Waleed
> > 
> > 
> > 
> > -Original Message-
> > Sent: Tuesday, April 23, 2002 6:49 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > I am writing a paper on application efficiency and
> I
> > have a number of simple
> > tests in that paper that illustrates that in a
> very
> > efficient application
> > the
> > switch to x$dual can make around a 70-80 percent
> > improvent. But beaware: the
> > test suite only accesses x$dual and not other
> tables
> > so the impact is large
> > and the application is written in the most
> efficient
> > way.  Running the same
> > test but in a worse efficiency case. shows only a
> > 2-3 percent gain ..
> > 
> > Anjo.
> > 
> > 
> > "Khedr, Waleed" wrote:
> > 
> > > Can you guys tell me about all the overhead to
> > resolve the view definition
> > > to reach the magical X$dual table?
> > >
> > > Waleed
> > >
> > > -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
> > <[EMAIL PROTECTED]>
> > > 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
> > <[EMAIL PROTECTED]>
> > > > 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 <[EMAIL PROTECTED]>
> > 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
> > > > > > <[EMAIL PROTECTED]>
> > > > > > 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

Re: Currval and buffer gets

[Gaja Krishna Vaidyanatha]

Agreed! No arguments there. I am all for designing and
writing scalable code from the starting block and
avoiding wastage of resources. But sometimes the
problem is much more complex. I am sure you have
inherited "bad application design" or "bad code"
enough times, and may I add "Not by choice". Add to
that, the inherent complexity of a production
environment, the options that you may have "on hand"
to fix a given problem could be limited.

We could all stand from a purist's perspective and
claim that every line of bad SQL needs to be
re-designed, re-written and re-optimized. That would
be perfectin an ideal world. But that is easier
said, than done, in some cases.

In this forum, our goal is to try and provide
potential solutions to problems. In the final
analysis, it is upto the individual to seek out the
best alternative for his/her environment, based on
various factors such as time-to-implement, cost, risk
and benefit. And sometimes all it takes is a "cute
exercise" such as the one on X$DUAL, to fix a problem.

;-)

Regards,

Gaja


--- Jonathan Lewis <[EMAIL PROTECTED]>
wrote:
> 
> I think there is a completely different level at
> which
> to view this issue.
> 
> If your application is using dual "like there is no
> tomorrow"
> then there is almost certainly something wrong with
> your application design or code which is a much
> more significant threat to performance - both
> through
> bottlenecks and wasted CPU.
> 
> Sure, it's cute to play with replacing DUAL with a 
> view called DUAL on x$dual - or playing slightly
> more safely by recreating DUAL as an IOT, but 
> if you are hammering DUAL, it probably won't be the 
> CBC latch on dual's bucket that is the problem.
> 
> 
> BTW - counter-example for anyone thinking of 
> using a view.  
> 
> If your developers decide that they will get Oracle
> to do all the arithmetic to avoid problems of IEEE 
> rounding or some such issue. and have millions of 
> lines like:
> select 2.4 * 5.1 from dual;
> select 18.7 / 2.1 from dual;
> select 1 + 1 from dual;
> 
> You will really kill the system, because every time 
> you hard-parse a statement containing a view, Oracle
> 
> re-executes a recursive query like:
> select text from view$ where rowid = ...
> 
> (Believe it - it has been done).
> 
> 
> 
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
> 
> Author of:
> Practical Oracle 8i: Building Efficient Databases
> 
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
> 
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> 
> 
> 
> |I think the issue of using SYS.DUAL vs. X$DUAL is
> much
> |beyond just "response time". It is more related to
> |"easing a potential bottleneck" in your database,
> in a
> |production environment supporting multiple
> sessions.
> |Given that it takes 5 LIOs (upto Oracle8i) and 3
> LIOs
> |(in Oracle9i for every access to SYS.DUAL, the
> issue
> |then boils down to the contention for the "cache
> |buffers chains" latch to access blocks in the
> database
> |buffer cache. So just because it is only 5(3) LIOs,
> |that does not make it OK.
> |
> |If your application is using SYS.DUAL "like there
> is
> |no tomorrow", the cache buffers chains latch
> becomes
> |your single point of contention. This is true, even
> if
> |you have _DB_BLOCK_HASH_BUCKETS set to a value
> higher
> |than its default.
> |
> 
> 



__
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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Currval and buffer gets

[Glenn Travis]

Title: RE: Currval and buffer gets



On 
HP-UX:
model   
= 9000/800/N4000-55OS release level= B.11.11processor count = 8clock 
speed = 550 
MHzmemory  = 16384 
Mb
SQL> set serveroutput on size 
1;SQL> @test.sql 27  /run time using view x_$dual 
in centiseconds=600run time using table dual in centiseconds=700run time 
using c interface in centiseconds=200
 
PL/SQL procedure successfully 
completed.

  -Original Message-From: Khedr, Waleed 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, April 23, 2002 10:54 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Currval and buffer gets
  Thanks (this is 
  why I'm surprised that Oracle did not improve dual and the way they 
  implemented pl/sql functions. SYSDATE is done using a C interface now in 
  PL/SQL)
  Just to confirm 
  your results I got these times:
   
   run time using view x_$dual in 
  centiseconds=2100.0004  run time using table dual 
  in 
  centiseconds=2999.9998    run time 
  using c interface in 
  centiseconds=1100.0002   

   using ths 
  code:
   
  declarenn date;ss1 date;ss2 date;beginss1 := 
  sysdate;for i in 1..10 loopselect sysdate into nn from 
  sys.x_$dual;end loop;ss2 := sysdate;dbms_output.put_line('run time 
  using view x_$dual in centiseconds='||(ss2 - ss1 ) * 24 * 60 * 60 * 
  100);
  ---ss1 := sysdate;for i in 1..10 loopselect sysdate 
  into nn from dual;end loop;ss2 := 
  sysdate;dbms_output.put_line('run time using table dual in 
  centiseconds='||(ss2 - ss1 ) * 24 * 60 * 60 * 100);---ss1 := 
  sysdate;for i in 1..10 loopnn := sysdate;end loop;ss2 := 
  sysdate;dbms_output.put_line('run time using c interface in 
  centiseconds='||(ss2 - ss1 ) * 24 * 60 * 60 * 
100);end;
  
-Original Message-From: 
[EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, April 
23, 2002 10:23 PMTo: Multiple recipients of list 
    ORACLE-LSubject: RE: Currval and buffer gets
Waleed/Kevin/Jonathan ..., 
8.1.7.2 on HP-UX 64bit. Modified 
version: 
declare n1 number; n2 number; ss date; begin n1 := sys.dbms_utility.get_time; 
for i in 1..10 loop select 
sysdate into ss from sys.x_$dual; end loop; 
n2 := sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using view x_$dual in 
centiseconds='||(n2-n1)); -- n1 := sys.dbms_utility.get_time; for i in 
1..10 loop select sysdate into ss from 
sys.dual; end loop; n2 := 
sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using table dual   in 
centiseconds='||(n2-n1)); -- n1 := sys.dbms_utility.get_time; for i in 
1..10 loop ss := sysdate; end loop; n2 := 
sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using direct :=    
in centiseconds='||(n2-n1)); end; 
Output: run time using view x_$dual 
in centiseconds=1167 run time using table 
dual   in centiseconds=1661 run time using 
direct :=    in centiseconds=339 
Alex. 

Re: Currval and buffer gets

[Gaja Krishna Vaidyanatha]

Hello Yechiel,

Not sure what you mean...I was referring to the "cache
buffers chains" latch in my note NOT the library cache
load lock.

Gaja

--- Yechiel Adar <[EMAIL PROTECTED]> wrote:
> Hello Gaja
> 
> I checked the report from YAPP and library cache
> load lock accounts
> only to 0.75% of the wait time. 28 seconds during
> 6100 seconds between
> snaps.
> 
> Yechiel Adar
> Mehish
> 
> - Original Message -
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> Sent: Wednesday, April 24, 2002 5:03 PM
> 
> 
> > All,
> >
> > I think the issue of using SYS.DUAL vs. X$DUAL is
> much
> > beyond just "response time". It is more related to
> > "easing a potential bottleneck" in your database,
> in a
> > production environment supporting multiple
> sessions.
> > Given that it takes 5 LIOs (upto Oracle8i) and 3
> LIOs
> > (in Oracle9i for every access to SYS.DUAL, the
> issue
> > then boils down to the contention for the "cache
> > buffers chains" latch to access blocks in the
> database
> > buffer cache. So just because it is only 5(3)
> LIOs,
> > that does not make it OK.
> >
> > If your application is using SYS.DUAL "like there
> is
> > no tomorrow", the cache buffers chains latch
> becomes
> > your single point of contention. This is true,
> even if
> > you have _DB_BLOCK_HASH_BUCKETS set to a value
> higher
> > than its default.
> >
> > As Cary has mentioned many times before, the
> problem
> > here is "application serialization". For more on
> this
> > subject, please read Cary's papers "Why a 99%+
> buffer
> > cache hit ratio is NOT Ok" on
> > http://www.hotsos.com/catalog and a recent paper
> at
> > IOUG-A Live 2002 which talks about some common
> > "Misunderstandings about Oracle Internals".
> >
> > Best regards,
> >
> > Gaja
> >
> >
> > --- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote:
> > > Kevin and Jonathan,
> > >
> > > Thanks for the explanation. It's weird for me
> that
> > > Oracle is still
> > > maintaining this kind of dependency between the
> SQL
> > > and PL/SQL engines for
> > > minor sql functions. Also regarding the dual and
> > > x$dual, it does not sound
> > > good to me that Oracle still is implementing
> dual as
> > > a table segment even in
> > > Oracle 9i.
> > >
> > > I would give Gaja all the excuses to recommend
> using
> > > something else other
> > > than sys.dual to overcome this limitation.
> > >
> > > But on the other hand the difference in
> performance
> > > and the over all gain is
> > > too minor to use x$dual (look at the test
> below).
> > >
> > > Modifying the code and changing the design (or
> even
> > > tuning one sql) would be
> > > more promising.
> > >
> > > Thanks everybody,
> > >
> > >
> > > Waleed
> > >
> > >
> > > declare
> > > nn number;
> > > ss1 date;
> > > ss2 date;
> > > begin
> > > ss1 := sysdate;
> > > for i in 1..10 loop
> > > select 2 into nn from sys.x_$dual;
> > > end loop;
> > > ss2 := sysdate;
> > > dbms_output.put_line('run time using view
> x_$dual in
> > > centiseconds='||(ss2 -
> > > ss1 ) * 24 * 60 * 60 * 100);
> > >
> > > ---
> > > ss1 := sysdate;
> > > for i in 1..10 loop
> > > select 2 into nn from dual;
> > > end loop;
> > > ss2 := sysdate;
> > > dbms_output.put_line('run time using table dual
> in
> > > centiseconds='||(ss2 -
> > > ss1 ) * 24 * 60 * 60 * 100);
> > > end;
> > >
> > > -Original Message-
> > > Sent: Tuesday, April 23, 2002 6:18 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > It's a change that also made it into 8.1.7.3
> > > (or possibly 8.1.7.2) - check in
> > >
> > > $ORACLE_HOME/rdbms/admin/standard.sql
> > >
> > >
> > > Jonathan Lewis
> > > http://www.jlcomp.demon.co.uk
> > >
> > > Author of:
> > > Practical Oracle 8i: Building Efficient
> Databases
> > >
> > > Next Seminar - Australia - July/August
> > > http://www.jlcomp.demon.co.uk/seminar.html
> > >
> > > Host to The Co-Operative Oracle Users' FAQ
> > > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> > >
> > >
> > >
> > > -Original Message-
> > > To: Multiple recipients of list ORACLE-L
> > > <[EMAIL PROTECTED]>
> > > Date: 23 April 2002 22:05
> > >
> > >
> > > |I did in 8i (8.1.7.3) and did not see what you
> > > said:
> > > |
> > > |alter session set sql_trace = true
> > > |
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Jonathan Lewis
> > >   INET: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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

Re: Currval and buffer gets

[Anjo Kolk]

The acess path knows about DUAL and that it will return only 1 row. Create any
dual table under another user and you will see that it returns all rows.

Anjo.


"Khedr, Waleed" wrote:

> Do you know of any reason that keeps Oracle implementing dual the way they
> have now and its effect on all the other PL/SQL functions?
>
> Also I saw something on the Metalink where some one inserted many records in
> dual and when selecting count(*) from dual it returns the right count but
> when selecting any expression from dual it returns it only once.
>
> Also completely agree with:
>
> >If your application is using dual "like there is no tomorrow"
> >then there is almost certainly something wrong with
> >your application design or code which is a much
> >more significant threat to performance - both through
> >bottlenecks and wasted CPU.
>
> Thanks
>
> Waleed
>
> -Original Message-
> Sent: Wednesday, April 24, 2002 11:59 AM
> To: Multiple recipients of list ORACLE-L
>
> I think there is a completely different level at which
> to view this issue.
>
> If your application is using dual "like there is no tomorrow"
> then there is almost certainly something wrong with
> your application design or code which is a much
> more significant threat to performance - both through
> bottlenecks and wasted CPU.
>
> Sure, it's cute to play with replacing DUAL with a
> view called DUAL on x$dual - or playing slightly
> more safely by recreating DUAL as an IOT, but
> if you are hammering DUAL, it probably won't be the
> CBC latch on dual's bucket that is the problem.
>
> BTW - counter-example for anyone thinking of
> using a view.
>
> If your developers decide that they will get Oracle
> to do all the arithmetic to avoid problems of IEEE
> rounding or some such issue. and have millions of
> lines like:
> select 2.4 * 5.1 from dual;
> select 18.7 / 2.1 from dual;
> select 1 + 1 from dual;
>
> You will really kill the system, because every time
> you hard-parse a statement containing a view, Oracle
> re-executes a recursive query like:
> select text from view$ where rowid = ...
>
> (Believe it - it has been done).
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> |I think the issue of using SYS.DUAL vs. X$DUAL is much
> |beyond just "response time". It is more related to
> |"easing a potential bottleneck" in your database, in a
> |production environment supporting multiple sessions.
> |Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
> |(in Oracle9i for every access to SYS.DUAL, the issue
> |then boils down to the contention for the "cache
> |buffers chains" latch to access blocks in the database
> |buffer cache. So just because it is only 5(3) LIOs,
> |that does not make it OK.
> |
> |If your application is using SYS.DUAL "like there is
> |no tomorrow", the cache buffers chains latch becomes
> |your single point of contention. This is true, even if
> |you have _DB_BLOCK_HASH_BUCKETS set to a value higher
> |than its default.
> |
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jonathan Lewis
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Anjo Kolk
  INET: [EMAIL PROTECTED]

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: [EMAIL 

RE: Currval and buffer gets

[Khedr, Waleed]

Do you know of any reason that keeps Oracle implementing dual the way they
have now and its effect on all the other PL/SQL functions?

Also I saw something on the Metalink where some one inserted many records in
dual and when selecting count(*) from dual it returns the right count but
when selecting any expression from dual it returns it only once.

Also completely agree with:

>If your application is using dual "like there is no tomorrow"
>then there is almost certainly something wrong with
>your application design or code which is a much
>more significant threat to performance - both through
>bottlenecks and wasted CPU.


Thanks

Waleed

-Original Message-
Sent: Wednesday, April 24, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L



I think there is a completely different level at which
to view this issue.

If your application is using dual "like there is no tomorrow"
then there is almost certainly something wrong with
your application design or code which is a much
more significant threat to performance - both through
bottlenecks and wasted CPU.

Sure, it's cute to play with replacing DUAL with a 
view called DUAL on x$dual - or playing slightly
more safely by recreating DUAL as an IOT, but 
if you are hammering DUAL, it probably won't be the 
CBC latch on dual's bucket that is the problem.


BTW - counter-example for anyone thinking of 
using a view.  

If your developers decide that they will get Oracle
to do all the arithmetic to avoid problems of IEEE 
rounding or some such issue. and have millions of 
lines like:
select 2.4 * 5.1 from dual;
select 18.7 / 2.1 from dual;
select 1 + 1 from dual;

You will really kill the system, because every time 
you hard-parse a statement containing a view, Oracle 
re-executes a recursive query like:
select text from view$ where rowid = ...

(Believe it - it has been done).



Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



|I think the issue of using SYS.DUAL vs. X$DUAL is much
|beyond just "response time". It is more related to
|"easing a potential bottleneck" in your database, in a
|production environment supporting multiple sessions.
|Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
|(in Oracle9i for every access to SYS.DUAL, the issue
|then boils down to the contention for the "cache
|buffers chains" latch to access blocks in the database
|buffer cache. So just because it is only 5(3) LIOs,
|that does not make it OK.
|
|If your application is using SYS.DUAL "like there is
|no tomorrow", the cache buffers chains latch becomes
|your single point of contention. This is true, even if
|you have _DB_BLOCK_HASH_BUCKETS set to a value higher
|than its default.
|


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: Currval and buffer gets

[Yechiel Adar]

Hello Gaja

I checked the report from YAPP and library cache load lock accounts
only to 0.75% of the wait time. 28 seconds during 6100 seconds between
snaps.

Yechiel Adar
Mehish

- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Wednesday, April 24, 2002 5:03 PM


> All,
>
> I think the issue of using SYS.DUAL vs. X$DUAL is much
> beyond just "response time". It is more related to
> "easing a potential bottleneck" in your database, in a
> production environment supporting multiple sessions.
> Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
> (in Oracle9i for every access to SYS.DUAL, the issue
> then boils down to the contention for the "cache
> buffers chains" latch to access blocks in the database
> buffer cache. So just because it is only 5(3) LIOs,
> that does not make it OK.
>
> If your application is using SYS.DUAL "like there is
> no tomorrow", the cache buffers chains latch becomes
> your single point of contention. This is true, even if
> you have _DB_BLOCK_HASH_BUCKETS set to a value higher
> than its default.
>
> As Cary has mentioned many times before, the problem
> here is "application serialization". For more on this
> subject, please read Cary's papers "Why a 99%+ buffer
> cache hit ratio is NOT Ok" on
> http://www.hotsos.com/catalog and a recent paper at
> IOUG-A Live 2002 which talks about some common
> "Misunderstandings about Oracle Internals".
>
> Best regards,
>
> Gaja
>
>
> --- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote:
> > Kevin and Jonathan,
> >
> > Thanks for the explanation. It's weird for me that
> > Oracle is still
> > maintaining this kind of dependency between the SQL
> > and PL/SQL engines for
> > minor sql functions. Also regarding the dual and
> > x$dual, it does not sound
> > good to me that Oracle still is implementing dual as
> > a table segment even in
> > Oracle 9i.
> >
> > I would give Gaja all the excuses to recommend using
> > something else other
> > than sys.dual to overcome this limitation.
> >
> > But on the other hand the difference in performance
> > and the over all gain is
> > too minor to use x$dual (look at the test below).
> >
> > Modifying the code and changing the design (or even
> > tuning one sql) would be
> > more promising.
> >
> > Thanks everybody,
> >
> >
> > Waleed
> >
> >
> > declare
> > nn number;
> > ss1 date;
> > ss2 date;
> > begin
> > ss1 := sysdate;
> > for i in 1..10 loop
> > select 2 into nn from sys.x_$dual;
> > end loop;
> > ss2 := sysdate;
> > dbms_output.put_line('run time using view x_$dual in
> > centiseconds='||(ss2 -
> > ss1 ) * 24 * 60 * 60 * 100);
> >
> > ---
> > ss1 := sysdate;
> > for i in 1..10 loop
> > select 2 into nn from dual;
> > end loop;
> > ss2 := sysdate;
> > dbms_output.put_line('run time using table dual in
> > centiseconds='||(ss2 -
> > ss1 ) * 24 * 60 * 60 * 100);
> > end;
> >
> > -Original Message-
> > Sent: Tuesday, April 23, 2002 6:18 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > It's a change that also made it into 8.1.7.3
> > (or possibly 8.1.7.2) - check in
> >
> > $ORACLE_HOME/rdbms/admin/standard.sql
> >
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Author of:
> > Practical Oracle 8i: Building Efficient Databases
> >
> > Next Seminar - Australia - July/August
> > http://www.jlcomp.demon.co.uk/seminar.html
> >
> > Host to The Co-Operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> >
> >
> > -Original Message-
> > To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> > Date: 23 April 2002 22:05
> >
> >
> > |I did in 8i (8.1.7.3) and did not see what you
> > said:
> > |
> > |alter session set sql_trace = true
> > |
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Jonathan Lewis
> >   INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') an

Re: Currval and buffer gets

[Jonathan Lewis]

I think there is a completely different level at which
to view this issue.

If your application is using dual "like there is no tomorrow"
then there is almost certainly something wrong with
your application design or code which is a much
more significant threat to performance - both through
bottlenecks and wasted CPU.

Sure, it's cute to play with replacing DUAL with a 
view called DUAL on x$dual - or playing slightly
more safely by recreating DUAL as an IOT, but 
if you are hammering DUAL, it probably won't be the 
CBC latch on dual's bucket that is the problem.


BTW - counter-example for anyone thinking of 
using a view.  

If your developers decide that they will get Oracle
to do all the arithmetic to avoid problems of IEEE 
rounding or some such issue. and have millions of 
lines like:
select 2.4 * 5.1 from dual;
select 18.7 / 2.1 from dual;
select 1 + 1 from dual;

You will really kill the system, because every time 
you hard-parse a statement containing a view, Oracle 
re-executes a recursive query like:
select text from view$ where rowid = ...

(Believe it - it has been done).



Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



|I think the issue of using SYS.DUAL vs. X$DUAL is much
|beyond just "response time". It is more related to
|"easing a potential bottleneck" in your database, in a
|production environment supporting multiple sessions.
|Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
|(in Oracle9i for every access to SYS.DUAL, the issue
|then boils down to the contention for the "cache
|buffers chains" latch to access blocks in the database
|buffer cache. So just because it is only 5(3) LIOs,
|that does not make it OK.
|
|If your application is using SYS.DUAL "like there is
|no tomorrow", the cache buffers chains latch becomes
|your single point of contention. This is true, even if
|you have _DB_BLOCK_HASH_BUCKETS set to a value higher
|than its default.
|


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Currval and buffer gets

[James McCann]

Gaja is correct. I had big problems recently with a highly concurrent
application selecting from DUAL  too many times, resulting in an extremely
hot DUAL table. I wish I had knew this trick a few weeks ago. As it was, an
bit of application tuning sorted it out.

Jim

-Original Message-
Krishna Vaidyanatha
Sent: 24 April 2002 16:04
To: Multiple recipients of list ORACLE-L


All,

I think the issue of using SYS.DUAL vs. X$DUAL is much
beyond just "response time". It is more related to
"easing a potential bottleneck" in your database, in a
production environment supporting multiple sessions.
Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
(in Oracle9i for every access to SYS.DUAL, the issue
then boils down to the contention for the "cache
buffers chains" latch to access blocks in the database
buffer cache. So just because it is only 5(3) LIOs,
that does not make it OK.

If your application is using SYS.DUAL "like there is
no tomorrow", the cache buffers chains latch becomes
your single point of contention. This is true, even if
you have _DB_BLOCK_HASH_BUCKETS set to a value higher
than its default.

As Cary has mentioned many times before, the problem
here is "application serialization". For more on this
subject, please read Cary's papers "Why a 99%+ buffer
cache hit ratio is NOT Ok" on
http://www.hotsos.com/catalog and a recent paper at
IOUG-A Live 2002 which talks about some common
"Misunderstandings about Oracle Internals".

Best regards,

Gaja


--- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote:
> Kevin and Jonathan,
>
> Thanks for the explanation. It's weird for me that
> Oracle is still
> maintaining this kind of dependency between the SQL
> and PL/SQL engines for
> minor sql functions. Also regarding the dual and
> x$dual, it does not sound
> good to me that Oracle still is implementing dual as
> a table segment even in
> Oracle 9i.
>
> I would give Gaja all the excuses to recommend using
> something else other
> than sys.dual to overcome this limitation.
>
> But on the other hand the difference in performance
> and the over all gain is
> too minor to use x$dual (look at the test below).
>
> Modifying the code and changing the design (or even
> tuning one sql) would be
> more promising.
>
> Thanks everybody,
>
>
> Waleed
>
>
> declare
> nn number;
> ss1 date;
> ss2 date;
> begin
> ss1 := sysdate;
> for i in 1..10 loop
> select 2 into nn from sys.x_$dual;
> end loop;
> ss2 := sysdate;
> dbms_output.put_line('run time using view x_$dual in
> centiseconds='||(ss2 -
> ss1 ) * 24 * 60 * 60 * 100);
>
> ---
> ss1 := sysdate;
> for i in 1..10 loop
> select 2 into nn from dual;
> end loop;
> ss2 := sysdate;
> dbms_output.put_line('run time using table dual in
> centiseconds='||(ss2 -
> ss1 ) * 24 * 60 * 60 * 100);
> end;
>
> -Original Message-
> Sent: Tuesday, April 23, 2002 6:18 PM
> To: Multiple recipients of list ORACLE-L
>
>
> It's a change that also made it into 8.1.7.3
> (or possibly 8.1.7.2) - check in
>
> $ORACLE_HOME/rdbms/admin/standard.sql
>
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
> -Original Message-
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> Date: 23 April 2002 22:05
>
>
> |I did in 8i (8.1.7.3) and did not see what you
> said:
> |
> |alter session set sql_trace = true
> |
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jonathan Lewis
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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
Direct

RE: Currval and buffer gets

[Ramon E. Estevez]

Title: RE: Currval and buffer gets



Waleed, Kevin, Jonathan, Alexander
 
8.1.7.0  on Windows 2000
 
run 
time using view x$dual in centiseconds=494run time using table 
dual   in centiseconds=896run time using direct 
:=    in centiseconds=1007
Ramon
 

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of 
  [EMAIL PROTECTED]Sent: Tuesday, 23 April, 2002 
  9:23 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Currval and buffer gets
  Waleed/Kevin/Jonathan ..., 
  8.1.7.2 on HP-UX 64bit. Modified 
  version: 
  declare n1 number; n2 number; ss date; begin n1 := sys.dbms_utility.get_time; 
  for i in 1..10 loop select 
  sysdate into ss from sys.x_$dual; end loop; 
  n2 := sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using view x_$dual in 
  centiseconds='||(n2-n1)); -- n1 := sys.dbms_utility.get_time; for i in 
  1..10 loop select sysdate into ss from 
  sys.dual; end loop; n2 := 
  sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using table dual   in 
  centiseconds='||(n2-n1)); -- n1 := sys.dbms_utility.get_time; for i in 
  1..10 loop ss := sysdate; end loop; n2 := 
  sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using direct :=    in 
  centiseconds='||(n2-n1)); end; 
  Output: run time using view x_$dual in 
  centiseconds=1167 run time using table 
  dual   in centiseconds=1661 run time using 
  direct :=    in centiseconds=339 
  Alex. 

RE: Currval and buffer gets

[Gaja Krishna Vaidyanatha]

All,

I think the issue of using SYS.DUAL vs. X$DUAL is much
beyond just "response time". It is more related to
"easing a potential bottleneck" in your database, in a
production environment supporting multiple sessions.
Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
(in Oracle9i for every access to SYS.DUAL, the issue
then boils down to the contention for the "cache
buffers chains" latch to access blocks in the database
buffer cache. So just because it is only 5(3) LIOs,
that does not make it OK.

If your application is using SYS.DUAL "like there is
no tomorrow", the cache buffers chains latch becomes
your single point of contention. This is true, even if
you have _DB_BLOCK_HASH_BUCKETS set to a value higher
than its default.

As Cary has mentioned many times before, the problem
here is "application serialization". For more on this
subject, please read Cary's papers "Why a 99%+ buffer
cache hit ratio is NOT Ok" on
http://www.hotsos.com/catalog and a recent paper at
IOUG-A Live 2002 which talks about some common
"Misunderstandings about Oracle Internals".

Best regards,

Gaja


--- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote:
> Kevin and Jonathan,
> 
> Thanks for the explanation. It's weird for me that
> Oracle is still
> maintaining this kind of dependency between the SQL
> and PL/SQL engines for
> minor sql functions. Also regarding the dual and
> x$dual, it does not sound
> good to me that Oracle still is implementing dual as
> a table segment even in
> Oracle 9i.
> 
> I would give Gaja all the excuses to recommend using
> something else other
> than sys.dual to overcome this limitation.
> 
> But on the other hand the difference in performance
> and the over all gain is
> too minor to use x$dual (look at the test below).
> 
> Modifying the code and changing the design (or even
> tuning one sql) would be
> more promising.
> 
> Thanks everybody,
> 
> 
> Waleed
> 
> 
> declare
> nn number;
> ss1 date;
> ss2 date;
> begin
> ss1 := sysdate;
> for i in 1..10 loop
> select 2 into nn from sys.x_$dual;
> end loop;
> ss2 := sysdate;
> dbms_output.put_line('run time using view x_$dual in
> centiseconds='||(ss2 -
> ss1 ) * 24 * 60 * 60 * 100);
> 
> ---
> ss1 := sysdate;
> for i in 1..10 loop
> select 2 into nn from dual;
> end loop;
> ss2 := sysdate;
> dbms_output.put_line('run time using table dual in
> centiseconds='||(ss2 -
> ss1 ) * 24 * 60 * 60 * 100);
> end;
> 
> -Original Message-
> Sent: Tuesday, April 23, 2002 6:18 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> It's a change that also made it into 8.1.7.3
> (or possibly 8.1.7.2) - check in 
> 
> $ORACLE_HOME/rdbms/admin/standard.sql
> 
> 
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
> 
> Author of:
> Practical Oracle 8i: Building Efficient Databases
> 
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
> 
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> 
> 
> 
> -Original Message-
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> Date: 23 April 2002 22:05
> 
> 
> |I did in 8i (8.1.7.3) and did not see what you
> said:
> |
> |alter session set sql_trace = true
> |
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Jonathan Lewis
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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:

RE: Currval and buffer gets

[Jamadagni, Rajendra]

For this script I get following results on 8161 on DGUX

SQL> @1
run time using view x_$dual in centiseconds=1400
run time using table dual in centiseconds=2600

-
On 9012 on AIX I get
SQL> @1
run time using view x_$dual in centiseconds=600
run time using table dual in
centiseconds=900.0003

--
On 8161 here is the output from tkprof for simple loop of assigning sysdate
to a local variable.
DECLARE
dummy date;
BEGIN
FOR i IN 1..10 LOOP
dummy := SYSDATE;
END LOOP;
END;

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
0
Execute  0  0.00   0.00  0  0  0
0
Fetch0  0.00   0.00  0  0  0
0
--- --   -- -- -- --
--
total1  0.00   0.00  0  0  0
0

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: SYS



SELECT SYSDATE
FROM
 SYS.DUAL

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.01  0  0  0
0
Execute  36646  5.85   5.62  0  0  0
0
Fetch36646  3.60   3.17  0  36646 146584
36646
--- --   -- -- -- --
--
total73293  9.45   8.80  0  36646 146584
36646

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: SYS   (recursive depth: 1)


For the same loop on 9012 I get following ...

DECLARE
dummy date;
BEGIN
FOR i IN 1..10 LOOP
dummy := SYSDATE;
END LOOP;
END;

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.01   0.00  0  0  0
0
Execute  1  0.77   0.76  0  0  0
1
Fetch0  0.00   0.00  0  0  0
0
--- --   -- -- -- --
--
total2  0.78   0.76  0  0  0
1

This is not even when using x_$dual.
HTH
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!
-Original Message-
Sent: Tuesday, April 23, 2002 9:23 PM
To: Multiple recipients of list ORACLE-L


Thanks for the test.

I ran this test to compare the performance difference dealing with dual and
x$dual:

declare
nn number;
ss1 date;
ss2 date;
begin
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using view x_$dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);

---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;


***1

This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and 
delete this e-mail message from your computer, Thank you.

***1

RE: Currval and buffer gets

[Connor McDonald]

Concurrency test from 1 => 10 on Solaris 8.1.7.3
revealed no crashes - using

#!/bin/ksh
for i in 1 2 3 4 ...
do
  sqlplus u/p @the_test.sql &
done

hth
connor

 --- Rachel Carmichael <[EMAIL PROTECTED]> wrote:
> as simultaneously as I could make it on W2K, 9.0.1.2
>  (which means the
> first one was running as I started the second one)
> 
> no problems. Now I ran as sys and changed it to
> x$dual because I got
> errors (table or view not found) when I ran as
> system and used x_$dual
> so I don't know if that changed the test
> 
> 
> --- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote:
> > Never mess up with Oracle's memory:
> > 
> > It seems that x$dual is not designed for
> concurrent access. When I
> > try to
> > run the code below in two simultaneous windows the
> first one comes
> > back and
> > the second crash (8173):
> > 
> > declare
> > nn number;
> > ss1 date;
> > ss2 date;
> > begin
> > ---
> > ss1 := sysdate;
> > for i in 1..10 loop
> > select 2 into nn from sys.x_$dual;
> > end loop;
> > ss2 := sysdate;
> > dbms_output.put_line('run time using table dual in
> > centiseconds='||(ss2 -
> > ss1 ) * 24 * 60 * 60 * 100);
> > end;
> > 
> > 
> > If the x$dual gets replaced by dual it works fine.
> > 
> > Can any one duplicate this?
> > 
> > 
> > Thanks
> > 
> > 
> > Waleed
> > 
> > 
> > 
> > -Original Message-
> > Sent: Tuesday, April 23, 2002 6:49 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > I am writing a paper on application efficiency and
> I have a number of
> > simple
> > tests in that paper that illustrates that in a
> very efficient
> > application
> > the
> > switch to x$dual can make around a 70-80 percent
> improvent. But
> > beaware: the
> > test suite only accesses x$dual and not other
> tables so the impact is
> > large
> > and the application is written in the most
> efficient way.  Running
> > the same
> > test but in a worse efficiency case. shows only a
> 2-3 percent gain
> > ..
> > 
> > Anjo.
> > 
> > 
> > "Khedr, Waleed" wrote:
> > 
> > > Can you guys tell me about all the overhead to
> resolve the view
> > definition
> > > to reach the magical X$dual table?
> > >
> > > Waleed
> > >
> > > -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
> <[EMAIL PROTECTED]>
> > > 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
> <[EMAIL PROTECTED]>
> > > > 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 <[EMAIL PROTECTED]>
> 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
> > > > > > <[EMAIL PROTECTED]>
> > > > > > 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
> > > > > > > t

Re: Currval and buffer gets

[Yechiel Adar]

Problem solved but the mystery continue.
I tried the describe in TOAD and got 'object x$dual not found'.
The describe works in sqlplus.

Yechiel Adar
Mehish

- Original Message - 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Tuesday, April 23, 2002 8:23 PM


> Hi Yechiel,
> 
> Below is a spool file from my 8.1.7 database. I am
> logged in as SYS and I am able to do a "DESC" on
> X$DUAL. Wonder why it did not work on yours.
> 
> SQL> desc x$dual
>  Name  Null?
> Type
>  - 
> 
>  ADDR
> RAW(4)
>  INDX
> NUMBER
>  INST_ID
> NUMBER
>  DUMMY
> VARCHAR2(1)
> 
> SQL> spool off
> 
> Cheers,
> 
> Gaja
> 
> --- Yechiel Adar <[EMAIL PROTECTED]> wrote:
> > 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
> > <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]> 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
> > > > <[EMAIL PROTECTED]>
> > > > 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 <[EMAIL PROTECTED]>
> > 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 .currval from dual;
> > > > > >
> > > > > > Select .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: [EMAIL PROTECTED]
> > > > > >
> > > > > > 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: [EMAIL PROTECTED] (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 M

RE: Currval and buffer gets

[Khedr, Waleed]

Title: RE: Currval and buffer gets



Thanks (this is 
why I'm surprised that Oracle did not improve dual and the way they implemented 
pl/sql functions. SYSDATE is done using a C interface now in 
PL/SQL)
Just to confirm 
your results I got these times:
 
 run time using view x_$dual in 
centiseconds=2100.0004  run time using table dual 
in 
centiseconds=2999.9998    run time using 
c interface in 
centiseconds=1100.0002   
 using ths 
code:
 
declarenn date;ss1 date;ss2 date;beginss1 := 
sysdate;for i in 1..10 loopselect sysdate into nn from 
sys.x_$dual;end loop;ss2 := sysdate;dbms_output.put_line('run time 
using view x_$dual in centiseconds='||(ss2 - ss1 ) * 24 * 60 * 60 * 
100);
---ss1 := sysdate;for i in 1..10 loopselect sysdate into 
nn from dual;end loop;ss2 := sysdate;dbms_output.put_line('run time 
using table dual in centiseconds='||(ss2 - ss1 ) * 24 * 60 * 60 * 
100);---ss1 := sysdate;for i in 1..10 loopnn := 
sysdate;end loop;ss2 := sysdate;dbms_output.put_line('run time using 
c interface in centiseconds='||(ss2 - ss1 ) * 24 * 60 * 60 * 
100);end;

  -Original 
  Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, April 23, 
  2002 10:23 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Currval and buffer gets
  Waleed/Kevin/Jonathan ..., 
  8.1.7.2 on HP-UX 64bit. Modified 
  version: 
  declare n1 number; n2 number; ss date; begin n1 := sys.dbms_utility.get_time; 
  for i in 1..10 loop select 
  sysdate into ss from sys.x_$dual; end loop; 
  n2 := sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using view x_$dual in 
  centiseconds='||(n2-n1)); -- n1 := sys.dbms_utility.get_time; for i in 
  1..10 loop select sysdate into ss from 
  sys.dual; end loop; n2 := 
  sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using table dual   in 
  centiseconds='||(n2-n1)); -- n1 := sys.dbms_utility.get_time; for i in 
  1..10 loop ss := sysdate; end loop; n2 := 
  sys.dbms_utility.get_time; sys.dbms_output.put_line('run time using direct :=    in 
  centiseconds='||(n2-n1)); end; 
  Output: run time using view x_$dual in 
  centiseconds=1167 run time using table 
  dual   in centiseconds=1661 run time using 
  direct :=    in centiseconds=339 
  Alex. 

RE: Currval and buffer gets

[Khedr, Waleed]

I found that the error happens when I run the test using some Oracle tool
but worked fine using sqlplus.

Thanks

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


as simultaneously as I could make it on W2K, 9.0.1.2  (which means the
first one was running as I started the second one)

no problems. Now I ran as sys and changed it to x$dual because I got
errors (table or view not found) when I ran as system and used x_$dual
so I don't know if that changed the test


--- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote:
> Never mess up with Oracle's memory:
> 
> It seems that x$dual is not designed for concurrent access. When I
> try to
> run the code below in two simultaneous windows the first one comes
> back and
> the second crash (8173):
> 
> declare
> nn number;
> ss1 date;
> ss2 date;
> begin
> ---
> ss1 := sysdate;
> for i in 1..10 loop
> select 2 into nn from sys.x_$dual;
> end loop;
> ss2 := sysdate;
> dbms_output.put_line('run time using table dual in
> centiseconds='||(ss2 -
> ss1 ) * 24 * 60 * 60 * 100);
> end;
> 
> 
> If the x$dual gets replaced by dual it works fine.
> 
> Can any one duplicate this?
> 
> 
> Thanks
> 
> 
> Waleed
> 
> 
> 
> -Original Message-
> Sent: Tuesday, April 23, 2002 6:49 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I am writing a paper on application efficiency and I have a number of
> simple
> tests in that paper that illustrates that in a very efficient
> application
> the
> switch to x$dual can make around a 70-80 percent improvent. But
> beaware: the
> test suite only accesses x$dual and not other tables so the impact is
> large
> and the application is written in the most efficient way.  Running
> the same
> test but in a worse efficiency case. shows only a 2-3 percent gain
> ..
> 
> Anjo.
> 
> 
> "Khedr, Waleed" wrote:
> 
> > Can you guys tell me about all the overhead to resolve the view
> definition
> > to reach the magical X$dual table?
> >
> > Waleed
> >
> > -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 <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]> 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
> > > > > <[EMAIL PROTECTED]>
> > > > > 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
> > > > > accesse

RE: Currval and buffer gets

[Rachel Carmichael]

as simultaneously as I could make it on W2K, 9.0.1.2  (which means the
first one was running as I started the second one)

no problems. Now I ran as sys and changed it to x$dual because I got
errors (table or view not found) when I ran as system and used x_$dual
so I don't know if that changed the test


--- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote:
> Never mess up with Oracle's memory:
> 
> It seems that x$dual is not designed for concurrent access. When I
> try to
> run the code below in two simultaneous windows the first one comes
> back and
> the second crash (8173):
> 
> declare
> nn number;
> ss1 date;
> ss2 date;
> begin
> ---
> ss1 := sysdate;
> for i in 1..10 loop
> select 2 into nn from sys.x_$dual;
> end loop;
> ss2 := sysdate;
> dbms_output.put_line('run time using table dual in
> centiseconds='||(ss2 -
> ss1 ) * 24 * 60 * 60 * 100);
> end;
> 
> 
> If the x$dual gets replaced by dual it works fine.
> 
> Can any one duplicate this?
> 
> 
> Thanks
> 
> 
> Waleed
> 
> 
> 
> -Original Message-
> Sent: Tuesday, April 23, 2002 6:49 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I am writing a paper on application efficiency and I have a number of
> simple
> tests in that paper that illustrates that in a very efficient
> application
> the
> switch to x$dual can make around a 70-80 percent improvent. But
> beaware: the
> test suite only accesses x$dual and not other tables so the impact is
> large
> and the application is written in the most efficient way.  Running
> the same
> test but in a worse efficiency case. shows only a 2-3 percent gain
> ..
> 
> Anjo.
> 
> 
> "Khedr, Waleed" wrote:
> 
> > Can you guys tell me about all the overhead to resolve the view
> definition
> > to reach the magical X$dual table?
> >
> > Waleed
> >
> > -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 <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]> 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
> > > > > <[EMAIL PROTECTED]>
> > > > > 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 <[EMAIL PROTECTED]> wrote:
> > > > > > > I did two statspack snapshots, one hour and

RE: Currval and buffer gets

[Alexander . Feinstein]

Title: RE: Currval and buffer gets





Waleed/Kevin/Jonathan ...,


8.1.7.2 on HP-UX 64bit.
Modified version:


declare
n1 number;
n2 number;
ss date;
begin
n1 := sys.dbms_utility.get_time;
for i in 1..10 loop
select sysdate into ss from sys.x_$dual;
end loop;
n2 := sys.dbms_utility.get_time;
sys.dbms_output.put_line('run time using view x_$dual in centiseconds='||(n2-n1));
--
n1 := sys.dbms_utility.get_time;
for i in 1..10 loop
select sysdate into ss from sys.dual;
end loop;
n2 := sys.dbms_utility.get_time;
sys.dbms_output.put_line('run time using table dual   in centiseconds='||(n2-n1));
--
n1 := sys.dbms_utility.get_time;
for i in 1..10 loop
ss := sysdate;
end loop;
n2 := sys.dbms_utility.get_time;
sys.dbms_output.put_line('run time using direct :=    in centiseconds='||(n2-n1));
end;


Output:
run time using view x_$dual in centiseconds=1167
run time using table dual   in centiseconds=1661
run time using direct :=    in centiseconds=339


Alex.

RE: Currval and buffer gets

[Khedr, Waleed]

*** 2002-04-23 20:11:26.129
*** SESSION ID:(198.62474) 2002-04-23 20:11:26.125
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object),
addr: 0x1c, PC: opifch()+4668
*** 2002-04-23 20:11:26.766
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [opifch()+4668] [SIGSEGV]
[Address not mapped to object] [28] [] []
Current SQL statement for this session:
declare
nn number;
ss1 date;
ss2 date;
begin
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;
- Call Stack Trace -
calling  call entryargument values in hex

location type point(? means dubious value)

  

ksedmp()+168 CALL ksedst()+0   2D8 ? 1 ? EFFFBD98 ?
   EFFFB83C ? EFFFB820 ? 0 ?
..
.

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 23, 2002 8:39 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]


Waleed,
I am using 81714 under NT4.

I can NOT duplicate the problem you're seeing (but I ran it logged in as sys
and used sys.x$dual).
ie - I can run your script from 2 simultaneous windows & it works ok.
What platform & patch are you on?

Timing wise - the version using sys.x$dual took 500 or 600cs whilst the one
using plain dual took 900 or 1000 cs.

I then created a view (x_$dual) on sys.x$dual and granted select to another
user.
Running from that other (DBA) user I again could not reproduce the problem
you see.

The performance in this case (using a view) was not noticeably different to
being logged in as sys.

What error does your second session get?

Regards
Bruce Reardon


-Original Message-
Sent: Wednesday, 24 April 2002 10:43

No, I couldn't duplicate this effect (8.1.7.0.0/Linux
and 9.0.1.1.1/Win2k) - 1) first test 2 simultaneous
sessions and then 2) second test 3 simultaneous
sessions running.

Regards
Lyubomir Petrov

-Original Message-
Sent: Wednesday, 24 April 2002 9:43

Never mess up with Oracle's memory:

It seems that x$dual is not designed for concurrent access. When I try to
run the code below in two simultaneous windows the first one comes back and
the second crash (8173):

declare
nn number;
ss1 date;
ss2 date;
begin
---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;


If the x$dual gets replaced by dual it works fine.
Can any one duplicate this?

Thanks
Waleed

-Original Message-
Sent: Tuesday, April 23, 2002 6:49 PM

I am writing a paper on application efficiency and I have a number of simple
tests in that paper that illustrates that in a very efficient application
the
switch to x$dual can make around a 70-80 percent improvent. But beaware: the
test suite only accesses x$dual and not other tables so the impact is large
and the application is written in the most efficient way.  Running the same
test but in a worse efficiency case. shows only a 2-3 percent gain ..

Anjo.


"Khedr, Waleed" wrote:

> Can you guys tell me about all the overhead to resolve the view definition
> to reach the magical X$dual table?
>
> Waleed
>
> -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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
> > 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.

RE: Currval and buffer gets

[Khedr, Waleed]

Kevin and Jonathan,

Thanks for the explanation. It's weird for me that Oracle is still
maintaining this kind of dependency between the SQL and PL/SQL engines for
minor sql functions. Also regarding the dual and x$dual, it does not sound
good to me that Oracle still is implementing dual as a table segment even in
Oracle 9i.

I would give Gaja all the excuses to recommend using something else other
than sys.dual to overcome this limitation.

But on the other hand the difference in performance and the over all gain is
too minor to use x$dual (look at the test below).

Modifying the code and changing the design (or even tuning one sql) would be
more promising.

Thanks everybody,


Waleed


declare
nn number;
ss1 date;
ss2 date;
begin
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using view x_$dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);

---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;

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


It's a change that also made it into 8.1.7.3
(or possibly 8.1.7.2) - check in 

$ORACLE_HOME/rdbms/admin/standard.sql


Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 23 April 2002 22:05


|I did in 8i (8.1.7.3) and did not see what you said:
|
|alter session set sql_trace = true
|


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Currval and buffer gets

[Reardon, Bruce (CALBBAY)]

Waleed,
I am using 81714 under NT4.

I can NOT duplicate the problem you're seeing (but I ran it logged in as sys and used 
sys.x$dual).
ie - I can run your script from 2 simultaneous windows & it works ok.
What platform & patch are you on?

Timing wise - the version using sys.x$dual took 500 or 600cs whilst the one using 
plain dual took 900 or 1000 cs.

I then created a view (x_$dual) on sys.x$dual and granted select to another user.
Running from that other (DBA) user I again could not reproduce the problem you see.

The performance in this case (using a view) was not noticeably different to being 
logged in as sys.

What error does your second session get?

Regards
Bruce Reardon


-Original Message-
Sent: Wednesday, 24 April 2002 10:43

No, I couldn't duplicate this effect (8.1.7.0.0/Linux
and 9.0.1.1.1/Win2k) - 1) first test 2 simultaneous
sessions and then 2) second test 3 simultaneous
sessions running.

Regards
Lyubomir Petrov

-Original Message-
Sent: Wednesday, 24 April 2002 9:43

Never mess up with Oracle's memory:

It seems that x$dual is not designed for concurrent access. When I try to
run the code below in two simultaneous windows the first one comes back and
the second crash (8173):

declare
nn number;
ss1 date;
ss2 date;
begin
---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;


If the x$dual gets replaced by dual it works fine.
Can any one duplicate this?

Thanks
Waleed

-Original Message-
Sent: Tuesday, April 23, 2002 6:49 PM

I am writing a paper on application efficiency and I have a number of simple
tests in that paper that illustrates that in a very efficient application
the
switch to x$dual can make around a 70-80 percent improvent. But beaware: the
test suite only accesses x$dual and not other tables so the impact is large
and the application is written in the most efficient way.  Running the same
test but in a worse efficiency case. shows only a 2-3 percent gain ..

Anjo.


"Khedr, Waleed" wrote:

> Can you guys tell me about all the overhead to resolve the view definition
> to reach the magical X$dual table?
>
> Waleed
>
> -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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]> 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
> > > > <[EMAIL PROTECTED]>
> > > > 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

RE: Currval and buffer gets

[Khedr, Waleed]

Thanks for the test.I ran this 
test to compare the performance difference dealing with dual and 
x$dual:
declarenn number;ss1 date;ss2 
date;beginss1 := sysdate;for i in 1..10 loopselect 2 into nn 
from sys.x_$dual;end loop;ss2 := sysdate;dbms_output.put_line('run 
time using view x_$dual in centiseconds='||(ss2 - ss1 ) * 24 * 60 * 60 * 
100);---ss1 := sysdate;for i in 1..10 loopselect 2 into 
nn from dual;end loop;ss2 := sysdate;dbms_output.put_line('run time 
using table dual in centiseconds='||(ss2 - ss1 ) * 24 * 60 * 60 * 
100);end;
 
Here is the results:
run time using view x_$dual in 
centiseconds=1100.0002  run time using table dual 
in 
centiseconds=1799.9997    
For 100,000 executions it took 11 sec using 
x$dual and took 18 sec using dual.
 
Regards,
 
Waleed
-Original Message-From: Lyubomir Petrov [mailto:[EMAIL PROTECTED]]Sent: Tuesday, April 
23, 2002 8:43 PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
Currval and buffer getsNo, I couldn't duplicate this effect 
(8.1.7.0.0/Linuxand 9.0.1.1.1/Win2k) - 1) first test 2 
simultaneoussessions and then 2) second test 3 simultaneoussessions 
running.RegardsLyubomir Petrov--- "Khedr, Waleed" 
<[EMAIL PROTECTED]> wrote:> Never mess up with Oracle's 
memory:>> It seems that x$dual is not designed for 
concurrent> access. When I try to> run the code below in two 
simultaneous windows the> first one comes back and> the second 
crash (8173):>> declare> nn number;> ss1 
date;> ss2 date;> begin> ---> ss1 := 
sysdate;> for i in 1..10 loop> select 2 into nn from 
sys.x_$dual;> end loop;> ss2 := sysdate;> 
dbms_output.put_line('run time using table dual in> centiseconds='||(ss2 
-> ss1 ) * 24 * 60 * 60 * 100);> end;>>> If 
the x$dual gets replaced by dual it works fine.>> Can any one 
duplicate this?>>> Thanks>>> 
Waleed -Original Message-> Sent: 
Tuesday, April 23, 2002 6:49 PM> To: Multiple recipients of list 
ORACLE-L>>> I am writing a paper on application efficiency 
and I> have a number of simple> tests in that paper that 
illustrates that in a very> efficient application> the> 
switch to x$dual can make around a 70-80 percent> improvent. But beaware: 
the> test suite only accesses x$dual and not other tables> so the 
impact is large> and the application is written in the most 
efficient> way.  Running the same> test but in a worse 
efficiency case. shows only a> 2-3 percent gain ..>> 
Anjo.>>> "Khedr, Waleed" wrote:>> > Can 
you guys tell me about all the overhead to> resolve the view 
definition> > to reach the magical X$dual table?> >> 
> Waleed> >> > -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> <[EMAIL PROTECTED]>> > 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> 
<[EMAIL PROTECTED]>> > > 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 
<[EMAIL PROTECTED]>> 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> > > 

RE: Currval and buffer gets

[Lyubomir Petrov]

No, I couldn't duplicate this effect (8.1.7.0.0/Linux
and 9.0.1.1.1/Win2k) - 1) first test 2 simultaneous
sessions and then 2) second test 3 simultaneous
sessions running.

Regards
Lyubomir Petrov

--- "Khedr, Waleed" <[EMAIL PROTECTED]> wrote:
> Never mess up with Oracle's memory:
> 
> It seems that x$dual is not designed for concurrent
> access. When I try to
> run the code below in two simultaneous windows the
> first one comes back and
> the second crash (8173):
> 
> declare
> nn number;
> ss1 date;
> ss2 date;
> begin
> ---
> ss1 := sysdate;
> for i in 1..10 loop
> select 2 into nn from sys.x_$dual;
> end loop;
> ss2 := sysdate;
> dbms_output.put_line('run time using table dual in
> centiseconds='||(ss2 -
> ss1 ) * 24 * 60 * 60 * 100);
> end;
> 
> 
> If the x$dual gets replaced by dual it works fine.
> 
> Can any one duplicate this?
> 
> 
> Thanks
> 
> 
> Waleed
> 
> 
> 
> -Original Message-
> Sent: Tuesday, April 23, 2002 6:49 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> I am writing a paper on application efficiency and I
> have a number of simple
> tests in that paper that illustrates that in a very
> efficient application
> the
> switch to x$dual can make around a 70-80 percent
> improvent. But beaware: the
> test suite only accesses x$dual and not other tables
> so the impact is large
> and the application is written in the most efficient
> way.  Running the same
> test but in a worse efficiency case. shows only a
> 2-3 percent gain ..
> 
> Anjo.
> 
> 
> "Khedr, Waleed" wrote:
> 
> > Can you guys tell me about all the overhead to
> resolve the view definition
> > to reach the magical X$dual table?
> >
> > Waleed
> >
> > -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
> <[EMAIL PROTECTED]>
> > 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
> <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]>
> 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
> > > > > <[EMAIL PROTECTED]>
> > > > > 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 <[EMAIL PROTECTED]>
> wrote:
> > > > > > > I did two statspack snapshots, one hour
> and
> > > > > forty
> > > > > > > minutes apart.
> > > > > > >

RE: Currval and buffer gets

[Khedr, Waleed]

Never mess up with Oracle's memory:

It seems that x$dual is not designed for concurrent access. When I try to
run the code below in two simultaneous windows the first one comes back and
the second crash (8173):

declare
nn number;
ss1 date;
ss2 date;
begin
---
ss1 := sysdate;
for i in 1..10 loop
select 2 into nn from sys.x_$dual;
end loop;
ss2 := sysdate;
dbms_output.put_line('run time using table dual in centiseconds='||(ss2 -
ss1 ) * 24 * 60 * 60 * 100);
end;


If the x$dual gets replaced by dual it works fine.

Can any one duplicate this?


Thanks


Waleed



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


I am writing a paper on application efficiency and I have a number of simple
tests in that paper that illustrates that in a very efficient application
the
switch to x$dual can make around a 70-80 percent improvent. But beaware: the
test suite only accesses x$dual and not other tables so the impact is large
and the application is written in the most efficient way.  Running the same
test but in a worse efficiency case. shows only a 2-3 percent gain ..

Anjo.


"Khedr, Waleed" wrote:

> Can you guys tell me about all the overhead to resolve the view definition
> to reach the magical X$dual table?
>
> Waleed
>
> -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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]> 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
> > > > <[EMAIL PROTECTED]>
> > > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > > >
> > > > > > Select .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

Re: Currval and buffer gets

[Anjo Kolk]

I am writing a paper on application efficiency and I have a number of simple
tests in that paper that illustrates that in a very efficient application the
switch to x$dual can make around a 70-80 percent improvent. But beaware: the
test suite only accesses x$dual and not other tables so the impact is large
and the application is written in the most efficient way.  Running the same
test but in a worse efficiency case. shows only a 2-3 percent gain ..

Anjo.


"Khedr, Waleed" wrote:

> Can you guys tell me about all the overhead to resolve the view definition
> to reach the magical X$dual table?
>
> Waleed
>
> -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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]> 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
> > > > <[EMAIL PROTECTED]>
> > > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > > >
> > > > > > Select .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: [EMAIL PROTECTED]
> > > > > >
> > > > > > 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: [EMAIL PROTECT

RE: Currval and buffer gets

[Khedr, Waleed]

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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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
> > > <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > >
> > > > > Select .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: [EMAIL PROTECTED]
> > > > >
> > > > > Fat City Network Services-- (858) 538-5051
> > > FAX:
> > > > > (858)

Re: Currval and buffer gets

[Jonathan Lewis]

It's a change that also made it into 8.1.7.3
(or possibly 8.1.7.2) - check in 

$ORACLE_HOME/rdbms/admin/standard.sql


Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 23 April 2002 22:05


|I did in 8i (8.1.7.3) and did not see what you said:
|
|alter session set sql_trace = true
|


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Currval and buffer gets

[Khedr, Waleed]

Can you guys tell me about all the overhead to resolve the view definition
to reach the magical X$dual table?

Waleed

-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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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
> > > <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > >
> > > > > Select .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: [EMAIL PROTECTED]
> > > > >
> > > > > 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: [EMAIL PROTECTED] (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://ga

RE: Currval and buffer gets

[Khedr, Waleed]

I did in 8i (8.1.7.3) and did not see what you said:

alter session set sql_trace = true


call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse0  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch0  0.00   0.00  0  0  0
0
--- --   -- -- -- --
--
total1  0.00   0.00  0  0  0
0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 26  



BEGIN DBMS_OUTPUT.GET_LINE(:Buffer, :Status); END;


call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse3  0.00   0.00  0  0  0
0
Execute  3  0.07   0.08  0  0  0
3
Fetch0  0.00   0.00  0  0  0
0
--- --   -- -- -- --
--
total6  0.07   0.08  0  0  0
3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26  



DECLARE
dummy date;
BEGIN
FOR i IN 1..10 LOOP
dummy := SYSDATE;
END LOOP;
END;

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.01   0.00  0  0  0
0
Execute  1 10.44  10.45  0  0  0
1
Fetch0  0.00   0.00  0  0  0
0
--- --   -- -- -- --
--
total2 10.45  10.45  0  0  0
1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26  



BEGIN DBMS_OUTPUT.ENABLE; END;


call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.01  0  0  0
0
Execute  1  0.00   0.00  0  0  0
1
Fetch0  0.00   0.00  0  0  0
0
--- --   -- -- -- --
--
total2  0.00   0.01  0  0  0
1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26  



commit


call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.00   0.00  0  0  0
0
Execute  1  0.00   0.00  0  0  0
0
Fetch0  0.00   0.00  0  0  0
0
--- --   -- -- -- --
--
total2  0.00   0.00  0  0  0
0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 26  






OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse6  0.01   0.01  0  0  0
0
Execute  7 10.51  10.53  0  0  0
5
Fetch0  0.00   0.00  0  0  0
0
--- --   -- -- -- --
--
total   13 10.52  10.54  0  0  0
5

Misses in library cache during parse: 4
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse0  0.00   0.00  0  0  0
0
Execute  0  0.00   0.00  0  0  0
0
Fetch0  0.00   0.00  0  0  0
0
--- --   -- -- -- --
--
total0  0.00   0.00  0  0  0
0

Misses in li

Re: Currval and buffer gets

[Anjo Kolk]

Well you may be right, but rman is using x$dual.
X$dual has been there since 8.1.5 (?).

Anjo.

Yechiel Adar wrote:

> 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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]> 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
> > > > <[EMAIL PROTECTED]>
> > > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > > >
> > > > > > Select .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: [EMAIL PROTECTED]
> > > > > >
> > > > > > 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: [EMAIL PROTECTED] (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/007213145

RE: Currval and buffer gets

[Toepke, Kevin M]

Waleed:

I should have clarified...calls to SYSDATE from PL/SQL get converted.

Try executing the following PL/SQL code in Oracle 8i (or Oracle 8) and again
in Oracle 9i after turning tracing on. In Oracle 8i you will see "SELECT
SYSDATE FROM DUAL" in your tkprof output. In Oracle 9i you will not! Plus
Oracle 9i will be much faster.

Caver

DECLARE
dummy date;
BEGIN
FOR i IN 1..10 LOOP
dummy := SYSDATE;
END LOOP;
END;
/

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


I do not think this is right!

Waleed

-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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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
> > > <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > >
> > > > > Select .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: [EMAIL PROTECTED]
> > > > >
> > > > > Fat City Network Services-- (858) 538-5051
> > > FAX:
> > > > > (858) 538-5051
> > > > > San Dieg

RE: Currval and buffer gets

[Toepke, Kevin M]

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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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
> > > <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > >
> > > > > Select .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: [EMAIL PROTECTED]
> > > > >
> > > > > Fat City Network Services-- (858) 538-5051
> > > FAX:
> > > > > (858) 538-5051
> > > > > San Diego, California-- Public Internet
> > > > > access / Mailing Lists
> > > > >
> > > >
> > >
> > 
> > > > > To REMOVE yours

Re: Currval and buffer gets

[Anjo Kolk]

SYSDATE for sure, but USER is still done the old fashioned way as far as I
can see.

Anjo.


"Toepke, Kevin M" wrote:

> 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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]> 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
> > > > <[EMAIL PROTECTED]>
> > > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > > >
> > > > > > Select .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: [EMAIL PROTECTED]
> > > > > >
> > > > > > 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: [EMAIL PROTECTE

RE: Currval and buffer gets

[Khedr, Waleed]

I do not think this is right!

Waleed

-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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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
> > > <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > >
> > > > > Select .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: [EMAIL PROTECTED]
> > > > >
> > > > > 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: [EMAIL PROTECTED] (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 c

Re: Currval and buffer gets

[Gaja Krishna Vaidyanatha]

Hi Yechiel,

Below is a spool file from my 8.1.7 database. I am
logged in as SYS and I am able to do a "DESC" on
X$DUAL. Wonder why it did not work on yours.

SQL> desc x$dual
 Name  Null?   
Type
 - 

 ADDR  
RAW(4)
 INDX  
NUMBER
 INST_ID   
NUMBER
 DUMMY 
VARCHAR2(1)

SQL> spool off

Cheers,

Gaja

--- Yechiel Adar <[EMAIL PROTECTED]> wrote:
> 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
> <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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
> > > <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]>
> 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 .currval from dual;
> > > > >
> > > > > Select .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: [EMAIL PROTECTED]
> > > > >
> > > > > 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: [EMAIL PROTECTED] (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
> >

Re: Currval and buffer gets

[Gaja Krishna Vaidyanatha]

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=838976 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=838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 41
p1=838976 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=838976 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=838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 41
p1=838976 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=838976 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=838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 41
p1=838976 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 <[EMAIL PROTECTED]> 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=838976 p2=1 p3=0
> > *** 2002-04-22 16:04:25.617
> > WAIT #1: nam='SQL*Net message from client' ela=
> 1152
> > p1=838976 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=838976 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=838976 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=838976 p2=1 p3=0
> > WAIT #1: nam='SQL*Net message from client' ela=
> 815
> > p1=838976 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 <[EMAIL PROTECTED]> wrote:
> > > Hello Gaja,
> > >
> > > am I missing something ?
> > >
> > > is it 0 logical IO indeed ? or  it's not
> accounted
> > > for X$ tables ?
> > >
> > > regards...
> > >
> > >
> > >

<>


=
Gaja Krishna Vaidyanatha
Dir

RE: Currval and buffer gets

[Toepke, Kevin M]

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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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
> > > <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > >
> > > > > Select .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: [EMAIL PROTECTED]
> > > > >
> > > > > 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: [EMAIL PROTECTED] (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

Re: Currval and buffer gets

[Yechiel Adar]

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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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
> > > <[EMAIL PROTECTED]>
> > > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > > >
> > > > > Select .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: [EMAIL PROTECTED]
> > > > >
> > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> > > >
> > > > Fat City Network Services 

RE: Currval and buffer gets

[Khedr, Waleed]

 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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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
> > <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > >
> > > > Select .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: [EMAIL PROTECTED]
> > > >
> > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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
> > -

Re: Currval and buffer gets

[Danisment Gazi Unal]

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=838976 p2=1 p3=0
*** 2002-04-22 16:04:25.617
WAIT #1: nam='SQL*Net message from client' ela= 1152
p1=838976 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=838976 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=838976 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=838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 815
p1=838976 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 <[EMAIL PROTECTED]> wrote:
> Hello Gaja,
>
> am I missing something ?
>
> is it 0 logical IO indeed ? or  it's not accounted
> for X$ tables ?
>
> regards...
>
>
>
> Gaja Krishna Vaidyanatha wrote:
>
> > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > >
> > > Select .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: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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/007213

Re: Currval and buffer gets

[Yechiel Adar]

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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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
> > <[EMAIL PROTECTED]>
> > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > > >
> > > > Select .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: [EMAIL PROTECTED]
> > > >
> > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051  FAX:
> > (858) 538-5051
> > San Diego, California-- Public Internet
> 

Re: Currval and buffer gets

[Gaja Krishna Vaidyanatha]

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=838976 p2=1 p3=0
*** 2002-04-22 16:04:25.617
WAIT #1: nam='SQL*Net message from client' ela= 1152
p1=838976 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=838976 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=838976 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=838976 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 815
p1=838976 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 <[EMAIL PROTECTED]> wrote:
> Hello Gaja,
> 
> am I missing something ?
> 
> is it 0 logical IO indeed ? or  it's not accounted
> for X$ tables ?
> 
> regards...
> 
> 
> 
> Gaja Krishna Vaidyanatha wrote:
> 
> > 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > >
> > > Select .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: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> > the me

Re: Currval and buffer gets

[Danisment Gazi Unal]

Hello Gaja,

am I missing something ?

is it 0 logical IO indeed ? or  it's not accounted for X$ tables ?

regards...



Gaja Krishna Vaidyanatha wrote:

> 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> >
> > Select .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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).

--
Danisment Gazi Unal
http://www.ubTools.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Danisment Gazi Unal
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: Currval and buffer gets

[Gaja Krishna Vaidyanatha]

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 <[EMAIL PROTECTED]> 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
> <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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 .currval from dual;
> > >
> > > Select .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: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> > 
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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

_

RE: Currval and buffer gets

[Khedr, Waleed]

returning_clause is supported in most languages.

Also you might need to encapsulate some of your logic using stored
procedures.

Good luck.

Waleed

-Original Message-
Sent: Monday, April 22, 2002 1:06 PM
To: Multiple recipients of list ORACLE-L


Thank all of you for the replies.
Unfortunately the program is in c++.

Gaja, I will forward your suggestion to the development team.

Yechiel Adar
Mehish

- Original Message - 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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 .currval from dual;
> >
> > Select .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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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)

Re: Currval and buffer gets

[Yechiel Adar]

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 <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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 .currval from dual;
> >
> > Select .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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: Currval and buffer gets

[Yechiel Adar]

Thank all of you for the replies.
Unfortunately the program is in c++.

Gaja, I will forward your suggestion to the development team.

Yechiel Adar
Mehish

- Original Message - 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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 .currval from dual;
> >
> > Select .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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Currval and buffer gets

[Khedr, Waleed]

 If you are using PL/sql then try to reference the sequence next value in
the update/insert statement itself. Aso the update/insert can return the
value of the sequence to a PL/SQL memory variable.

regards,

Waleed

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/21/02 1:23 PM

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 .currval from dual;

Select .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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: Currval and buffer gets

[Jonathan Lewis]

The 5 buffer gets are 4 CURRENT on the
segment header block and one consistent
on the data block.  It's a (relatively minor)
bug in the internal code for scans.

This changes in 9.0.1 to 2 Consistent on
the segment header and one consistent
on the data block in version 9.

The large number of selects suggests
(though not it's not always the case) that
you have some pl/sql code which is not
using sequences to best effect.

Look for code like:

select  xxx.nextval into m_var from dual;
insert into parent table values(
m_var, etc...
);

select xxx.currval into m_var from dual;
insert into child table values(
m_var, 
);

If this sort of thing is happening, look
for the opportunity to re-write it using

declare
m_varnumber;
begin
insert into parent values (
xxx.nextval, etc.
)
returning seq_col into m_var;

end;

The option for inserting the NEXTVAL
directly, and the RETURNING clause
to find out what the inserted value was
can reduce calls to SQL from PL/SQL
quite dramatically.




Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 21 April 2002 17:59


|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 .currval from dual;
|
|Select .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: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: Currval and buffer gets

[Gaja Krishna Vaidyanatha]

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 <[EMAIL PROTECTED]> 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 .currval from dual;
> 
> Select .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: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).