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.000000000000000000000000000000000004

run time using table dual in

centiseconds=2999.999999999999999999999999999999999998

run time using c interface in

centiseconds=1100.000000000000000000000000000000000002

 using ths code:  

declare
nn date;
ss1 date;
ss2 date;
begin
ss1 := sysdate;
for i in 1..100000 loop
select 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..100000 loop
select 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..100000 loop
nn := 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-----
[mailto:Alexander.Feinstein_at_mitchell1.com]
Sent: Tuesday, April 23, 2002 10:23 PM
To: Multiple recipients of list ORACLE-L



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..100000 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..100000 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..100000 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. 






-- 

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).