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

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 26 Dec 2002 18:43:59 -0800
Message-ID: <F001.00522C49.20021226184359@fatcity.com>

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 <http://www.hotsos.com/training/clinic101> , Jan 7-9 Knoxville
- Steve Adams's <http://miracleas.dk/events/MMC2003/invitation.html> Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium <http://www.hotsos.com/events/symposium> on OracleR System Performance, Feb 9-12 Dallas

-----Original Message-----
Rajendra
Sent: Thursday, December 26, 2002 12:29 PM To: Multiple recipients of list ORACLE-L

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: Cary Millsap
  INET: cary.millsap_at_hotsos.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 - 20:43:59 CST