Note: Tested in Oracle 19.13
1. Test Setup
At first, we create a test table with 2 date columns and 2 timestamp columns, insert 2 rows:
drop table test_tab;
create table test_tab (id number, dt1 date, dt2 date, sts1 timestamp with time zone, sts2 timestamp with time zone);
insert into test_tab values (1, sysdate+1, sysdate+11, systimestamp+interval'1'day, systimestamp+interval'11'day);
insert into test_tab values (2, sysdate+2, sysdate+22, systimestamp+interval'2'day, systimestamp+interval'22'day);
commit;
--Each function execution makes 1 SQL sysdate call (from dual) and 1 Plsql sysdate call
create or replace function test_sysdate_fun return date as
l_date date;
begin
select sysdate into l_date from dual; --SQL sysdate call
l_date := sysdate; --Plsql sysdate call
dbms_output.put_line(l_date);
return l_date;
end;
/
and pessdt is executed once for each Plsql sysdate call (see later section about package body SYS.STANDARD).
We can compose a GDB script to track both calls.
------- gdb_cmd_sysdate: gdb -x gdb_cmd_sysdate -p <pid> -------
set pagination off
set logging file /tmp/gdb_cmd_sysdate.log
set logging overwrite on
set logging on
set $s = 0
set $p = 0
break kxsCaptureSysDate
command
printf "<<< SQL sysdate Call === (%i).===>>>\n", ++$s
bt 4
continue
end
break pessdt
command
printf "<<< Plsql sysdate Call === (%i).===>>>\n", ++$p
bt 4
continue
end
2. Test Run
Open a Sqlplus session, start above GDB script on its process (<pid>), then make the test.
Each sysdate call is reported as either from SQL, or from Plsql.
First, we test the function call in Plsql:
declare
l_date date;
begin
l_date := test_sysdate_fun;
end;
/
Breakpoint 1, 0x000000000372e1c0 in kxsCaptureSysDate ()
<<< SQL sysdate Call === (1).===>>>
#0 0x000000000372e1c0 in kxsCaptureSysDate ()
#1 0x0000000012c8bef0 in kxsSetDateTime ()
#2 0x0000000012b11b44 in opiexe ()
#3 0x0000000012b201eb in opipls ()
Breakpoint 2, 0x0000000005677200 in pessdt ()
<<< Plsql sysdate Call === (1).===>>>
#0 0x0000000005677200 in pessdt ()
#1 0x0000000012fa5f58 in pevm_icd_call_common ()
#2 0x0000000012f9fe62 in pfrinstr_BCAL ()
#3 0x0000000012f9ed5c in pfrrun_no_tool ()
Then, we execute a query involing SQL and Plsql sysdate calls:
select id, dt1, sysdate, sysdate
,test_sysdate_fun rs
from test_tab
where (dt1 > sysdate - 10)
and (test_sysdate_fun > sysdate - 10)
and (dt2 > test_sysdate_fun - 20);
<<< SQL sysdate Call === (1).===>>>
#0 0x000000000372e1c0 in kxsCaptureSysDate ()
<<< SQL sysdate Call === (2).===>>>
#0 0x000000000372e1c0 in kxsCaptureSysDate ()
<<< Plsql sysdate Call === (1).===>>>
#0 0x0000000005677200 in pessdt ()
<<< SQL sysdate Call === (3).===>>>
#0 0x000000000372e1c0 in kxsCaptureSysDate ()
<<< Plsql sysdate Call === (2).===>>>
#0 0x0000000005677200 in pessdt ()
<<< SQL sysdate Call === (4).===>>>
#0 0x000000000372e1c0 in kxsCaptureSysDate ()
<<< Plsql sysdate Call === (3).===>>>
#0 0x0000000005677200 in pessdt ()
<<< SQL sysdate Call === (5).===>>>
#0 0x000000000372e1c0 in kxsCaptureSysDate ()
<<< Plsql sysdate Call === (4).===>>>
#0 0x0000000005677200 in pessdt ()
<<< SQL sysdate Call === (6).===>>>
#0 0x000000000372e1c0 in kxsCaptureSysDate ()
<<< Plsql sysdate Call === (5).===>>>
#0 0x0000000005677200 in pessdt ()
First 1 SQL sysdate call is from sql where condition dt1.
Then 2 sysdate calls are from 1 test_sysdate_fun where condition (no table column referred).
Then 4 sysdate calls are from 2 test_sysdate_fun where condition dt2 for 2 rows (table column dt2 referred).
Then 4 sysdate calls are from 2 test_sysdate_fun column expressions for 2 rows.
(Note that each test_sysdate_fun execution triggers 1 SQL sysdate call and 1 Plsql sysdate call)
Above test shows that for SQL sysdate, all multiple direct calls in where conditions and column values for multiple rows are merged (bound) to one single SQL sysdate call for each SQL statement execution.
But for Plsql function, when it appears in column unrelated where condition, it is called only once for multiple rows;
when it appears in column related where condition or expression, it is called once for each column and each row.
Here some more examples with their number of sysdate calls:
-- Only 1 SQL sysdate call in each query
-- (it does not matter how many times in select columns or in where conditions)
alter session set nls_date_format ='YYYY*MON*DD HH24:MI:SS';
select id, dt1
from test_tab
where (dt1 > sysdate - 10);
select sysdate, sysdate + 1, sysdate + 2, id, dt1
from test_tab
where (dt1 > sysdate - 10);
select sysdate, sysdate + 1, sysdate + 2, id, dt1
from test_tab
where (dt1 > sysdate - 10)
and (dt2 > sysdate - 20);
with sq1 as (select sysdate d1 from dual),
sq2 as (select sysdate d2 from dual),
sq3 as (select sysdate d3 from dual)
select * from sq1, sq2, sq3;
-- 13 sysdate calls (first 1 SQL sysdate call from 2 sql where conditions,
-- then 2 SQL call and 2 Plsql call from test_sysdate_funn where conditions,
-- 2 SQL call and 2 Plsql call from test_sysdate_funn rs1 for 2 rows
-- 2 SQL call and 2 Plsql call from test_sysdate_funn rs2 for 2 rows)
select sysdate, sysdate + 1, sysdate + 2, id, dt1
,test_sysdate_fun rs1
,test_sysdate_fun rs2
from test_tab
where (dt1 > sysdate - 10)
and (dt2 > sysdate - 20)
and (test_sysdate_fun > sysdate - 10)
and (test_sysdate_fun > sysdate - 20);
-- 1 SQL sysdate call, query takes more than 10 seconds, but all 4 column values are same.
with sq1 as (select SYSDATE d1 from dual connect by level <= 1e3)
,sq2 as (select SYSDATE d2 from dual connect by level <= 1e5)
select min(d1) min_d1, max(d1) max_d1, min(d2) min_d2, max(d2) max_d2 from sq1, sq2;
3. systimestamp
In above gdb script, replacing kxsCaptureSysDate by kxsCaptureSysTime for SQL call,
and pessdt by pessts for Plsql call,
we can make the similar tests for systimestamp calls.
--Each function execution makes 1 SQL systimestamp call (from dual) and 1 Plsql systimestamp call
create or replace function test_systimestamp_fun return timestamp with time zone as
l_sts timestamp with time zone;
begin
select systimestamp into l_sts from dual;
l_sts := systimestamp;
dbms_output.put_line(l_sts);
return l_sts;
end;
/
alter session set nls_timestamp_tz_format ='YYYY*MON*DD HH24:MI:SS.FF3 TZR TZD';
-- 1 SQL systimestamp call
select systimestamp, systimestamp + interval'10'day, systimestamp + interval'20'day, id, sts1
from test_tab
where (sts1 > systimestamp - interval'10'day)
and (sts2 > systimestamp - interval'20'day);
--There are 7 systimestamp calls.
--First 1 SQL systimestamp call is from sql where condition.
--Then 2 systimestamp calls are from 1 test_systimestamp_fun where condition.
--Then 4 systimestamp calls are from 2 test_systimestamp_fun executions for 2 rows.
--Each test_systimestamp_fun execution triggers 1 SQL systimestamp call and 1 Plsql systimestamp call.
select id, sts1
,test_systimestamp_fun sts
from test_tab
where (sts1 > systimestamp - interval'10'day)
and (test_systimestamp_fun > systimestamp - interval'10'day);
-- 1 SQL systimestamp call, query takes more than 10 seconds, all 4 column values are same.
with sq1 as (select SYSTIMESTAMP d1 from dual connect by level <= 1e3)
,sq2 as (select SYSTIMESTAMP d2 from dual connect by level <= 1e5)
select min(d1) min_d1, max(d1) max_d1, min(d2) min_d2, max(d2) max_d2 from sq1, sq2;
4. Plsql SYS.STANDARD sysdate/pessdt, systimestamp/pessts
Package body SYS.STANDARD contains code and comments about sysdate/pessdt and systimestamp/pessts:
function pessdt return DATE;
pragma interface (c,pessdt);
-- Bug 1287775: back to calling ICD.
-- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, 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;
return d;
exception
when ICD_UNABLE_TO_COMPUTE then
select sysdate into d from sys.dual;
return d;
end;
function pessts return timestamp_tz_unconstrained;
pragma interface (c,pessts);
-- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
-- the old 'SELECT systimestamp 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 systimestamp RETURN timestamp_tz_unconstrained
IS t timestamp_tz_unconstrained;
BEGIN
t := pessts;
RETURN t;
EXCEPTION
WHEN ICD_UNABLE_TO_COMPUTE THEN
SELECT systimestamp INTO t FROM sys.dual;
RETURN t;
END;
This performance regression was introduced with the fix in Bug:616870.
With this fix calling SYSDATE from PL/SQL is much slower as SYSDATE
is obtained by selecting from DUAL.
This fix restores server side performance by allowing server side PLSQL
to use internal calls rather than selecting from DUAL.
5. Related Work
Blog: Different SYSDATE behaviors in SQL and PL/SQL wrote:
"In SQL, SYSDATE is called just once for the entire statement.
In PL/SQL, SYSDATE is called every time it is invoked."
Its tests are based on the count of different sysdate returned values in multiple sysdate calls from SQL and Plsql.
In this Blog, we track each sysdate call instead of its returned value to show the different number of sysdate calls from SQL and Plsql.
