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..100000 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..100000 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 <ORACLE-L_at_fatcity.com>
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: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).