Raj,
Good point, they seem to have included some optimizations
for SYSDATE, but not for other functions. Your test
reproduced on 8174 and 9201 for me as well as your 9202, but
this test shows that the optimization only applys to SYSDATE
on 8174 and 9201...
--------------------- begin SQL*Plus output
---------------------
SQL> alter session set sql_trace=true;
Session altered.
SQL>
SQL> declare
2 a varchar2(30);
3 begin
4 a := user;
5 dbms_output.put_line(a);
6 end;
7 /
SYS
PL/SQL procedure successfully completed.
SQL> exit
--------------------- end SQL*Plus output
---------------------
--------------------- begin SQL trace output
---------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.1 , CPU type 586
Instance name: test92
Redo thread mounted by this instance: 1
Oracle process number: 12
Windows thread id: 976, image: ORACLE.EXE
*** 2002-12-26 12:58:05.000
*** SESSION ID:(10.5846) 2002-12-26 12:58:05.000
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0
tim=623992443526 hv=1197935484 ad='7826694c'
alter session set sql_trace=true
END OF STMT
EXEC
#1:c=0,e=135,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=62399244
3506
=====================
PARSING IN CURSOR #1 len=100 dep=0 uid=0 oct=47 lid=0
tim=623992489820 hv=3729705022 ad='7820df4c'
declare
a varchar2(30);
begin
a := user;
dbms_output.put_line(a);
end;
END OF STMT
PARSE
#1:c=0,e=526,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=62399248
9804
=====================
PARSING IN CURSOR #2 len=25 dep=1 uid=0 oct=3 lid=0
tim=623992494739 hv=1039632228 ad='782570e4'
SELECT user from sys.dual
END OF STMT
PARSE
#2:c=0,e=106,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=62399249
4721
EXEC
#2:c=0,e=109,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=62399249
6569
FETCH
#2:c=0,e=144,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=62399249
7259
EXEC
#1:c=10014,e=3905,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=623
992498082
=====================
PARSING IN CURSOR #3 len=52 dep=0 uid=0 oct=47 lid=0
tim=623992499721 hv=1697159799 ad='7824ebb0'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE
#3:c=0,e=444,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=62399249
9706
EXEC
#3:c=0,e=583,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=62399250
3589
XCTEND rlbk=0, rd_only=1
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL
DUAL '
--------------------- begin SQL trace output
---------------------
...they keep changing things! Thanks for the correction!
-Tim
>
> I thought the very same Tim ...
> But ...
>
> oraclei@elara-ABC1> sys
>
> SQL*Plus: Release 9.2.0.2.0 - Production on Thu Dec 26
> 13:14:58 2002
> Copyright (c) 1982, 2002, Oracle Corporation. All rights
> reserved.
> Connected.
> SQL> alter session set sql_trace=true;
>
> Session altered.
>
> SQL> declare
> 2 a date;
> 3 begin
> 4 dbms_output.enable(100000);
> 5 a := sysdate;
> 6 dbms_output.put_line(to_char(a, 'MM-DD-YYYY
> HH24:MI:SS'));
> 7 end;
> 8 /
>
> PL/SQL procedure successfully completed.
>
> SQL> exit
> Disconnected from Oracle9i Enterprise Edition Release
> 9.2.0.2.0 - 64bit Production
> With the Partitioning, Real Application Clusters, OLAP and
> Oracle Data Mining options
> JServer Release 9.2.0.2.0 - Production
> oraclei@elara-ABC1>
>
> and
>
> /var/opt/oracle/logs/ABC/abc1_ora_9511050.trc
> Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit
> Production With the Partitioning, Real Application
> Clusters, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.2.0 - Production
> ORACLE_HOME = /usr/opt/oracle/current
> System name: AIX
> Node name: elara
> Release: 1
> Version: 5
> Machine: 0023565A4C00
> Instance name: ABC1
> Redo thread mounted by this instance: 1
> Oracle process number: 30
> Unix process pid: 9511050, image: oracle@elara (TNS V1-V3)
>
> *** SESSION ID:(36.2802) 2002-12-26 13:15:08.044
> APPNAME mod='sqlplus@elara (TNS V1-V3)' mh=0 act='' ah=0
> =====================
> PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0
> tim=1016529793013758 hv=3943786303 ad='32ae5788'
> alter session set sql_trace=true
> END OF STMT
> EXEC
> #1:c=0,e=108,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=101652
> 9793011934 *** 2002-12-26 13:15:54.498
> =====================
> PARSING IN CURSOR #1 len=127 dep=0 uid=0 oct=47 lid=0
> tim=1016529838377159 hv=174346551 ad='301694f8'
> declare
> a date;
> begin
> dbms_output.enable(100000);
> a := sysdate;
> dbms_output.put_line(to_char(a, 'MM-DD-YYYY HH24:MI:SS'));
> end;
> END OF STMT
> PARSE
> #1:c=0,e=8986,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=10165
> 29838377153 EXEC
> #1:c=0,e=443,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=101652
> 9838377721 XCTEND rlbk=0, rd_only=1
>
> ???
> 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: Thursday, December 26, 2002 12:59 PM
> To: Multiple recipients of list ORACLE-L
>
>
> In PL/SQL, every statement which references SYSDATE or
> USER or USERENV or similar functions (such as your second
> suggestion) is automatically translated into an individual
> SELECT xxx FROM DUAL statement, behind the scenes.
>
> You don't have a choice. Call it a weakness of PL/SQL...
>
> [Attachment: ESPN_Disclaimer.txt]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
