Oracle-L: RE: Cache on sysdate? --From 9i performance planning manual

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 26 Dec 2002 10:28:45 -0800
Message-ID: <F001.005225AC.20021226102845@fatcity.com>

I thought the very same Tim ...
But ...

oraclei_at_elara-ABC1> sys

SQL*Plus: Release 9.2.0.2.0 - Production on Thu Dec 26 13:14:58 2002

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_at_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_at_elara (TNS V1-V3)

SESSION ID:(36.2802) 2002-12-26 13:15:08.044 APPNAME mod='sqlplus_at_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-----
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...

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.com

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

text/plain attachment: ESPN_Disclaimer.txt

Received on Thu Dec 26 2002 - 12:28:45 CST