Tom

If we have to use sysdate numerous number of times in our program, how do you suggest we do it.

Why is it expensive to select it from dual, every time we need it.

Thank you

1) Replace DUAL with an IOT or a view on X$DUAL - you should get approval from Oracle support before doing this. This makes the select-from-dual slightly faster.

2) See </code> http://www.oracledba.co.uk/tips/quick_date.htm <code>for a "sysdate" that does not use the SQL engine

3) Probably most effective is to have a look at why you are getting sysdate so frequently.

Not to be a contrarian HOWEVER (i'm going to be...)

1) you'll never get that approval.  DUAL is too embedded all over the place.  Strange things could happen (no, i have no examples -- just past experience tells me making a seemingly innocent change like this can lead to all kinds of bad things).

2) that sysdate, in addition to having the wrap problem every 248 days (you don't need a LONG session, you just need to start your appliation on that 248'th day -- once a year, you have a terrible BUG in your application as the number will wrap and your dates will be WAY..... off.  Thats like having a Y2k bug once or twice a year every year for the rest of your life!) has a problem in that the dbms_utility.get_time clock is NOT syncronized with the system clock.

Hence, using the sysda.te function we'll find:

ops$tkyte@ORA717DEV.US.ORACLE.COM> begin
  2          for i in 1 .. 10000
  3          loop
  4                  if ( sysdate <> sysda.te )
  5                  then
  6                          dbms_output.put_line( 'found one ' || i );
  7                          exit;
  8                  end if;
  9      end loop;
 10  end;
 11  /
found one 3479

PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> /
found one 4077

PL/SQL procedure successfully completed.


The sysda.te and sysdate function will differ by upto 1 second from eachother as the get_time function rolls seconds on a different beat then sysdate does.  This could cause issues for some applications that rely on sysdate increasing (if they use a mix of sysda.te and sysdate as most will)

3) best answser. I ran the tests at the above referenced page and found:

a) sysdate: 12.14 seconds
b) sysda.te: 10.74 seconds
c) NO TRIGGER, use insert into stamp (x,date_col) select rownum, sysdate.... 
   <b>0.64 seconds</b>

If performance were the overreaching goal here, you would not have SQL call PLSQL in the form of a trigger to do something that is more easily done in the SQL in the first place. 

Tom,

What is the theory behind the following occurrance
SQL> insert into dual select * from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

  COUNT(*)
----------
     2

Thanks 

In 8.1.7

SQL> select * from dual;

Statistics
----------------------------------------------------------
          4  db block gets
          1  consistent gets

In 9.0.1

SQL> select * from dual;

Statistics
----------------------------------------------------------
          2  db block gets
          1  consistent gets

Always nice to see those good folks at Oracle doing things for us :-) 

Tom,

To see if there's a performance difference, I ran these pl/sqls:

declare
t_date date;
t_n number;
begin
t_n := dbms_utility.get_time;
for i in 1..10000
loop
select sysdate into t_date from dual;
end loop;
dbms_output.put_line(dbms_utility.get_time - t_n);
end;

(Output between 73 and 81)

call count cpu elapsed
------- ------ -------- ----------
Parse 1 0.00 0.01
Execute 1 1.07 1.30
Fetch 0 0.00 0.00
------- ------ -------- ----------
total 2 1.07 1.31

declare
t_date date;
t_n number;
begin
t_n := dbms_utility.get_time;
--
for i in 1..10000
loop
t_date := sysdate;
end loop;
dbms_output.put_line(dbms_utility.get_time - t_n);
--
end;

(Output between 21 and 24)

call count cpu elapsed disk
------- ------ -------- ---------- ----------
Parse 1 0.00 0.00 0
Execute 1 0.22 0.21 0
Fetch 0 0.00 0.00 0
------- ------ -------- ---------- ----------
total 2 0.22 0.21 0


Using "t_date := sysdate;" is consistently faster. And I don't see the "SELECT SYSDATE FROM DUAL" in the tkprof output. What exactly is Oracle doing?

It seems that starting from 8.1.7.4, assigning sysdate to
a variable no longer does a "select sysdate into ...".
In stdbody.sql, sysdate will only do "select sysdate into.."
if function pessdt returns null. And that's a C call.

Hi,

This is Som from India.
I was just going through the Standard Package provided by Oracle.
The last function is quit interesting and not understandable to me.
Could you please explain this to me, by giving an example?
The function is :

FUNCTION || RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LEFT VARCHAR2 IN
RIGHT VARCHAR2 IN

