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

Cary Millsap Thu, 26 Dec 2002 19:11:11 -0800

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

The Oracle kernel tallies time to this event when an application that is linked single-task spends time between database calls. It’s usually time spent awaiting either user input or application processing.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101, Jan 7–9 Knoxville
- Steve Adams's Miracle Master Class, Jan 13–15 Copenhagen
- 2003 Hotsos Symposium on Oracle^® System Performance, Feb 9–12 Dallas

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Jamadagni, Rajendra
Sent: Thursday, December 26, 2002 12:29 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Cache on sysdate? --From 9i performance planning manual

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

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

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

Reply via email to