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=1016529793011934
*** 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=1016529838377153
EXEC #1:c=0,e=443,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=1016529838377721
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-----
From: Tim Gorman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 26, 2002 12:59 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Cache on sysdate? --From 9i performance planning manual
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...
********************************************************************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 corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