I will be waiting for your positive answer.
Thanks a lot.

If || were a "standard" function call, we would use it like:

x := ||(a,b);

but a||b doesn't look much like a function.

1) Why is it declared in Standard at all - doesn't the parser know what to do with it without reference to a package?

2) Or is a||b some obscure valid syntax for a function?

Thanks
Jon

|| is a function just like +, -, *, etc. etc.

Take a closer look at standard. They are all there.

In fact some languages (C++ and Ada to name two) will let you overload these operators. For example if you have defined an object of widgets and you want to be able to add them.

Oracle sysdate is completely dependent on the operating system date. They are the same in value. Tested with Oracle 9i on linux: if setting the OS date using "date -s" to an arbitrary date, the oracle sysdate will have that value, and ticking alone with it...

A bit of a long-winded example to prove that getting the date from your own table is consistently much faster than getting it from dual.

SQL>  create table mydual  (c1 number)  storage (initial 8192) pctfree 0 pctused 99;

Table created.

SQL> insert into mydual values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table mydual add constraint pk_mydual primary key (c1) using index tablespace user_indexes;

Table altered.

SQL> analyze  table mydual compute statistics;

Table analyzed.

SQL> set autot on
SQL> select sysdate from dual;

SYSDATE
---------
12-APR-03


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'


Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        207  bytes sent via SQL*Net to client
        122  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select sysdate from mydual;

SYSDATE
---------
12-APR-03


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   INDEX (FULL SCAN) OF 'PK_MYDUAL' (UNIQUE) (Cost=1 Card=1)


Statistics
----------------------------------------------------------
         39  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        207  bytes sent via SQL*Net to client
        124  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

(Plenty of recursive calls first time)

SQL> /

SYSDATE
---------
12-APR-03


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   INDEX (FULL SCAN) OF 'PK_MYDUAL' (UNIQUE) (Cost=1 Card=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        207  bytes sent via SQL*Net to client
        124  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

(No recursive calls or block gets)
SQL> /

SYSDATE
---------
12-APR-03


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   INDEX (FULL SCAN) OF 'PK_MYDUAL' (UNIQUE) (Cost=1 Card=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        207  bytes sent via SQL*Net to client
        124  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set timing on
SQL>  declare a date;
  2     begin
  3     for i in 1..100000  loop
  4    select sysdate into a from mydual;
  5   end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.08
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.08
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.09
SQL>  declare a date;
  2     begin
  3     for i in 1..100000  loop
  4    select sysdate into a from dual;
  5    end loop;
  6   end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.05
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.00
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.05
SQL> 

mydual is always faster - much.
 

Tom,

If I have the following SQL:
SELECT to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), ... FROM my_view a WHERE a.val = :1

Suppose this returns many rows and takes several minutes to run. Will the sysdate value selected be the same for every row? My tests indicates it does return the same value. I just want to find out if it is documented behavior.

Thank you very much.

Tom
I could not understand what Connor wanted to convey here. Please explain

you don't say which item -- he said 2 things, assuming you meant:


In 8.1.7

SQL> select * from dual;

Statistics
----------------------------------------------------------
          4  db block gets
          1  consistent gets

In 9.0.1

SQL> select * from dual;

Statistics
----------------------------------------------------------
          2  db block gets
          1  consistent gets

Always nice to see those good folks at Oracle doing things for us :-) 


....

Look at the db block gets -- they went down.  In fact, on my machines

