I was recently asked to investigate why a job that was to update a few thousand rows took an hour where it had been proven to do the work in just seconds in other environments.

Was this new environment set up with much more data? No, actually much less as the tests before had been with production like data though not production copy.

After getting data for three executions it showed that the time was ALWAYS just a fraction over 1 hour. Interesting that something that suddenly takes longer has no variation. So no other processes locking or any such thing then.

The command itself vas straight forward, it executed a stored procedure via the scheduler. Thos job is enough to show the effect.

begin
 dbms_scheduler.create_job (
   job_name   => 'my_job' 
  ,job_type   => 'PLSQL_BLOCK'
  ,job_action => 'begin null; end;'
  ,start_date => sysdate
  ,enabled    => true); 
end;
/

In this example the job does nothing, but it serves the purpose of showing the mechanics.

The problem here is related to “start_date”, one could argue that it is a poor name. It turns out it is not a date at al but rather a “timestamp with timezone”. Why is that a problem you may ask. Often it is not and it works just as you expected, but if your session has a different timezone than your database then your timezone is used when converting sysdate to timestamp with timezone.

Let’s take a look at how it works.

Lets begin with the timestamp I may have expected:

2020-04-08 19:34:34,000000000 EUROPE/STOCKHOLM

On a machine not set up like mine it could look like this:

2020-04-08 19:34:34,000000000 EUROPE/LONDON

Same time-value but in different timezone. Which is not the SAME time, it is one hour later.

Now look at how a sysdate gets converted to timestamp with timezone depending on your sessions timezone.

> alter session set time_zone = 'Europe/Stockholm';

Session altered.

> select cast(sysdate as timestamp with time zone) ts from dual;
TS 
----------------------------------------------
2020-04-08 19:39:53,000000000 EUROPE/STOCKHOLM

> alter session set time_zone = 'Europe/London';

Session altered.

> select cast(sysdate as timestamp with time zone) ts from dual;
TS 
-------------------------------------------
2020-04-08 19:39:53,000000000 EUROPE/LONDON

This shows how the session’s setting for timezone is used when a sysdate is converted to a timestamp with time zone. The same result occurs when it is implicit as in assigning sysdate to a timestamp with time zone parameter.

For the job it is then used by the database which has a different timezone so the job is delayed when run on a computer that has a timezone setting different from the database.

Is this just a theoretical problem as the time zone is explicitly set in the example? No it can be set in many different ways. One is that the environment variable ORA_SDTZ can be set. The default is based on the operating system. Meaning the OS you have defines it when the session is started unless you have something else defining it. The OS sets it by setting NLS_TERRITORY which is used to derive the time zone.

Take-Aways

Never allow a sysdate to be converted to timestamp with timezone unless you are sure it is exactly what you want. You cannot know how it is interpreted when executed on a machine other than your own.

Always check the data type you are assigning a date to. It may – like in this case – be called data but actually be “timestamp with time zone”. When you forget to check, you may end up with surprising results and difficult to troubleshoot errors. After all, I was asked to look at a performance problem which turned out to be a data type conversion problem.