| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cache on sysdate? --From 9i performance planning manual
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...
Session altered.
SQL>
SQL> declare
2 a varchar2(30);
3 begin
4 a := user; 5 dbms_output.put_line(a);
SYS
PL/SQL procedure successfully completed.
SQL> exit
--------------------- end SQL*Plus output --------------------- --------------------- begin SQL trace output ---------------------
Redo thread mounted by this instance: 1
Oracle process number: 12
Windows thread id: 976, image: ORACLE.EXE
...they keep changing things! Thanks for the correction!
-Tim
>
> 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
> 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_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=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: tim_at_sagelogix.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).Received on Thu Dec 26 2002 - 14:04:23 CST
 |
 |