817, 4 db block gets, 1 consistent gets (5 logical IO's)
901, 0 db block gets, 3 consistent gets (3 logical IO's)

meaning a significant reduction in the amount of work it takes to process dual


 

I do not know if this question fits in here, but how can I get the current time from the client machine through a web form?

SYSDATE gives the current date/time from the database. What gies the current date/time from the client?

Thanks!

> I do not know if this question fits in here, but how can I
> get the current time
> from the client machine through a web form?

A JavaBean will do this for you. You can download the web util package from otn. It contains heaps of examples.

hi tom
i want to ask you a simple question from your view but a bit difficult from mine
we use one PLSQL code here
declare
sysdate date ;
begin
..
..
end;
it works fine
that means we can define sysdate as a name of a variable in PLSQL block
where as 
SQL> create table te(sysdate number);
create table te(sysdate number)
                *
ERROR at line 1:
ORA-00904: : invalid identifier
and we cant define sysdate as column name of table name
why this diffrence is there
 

Hi Tom,
Faced a problem quite similar to "sysdate" variable declaration here. One table having column name "CURRENT_DATE" started giving problem under 9i (table was probably migrated from 8i to 9i). Had to rename that column. A select from that column would always return system date rather than data from table.
But this is not the output in case of user defined functions.

What is the preference given for table columns, sql and user defined functions?

Since a column name may match a supplied function with some future release, what are the precautions we can take?

Thanks

Tom,

can you please tell us how different is this new current_date from sysdate?

I don't know if it is possible to alter the value of sysdate only for a session, in other words how can I trick the dates of a session without altering the server system date?

Thanks in advance.

Hi
you have this package

create package my_pkg
as
g_sysdate date default sysdate;
....
end;
/

and use g_sysdate everywhere so the date/timestamp we use is ALWAYS the same for all inserts/updates and such. So, rather then:


I dont understand why the sysdate is always the same because even you use this in a package everytime you call

my_pkg.g_sysdate doesnt this cause a function call (sysdate) everytime you run it? how can this be a constant value?

Apparently, current_date rounds whereas sysdate truncs the seconds(Oracle 9.2.0.5)

select 'sysdate: ' || sysdate from dual union
select 'current_date: ' || current_date from dual union
select 'systimestamp: ' || systimestamp from dual union
select '(sysdate - current_date)*24*3600: ' || (sysdate - current_date)*24*3600 from dual;

Looks fine when below 0.5 seconds:
(sysdate - current_date)*24*3600: 0
current_date: 2006-05-26 07:18:51
sysdate: 2006-05-26 07:18:51
systimestamp: 26-MAY-06 07.18.51.460000 AM +08:00

However, when we pass 0.5 seconds, the difference is 1 second between them.
(sysdate - current_date)*24*3600: -1
current_date: 2006-05-26 07:19:11
sysdate: 2006-05-26 07:19:10
systimestamp: 26-MAY-06 07.19.10.898000 AM +08:00

I have a follow-query I hope is relevant to this and can't find you covering this elsewhere.
If I want to maintain created and updated date on a table, is it OK to use default values for both on creation?

CREATE TABLE defaulttable (created DATE DEFAULT SYSDATE, updated DATE DEFAULT SYSDATE, dummy VARCHAR(1));
CREATE OR REPLACE TRIGGER defaulttable_tg BEFORE UPDATE
ON defaulttable
FOR EACH ROW
BEGIN
:NEW.updated := SYSDATE;
END;

INSERT INTO defaulttable (dummy) values (1);
SELECT count(*) FROM defaulttable where created != updated;

Or is it possible that sometimes created and updated will not be the same? If so I have to use something like the following:

CREATE TABLE triggertable (created DATE DEFAULT SYSDATE, updated DATE);

CREATE OR REPLACE TRIGGER triggertable_tg BEFORE INSERT OR UPDATE
ON triggertable
FOR EACH ROW
BEGIN
IF INSERTING
THEN
:NEW.updated := :NEW.created;
ELSIF UPDATING
THEN
:NEW.updated := SYSDATE;
END IF;
END;

I find the default values neater, and it seems to work when testing, but I am worried that it will not always work.

Sorry, but no idea why that should be true. Is this just from observation, or is this guaranteed to be the case? Is this documented anywhere?

Hi, Tom

I recently participated in a code walkthrough of a sql statement that contained a where clause like this:

nvl(my_date_column,sysdate) >= sysdate

I expressed concern that the sysdates might not be equal in extremely rare cases. Having read through this entire thread, I am reasonably sure that my concern is unfounded, but I just want to clarify it.

My understanding now is that the sysdate value will always be the same within a single sql statement, regardless of how complex or long-running that sql statement is. Is this a fundamental part of the way Oracle works and is guaranteed to be true now and in all foreseeable future releases?

I know that 10g made significant changes to the way that special columns like sysdate and user are handled. Did this have any effect on the consistency of the sysdate value?

Also, I assume that this is not true within pl/sql. If you have an insert using sysdate inside a cursor, the dates would change, but an "insert ... select ..., sysdate, ... from ..." would be guaranteed to be all the same.

Thanks for providing this very valuable service to us for so many years and thanks in advance for your response.

Thanks for the clarification, Tom.

About 3 posts before this in the trigger question, you responded that the default values (in the create table statement) would be consistent. Does that imply it would not be consistent for the assignment in the body of the trigger? I would assume that value would be independently generated. Is that right?

Similarly, for functions called within the sql statement, are the sysdate, etc. values independently generated in the assignments and sql statements in the function?

Or is the "bound" value carried forward into all functions and triggers initiated by the sql statement?

Thanks again for your help.

if you do:

  :new.d1 := sysdate;
  :new.d2 := sysdate;


d1 and d2 could be "different"


if you PASS sysdate to the function called from SQL, it'll remain constant.  If your function REFERENCES sysdate itself, it'll change:


ops$tkyte@ORA10GR2> create or replace context my_ctx1 using f1
  2  /

Context created.

ops$tkyte@ORA10GR2> create or replace context my_ctx2 using f2
  2  /

Context created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace function f1( p_date in date ) return date
  2  as
  3  begin
  4          dbms_session.set_context( 'my_ctx1', 'cnt', nvl(sys_context( 'my_ctx1', 'cnt' ),0) +1 );
  5          dbms_lock.sleep( 0.25 );
  6          return p_date;
  7  end;
  8  /

Function created.

ops$tkyte@ORA10GR2> create or replace function f2( p_date in date ) return date
  2  as
  3  begin
  4          dbms_session.set_context( 'my_ctx2', 'cnt', nvl(sys_context( 'my_ctx2', 'cnt' ),0) +1 );
  5          dbms_lock.sleep( 0.25 );
  6          return SYSDATE;
  7  end;
  8  /

Function created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select f1( sysdate ), f2( sysdate )
  2    from all_objects
  3   where rownum <= 5;

F1(SYSDATE)          F2(SYSDATE)
-------------------- --------------------
24-jun-2006 11:30:43 24-jun-2006 11:30:44
24-jun-2006 11:30:43 24-jun-2006 11:30:44
24-jun-2006 11:30:43 24-jun-2006 11:30:45
24-jun-2006 11:30:43 24-jun-2006 11:30:45
24-jun-2006 11:30:43 24-jun-2006 11:30:46

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select sys_context( 'my_ctx1', 'cnt' ),
  2         sys_context( 'my_ctx1', 'cnt' )
  3    from dual;

SYS_CONTEXT('MY_CTX1','CNT')
-------------------------------------------------------------------------------
SYS_CONTEXT('MY_CTX1','CNT')
-------------------------------------------------------------------------------
5
5


 

in regards to getting sysdate/systimestamp and others documented as to how they behave when referenced multiple times in a sql statement...

in the comment above.

Thanks, Tom.

That makes it very clear. My concern about the sysdates not matching is definitely not necessary.

Thanks for taking the time to explain it.

what is the difference between current_date and sysdate ?

Sometimes there is a difference of 1 second between these 2

ops$tkyte%ORA10GR2> select current_date, sysdate, systimestamp from dual;

CURRENT_DATE         SYSDATE
-------------------- --------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
19-jan-2008 22:42:43 19-jan-2008 22:42:42
19-JAN-08 10.42.42.727207 PM -05:00

thanks

Then what is the difference between current_date and sysdate ?

If they are the same, why have both instead of just one ?

ops$tkyte%ORA10GR2> select sysdate, current_date from dual;

SYSDATE              CURRENT_DATE
-------------------- --------------------
23-jan-2008 14:23:32 23-jan-2008 14:23:32


ops$tkyte%ORA10GR2> alter session set time_zone = 'PST';

Session altered.

ops$tkyte%ORA10GR2> select sysdate, current_date from dual;

SYSDATE              CURRENT_DATE
-------------------- --------------------
23-jan-2008 14:23:46 23-jan-2008 11:23:47

should have checked docs first

Hi Tom,

When selecting the date value which was inserted using the current_date function , I am getting a no rows found message. But when the session time zone is changed to dbtimezone, it works fine. Is it because the current_date has the session timezone information ? But the date column will not store the timzone information, right? It would be great if you could explain the usage of current_date. In our application the records are inserted using current_date rather than sysdate.


SQL> create table dt (cu_dt DATE);

Table created.

SQL> alter session set nls_date_format='DD-MON-YYYY HH:MI:SS';

Session altered.

SQL> insert into dt values(current_date);

1 row created.

SQL> select * from dt;

CU_DT
--------------------
15-NOV-2008 06:59:41

SQL> select * from dt where cu_dt=to_date('15-NOV-2008 06:59:41','DD-MON-YYYY HH:MI:SS');

no rows selected

SQL> insert into dt values(sysdate);

1 row created.

SQL> select * from dt;

CU_DT
--------------------
15-NOV-2008 06:59:41
15-NOV-2008 08:30:44

SQL> select * from dt where cu_dt=to_date('15-NOV-2008 08:30:44','DD-MON-YYYY HH:MI:SS');

CU_DT
--------------------
15-NOV-2008 08:30:44

SQL> select dbtimezone from dual;

DBTIME
------
-04:00

SQL> alter session set time_zone='-04:00';

Session altered.

SQL> insert into dt values(current_date);

1 row created.

SQL> select * from dt;

CU_DT
--------------------
15-NOV-2008 06:59:41
15-NOV-2008 08:30:44
15-NOV-2008 09:32:51

SQL> select * from dt where cu_dt=to_date('15-NOV-2008 09:32:51','DD-MON-YYYY HH:MI:SS');

CU_DT
--------------------
15-NOV-2008 09:32:51

ops$tkyte%ORA10GR2> create table t ( x date, y date );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( current_date, sysdate );

1 row created.

ops$tkyte%ORA10GR2> select t.*, dump(x), dump(y) from t;

X                    Y
-------------------- --------------------
DUMP(X)
-------------------------------------------------------------------------------
DUMP(Y)
-------------------------------------------------------------------------------
18-nov-2008 17:26:47 18-nov-2008 17:26:47
Typ=12 Len=7: 120,108,11,18,18,27,48
Typ=12 Len=7: 120,108,11,18,18,27,48


ops$tkyte%ORA10GR2> @bigdate
ops$tkyte%ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte%ORA10GR2> column x_str new_val X
ops$tkyte%ORA10GR2> select x x_str from t;

X_STR
--------------------
18-nov-2008 17:26:47

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t where x = to_date( '&X', 'dd-mon-yyyy hh24:mi:ss' );
old   1: select * from t where x = to_date( '&X', 'dd-mon-yyyy hh24:mi:ss' )
new   1: select * from t where x = to_date( '18-nov-2008 17:26:47', 'dd-mon-yyyy hh24:mi:ss' )

X                    Y
-------------------- --------------------
18-nov-2008 17:26:47 18-nov-2008 17:26:47

Thanks Tom,

I have executed the steps you mentioned and it gives the expected result. But when I changed the nls_date_format to 12hr format, I am not getting the result. 


SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

SQL> create table t ( x date, y date );

Table created.

SQL> insert into t values ( current_date, sysdate );

1 row created.

SQL> select t.*, dump(x), dump(y) from t;

X         Y
--------- ---------
DUMP(X)
--------------------------------------------------------------------------------
DUMP(Y)
--------------------------------------------------------------------------------
21-NOV-08 21-NOV-08
Typ=12 Len=7: 120,108,11,21,20,24,51
Typ=12 Len=7: 120,108,11,21,9,54,51


SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> column x_str new_val X
SQL> select x x_str from t;

X_STR
--------------------
21-nov-2008 19:23:50

SQL> select * from t where x = to_date( '&X', 'dd-mon-yyyy hh24:mi:ss' );
old   1: select * from t where x = to_date( '&X', 'dd-mon-yyyy hh24:mi:ss' )
new   1: select * from t where x = to_date( '21-nov-2008 19:23:50', 'dd-mon-yyyy hh24:mi:ss' )

X                    Y
-------------------- --------------------
21-nov-2008 19:23:50 21-nov-2008 08:53:50

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh:mi:ss';

Session altered.

SQL> select x x_str from t;

X_STR
--------------------
21-nov-2008 07:23:50

SQL> select * from t where x = to_date( '&X', 'dd-mon-yyyy hh:mi:ss' );
old   1: select * from t where x = to_date( '&X', 'dd-mon-yyyy hh:mi:ss' )
new   1: select * from t where x = to_date( '21-nov-2008 07:23:50', 'dd-mon-yyyy hh:mi:ss' )

no rows selected

I solved the problems in our application using the bind variables. (As you always pointing out). We were having a dynamic query using date as paramenters. I changed it with the "USING" clause and it working fine.

Thanks again. 

ops$tkyte%ORA10GR2> create table t ( id number, x date );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 1, to_date( '01-jan-2008 19:20:30', 'dd-mon-yyyy hh24:mi:ss' ) );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 2, to_date( '01-jan-2008 07:20:30', 'dd-mon-yyyy hh24:mi:ss' ) );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select id, to_char( x, 'hh24:mi:ss' ), to_char( x, 'hh:mi:ss' ) from t;

        ID TO_CHAR( TO_CHAR(
---------- -------- --------
         1 19:20:30 07:20:30
         2 07:20:30 07:20:30

Thanks Tom. :)

Hi Tom,

While firing queries like say as below.
select * from ORDERS where created_date like SYSDATE;

-- NLS DATE FORMAT is in DD/MM/YYYY HH24:MI:SS
I am getting records. even if the created_date time doesnot match with the sysdate. Its doing a trunc on both sided.

Am i correct in assuming this.

ops$tkyte%ORA10GR2> create table t ( x date );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( sysdate );

1 row created.

ops$tkyte%ORA10GR2> exec dbms_lock.sleep(2)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR

ops$tkyte%ORA10GR2> select x, sysdate from t where x like sysdate;

X         SYSDATE
--------- ---------
19-AUG-10 19-AUG-10

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

Session altered.

ops$tkyte%ORA10GR2> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD/MM/YYYY HH24:MI:SS

ops$tkyte%ORA10GR2> select x, sysdate from t where x like sysdate;

no rows selected

create a function to calculate sal by passing empno increment due date as paramater
1 year -- no sal increment,
2 year -- 10% increment,
3 year -- 20% increment,
4 year -- 30% increment.

Hi Tom, thanks for all your support until here! =D

So, after all of this talk, I still have a question.

We have an application with a PL/SQL with many sysdate statements as below:

--> dbms_output.put_line(to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'));

--> v_date date := sysdate

--> insert into table xxx values (...,...,sysdate)

and so on...

And when I execute trace in the session that is running this PL/SQL I see many "select sysdate from dual" with the same sql_id, and also after tkprof too. However,in the code, there isn´t this statement, so, My question is:

When I issue only sysdate inside the database, is it executed "select sysdate from dual"? If not, please, could you please clarify what oracle database really do?

Thanks in advance!
Regards,
GMartins.

my conjecture is ( cannot verify nor falsify them ):
it wasn't true for releases >= 7.2

good or bad estimation ?

Actually I´m using Oracle 10.2.0.3 on HP-UX Itanium.
And the strange thing as I said, is that there isn´t any "select sysdate from dual" inside de code, but I still get this statement on trace/tkprof.

So, I´m confused now... could you please clarify something?

Thanks in advance!
Regards,
GMartins.

ops$tkyte%ORA10GR2> create table t ( x int, y date );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA10GR2> declare
  2          v_date date := sysdate;
  3  begin
  4          dbms_output.put_line(to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'));
  5          dbms_output.put_line(to_char(v_date,'dd/mm/yyyy hh24:mi:ss'));
  6          insert into t (x,y) values (100,sysdate);
  7  end;
  8  /
26/06/2012 09:08:19
26/06/2012 09:08:19

PL/SQL procedure successfully completed.

Tom, yes, that´s a little strange, because I can´t reproduce too. I will investigate Forms to see if there is some kind of this statement coming from there.

Continuing this issue, to make more strange that is... When I see the event of the session which is executing "select sysdate from dual" I got "TCP Socket (KGAS)". So, as I know, this event is when you´re taking some data from another database/server, right? And how can this occur with just a "select sysdate from dual".

When I query v$sqlarea with hash_value I got the folowing:
=================================================================
ROWS_PROCESSED SQL_TEXT DISK_READS BUFFER_GETS EXECUTIONS
-------------- ----------------------------- ---------- ----------- ----------
4806902 SELECT SYSDATE FROM SYS.DUAL 0 0 4806908
=================================================================

And the session has LAST_CALL_ET more then 45 minutos executing just "select sysdate from dual". It seems to be in a loop, by as I told you, there isn´t any statement on PL/SQL code. Did you have any idea what is going on?

Thankyou very much for your attention and help!
GMartins.

You´re totally right about "TCP Socket (KGAS)"! Talking with some analysts, I discovered that there was a change on Web Services. But the issue about sysdate still.

Why there´s lots of "sysdate from dual" when I trace the session? Is it caused by "sysdate" function call, some resource from Forms, ... I´m lost with that case... =/

Thanks in advance!

Yes, I will investigate this case with Forms Application Analyst! Thanks for advice, and I really think this is the case!

Answering you the question before, look what i've got!

=============================================================
    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   OPS$SSBESK     latch free                                   1542467439 gn7q8hjdz0bvg


    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   OPS$SSBESK     latch free                                   1542467439 gn7q8hjdz0bvg


    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   xxxxxxx        latch: row cache objects                     1542467439 gn7q8hjdz0bvg


    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   xxxxxxx        db file sequential read                      1542467439 gn7q8hjdz0bvg


    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   xxxxxxx        buffer busy waits                            1542467439 gn7q8hjdz0bvg


    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   xxxxxxx        latch: library cache lock                    1542467439 gn7q8hjdz0bvg


    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   xxxxxxx        log buffer space                             1542467439 gn7q8hjdz0bvg


    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   xxxxxxx        latch: session allocation                    1542467439 gn7q8hjdz0bvg

    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   xxxxxxx        latch: cache buffers lru chain               1542467439 gn7q8hjdz0bvg

    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- -------------
   3850   47337 7814         ACTIVE   xxxxxxx        PL/SQL lock timer                            2767397235 3zvryskkg68bm

15:54:45 system@DB> @hash 2767397235

ROWS_PROCESSED SQL_TEXT                                                          DISK_READS BUFFER_GETS EXECUTIONS
-------------- ----------------------------------------------------------------- ---------- ----------- ----------
         78158 SELECT USER  FROM SYS.DUAL                                                 0           0      78158

15:55:31 system@DB> select program_id,program_line# from v$sql where sql_id='gn7q8hjdz0bvg';

PROGRAM_ID PROGRAM_LINE#
---------- -------------
         0             2
         0             2

2 rows selected.


    SID SERIAL# SPID         STATUS   USERNAME       EVENT                                    SQL_HASH_VALUE SQL_ID        LAST_CALL_MIN
------- ------- ------------ -------- -------------- ---------------------------------------- -------------- ------------- -------------
   3850   47337 7814         ACTIVE   xxxxxxx        TCP Socket (KGAS)                            1272729223 5pjx5pj5xsmn7             2

1 row selected.

===========================================================================================

16:19:19 system@DB> @hash 1272729223

ROWS_PROCESSED SQL_TEXT                                                          DISK_READS BUFFER_GETS EXECUTIONS
-------------- ----------------------------------------------------------------- ---------- ----------- ----------
       5284719 SELECT SYSDATE  FROM SYS.DUAL                                              0           0    5284725

1 row selected.

16:19:28 system@DB> select program_id,program_line# from v$sql where sql_id='5pjx5pj5xsmn7';

PROGRAM_ID PROGRAM_LINE#
---------- -------------
         0             2
         0             2

2 rows selected.

=============================================================

How can the same SQL be waiting for so many different events during the same session with "select sysdate from dual" statement? And as you can see, the are two hash_value and sql_id for it. =/

Hmm that´s interesting! So, is it possible where I query

FROM v$session s, v$process p, v$session_wait sw
WHERE s.paddr = p.addr
AND s.sid = sw.sid

Let me know if a understood.
I got s.sql_hash_value and s.sql_id in a moment and sw.event in another moment in the same query because there is no read consistence?

That´s why I can see many different events from v$session_wait and the same hash_value and sql_id from v$session?!

One more time, i'm very thankful for all attention and help!

All of this information is amazing!

To conclude, I only can think now... that there´s something else executing so fast, but so fast that I can´t got it in executing time, and it´s causing latch, sequential read and so on.

But I´m happy now because I understood the scenario and another Oracle Engineer answer me that:

"A code that contains "to_char(***sysdate***)" could generate behind something like "select sysdate from dual;" .

Which probably is my case when I started to post here.

Thank you guys and Thank you Tom for this great and helpful Job!

Thanks / Regards
GMartins.

One place to search in Forms for possible frequent "SELECT sysdate FROM dual" statements: if the "Initial Value" property of a Forms Item is set to "$$DBDATE$$", there is a good chance Forms sends such a SELECT to the database each time a new record is created in that "Forms Data Block" (I haven't checked though, I don't have Forms installed right now).

I understand that you cannot change sysdate for a session, only for the db. I was wondering exactly how foolish it would be to wrap sysdate so that you could provide a different value if you wanted to?

The thought was to use a wrapper function and a session variable (sys_context style) or a package variable (or accessor method) to hold the date for a session. If this value was set to a valid date then that date would be returned, otherwise sysdate would be returned.

So far the only downside I have found is that you must use a scalar sub-query when calling it from SQL or it will be called once for every row and be extremely slow (when used in scalar sub-query it is only called once and the value is cached). I noticed in an earlier post that you stated that sysdate will return the same values across a long running SQL statement so using a scalar sub-query to get it doesn't break anything.

I seem to come back to this problem again and again and I would really like to know that this is either a valid solution or I'm over engineering this.

Here is the code I whipped up off the top of my head

create or replace package settable_sysdate is
procedure set_date(in_date in varchar2);
function get_date return date;
end settable_sysdate;
/
create or replace package body settable_sysdate is

g_date varchar2(100);

procedure set_date(in_date in varchar2)
is
begin
g_date := in_date;
end;


function get_date return date
is
l_date date := null;
begin
if(g_date is not null)then
return to_date(g_date, 'mm/dd/yyyy');
else
return sysdate;
end if;
exception
when others then
return sysdate;
end;
end settable_sysdate;
/

create table SOME_LARGISH_THING
(
some_id NUMBER
);

begin
for i in 1..700000 loop
insert into some_largish_thing (some_id) values (i);
end loop;
commit;
end;
/

select systimestamp from dual;

--700k sysdates
select distinct the_date from (select some_id, sysdate the_date from some_largish_thing);
select systimestamp from dual;

--700k settable_sysdates
select distinct the_date from (select some_id, settable_sysdate.get_date the_date from some_largish_thing);
select systimestamp from dual;

--scalar sub-query causing settable_sysdate.get_date to only be called once, big improvement
select distinct the_date from (select some_id, (select settable_sysdate.get_date from dual) the_date from some_largish_thing);
select systimestamp from dual;

--Showing that setting the date actually works.
exec settable_sysdate.set_date('01/01/4000');
select distinct the_date from (select some_id, (select settable_sysdate.get_date from dual) the_date from some_largish_thing);
select systimestamp from dual;

drop table some_largish_thing;

ops$tkyte%ORA11GR2> create or replace package settable_sysdate
  2  is
  3      procedure set_date(in_date in DATE);
  4      function get_date return date;
  5  end settable_sysdate;
  6  /

Package created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace package body settable_sysdate
  2  is
  3
  4      g_date date;
  5
  6      procedure set_date(in_date in date)
  7      is
  8      begin
  9          dbms_session.set_context
 10          ( 'my_ctx', 'the_date', to_char(in_date, 'yyyymmddhh24miss' ) );
 11          g_date := in_date;
 12      end;
 13
 14
 15      function get_date return date
 16      is
 17      begin
 18          return nvl( g_date, sysdate );
 19      end;
 20
 21  end settable_sysdate;
 22  /

Package body created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec settable_sysdate.set_date( sysdate-1 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR2> select distinct settable_sysdate.get_date()
  2    from t;

SETTABLE_
---------
15-JUL-13

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select distinct (select settable_sysdate.get_date() from dual)
  2    from t;

(SELECTSE
---------
15-JUL-13

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select distinct nvl( to_date( sys_context( 'my_ctx', 'the_date' ), 'yyyymmddhh24miss' ), sysdate)
  2    from t;

NVL(TO_DA
---------
15-JUL-13

select distinct settable_sysdate.get_date() from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      4.70       5.35      14659      14663          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      4.70       5.35      14659      14664          0           1
********************************************************************************
select distinct (select settable_sysdate.get_date() from dual) from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.23       0.23      14659      14663          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.23       0.24      14659      14665          0           1
********************************************************************************
select distinct nvl( to_date( sys_context( 'my_ctx', 'the_date' ), 'yyyymmddhh24miss' ), sysdate)
  from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.22       0.22      14659      14663          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.22       0.22      14659      14664          0           1

All right, all right...what you've never over-engineered a simple solution right out of the gate?
I definitely should have thought that through a lot more before posting. That's what I get for enjoying you blasting others. Thanks for responding and letting me know I was on the right path.

Tom, I don't think that your suggested settable_sysdate quite gets over the problem of multiple sysdate values being generated for a long running query when no pseudo-sysdate has been set, at least not in the absence of result caching.

I suppose you could probably:

i) set a sys_context to ensure that a single value of the actual sysdate is returned for such queries, or
ii) pass in sysdate to settable_sysdate.get_date as a parameter from SQL and have the function return that, or
iii) return g_date from the function, and use coalesce(settable_sysdate.get_date,sysdate) in the SQL.

Probably (ii), I'd think.

ON my database server date is 16-05-2019 and my client pc date is 15-05-2019 so have any in-built function to fetch client PC date?

select current_date, sysdate , 
       current_timestamp, systimestamp
from   dual;

CURRENT_DATE          SYSDATE               CURRENT_TIMESTAMP         SYSTIMESTAMP               
16-MAY-2019 09:58:38  16-MAY-2019 01:58:38  16-MAY-2019 09.58.38 +01  16-MAY-2019 01.58.38 -07