Re: Currval and buffer gets
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 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
RE: Currval and buffer gets
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
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
RE: Currval and buffer gets
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
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://www.orafaq.com
--
Author: Gaja Krishna Vaidyanatha
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San
RE: Currval and buffer gets
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 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
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
Re: Currval and buffer gets
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
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') 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.
RE: Currval and buffer gets
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
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 PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of
Re: Currval and buffer gets
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 the HELP command for other information
(like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Khedr, Waleed
=== message truncated ===
=
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
RE: Currval and buffer gets
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.sql27 /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
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
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 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
Re: Currval and buffer gets
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 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
Re: Currval and buffer gets
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 > > >
RE: Currval and buffer gets
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 -- 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
Re: Currval and buffer gets
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-- (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
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 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
Re: Currval and buffer gets
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... stuff deleted = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml
Re: Currval and buffer gets
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 -- 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
RE: Currval and buffer gets
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 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/ --
Re: Currval and buffer gets
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 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
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 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
RE: Currval and buffer gets
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 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
Re: Currval and buffer gets
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/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
RE: Currval and buffer gets
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
RE: Currval and buffer gets
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://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
Re: Currval and buffer gets
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
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) 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
Re: Currval and buffer gets
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 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
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?
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
RE: Currval and buffer gets
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.
Then I generated a report and loaded it
into
oraperf.com.
In the report I saw that the two SQL
statements
that
=== message truncated ===
__
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: Lyubomir Petrov
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet
RE: Currval and buffer gets
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 PMwhat 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
PMHello
Yechiel, X$DUAL is an
Oracle-internal table "in the SGA" andwill not
be shown in an ALL_OBJECTS listing.Obviously, you need
to be SYS to see this. You can doa describe as
SYS and you will see it. Which is thereason 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
RE: Currval and buffer gets
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 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
RE: Currval and buffer gets
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
*** 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.
Cheers,
Gaja
--- Yechiel Adar [EMAIL PROTECTED] wrote:
Hello Gaja
I
RE: Currval and buffer gets
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
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
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.
RE: Currval and buffer gets
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
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;
RE: Currval and buffer gets
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
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
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
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
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
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 __ 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
Re: Currval and buffer gets
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
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 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
Currval and buffer gets
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).
Re: Currval and buffer gets
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).
Re: Currval and buffer gets
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).
