RE: Currval and buffer gets

On HP-UX:

model           = 9000/800/N4000-55
OS release level= B.11.11
processor count = 8
clock speed     = 550 MHz
memory          = 16384 Mb

SQL> set serveroutput on size 10000;
SQL> @test.sql
 27 /
run time using view x_$dual in centiseconds=600 run time using table dual in centiseconds=700 run time using c interface in centiseconds=200  

PL/SQL procedure successfully completed.

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

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-----
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: Glenn Travis
  INET: Glenn.Travis_at_sas.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).