RE: Cache on sysdate? --From 9i performance planning manual
Title: RE: Cache on sysdate? --From 9i performance planning manual
Thanks
Raj. That's very cool. Now I can do:
SQL delete from dual;
1 row
deleted.
SQLdeclare 2 a
date :=sysdate; 3 begin 4
dbms_output.put_line(to_char(a,'MMDD HH24:MI:SS')); 5*
end;
20021227 05:36:54
PL/SQL
procedure successfully completed.
That
further proves it no longer uses "select sysdate into a from
dual;".
Richard Ji
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 26,
2002 5:14 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Cache on sysdate? --From 9i performance
planning manual
Richard,
If you look in the tracefile ... there is no select from
dual. It used to be like that but things changed (as Anjo mentions maybe
be around 8iR3). The sysdate call is now a C function call.
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:
Richard Ji [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 26, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: Cache on sysdate? --From 9i performance planning
manual
That's because doing dt := sysdate is more optimized, it's
still select sysdate into dt from dual but more
optimized. select from dual doing 4 db block
gets in 8i and 2 db block gets in 9i, but you can tune it to cut it
down.
Richard Ji
-Original Message- Sent:
Thursday, December 26, 2002 2:45 PM To: Multiple
recipients of list ORACLE-L
That's what I thought too. But the results of testing
are somewhat different. Maybe
it's evaluated within the PL/SQL engine and does not require a context
switch to the SQL engine.
Platform : Sun Solaris 2.6 Oracle : 8.1.7.4 (32 bit)
DEV:43#14739-23049@dual1 DEV:43#14739-23049declare
2 dt date; 3
begin 4
5 for i in
1..1
6 loop 7 select sysdate into dt from
dual; 8 -- dt :=
sysdate; 9 end loop;
10 end; 11
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.97
DEV:43#14739-23049@dual1 DEV:43#14739-23049declare
2 dt date; 3
begin 4
5 for i in
1..1
6 loop 7 -- select sysdate into dt from
dual; 8 dt :=
sysdate; 9 end loop;
10 end; 11
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.92
Regards, Denny
Quoting K Gopalakrishnan [EMAIL PROTECTED]:
Raj: Both are same. It is internally translated as a select call to
dual. KG
--- "Jamadagni, Rajendra"
[EMAIL PROTECTED] wrote:
Can someone please explain me why you have to use
select sysdate from dual
/ when
my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take
an explanation
Raj
= Have a nice
day !!
Best Regards, K
Gopalakrishnan, Bangalore, INDIA.
-- Please see the official ORACLE-L
FAQ: http://www.orafaq.net --
Author: INET:
[EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
-- Please see the official ORACLE-L
FAQ: http://www.orafaq.net --
Author: Richard Ji
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: Cache on sysdate? --From 9i performance planning manual
What you can do is like this...
1. At the startup of Application Server, do a SELECT sysdate FROM dual, store
the sysdate, and also get the system date of the application server, that way
you will know the difference between the App Server date and the DB server
date.
2. Everytime you want to use sysdate, get the system date of the application
server, apply the difference calculated in the step 1 and use it instead of
sysdate.
this will save a lot of 'block gets' at the DB level and also the network
round trips.
Regards
Naveen
-Original Message-
Sent: Thursday, December 26, 2002 6:49 PM
To: Multiple recipients of list ORACLE-L
Hi, list friends:
I am reading oracle 9.2 performance planning manual, and see it says:
Today's date. SELECT SYSDATE FROM DUAL can account for over 60% of the
workload on a database.
{page 33 of that manual}
How to understand that words? And does it mean to get the sysdate
from application server rather than the db server? How to do it in the sql
statement? anyone have examples?
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(ChinaOracle User Group)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: chao_ping
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: Cache on sysdate? --From 9i performance planning manual
Hi,
It would account for 100% of a database's workload or it could account
for 0.01% of it. Or maybe 42%, which is another good number.
This is of course nonsense taken as a statement by itself. Check out
Connor McDonald's article on Dual (and probably many others have written
about it, too) on www.OracleDBA.co.uk for alternatives (Jonathan Lewis
has done studies on this, too).
No, it has nothing to do with iAS or such. It simply means to minimize
the number of times you get the information and thereby reduce workload
on any system.
The road to scaling applications goes through the simple principle of
doing as little work as possible :-).
Best regards,
Mogens
chao_ping wrote:
Hi, list friends:
I am reading oracle 9.2 performance planning manual, and see it says:
Today's date. SELECT SYSDATE FROM DUAL can account for over 60% of the
workload on a database.
{page 33 of that manual}
How to understand that words? And does it mean to get the sysdate from application server rather than the db server? How to do it in the sql statement? anyone have examples?
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(ChinaOracle User Group)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: Cache on sysdate? --From 9i performance planning manual
You could use NTP to synchronouse the time on your app server and db server.
-Original Message-
Sent: Thursday, December 26, 2002 10:59 AM
To: Multiple recipients of list ORACLE-L
What you can do is like this...
1. At the startup of Application Server, do a SELECT sysdate FROM dual,
store
the sysdate, and also get the system date of the application server, that
way
you will know the difference between the App Server date and the DB server
date.
2. Everytime you want to use sysdate, get the system date of the application
server, apply the difference calculated in the step 1 and use it instead of
sysdate.
this will save a lot of 'block gets' at the DB level and also the network
round trips.
Regards
Naveen
-Original Message-
Sent: Thursday, December 26, 2002 6:49 PM
To: Multiple recipients of list ORACLE-L
Hi, list friends:
I am reading oracle 9.2 performance planning manual, and see it
says:
Today's date. SELECT SYSDATE FROM DUAL can account for over 60% of the
workload on a database.
{page 33 of that manual}
How to understand that words? And does it mean to get the sysdate
from application server rather than the db server? How to do it in the sql
statement? anyone have examples?
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(ChinaOracle User Group)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: chao_ping
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Richard Ji
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: Cache on sysdate? --From 9i performance planning manual
Title: RE: Cache on sysdate? --From 9i performance planning manual Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation 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! *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.*1
RE: Cache on sysdate? --From 9i performance planning manual
Raj: Both are same. It is internally translated as a select call to dual. KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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... Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj __ Rajendra JamadagniMIS, 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! [Attachment: ESPN_Disclaimer.txt] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Cache on sysdate? --From 9i performance planning manual
Title: 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 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(10); 5 a := sysdate; 6 dbms_output.put_line(to_char(a, 'MM-DD- 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(10); a := sysdate; dbms_output.put_line(to_char(a, 'MM-DD- 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
RE: Cache on sysdate? --From 9i performance planning manual
Actually this may not be true any more starting from Oracle 8.1.7. Many of the functions started to have a C language interface in 8.1.7. Check stdbody.sql to find which function has a C prgama. Regards, Waleed -Original Message- Sent: Thursday, December 26, 2002 1:24 PM To: Multiple recipients of list ORACLE-L Raj: Both are same. It is internally translated as a select call to dual. KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Cache on sysdate? --From 9i performance planning manual
Raj: SQL trace or the event 10046 is NOT the right to trace the PL/SQL executions. You need to either use the dbms package dbms_trace or event 10938 to populate the SYS table PLSQL_TRACE_EVENTs and select from that table. SQL_TRACE is just SQL Trace. NOT PL/SQL Trace.. Best Regards, K Gopalakrishnan Bangalore, INDIA --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: 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(10); 5 a := sysdate; 6 dbms_output.put_line(to_char(a, 'MM-DD- 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(10); a := sysdate; dbms_output.put_line(to_char(a, 'MM-DD- 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 JamadagniMIS, 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... 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Cache on sysdate? --From 9i performance planning manual
That's what I thought too. But the results of testing are somewhat different. Maybe it's evaluated within the PL/SQL engine and does not require a context switch to the SQL engine. Platform : Sun Solaris 2.6 Oracle : 8.1.7.4 (32 bit) DEV:43#14739-23049@dual1 DEV:43#14739-23049declare 2dt date; 3 begin 4 5 for i in 1..1 6 loop 7 select sysdate into dt from dual; 8 --dt := sysdate; 9end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.97 DEV:43#14739-23049@dual1 DEV:43#14739-23049declare 2dt date; 3 begin 4 5 for i in 1..1 6 loop 7 --select sysdate into dt from dual; 8 dt := sysdate; 9end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.92 Regards, Denny Quoting K Gopalakrishnan [EMAIL PROTECTED]: Raj: Both are same. It is internally translated as a select call to dual. KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Cache on sysdate? --From 9i performance planning manual
Following is from stdbody.sql. Now select sysdate from dual is used only when pessdt returns NULL. -- Bug 1287775: back to calling ICD. -- Special: if the ICD returns NULL, that means we should do the old -- 'SELECT SYSDATE FROM DUAL;' thing. This allows us to do the SELECT from -- PL/SQL rather than having to do it from C (within the ICD.) function sysdate return date is d date; begin d := pessdt; if (d IS NULL) then select sysdate into d from sys.dual; end if; return d; end; - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 26, 2002 11:03 AM Actually this may not be true any more starting from Oracle 8.1.7. Many of the functions started to have a C language interface in 8.1.7. Check stdbody.sql to find which function has a C prgama. Regards, Waleed -Original Message- Sent: Thursday, December 26, 2002 1:24 PM To: Multiple recipients of list ORACLE-L Raj: Both are same. It is internally translated as a select call to dual. KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Shaleen INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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... - begin SQL*Plus output - SQL alter session set sql_trace=true; Session altered. SQL SQL declare 2 a varchar2(30); 3 begin 4 a := user; 5 dbms_output.put_line(a); 6 end; 7 / SYS PL/SQL procedure successfully completed. SQL exit - end SQL*Plus output - - begin SQL trace output - Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Windows 2000 Version 5.1 , CPU type 586 Instance name: test92 Redo thread mounted by this instance: 1 Oracle process number: 12 Windows thread id: 976, image: ORACLE.EXE *** 2002-12-26 12:58:05.000 *** SESSION ID:(10.5846) 2002-12-26 12:58:05.000 = PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=623992443526 hv=1197935484 ad='7826694c' alter session set sql_trace=true END OF STMT EXEC #1:c=0,e=135,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=62399244 3506 = PARSING IN CURSOR #1 len=100 dep=0 uid=0 oct=47 lid=0 tim=623992489820 hv=3729705022 ad='7820df4c' declare a varchar2(30); begin a := user; dbms_output.put_line(a); end; END OF STMT PARSE #1:c=0,e=526,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=62399248 9804 = PARSING IN CURSOR #2 len=25 dep=1 uid=0 oct=3 lid=0 tim=623992494739 hv=1039632228 ad='782570e4' SELECT user from sys.dual END OF STMT PARSE #2:c=0,e=106,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=62399249 4721 EXEC #2:c=0,e=109,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=62399249 6569 FETCH #2:c=0,e=144,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=62399249 7259 EXEC #1:c=10014,e=3905,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=623 992498082 = PARSING IN CURSOR #3 len=52 dep=0 uid=0 oct=47 lid=0 tim=623992499721 hv=1697159799 ad='7824ebb0' BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; END OF STMT PARSE #3:c=0,e=444,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=62399249 9706 EXEC #3:c=0,e=583,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=62399250 3589 XCTEND rlbk=0, rd_only=1 STAT #2 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL ' - begin SQL trace output - ...they keep changing things! Thanks for the correction! -Tim 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(10); 5 a := sysdate; 6 dbms_output.put_line(to_char(a, 'MM-DD- 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=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(10); a := sysdate; dbms_output.put_line(to_char(a, 'MM-DD- 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 JamadagniMIS,
Re: Cache on sysdate? --From 9i performance planning manual
Used to, no longer ;-) Change happened in 8.1.7.2 or so. On Thursday 26 December 2002 10:23, you wrote: Raj: Both are same. It is internally translated as a select call to dual. KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Cache on sysdate? --From 9i performance planning manual
That's because doing dt := sysdate is more optimized, it's still select sysdate into dt from dual but more optimized. select from dual doing 4 db block gets in 8i and 2 db block gets in 9i, but you can tune it to cut it down. Richard Ji -Original Message- Sent: Thursday, December 26, 2002 2:45 PM To: Multiple recipients of list ORACLE-L That's what I thought too. But the results of testing are somewhat different. Maybe it's evaluated within the PL/SQL engine and does not require a context switch to the SQL engine. Platform : Sun Solaris 2.6 Oracle : 8.1.7.4 (32 bit) DEV:43#14739-23049@dual1 DEV:43#14739-23049declare 2dt date; 3 begin 4 5 for i in 1..1 6 loop 7 select sysdate into dt from dual; 8 --dt := sysdate; 9end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.97 DEV:43#14739-23049@dual1 DEV:43#14739-23049declare 2dt date; 3 begin 4 5 for i in 1..1 6 loop 7 --select sysdate into dt from dual; 8 dt := sysdate; 9end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.92 Regards, Denny Quoting K Gopalakrishnan [EMAIL PROTECTED]: Raj: Both are same. It is internally translated as a select call to dual. KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Cache on sysdate? --From 9i performance planning manual
Title: RE: Cache on sysdate? --From 9i performance planning manual Richard, If you look in the tracefile ... there is no select from dual. It used to be like that but things changed (as Anjo mentions maybe be around 8iR3). The sysdate call is now a C function call. 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: Richard Ji [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 26, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: Cache on sysdate? --From 9i performance planning manual That's because doing dt := sysdate is more optimized, it's still select sysdate into dt from dual but more optimized. select from dual doing 4 db block gets in 8i and 2 db block gets in 9i, but you can tune it to cut it down. Richard Ji -Original Message- Sent: Thursday, December 26, 2002 2:45 PM To: Multiple recipients of list ORACLE-L That's what I thought too. But the results of testing are somewhat different. Maybe it's evaluated within the PL/SQL engine and does not require a context switch to the SQL engine. Platform : Sun Solaris 2.6 Oracle : 8.1.7.4 (32 bit) DEV:43#14739-23049@dual1 DEV:43#14739-23049declare 2 dt date; 3 begin 4 5 for i in 1..1 6 loop 7 select sysdate into dt from dual; 8 -- dt := sysdate; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.97 DEV:43#14739-23049@dual1 DEV:43#14739-23049declare 2 dt date; 3 begin 4 5 for i in 1..1 6 loop 7 -- select sysdate into dt from dual; 8 dt := sysdate; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.92 Regards, Denny Quoting K Gopalakrishnan [EMAIL PROTECTED]: Raj: Both are same. It is internally translated as a select call to dual. KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). 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
RE: Cache on sysdate? --From 9i performance planning manual
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. Its 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 79 Knoxville - Steve Adams's Miracle Master Class, Jan 1315 Copenhagen - 2003 Hotsos Symposium on Oracle System Performance, Feb 912 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 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(10); 5 a := sysdate; 6 dbms_output.put_line(to_char(a, 'MM-DD- 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(10); a := sysdate; dbms_output.put_line(to_char(a, 'MM-DD- 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
It is 8.1.7.4, it doesn't do the select sysdate from dual anymore for dt:=sysdate; Anjo. On Thursday 26 December 2002 12:59, you wrote: That's because doing dt := sysdate is more optimized, it's still select sysdate into dt from dual but more optimized. select from dual doing 4 db block gets in 8i and 2 db block gets in 9i, but you can tune it to cut it down. Richard Ji -Original Message- Sent: Thursday, December 26, 2002 2:45 PM To: Multiple recipients of list ORACLE-L That's what I thought too. But the results of testing are somewhat different. Maybe it's evaluated within the PL/SQL engine and does not require a context switch to the SQL engine. Platform : Sun Solaris 2.6 Oracle : 8.1.7.4 (32 bit) DEV:43#14739-23049@dual1 DEV:43#14739-23049declare 2dt date; 3 begin 4 5 for i in 1..1 6 loop 7 select sysdate into dt from dual; 8 --dt := sysdate; 9end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.97 DEV:43#14739-23049@dual1 DEV:43#14739-23049declare 2dt date; 3 begin 4 5 for i in 1..1 6 loop 7 --select sysdate into dt from dual; 8 dt := sysdate; 9end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.92 Regards, Denny Quoting K Gopalakrishnan [EMAIL PROTECTED]: Raj: Both are same. It is internally translated as a select call to dual. KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
