Bad Performance of PL/SQL function vs build-in sysdate in Oracle DB-CodePudding

I have an oracle database, which is setup with timezone 'Europe/Berlin' and the data is stored in 'UTC'. In that environment sysdate will deliver current time in Berlin, that is why it cannot be used in queries, because it would compare dates of different time zones.

select * from table 
where time_col < sysdate --bad comparision

so in order to compare with the current time in the correct timezone I would need to change to

select * from table 
where time_col < cast(systimestamp AT TIME ZONE 'UTC' as date)

or using sys_extract_utc()

select * from table
where time_col < sys_extract_utc(systimestamp)

in order to make the timezone configurable, I added a parameters table, to retrieve the timezone from, making the query look like

select * from table 
where time_col < (select cast(systimestamp AT TIME ZONE nvl(parameter_value, 'UTC') as date)  
    FROM parameter_table 
    where parameter_name = 'DBTimeZone')

or joining the parameters tables

select * from table, parameter_table 
where time_col < cast(systimestamp AT TIME ZONE nvl(parameter_value, 'UTC') as date) 
and parameter_name = 'DBTimeZone'

in order to avoid making queries unreadable I added a pl/sql package to implement my own sysdate function like this

create or replace package time_pkg as
  function sysdateDB return date;
end;
/
create or replace package body time_pkg as
  dbtz VARCHAR2(100) := 'UTC';
  function sysdateDB return date
  as
  begin
   return sysdate at time zone dbtz;
  end;
  begin
   dbtz := util.GetParameter('DBTimeZone', dbtz);
  end;
/

so in any query I can use time_pkg.sysdateDB instead of sysdate to compare data in the database with the current time of the same timezone.

select * from table where time_col < time_pkg.sysdateDB

However with some very complex queries there is a huge performance difference.

sysdate needs just 1 second
sys_extract_utc(systimestamp) needs just 1 second
systimestamp at time zone 'UTC' needs 2 seconds
time_pkg.sysdateDB needs 50 seconds
time_pkg.sysdate (not in above listing, just returns sysdate) also needs 20 seconds.

So the performance of build-in sysdate is always better than calling a function which just returns sysdate

Any ideas how to either improve the performance of the pl/sql function or any alternative writting to get the current time at a configurable timezone

Disclaimer: there is probably a performance difference in every query, but if the query still returns below 100ms nobody notices a problem.

Maybe the problem is the choice of the columns in the database, instead of timestamp with timezone we use date. Not sure if timestamp with timezone would correclty compare to sysdate (without timezone). Anyway changing the complete datamodel is not an option currently.

CodePudding user response：

Making the function deterministic as you did is not a good solution. As it is, if you run your query twice, the function will be called just one time, and so it will not return the proper sysdate, instead it will return the sysdate of it's first run. To solve this issue, add a varying parameter to it, like this:

create or replace package time_pkg as
  function sysdateDB (sd date)return date deterministic;
  function sysdateDBnd (sd date)return date ;
end;
/
create or replace package body time_pkg as
  dbtz VARCHAR2(100) := 'UTC';
  function sysdateDB (sd date)return date deterministic
  as
  begin
   return sysdate at time zone dbtz;
  end;
  function sysdateDBnd (sd date)return date 
  as
  begin
   return sysdate at time zone dbtz;
  end;
  begin
   dbtz := 'UTC';
  end;
/

I used 2 function to let you easily see the difference (and I don't have you util package, so I just fixed the dbtz variable).

select max(time_pkg.sysdateDB(sysdate))
from dual
connect by level<10000000;
MAX(TIME_PKG.SYSDAT
-------------------
03/01/2023 16:50:30

Elapsed: 00:00:02.404

select max(time_pkg.sysdateDBnd(sysdate))
from dual
connect by level<10000000;
MAX(TIME_PKG.SYSDAT
-------------------
03/01/2023 16:51:15

Elapsed: 00:00:42.779

CodePudding user response：

Thanks to to comment of @gsalem I did a lookup on how to make a function "stable". Adding the keyword deterministic seems to do the job and almost reaches the performance of build-in oracle sysdate

create or replace package time_pkg as
  function sysdateDB return date deterministic;
end;
/
create or replace package body time_pkg as
  dbtz VARCHAR2(100) := 'UTC';
  function sysdateDB return date deterministic
  as
  begin
   return sysdate at time zone dbtz;
  end;
  begin
   dbtz := util.GetParameter('DBTimeZone', dbtz);
  end;
/