Thursday, May 25, 2017

JDBC, Oracle object/collection, dbms_pickler, NOPARALLEL sys.type$ query

When using the JDBC Thin driver to invoke PL/SQL programs with Oracle objects / collections, we can observe dbms_pickler calls, and two NOPARALLEL hinted sys.type$ queries having the same number of executions as dbms_pickler. These can result in intermittent performance degradation.

Note: All tests are done with java 8 and ojdbc7.jar on Oracle 12.1.0.2.0.
          See appended Test Code.


1. Test and Reasoning


Run:
   java OracleJDBC "jdbc:oracle:thin:k/s@testDB:1521:testDB" 3 5

then execute query below, watch its output:

select executions, sql_text, v.* 
  from v$sql v 
 where lower(sql_text) like '%dbms_pickler%' 
    or sql_text like '%NOPARALLEL%'; 
    
2 begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10); end;
2 SELECT /*+ NOPARALLEL */ 1, A.NAME, A.ATTRIBUTE#, ..., SO.NAME 
    FROM SYS.ATTRIBUTE$ A, SYS.TYPE$ T, SYS.TYPE$ AT, SYS. "_CURRENT_EDITION_OBJ" ATO, 
         SYS.USER$ ATU, SYS. "_CURRENT_EDITION_OBJ" SO, SYS.USER$ SU 
   WHERE T.TVOID = :B1 AND A.ATTR_TOID = ATO.OID$ ...
   ORDER BY ATTRIBUTE#

Since we have one call of StructDescriptor for Oracle Object "TEST_OBJ", and one call of ArrayDescriptor for Oracle Object Collection (nested table) "TEST_OBJ_ARRAY":

  StructDescriptor structDP = StructDescriptor.createDescriptor("TEST_OBJ", conn);
  ArrayDescriptor  arrayDP  = ArrayDescriptor.createDescriptor("TEST_OBJ_ARRAY", conn);

so there are two respective calls of dbms_pickler.get_type_shape to lookup their metadata in Oracle database.

Whereas with ojdbc5.jar, it looks like:
    dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7)
and there are no two NOPARALLEL hinted sys.type$ queries because there are no calls of dbms_pickler.get_metadata.

Further tested in Oracle 11g dbms_pickler or 12cR1 with ojdbc5.jar, it is not able to observe this behavior.

Comparing output of
    SQL > describe dbms_pickler
between 11g and 12cR1, we can see that 11g two APIs dbms_pickler.get_type_shape have 6 parameters; whereas 12cR1 APIs are enhanced with two more overloaded functions having 9 parameters. That could explain the difference:
    11g   dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7)
    12cR1 dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10)
Hence it is possible the regression was introduced in 12cR1 with ojdbc7.jar.

For each oracle connection, the created instances of StructDescriptor and ArrayDescriptor are cached, the subsequent repeated call gets the cached result (one optimization). That is why even we have a loop calls, only two calls of dbms_pickler.get_type_shape are sent to database.

As a further test, we can provoke a java dump by:
   SQL> drop type test_obj force;
and get an exception call stack:

java.sql.SQLException: Fail to construct descriptor: Unable to resolve type "TEST_OBJ"
        at oracle.sql.TypeDescriptor.getTypeDescriptor(TypeDescriptor.java:1042)
        at oracle.sql.StructDescriptor.isValidObject(StructDescriptor.java:89)
        at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:114)
        at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:74)
        at OracleJDBC.dbcall(OracleJDBC.java:60)
        at OracleJDBC.main(OracleJDBC.java:43)

Here the code path:

OracleJDBC.java
 StructDescriptor sDescr = StructDescriptor.createDescriptor("TEST_OBJ", connection);
--->
oracle.sql.StructDescriptor.java
  structdescriptor = new StructDescriptor(sqlname, connection);
--->
oracle.jdbc.oracore.OracleTypeADT
 init(OracleConnection oracleconnection)
  -> initMetadata(oracleconnection);
    -> initMetadata12(OracleConnection oracleconnection)
--->
dbms_pickler
   -> get_type_shape  
      -> get_metadata

The above test is performed with 12c deprecated oracle.sql.STRUCT and oracle.sql.ARRAY (first Java Test Code).

Run the same test with new wrapped API (second Java Test Code), and provoke the same exception. We can see both implementations are internally identical, but the deprecated API looks more obvious.


java.sql.SQLException: Fail to construct descriptor: Unable to resolve type "TEST_OBJ"
        at oracle.sql.TypeDescriptor.getTypeDescriptor(TypeDescriptor.java:1042)
        at oracle.sql.StructDescriptor.isValidObject(StructDescriptor.java:89)
        at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:114)
        at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:74)
        at oracle.jdbc.driver.PhysicalConnection.createStruct(PhysicalConnection.java:8732)
        at OracleJDBC.dbcall(OracleJDBC.java:61)
        at OracleJDBC.main(OracleJDBC.java:42)

Also tested with Java Stored Procedure in Oracle RDBMS embedded JVM (see Test Code: Java Stored Procedure) by:
   SQL > exec dbcallCtrl(3, 5);

It has the same behavior.


2. Trace file and HProfile


In the Test Code, we also make the Event 10046 Trace and HProfile, here excerpted outputs:


(a). Row Trace File



begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10); end;
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00         <--- oacdty=02:  SQLT_NUM: number
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=fffffd7ffbe8afe8  bln=22  avl=00  flg=05
 Bind#1
  oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00   <--- oacdty=01:  SQLT_CHR: varchar2
  oacflg=03 fl2=1000010 frm=01 csi=873 siz=32767 off=0
  kxsbbbfp=fffffd7ffbe81fe8  bln=32767  avl=08  flg=05
  value="TEST_OBJ"

...
 Bind#1
  oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=873 siz=32767 off=0
  kxsbbbfp=fffffd7ffbea3000  bln=32767  avl=14  flg=05
  value="TEST_OBJ_ARRAY"


(b). TKPROF Trace File



begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10); end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.01       0.01          0         59          0           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0         59          0           2

---- SQL_ID: 7sz0r2npbx7dp
SELECT /*+ NOPARALLEL */ 1, A.NAME, A.ATTRIBUTE#, DECODE(AT.TYPECODE, 9, 
  DECODE(A.CHARSETFORM, 2, 'NVARCHAR2', ATO.NAME), 96, DECODE(A.CHARSETFORM, 
  2, 'NCHAR', ATO.NAME), 112, DECODE(A.CHARSETFORM, 2, 'NCLOB', ATO.NAME), 
  ATO.NAME), DECODE(BITAND(AT.PROPERTIES, 64), 64, NULL, ATU.NAME), NULL, 
  A.ATTR_TOID, DECODE(BITAND(T.PROPERTIES, 65536), 65536, 'NO', 'YES'), 
  SU.NAME, SO.NAME 
FROM
 SYS.ATTRIBUTE$ A, SYS.TYPE$ T, SYS.TYPE$ AT, SYS. "_CURRENT_EDITION_OBJ" ATO,
   SYS.USER$ ATU, SYS. "_CURRENT_EDITION_OBJ" SO, SYS.USER$ SU WHERE T.TVOID =
   :B1 AND A.ATTR_TOID = ATO.OID$ AND ATO.OWNER# = ATU.USER# AND A.TOID = 
  T.TVOID AND T.PACKAGE_OBJ# IS NULL AND AT.TVOID = A.ATTR_TOID AND 
  AT.SUPERTOID = SO.OID$ (+) AND SO.OWNER# = SU.USER# (+) ORDER BY ATTRIBUTE#

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         37          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0         37          0           2
     
---- SQL_ID: 9vv8244bcq529
SELECT /*+ NOPARALLEL */ 1, U.NAME, O.NAME, O.OID$ 
FROM
 SYS. "_CURRENT_EDITION_OBJ" O, SYS.USER$ U, SYS.TYPE$ T WHERE T.SUPERTOID = 
  :B1 AND T.TVOID = O.OID$ AND O.SUBNAME IS NULL AND O.OWNER# = U.USER#

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          6          0           0

Note that sys.USER$ is a very thick declared table. For each row, it can requires 3 8-KB Blocks
(See Blog: Redo/Undo explosion from thick declared table insert)

SQL> select count(*) col_cnt, sum(data_length) max_rowsize, max(data_length) max_col_len 
       from dba_tab_cols 
      where owner='SYS' and table_name='USER$';
     
  COL_CNT MAX_ROWSIZE MAX_COL_LEN
  ------- ----------- -----------
       30       18670        4000    


(c). HProfile


Function Elapsed Time (microsecs) Data sorted by Total Subtree Elapsed Time (microsecs) for 'dbms_pickler_hpf_15_09_56'

Function Elapsed Time (microsecs) Data sorted by Total Subtree Elapsed Time (microsecs)

10517 microsecs (elapsed time) & 20 function calls

Subtree Ind% Function Ind% Descendants Ind% Calls Ind% Function Name
10517 100% 11 0.1% 10506 99.9% 3 15.0% __plsql_vm
10506 99.9% 374 3.6% 10132 96.3% 3 15.0% __anonymous_block
10112 96.1% 3945 37.5% 6167 58.6% 2 10.0% SYS.DBMS_PICKLER.GET_TYPE_SHAPE (Line 384)
1708 16.2% 1708 16.2% 0 0.0% 2 10.0% SYS.DBMS_PICKLER.__static_sql_exec_line141 (Line 141)
1263 12.0% 1263 12.0% 0 0.0% 2 10.0% SYS.DBMS_PICKLER.__static_sql_exec_line236 (Line 236)
1094 10.4% 1094 10.4% 0 0.0% 2 10.0% SYS.DBMS_PICKLER.__static_sql_exec_line175 (Line 175)
1067 10.1% 1067 10.1% 0 0.0% 2 10.0% SYS.DBMS_PICKLER.__static_sql_exec_line211 (Line 211)
1035 9.8% 1035 9.8% 0 0.0% 2 10.0% SYS.DBMS_PICKLER.__sql_fetch_line186 (Line 186)
20 0.2% 20 0.2% 0 0.0% 1 5.0% K.TRC_STOP.TRC_STOP (Line 1)
0 0.0% 0 0.0% 0 0.0% 1 5.0% SYS.DBMS_HPROF.STOP_PROFILING (Line 63)

(d). dbms_pickler.get_type_shape Test


In above TKPROF output, we saw two NOPARALLEL hinted queries, which are two REF CURSOR created when calling dbms_pickler.get_type_shape.

Make a direct Plsql call of dbms_pickler.get_type_shape, and look its output, we can get some understanding of its functionalities.

-------------------------- ATTR_RC: sql_id: 7sz0r2npbx7dp ----------------
--OPEN ATTR_RC FOR 
  SELECT /*+ NOPARALLEL */ 1, A.NAME, A.ATTRIBUTE#,
     DECODE(AT.TYPECODE,
            9, DECODE(A.CHARSETFORM, 2, 'NVARCHAR2', ATO.NAME),
            96, DECODE(A.CHARSETFORM, 2, 'NCHAR', ATO.NAME),
            112, DECODE(A.CHARSETFORM, 2, 'NCLOB', ATO.NAME),
            ATO.NAME) at_typecode,
     DECODE(BITAND(AT.PROPERTIES, 64), 64, NULL, ATU.NAME) at_properties, NULL, 
     A.ATTR_TOID,
     DECODE(BITAND(T.PROPERTIES, 65536), 65536, 'NO', 'YES') t_properties,
     SU.NAME, SO.NAME
  FROM SYS.ATTRIBUTE$ A, 
     SYS.TYPE$ T, SYS.TYPE$ AT,
     SYS."_CURRENT_EDITION_OBJ" ATO, SYS.USER$ ATU, 
     SYS."_CURRENT_EDITION_OBJ" SO, SYS.USER$ SU
  WHERE T.TVOID = TYPOID   --'7B54200768642599E054005056984D97'
     AND A.ATTR_TOID = ATO.OID$ AND
     ATO.OWNER# = ATU.USER# AND
     A.TOID = T.TVOID AND
     T.PACKAGE_OBJ# IS NULL AND
     AT.TVOID = A.ATTR_TOID AND
     AT.SUPERTOID = SO.OID$ (+) AND SO.OWNER# = SU.USER# (+)
   ORDER BY ATTRIBUTE#;
  
-------------------------- SUBTYPE_RC: sql_id: 9vv8244bcq529 ------------------
--OPEN SUBTYPE_RC FOR
  SELECT /*+ NOPARALLEL */ 1, U.NAME, O.NAME, O.OID$
  FROM SYS."_CURRENT_EDITION_OBJ" O, 
       SYS.USER$ U, SYS.TYPE$ T
  WHERE T.SUPERTOID = TYPOID     --'7B54200768642599E054005056984D97' 
        AND T.TVOID = O.OID$ AND   
        O.SUBNAME IS NULL AND  
        O.OWNER# = U.USER#;
          
-------------------------- Create Table -----------------------
          
create table attr_rc_pickler_table as
  select /*+ noparallel */ 1 c1, a.name, a.attribute#,
     decode(at.typecode,
            9, decode(a.charsetform, 2, 'nvarchar2', ato.name),
            96, decode(a.charsetform, 2, 'nchar', ato.name),
            112, decode(a.charsetform, 2, 'nclob', ato.name),
            ato.name) at_typecode,
     decode(bitand(at.properties, 64), 64, null, atu.name) at_properties, 'null' c_null, 
     a.attr_toid,
     decode(bitand(t.properties, 65536), 65536, 'no', 'yes') t_properties,
     su.name su_name, so.name so_name
  from sys.attribute$ a, 
     sys.type$ t, sys.type$ at,
     sys."_CURRENT_EDITION_OBJ" ato, sys.user$ atu, 
     sys."_CURRENT_EDITION_OBJ" so, sys.user$ su
  where 1=2;
  
alter table attr_rc_pickler_table modify (su_name null); 
alter table attr_rc_pickler_table modify (so_name null); 
       
create table subtype_rc_pickler_table as
  select /*+ noparallel */ 1 c1, u.name uname, o.name oname, o.oid$
  from sys."_CURRENT_EDITION_OBJ" o, 
       sys.user$ u, sys.type$ t
  where 1=2;

alter table subtype_rc_pickler_table modify (uname null); 
alter table subtype_rc_pickler_table modify (oname null);  

-------------- dbms_pickler.get_type_shape Plsql Call ----------------

create or replace procedure dbms_pkl_get_type_shape(p_typename varchar2, p_insert boolean := false) as
  l_ret number;
  l_fulltypename varchar2(32767) := p_typename;   --'test_obj'
  l_typoid raw(32767);
  l_version binary_integer;
  l_tds long raw(32767);
  l_instantiable varchar2(32767);
  l_supertype_owner varchar2(32767);
  l_supertype_name  varchar2(32767);
  l_attr_rc  dbms_pickler.type_attr_ref_cursor;
  l_subtype_rc dbms_pickler.type_subtype_ref_cursor;
  
  type attr_rc_type is table of attr_rc_pickler_table%rowtype index by pls_integer;
  l_attr_rc_tab attr_rc_type; 
  type subtype_rc_type is table of subtype_rc_pickler_table%rowtype index by pls_integer;
  l_subtype_rc_tab subtype_rc_type; 
begin 
  l_ret := dbms_pickler.get_type_shape(
           l_fulltypename,
           l_typoid, 
           l_version,
           l_tds, 
           l_instantiable,
           l_supertype_owner, 
           l_supertype_name, 
           l_attr_rc,
           l_subtype_rc);
  dbms_output.put_line(''); 
  dbms_output.put_line('------ get_type_shape typename = '||p_typename||' ------ ');
  dbms_output.put_line('get_type_shape return         ='||l_ret);
  dbms_output.put_line('get_type_shape fulltypename   ='||l_fulltypename);
  dbms_output.put_line('get_type_shape typoid         ='||l_typoid);
  dbms_output.put_line('get_type_shape version        ='||l_version);
  dbms_output.put_line('get_type_shape tds            ='||l_tds);
  dbms_output.put_line('get_type_shape instantiable   ='||l_instantiable);
  dbms_output.put_line('get_type_shape supertype_owner='||l_supertype_owner);
  dbms_output.put_line('get_type_shape supertype_name ='||l_supertype_name);  
  
  -- not yet fetched, always 0 
  --dbms_output.put_line('dbms_pickler.get_type_shape l_attr_rc%rowcount ='||l_attr_rc%rowcount);  
  
  dbms_output.put_line('------*** type_attr_ref_cursor ***------ ');
  --open l_attr_rc(l_typoid);  -- already opened
  if l_attr_rc%isopen then 
    dbms_output.put_line('get_type_shape l_attr_rc%isopen ='||'true');
  else
    dbms_output.put_line('get_type_shape l_attr_rc%isopen ='||'false');
  end if;
  fetch l_attr_rc bulk collect into l_attr_rc_tab;
  dbms_output.put_line('get_type_shape l_attr_rc%rowcount ='||l_attr_rc%rowcount); 
  if p_insert then
   forall i in 1..l_attr_rc_tab.count
     insert into attr_rc_pickler_table values l_attr_rc_tab(i);
   dbms_output.put_line('attr_rc_pickler_table inserted sql%rowcount ='||sql%rowcount);
   commit;
 end if;
  close l_attr_rc;  
  
  dbms_output.put_line('------*** type_subtype_ref_cursor ***------ ');
  if l_subtype_rc%isopen then 
    dbms_output.put_line('get_type_shape l_subtype_rc%isopen ='||'true');
  else
    dbms_output.put_line('get_type_shape l_subtype_rc%isopen ='||'false');
  end if;
  fetch l_subtype_rc bulk collect into l_subtype_rc_tab;
  dbms_output.put_line('get_type_shape l_subtype_rc%rowcount ='||l_subtype_rc%rowcount); 
  if p_insert then
   forall i in 1..l_subtype_rc_tab.count
     insert into subtype_rc_pickler_table values l_subtype_rc_tab(i);
   dbms_output.put_line('subtype_rc_pickler_table inserted sql%rowcount ='||sql%rowcount);
   commit;
 end if;  
  close l_subtype_rc; 
end;
/  

-------------- Test Output ----------------

--exec dbms_pkl_get_type_shape('TEST_OBJ');
exec dbms_pkl_get_type_shape('TEST_OBJ', TRUE);

  ------ get_type_shape typename = TEST_OBJ ------
  get_type_shape return         =0
  get_type_shape fulltypename   =K.TEST_OBJ
  get_type_shape typoid         =7B54200768642599E054005056984D97
  get_type_shape version        =1
  get_type_shape tds            =0000001C260100010002002900000000000F0608000701908100002A0007000A
  get_type_shape instantiable   =YES
  get_type_shape supertype_owner=
  get_type_shape supertype_name =
  ------*** type_attr_ref_cursor ***------
  get_type_shape l_attr_rc%isopen =true
  get_type_shape l_attr_rc%rowcount =2
  ------*** type_subtype_ref_cursor ***------
  get_type_shape l_subtype_rc%isopen =true
  get_type_shape l_subtype_rc%rowcount =0

-------------- Select REF CURSOR fetched Result ----------------

  select * from attr_rc_pickler_table;
  select * from subtype_rc_pickler_table;

3. Type Descriptor Retrieval Performance Fix


Comparing code of 12c deprecated APIs,

StructDescriptor structDP = StructDescriptor.createDescriptor("TEST_OBJ", conn);
ArrayDescriptor  arrayDP  = ArrayDescriptor.createDescriptor("TEST_OBJ_ARRAY", conn);

oneObj   = new STRUCT(structDP, conn, objField);
objArray = new ARRAY(arrayDP, conn, objVector.toArray()); 
with that of new APIs,

objStruct[i] = conn.createStruct("TEST_OBJ", objField);
objArray     = conn.createOracleArray("TEST_OBJ_ARRAY", objStruct);
and refer to the implementation of new APIs in PhysicalConnection.java of ojdbc7.jar:

oracle.jdbc.driver.PhysicalConnection.java
    ...
    public Struct createStruct(String s, Object aobj[])
    {
        StructDescriptor structdescriptor = StructDescriptor.createDescriptor(s, this);
        return new STRUCT(structdescriptor, this, aobj);
    }
    
    public Array createOracleArray(String s, Object obj)
    {
        return createARRAY(s, obj);
    }    
    
    public ARRAY createARRAY(String s, Object obj)
    {
        ArrayDescriptor arraydescriptor = ArrayDescriptor.createDescriptor(s, this);
        return new ARRAY(arraydescriptor, this, obj);
    }  
we can see that new API createStruct / createARRAY enclose both createDescriptor and constructor of deprecated API as one call.

From code path (call stack), we know the performance is caused by an execution of

 "begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10); end;"
located in OracleTypeADT.java, which is invoked by TypeDescriptor.getTypeDescriptor following the call of StructDescriptor.createDescriptor.

Oracle MOS:
    Intermittent Poor Performance Retrieving PL/SQL Object Types (Doc ID 1222553.1)
reveals 3 new API functions in PhysicalConnection.java:

 getAllTypeDescriptorsInCurrentSchema()
  getTypeDescriptorsFromListInCurrentSchema()
  getTypeDescriptorsFromList()
which return TypeDescriptors for the given Objects. In fact, it is a call of query:

"SELECT schema_name, typename, typoid, typecode, version, tds FROM TABLE(private_jdbc.Get_Type_Shape_Info(?))"
where private_jdbc.Get_Type_Shape_Info is implemented as:

dbms_pickler.get_type_shape(schema_name, v_in.typename, v_out.typoid, v_out.version, v_out.tds, lds_temp)
It has only 6 parameters, and is exact the same call as ojdbc5.jar.
private_jdbc is probably the MOS mentioned internal PL/SQL package introduced in RDBMS 11.

MOS Doc ID 1222553.1 claims:
    Only when using the new API methods will Get_Type_Shape_Info be used behind the scenes rather than DBMS_PICKLER.

However test shows that DBMS_PICKLER is still called because PRIVATE_JDBC.Get_Type_Shape_Info delegates the call to DBMS_PICKLER.get_type_shape, but only with 6 parameters same as with ojdbc5.jar. It can be confirmed with HProfile.

Therefore, if we use the deprecated API with getTypeDescriptorsFromList(), the perfromance is back to ojdbc5.jar.
See appended Java Code:
    Test Code: Java (Type Descriptor Retrieval Performance Fix by getTypeDescriptorsFromList)

However, with 12c new API createStruct / createARRAY, there seems no way to call getTypeDescriptorsFromList() because both createDescriptor and constructor are cemented in one single call.

One can also use dtrace to get more info about subroutine: pckltds in the call:

dbms_pickler.get_type_shape
---> dbms_pickler.get_descriptors
     ---> external name "pckltds"

dtrace -w -n 'pid$target::*pckltds*:entry {@[pid, ustack(20, 0)] = count() ; stop(); exit(0);}' -p 28016


4. Test Code: DB



drop table test_obj_tab;
drop type test_obj_array force;
drop type test_obj force;

create or replace type test_obj as object (
  id    number(8)
 ,name  varchar2(100));
/

create or replace type test_obj_array as table of test_obj;
/

create table test_obj_tab(id number(8), name varchar2(100), ins_time date);

select * from test_obj_tab order by ins_time desc;

create or replace procedure trc_start as
  l_ident_trc varchar2(100);
  l_ident_hpf varchar2(100);
begin
  l_ident_trc := 'dbms_pickler_trc_'||to_char(sysdate, 'HH24_MI_SS');
  l_ident_hpf := 'dbms_pickler_hpf_'||to_char(sysdate, 'HH24_MI_SS')||'.hpf';
  dbms_application_info.set_module('ksun_mod1', 'ksun_act1');
  execute immediate 'alter session set tracefile_identifier=''' || l_ident_trc || '''';
  dbms_monitor.session_trace_enable(waits=>true, binds=>true);
  sys.dbms_hprof.start_profiling('HPROF_DIR', l_ident_hpf);
end;
/

create or replace procedure trc_stop as
  l_ret    binary_integer;
  l_intval integer;
  l_strval varchar2(100);
begin
  sys.dbms_hprof.stop_profiling;
  l_ret := dbms_utility.get_parameter_value('session_cached_cursors', intval => l_intval, strval => l_strval);
  dbms_output.put_line('l_intval='||l_intval||', l_strval='||l_strval);
  execute immediate 'alter session set session_cached_cursors = 0';
  dbms_monitor.session_trace_disable;
  execute immediate 'alter session set session_cached_cursors = '||l_intval;
end;
/

create or replace function ins_tab(p_array test_obj_array) return number as
  l_rowcount integer;
begin
  insert into test_obj_tab select t.id, t.name, sysdate from table(cast (p_array as test_obj_array)) t;
  l_rowcount := sql%rowcount;
  commit;
  return l_rowcount;
end;
/


5. Test Code: Java (12c deprecated oracle.sql.STRUCT, oracle.sql.ARRAY)



import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.Vector;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.jdbc.OracleTypes;

// OracleJDBC  jdbcURL  loopCNT  objCNT
// OracleJDBC "jdbc:oracle:thin:k/s@testDB:1521:testDB" 3 5 

public class OracleJDBC {
  static String TRCSTART = "begin trc_start; end;";
  static String TRCSTOP  = "begin trc_stop; end;";
  static String INSTAB   = "begin :1 := ins_tab(:2); end;";
   
  public static void main(String[] args) {
    String jdbcURL = args[0];
    int loopCNT    = Integer.parseInt(args[1]);
    int objCNT     = Integer.parseInt(args[2]);
    CallableStatement cStmt;
    
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("Where is your Oracle JDBC Driver ?");
      e.printStackTrace();
      return;
    }
    
    Connection conn = null;
    try {
      conn = DriverManager.getConnection(jdbcURL);
      
      cStmt = conn.prepareCall(TRCSTART);
      cStmt.execute();
      cStmt.close();  
          
      for(int p=0; p < loopCNT; p++){
        dbcall(conn, p+1, objCNT);
      }
      
      cStmt = conn.prepareCall(TRCSTOP);
      cStmt.execute();
      cStmt.close();      
    } catch (SQLException e) {
      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
      return;
    }
  }
 
  static void dbcall(Connection conn, int loopNo, int objCNT) {
    CallableStatement cStmt;
    
    Object [] objField = new Object[2];
    STRUCT oneObj = null;
    Vector objVector = new Vector();
    ARRAY objArray = null;
    
    if (conn != null) {
      System.out.println("You made it, call DBMS_PICKLER to select TYPE info from sys.type$ !");
      try {
        StructDescriptor structDP = StructDescriptor.createDescriptor("TEST_OBJ", conn);
        ArrayDescriptor  arrayDP  = ArrayDescriptor.createDescriptor("TEST_OBJ_ARRAY", conn);
        for(int i=0; i < objCNT; i++){
          objField[0] = i;       
          objField[1] = "Name_" + i; 
          oneObj = new STRUCT(structDP, conn, objField);
          objVector.add(oneObj);
        }
        objArray = new ARRAY(arrayDP, conn, objVector.toArray()); 
        cStmt = conn.prepareCall(INSTAB);
        cStmt.registerOutParameter(1, OracleTypes.INTEGER);
        cStmt.setArray(2, objArray);
        cStmt.execute();
        int insRows = cStmt.getInt(1);
        cStmt.close();
        System.out.println("ARRAY Size = " + objArray.length() + ", DB Inserted Rows = " + insRows + ", loops = " + loopNo);
      } catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
      }
      System.out.println("END");
    } else {
      System.out.println("Failed to make connection!");
    }
  }
}


6. Test Code: Java (12c new API)



import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.Struct;
import java.sql.Array;
import java.sql.SQLException;
import java.util.Vector;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleConnection;

// OracleJDBC  jdbcURL  loopCNT  objCNT
// OracleJDBC "jdbc:oracle:thin:k/s@testDB:1521:testDB" 3 5

public class OracleJDBC {
  static String TRCSTART = "begin trc_start; end;";
  static String TRCSTOP  = "begin trc_stop; end;";
  static String INSTAB   = "begin :1 := ins_tab(:2); end;";
   
  public static void main(String[] args) {
   String jdbcURL = args[0];
   int loopCNT    = Integer.parseInt(args[1]);
   int objCNT     = Integer.parseInt(args[2]);
   CallableStatement cStmt;
   
   try {
     Class.forName("oracle.jdbc.driver.OracleDriver");
   } catch (ClassNotFoundException e) {
     System.out.println("Where is your Oracle JDBC Driver ?");
     e.printStackTrace();
     return;
   }
   
   Connection conn = null;
   try {
     conn = DriverManager.getConnection(jdbcURL);
     cStmt = conn.prepareCall(TRCSTART);
     cStmt.execute();
     cStmt.close();  
         
     for(int p=0; p < loopCNT; p++){
       dbcall(conn, p+1, objCNT);
     }
     
     cStmt = conn.prepareCall(TRCSTOP);
     cStmt.execute();
     cStmt.close();      
   } catch (SQLException e) {
     System.out.println("Connection Failed! Check output console");
     e.printStackTrace();
     return;
   }
  }
 
  static void dbcall(Connection conn, int loopNo, int objCNT) {
    CallableStatement cStmt;
    
    Object [] objField = new Object[2];
    Struct[] objStruct = new Struct[objCNT];
    Array objArray = null;
    
    if (conn != null) {
      System.out.println("You made it, call DBMS_PICKLER to select TYPE info from sys.type$ !");
      try {
        for(int i=0; i < objCNT; i++){
          objField[0] = i;       
          objField[1] = "Name_" + i; 
          objStruct[i] = conn.createStruct("TEST_OBJ", objField);
        }
        objArray = ((OracleConnection) conn).createOracleArray("TEST_OBJ_ARRAY", objStruct);
        
        cStmt = conn.prepareCall(INSTAB);
        cStmt.registerOutParameter(1, OracleTypes.INTEGER);
        cStmt.setArray(2, objArray);
        cStmt.execute();
        int insRows = cStmt.getInt(1);
        cStmt.close();
        //Object[] objArrayIns = (Object[])objArray.getArray();
        System.out.println("ARRAY Size = " + ((Object[])objArray.getArray()).length + ", DB Inserted Rows = " + insRows + ", loops = " + loopNo);
      } catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
      }
      System.out.println("SUC END");
    } else {
      System.out.println("Failed to make connection!");
    }
  }
}


7. Test Code: Java (Java Stored Procedure in Oracle RDBMS embedded JVM)



create or replace java source named "OracleJDBC" as

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.Struct;
import java.sql.Array;
import java.sql.SQLException;
import java.util.Vector;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.driver.OracleDriver;

// dbcallCtrl (3, 5)

public class OracleJDBC {
  static String TRCSTART = "begin trc_start; end;";
  static String TRCSTOP  = "begin trc_stop; end;";
  static String INSTAB   = "begin :1 := ins_tab(:2); end;";
   
  public static void dbcallCtrl(int loopCNT, int objCNT) throws SQLException{
    Connection conn = new OracleDriver().defaultConnection();
    CallableStatement cStmt;
    try {
      cStmt = conn.prepareCall(TRCSTART);
      cStmt.execute();
      cStmt.close();  
        
      for(int p=0; p < loopCNT; p++){
        dbcall(conn, p+1, objCNT);
      }
      
      cStmt = conn.prepareCall(TRCSTOP);
      cStmt.execute();
      cStmt.close(); 
    } catch (SQLException e) {
      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
    }         
  }
 
  static void dbcall(Connection conn, int loopNo, int objCNT) {
    CallableStatement cStmt;
    
    Object [] objField = new Object[2];
    Struct[] objStruct = new Struct[objCNT];
    Array objArray = null;
    
    if (conn != null) {
      System.out.println("You made it, call DBMS_PICKLER to select TYPE info from sys.type$ !");
      try {
        for(int i=0; i < objCNT; i++){
          objField[0] = i;       
          objField[1] = "Name_" + i; 
          objStruct[i] = conn.createStruct("TEST_OBJ", objField);
        }
        objArray = ((OracleConnection) conn).createOracleArray("TEST_OBJ_ARRAY", objStruct);
        
        cStmt = conn.prepareCall(INSTAB);
        cStmt.registerOutParameter(1, OracleTypes.INTEGER);
        cStmt.setArray(2, objArray);
        cStmt.execute();
        int insRows = cStmt.getInt(1);
        cStmt.close();
        //Object[] objArrayIns = (Object[])objArray.getArray();
        System.out.println("ARRAY Size = " + ((Object[])objArray.getArray()).length + ", DB Inserted Rows = " + insRows + ", loops = " + loopNo);
      } catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
      }
     System.out.println("SUC END");
   } else {
    System.out.println("Failed to make connection!");
   }
 }
}
/

create or replace procedure dbcallCtrl(loopNo number, objCNT number) as 
language java name 'OracleJDBC.dbcallCtrl(int, int)';
/

--set serveroutput on size 50000
--exec dbms_java.set_output(50000); 
--exec dbcallCtrl(3, 5);


8. Test Code: Java (Type Descriptor Retrieval Performance Fix by getTypeDescriptorsFromList)



import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.Vector;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.sql.TypeDescriptor;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleConnection;

// OracleJDBC  jdbcURL  loopCNT  objCNT
// OracleJDBC "jdbc:oracle:thin:k/s@testDB:1521:testDB" 3 5 

public class OracleJDBC {
  static String TRCSTART = "begin trc_start; end;";
  static String TRCSTOP  = "begin trc_stop; end;";
  static String INSTAB   = "begin :1 := ins_tab(:2); end;";
   
  public static void main(String[] args) {
    String jdbcURL = args[0];
    int loopCNT    = Integer.parseInt(args[1]);
    int objCNT     = Integer.parseInt(args[2]);
    CallableStatement cStmt;
    
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("Where is your Oracle JDBC Driver ?");
      e.printStackTrace();
      return;
    }
    
    Connection conn = null;
    try {
      conn = DriverManager.getConnection(jdbcURL);
      
      cStmt = conn.prepareCall(TRCSTART);
      cStmt.execute();
      cStmt.close();  
      
      String[][] schemaAndTypeNamePairs = {{"K","TEST_OBJ"}, {"K","TEST_OBJ_ARRAY"}};
      TypeDescriptor[] sqlTypes = ((OracleConnection) conn).getTypeDescriptorsFromList(schemaAndTypeNamePairs);
          
      for(int p=0; p < loopCNT; p++){
        dbcall(conn, p+1, objCNT, sqlTypes);
      }
      
      cStmt = conn.prepareCall(TRCSTOP);
      cStmt.execute();
      cStmt.close();      
    } catch (SQLException e) {
      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
      return;
    }
  }
 
  static void dbcall(Connection conn, int loopNo, int objCNT, TypeDescriptor[] sqlTypes) {
    CallableStatement cStmt;
    
    Object [] objField = new Object[2];
    STRUCT oneObj = null;
    Vector objVector = new Vector();
    ARRAY objArray = null;

    if (conn != null) {
      System.out.println("You made it, call DBMS_PICKLER to select TYPE info from sys.type$ !");
      try {
        StructDescriptor structDP = (StructDescriptor)sqlTypes[0];
        ArrayDescriptor  arrayDP  = (ArrayDescriptor)sqlTypes[1];
        
        for(int i=0; i < objCNT; i++){
          objField[0] = i;       
          objField[1] = "Name_" + i; 
          oneObj = new STRUCT(structDP, conn, objField);
          objVector.add(oneObj);
        }
        objArray = new ARRAY(arrayDP, conn, objVector.toArray()); 
        cStmt = conn.prepareCall(INSTAB);
        cStmt.registerOutParameter(1, OracleTypes.INTEGER);
        cStmt.setArray(2, objArray);
        cStmt.execute();
        int insRows = cStmt.getInt(1);
        cStmt.close();
        System.out.println("ARRAY Size = " + objArray.length() + ", DB Inserted Rows = " + insRows + ", loops = " + loopNo);
      } catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
      }
      System.out.println("END");
    } else {
      System.out.println("Failed to make connection!");
    }
  }
}

Wednesday, May 17, 2017

PLSQL Context Switch Functions and Cost

This Blog will try to investigate the cost of PLSQL context switch on the (speculated) Oracle internal function calls:
    (1) plsql_run
    (2) pfrrun
    (3) evapls
    (4) kgiPinObject
We will use Dtrace to acquire call count and duration in case of different 12c PLSQL features.

Blog: Oracle 12c: PRAGMA UDF – the truth debugs plsql_run calls of 12c PRAGMA UDF, and claims the impact of fetch size.

Blog: PL/SQL context switch calls Linux SystemTap to get stacktraces of first 3 functions, and applies perf to sample stacktraces, finally generates flamegraph. Because of sampling, it does not have an exact call counter, and Nyquist sampling theorem requires the sampling should be double fast.

The last function is discussed in Blog: Blog: Using DTrace to understand why PL/SQL packages can be faster than stored procedures . It uses Dtrace to compare PL/SQL packages and stored procedures performance, and demonstrates PL/SQL packages prevails the performance of stored procedures. Probably because PL/SQL packages are stateful objects, they have to be pinned in Library Cache. Whereas stored procedures are stateless, they can be freely discarded and re-loaded if necessary.

Even 4 popular PL/SQL errors are designated hereinafter for package state:
  ORA-04068: existing state of packages has been discarded
  ORA-04061: existing state of package %s has been invalidated
  ORA-04065: not executed, altered or dropped package %s
  ORA-06508: PL/SQL: could not find program unit being called:%s
This Blog will try to demonstrate that plsql_run, pfrrun and kgiPinObject are hardly to be the deciding factors of PLSQL context switch cost because lowing the calls and elapsed time of these 3 functions to an ignorable value does not produce the proportional benefits. Only evapls keeps constant and hence probably stays in the critical code path.

But all the above approaches only discuss context switch from SQL to PLSQL. It is still open to see how to investigate the inverse context switch from PLSQL to SQL.


1. Test


Run run_test_all (see appended code), we get the following result.

Note that 'base_sql_only' is an implementation in pure SQL, 'base_plsql_only' is one in pure PLSQL. Both will be served as base for context switch comparisons.
   
SQL > exec run_test_all(10000);
  base_sql_only      Elaplsed (ms) =       60
  base_plsql_only    Elaplsed (ms) =      790
  get_sql_cnt        Elaplsed (ms) =     2140
  get_udf_cnt        Elaplsed (ms) =     2130
  get_bulk_cnt       Elaplsed (ms) =     1130
  get_with_cnt       Elaplsed (ms) =     1100  
  update_loop_cnt    Elaplsed (ms) =     2940
  update_forall_cnt  Elaplsed (ms) =     2040

'get_with_cnt' is coded in 12c WITH Clause PL/SQL Function, which seems double fast. 'get_bulk_cnt' is also faster due to bulk collect.


2. Test with Dtrace


Run the similar test case in Sqlplus Window, and call Dtrace script (see appended code) to trace the process (pid 5678) by:
   ./plsql_cost.d -p 5678
we get the result as follows.

SQL > exec run_test('base_sql_only'      , 10000);
  plsql_run    Count = 2,     Total_time (ms) = 61,   Elpased (ms) = 71
  pfrrun       Count = 2,     Total_time (ms) = 61,   Elpased (ms) = 71
  evapls       Count = 0,     Total_time (ms) = 0,    Elpased (ms) = 0
  kgiPinObject Count = 10,    Total_time (ms) = 0,    Elpased (ms) = 70

SQL > exec run_test('base_plsql_only'   , 10000);
  plsql_run    Count = 2,     Total_time (ms) = 760,  Elpased (ms) = 763
  pfrrun       Count = 2,     Total_time (ms) = 760,  Elpased (ms) = 763
  evapls       Count = 0,     Total_time (ms) = 0,    Elpased (ms) = 0
  kgiPinObject Count = 9,     Total_time (ms) = 0,    Elpased (ms) = 763

SQL > exec run_test('get_sql_cnt'      , 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1296, Elpased (ms) = 2860
  pfrrun       Count = 10001, Total_time (ms) = 1175, Elpased (ms) = 2860
  evapls       Count = 10000, Total_time (ms) = 1770, Elpased (ms) = 2859
  kgiPinObject Count = 10009, Total_time (ms) = 112,  Elpased (ms) = 2860

SQL > exec run_test('get_udf_cnt'      , 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1291, Elpased (ms) = 3175
  pfrrun       Count = 10001, Total_time (ms) = 1170, Elpased (ms) = 3175
  evapls       Count = 10000, Total_time (ms) = 1773, Elpased (ms) = 3173
  kgiPinObject Count = 10009, Total_time (ms) = 116,  Elpased (ms) = 3175

SQL > exec run_test('get_bulk_cnt'     , 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1252, Elpased (ms) = 1720
  pfrrun       Count = 10001, Total_time (ms) = 1145, Elpased (ms) = 1720
  evapls       Count = 10000, Total_time (ms) = 1529, Elpased (ms) = 1708
  kgiPinObject Count = 10010, Total_time (ms) = 49,   Elpased (ms) = 1720

SQL > exec run_test('get_with_cnt'     , 10000);
  plsql_run    Count = 2,     Total_time (ms) = 0,    Elpased (ms) = 1664
  pfrrun       Count = 10001, Total_time (ms) = 1141, Elpased (ms) = 1664
  evapls       Count = 10000, Total_time (ms) = 1244, Elpased (ms) = 1653
  kgiPinObject Count = 10,    Total_time (ms) = 0,    Elpased (ms) = 1664
      
SQL > exec run_test('update_loop_cnt'  , 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1352, Elpased (ms) = 3760
  pfrrun       Count = 10001, Total_time (ms) = 1229, Elpased (ms) = 3760
  evapls       Count = 10000, Total_time (ms) = 1859, Elpased (ms) = 3756
  kgiPinObject Count = 10007, Total_time (ms) = 123,  Elpased (ms) = 3760

SQL > exec run_test('update_forall_cnt', 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1352, Elpased (ms) = 3083
  pfrrun       Count = 10001, Total_time (ms) = 1231, Elpased (ms) = 3083
  evapls       Count = 10000, Total_time (ms) = 1661, Elpased (ms) = 3077
  kgiPinObject Count = 10008, Total_time (ms) = 57,   Elpased (ms) = 3083


3. PLSQL_CODE_TYPE NATIVE


Changing plsql_code_type from default INTERPRETED to NATIVE
   alter system set plsql_code_type=native;
recompile the PLSQL Test Code, and repeat the same tests with Dtrace.

(check NATIVE comipled source by:
   select * from dba_stored_settings where object_name = 'BASE_SQL_ONLY';)

Run all test cases:

SQL > exec run_test_all(10000);
  base_sql_only      Elaplsed (ms) =       60
  base_plsql_only    Elaplsed (ms) =      800
  get_sql_cnt        Elaplsed (ms) =     2130
  get_udf_cnt        Elaplsed (ms) =     2110
  get_bulk_cnt       Elaplsed (ms) =     1110
  get_with_cnt       Elaplsed (ms) =     1040
  update_loop_cnt    Elaplsed (ms) =     2920
  update_forall_cnt  Elaplsed (ms) =     2040

There seems not much difference compared to INTERPRETED code.

Then test with Dtrace:

SQL > exec run_test('base_sql_only'   , 10000);
  plsql_run    Count = 2,     Total_time (ms) = 64,   Elpased (ms) = 73
  pfrrun       Count = 5,     Total_time (ms) = 0,    Elpased (ms) = 73
  evapls       Count = 0,     Total_time (ms) = 0,    Elpased (ms) = 0
  kgiPinObject Count = 7,     Total_time (ms) = 0,    Elpased (ms) = 73
  
SQL > exec run_test('base_plsql_only'   , 10000);
  plsql_run    Count = 2,     Total_time (ms) = 760,  Elpased (ms) = 763
  pfrrun       Count = 2,     Total_time (ms) = 760,  Elpased (ms) = 763
  evapls       Count = 0,     Total_time (ms) = 0,    Elpased (ms) = 0
  kgiPinObject Count = 9,     Total_time (ms) = 0,    Elpased (ms) = 763

SQL > exec run_test('get_sql_cnt'      , 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1171, Elpased (ms) = 2639
  pfrrun       Count = 4,     Total_time (ms) = 0,    Elpased (ms) = 2639
  evapls       Count = 10000, Total_time (ms) = 1604, Elpased (ms) = 2636
  kgiPinObject Count = 10002, Total_time (ms) = 111,  Elpased (ms) = 2639
  
SQL > exec run_test('get_udf_cnt'      , 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1175, Elpased (ms) = 3056
  pfrrun       Count = 4,     Total_time (ms) = 0,    Elpased (ms) = 3056
  evapls       Count = 10000, Total_time (ms) = 1642, Elpased (ms) = 3055
  kgiPinObject Count = 10004, Total_time (ms) = 110,  Elpased (ms) = 3056
  
SQL > exec run_test('get_bulk_cnt'     , 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1162, Elpased (ms) = 1953
  pfrrun       Count = 5,     Total_time (ms) = 0,    Elpased (ms) = 1953
  evapls       Count = 10000, Total_time (ms) = 1435, Elpased (ms) = 1942
  kgiPinObject Count = 10007, Total_time (ms) = 47,   Elpased (ms) = 1953
    
SQL > exec run_test('get_with_cnt'     , 10000);
  plsql_run    Count = 2,     Total_time (ms) = 0,    Elpased (ms) = 1655
  pfrrun       Count = 10005, Total_time (ms) = 1138, Elpased (ms) = 1655
  evapls       Count = 10000, Total_time (ms) = 1238, Elpased (ms) = 1644
  kgiPinObject Count = 5,     Total_time (ms) = 0,    Elpased (ms) = 1655
        
SQL > exec run_test('update_loop_cnt'  , 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1227, Elpased (ms) = 4377
  pfrrun       Count = 4,     Total_time (ms) = 0,    Elpased (ms) = 4377
  evapls       Count = 10000, Total_time (ms) = 1730, Elpased (ms) = 4373
  kgiPinObject Count = 10004, Total_time (ms) = 121,  Elpased (ms) = 4377
  
SQL > exec run_test('update_forall_cnt', 10000);
  plsql_run    Count = 10001, Total_time (ms) = 1235, Elpased (ms) = 3568
  pfrrun       Count = 5,     Total_time (ms) = 0,    Elpased (ms) = 3568
  evapls       Count = 10000, Total_time (ms) = 1549, Elpased (ms) = 3564
  kgiPinObject Count = 10004, Total_time (ms) = 59,   Elpased (ms) = 3568

By the way, changing optimization level from default 2 to 1 by
   alter system set plsql_optimize_level=1;
seems no impact.


4. Reasoning


Look all above test result, we can conclude the facts below:
(a). All approaches except 'get_with_cnt' have similar behavior, but different between INTERPRETED and NATIVE.

(b). NATIVE Compile eliminates pfrrun to the level of base tests in all cases except 'get_with_cnt'.

(c). 'get_with_cnt' has plsql_run and kgiPinObject close to base tests, and behaves same in NATIVE and INTERPRETED.
      Probably it is implemented as NATIVE compiled.

(d). 'kgiPinObject' has a comparable call count as that of plsql_run, but its duration is less than 10% of total elapsed time.
      Therefore, it alone seems having not so much contribution.

(e). Comparing all tests with base tests, we can see only evapls keeps constant 
      and hence probably stays in the critical code path.


5. PLSQL Test Code



drop table test_tab;
drop table test_tab2;

create table test_tab as select level id, rpad('ABC_'||level, 1000, 'x') name from dual connect by level <= 10000; 

create index test_tab#ix_id on test_tab(id);

select count(*) from test_tab;

create table test_tab2 as select * from test_tab;

create index test_tab2#ix_id on test_tab2(id);


create or replace function get_sql (p_id in number) return varchar2 is
  l_name varchar2(1000);
begin
  select name into l_name from test_tab where id = p_id;
  return l_name;
end;
/

create or replace function get_udf (p_id in number) return varchar2 is
  l_name varchar2(1000);
  PRAGMA UDF;
begin
  select name into l_name from test_tab where id = p_id;
  return l_name;
end;
/

create or replace procedure base_sql_only (p_cnt number) is
 type t_name_tab is table of test_tab.name%type;
 l_name_tab         t_name_tab   := t_name_tab();
begin
  select name bulk collect into l_name_tab from test_tab where rownum <= p_cnt;
  dbms_output.put_line('get_bulk_cnt.count = '||l_name_tab.count);
end;
/

create or replace procedure base_plsql_only (p_cnt number) is
 l_name varchar2(1000);
begin
 for i in 1 .. p_cnt loop
    l_name := rpad('ABC_'||i, 1000, 'x');
  end loop;
end;
/

create or replace procedure get_sql_cnt (p_cnt number) is
  l_name varchar2(1000);
begin
 for i in 1 .. p_cnt loop
    select get_sql(i) into l_name from test_tab where id = i;
  end loop;
end;
/

create or replace procedure get_udf_cnt (p_cnt number) is
  l_name varchar2(1000);
begin
 for i in 1 .. p_cnt loop
    select get_udf(i) into l_name from test_tab where id = i;
  end loop;
end;
/

create or replace procedure get_bulk_cnt (p_cnt number) is
 type t_name_tab is table of test_tab.name%type;
 l_name_tab         t_name_tab   := t_name_tab();
begin
  select get_sql(id) bulk collect into l_name_tab from test_tab where rownum <= p_cnt;
  dbms_output.put_line('get_bulk_cnt.count = '||l_name_tab.count);
end;
/

create or replace procedure get_with_cnt (p_cnt number) is
 type t_name_tab is table of test_tab.name%type;
 l_name_tab         t_name_tab   := t_name_tab();
begin
  execute immediate q'[
    with function get (p_id in number) return varchar2 is
      l_name varchar2(1000);
    begin
      select name into l_name from test_tab where id = p_id;
      return l_name;
    end;
    select get(id) from test_tab where rownum <= :p_cnt]'
    bulk collect into l_name_tab
    using p_cnt;
  dbms_output.put_line('get_with_cnt.count = '||l_name_tab.count);
end;
/

create or replace procedure update_loop_cnt (p_cnt number) is
  type t_id_tab is table of test_tab.id%type;
  l_id_tab         t_id_tab   := t_id_tab();
begin
 select id bulk collect into l_id_tab from test_tab where id <= p_cnt;
  for i in 1 .. p_cnt loop
    update test_tab2 set name = get_sql(id) where id = l_id_tab(i);
  end loop;
  commit;    
end;
/

create or replace procedure update_forall_cnt (p_cnt number) is
  type t_id_tab is table of test_tab.id%type;
  l_id_tab         t_id_tab   := t_id_tab();
begin
  select id bulk collect into l_id_tab from test_tab where id <= p_cnt;
    forall i in 1 .. p_cnt
      update test_tab2 set name = get_sql(id) where id = l_id_tab(i);
    dbms_output.put_line('update_forall_cnt.sql%rowcount = '||sql%rowcount);
    commit;    
end;
/

create or replace procedure run_test (p_case varchar2, p_cnt number) as
 l_start  number := dbms_utility.get_time;
begin
  case p_case
    when 'base_sql_only'     then base_sql_only(p_cnt);
    when 'base_plsql_only'   then base_plsql_only(p_cnt); 
    when 'get_sql_cnt'       then get_sql_cnt(p_cnt); 
    when 'get_udf_cnt'       then get_udf_cnt(p_cnt); 
    when 'get_bulk_cnt'      then get_bulk_cnt(p_cnt);
    when 'get_with_cnt'      then get_with_cnt(p_cnt);
    when 'update_loop_cnt'   then update_loop_cnt(p_cnt);
    when 'update_forall_cnt' then update_forall_cnt(p_cnt);     
  end case;
  dbms_output.put_line(rpad(p_case, 18)||' Elaplsed (ms) = '||lpad((dbms_utility.get_time - l_start)*10, 8));   
end;
/

create or replace procedure run_test_all(p_cnt number) as
begin
  run_test('base_sql_only'    , p_cnt);
  run_test('base_plsql_only'  , p_cnt);
  run_test('get_sql_cnt'      , p_cnt);
  run_test('get_udf_cnt'      , p_cnt);
  run_test('get_with_cnt'     , p_cnt);
  run_test('get_bulk_cnt'     , p_cnt);
  run_test('update_loop_cnt'  , p_cnt);
  run_test('update_forall_cnt', p_cnt);
end;
/


6. Dtrace Script: plsql_cost.d



#!/usr/sbin/dtrace -s

/* plsql_cost.d
 * chmod u+x plsql_cost.d
 * ./plsql_cost.d -p 
*/

BEGIN  {
  ttime_sr = 0; stime_sr = timestamp; etime_sr = timestamp;  /** plsql_run    **/
  ttime_pr = 0; stime_pr = timestamp; etime_pr = timestamp;  /** pfrrun       **/
  ttime_er = 0; stime_er = timestamp; etime_er = timestamp;  /** evapls       **/
  ttime_kp = 0; stime_kp = timestamp; etime_kp = timestamp;  /** kgiPinObject **/
} 

pid$target::plsql_run:entry { self->t_sr = timestamp; }
 
pid$target::pfrrun:entry    { self->t_pr = timestamp; } 
 
pid$target::evapls:entry    { self->t_er = timestamp; } 

pid$target::kgiPinObject:entry    { self->t_kp = timestamp; } 
  
pid$target::plsql_run:return /self->t_sr != 0/ { 
  @CNT_SR[probefunc] = count(); 
  ttime_sr   = ttime_sr + (timestamp - self->t_sr)/1000;
  self->t_sr = 0;
  etime_sr   = timestamp;
}
 
pid$target::pfrrun:return /self->t_pr != 0/ { 
  @CNT_PR[probefunc] = count(); 
  ttime_pr   = ttime_pr + (timestamp - self->t_pr)/1000;
  self->t_pr = 0;
  etime_pr   = timestamp;
} 
 
pid$target::evapls:return /self->t_er != 0/ { 
  @CNT_ER[probefunc] = count(); 
  ttime_er   = ttime_er + (timestamp - self->t_er)/1000;
  self->t_er = 0;
  etime_er   = timestamp;
}  
 
pid$target::kgiPinObject:return /self->t_kp != 0/ { 
  @CNT_KP[probefunc] = count(); 
  ttime_kp   = ttime_kp + (timestamp - self->t_kp)/1000;
  self->t_kp = 0;
  etime_kp   = timestamp;
}  
 
END { 
  /** plsql_run **/
  printa("\n%s    Count = %@d"     ,@CNT_SR);  
  printf(", Total_time (ms) = %d"  ,ttime_sr/1000);
  printf(", Elpased (ms) = %d\n"   ,(etime_sr - stime_sr)/1000000);
  
  /** pfrrun **/
  printa("%s       Count = %@d"    ,@CNT_PR);  
  printf(", Total_time (ms) = %d"  ,ttime_pr/1000);
  printf(", Elpased (ms) = %d\n"   ,(etime_pr - stime_pr)/1000000);
  
  /** evapls **/
  printa("%s       Count = %@d"    ,@CNT_ER);
  printf("%s", ttime_er == 0 ? "evapls       Count = 0":"");   
  printf(", Total_time (ms) = %d"  ,ttime_er/1000);
  printf(", Elpased (ms) = %d\n"   ,(etime_er - stime_er)/1000000); 
  
  /** kgiPinObject **/
  printa("%s Count = %@d"       ,@CNT_KP);   
  printf(", Total_time (ms) = %d"  ,ttime_kp/1000);
  printf(", Elpased (ms) = %d\n"   ,(etime_kp - stime_kp)/1000000);  
}


7. References


(1). The Joy of Low Hanging Fruit, Part 2: the Dismay and the Delight Steven Feuerstein  

(2). Oracle 12c: PRAGMA UDF – the truth Kamil Stawiarski

(3). PL/SQL context switch Frits Hoogland
 
(4). Using DTrace to understand why PL/SQL packages can be faster than stored procedures Kamil Stawiarski

Tuesday, May 2, 2017

Oracle Datetime (6) - PLSQL Arithmetic

(1)-Concepts      (2)-Examples      (3)-Assignments      (4)-Comparisons      (5)-SQL Arithmetic      (6)-PLSQL Arithmetic


PLSQL Datetime Arithmetic operators include:
    (1) One operand is datetime, another is number or interval, for example, Addition
    (2) Both operands are datetime, for example, Subtraction
For each case, run the Test Code and look the Output.

1. Number and Interval Arithmetic


Run code below:

--------------- Test Code --------------- 

create or replace procedure test_arithmetic_plsql as
  l_timestp_base      TIMESTAMP;   
  l_timestp_ltz_base  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz_base   TIMESTAMP WITH TIME ZONE;
  
  l_timestp           TIMESTAMP;   
  l_timestp_ltz       TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz        TIMESTAMP WITH TIME ZONE;
  l_intv              INTERVAL DAY TO SECOND;
  l_num               NUMBER;
  l_dump              varchar2(100);
begin
  l_num              := 5/1440;
  l_intv             := numtodsinterval(7,  'Minute');
  l_timestp_base     := to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_ltz_base := (l_timestp_base at time zone sessiontimezone);
  l_timestp_tz_base  := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
  
  dbms_output.put_line('------ 1. TIMESTAMP Arithmetics ------');
  l_timestp := l_timestp_base;
  
  dbms_output.put_line('l_timestp               = '||to_char(l_timestp, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump(l_timestp, 1016)) into l_dump from dual;
  dbms_output.put_line('l_timestp DUMP          : '||l_dump);
  dbms_output.put_line('');
  
  l_timestp := l_timestp_base + l_num;
  dbms_output.put_line('l_timestp+l_num         = '||to_char(l_timestp, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump((l_timestp), 1016)) into l_dump from dual;
  dbms_output.put_line('(l_timestp+l_num) DUMP  : '||l_dump); 
  dbms_output.put_line('');
  
  l_timestp := l_timestp_base + l_intv;
  dbms_output.put_line('l_timestp+l_intv        = '||to_char((l_timestp), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump((l_timestp), 1016)) into l_dump from dual;
  dbms_output.put_line('(l_timestp+l_intv) DUMP : '||l_dump); 
  dbms_output.put_line(''); 
  
  dbms_output.put_line('------ 2. TIMESTAMP WITH LOCAL TIME ZONE Arithmetics ------');
  l_timestp_ltz := l_timestp_ltz_base;
  
  dbms_output.put_line('l_timestp_ltz               = '||to_char(l_timestp_ltz, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump(l_timestp_ltz, 1016)) into l_dump from dual;
  dbms_output.put_line('l_timestp_ltz DUMP          : '||l_dump);
  dbms_output.put_line('');
  
  l_timestp_ltz := l_timestp_ltz_base+5/1440;
  dbms_output.put_line('l_timestp_ltz               = '||to_char((l_timestp_ltz), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump((l_timestp_ltz), 1016)) into l_dump from dual;
  dbms_output.put_line('(l_timestp_ltz+l_num) DUMP  : '||l_dump); 
  dbms_output.put_line('');
  
  l_timestp_ltz := l_timestp_ltz_base+l_intv;
  dbms_output.put_line('l_timestp_ltz               = '||to_char((l_timestp_ltz), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump((l_timestp_ltz), 1016)) into l_dump from dual;
  dbms_output.put_line('(l_timestp_ltz+l_intv) DUMP : '||l_dump); 
  dbms_output.put_line('');   
  
  dbms_output.put_line('------ 3. TIMESTAMP WITH TIME ZONE Arithmetics ------');
  l_timestp_tz := l_timestp_tz_base;
  
  dbms_output.put_line('l_timestp_tz               = '||to_char(l_timestp_tz, 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump(l_timestp_tz, 1016)) into l_dump from dual;
  dbms_output.put_line('l_timestp_tz DUMP          : '||l_dump);
  dbms_output.put_line('');
  
  l_timestp_tz := l_timestp_tz_base+5/1440;
  dbms_output.put_line('l_timestp_tz               = '||to_char((l_timestp_tz), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump((l_timestp_tz), 1016)) into l_dump from dual;
  dbms_output.put_line('(l_timestp_tz+l_num) DUMP  : '||l_dump); 
  dbms_output.put_line('');
  
  l_timestp_tz := l_timestp_tz_base+l_intv;
  dbms_output.put_line('l_timestp_tz               = '||to_char((l_timestp_tz), 'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump((l_timestp_tz), 1016)) into l_dump from dual;
  dbms_output.put_line('(l_timestp_tz+l_intv) DUMP : '||l_dump); 
  dbms_output.put_line('');  
end;
/

alter session set time_zone = 'Europe/Zurich';

exec test_arithmetic_plsql;

--------------- Test Output ---------------

  ------ 1. TIMESTAMP Arithmetics ------
  l_timestp               = 05*APR*2017 02:52:00 +00:00
  l_timestp DUMP          : Typ=180 Len=7: 78,75,4,5,3,35,1
  
  l_timestp+l_num         = 05*APR*2017 02:57:00 +00:00
  (l_timestp+l_num) DUMP  : Typ=180 Len=7: 78,75,4,5,3,3a,1
  
  l_timestp+l_intv        = 05*APR*2017 02:59:00 +00:00
  (l_timestp+l_intv) DUMP : Typ=180 Len=7: 78,75,4,5,3,3c,1
  
  ------ 2. TIMESTAMP WITH LOCAL TIME ZONE Arithmetics ------
  l_timestp_ltz               = 05*APR*2017 02:52:00 EUROPE/ZURICH CEST
  l_timestp_ltz DUMP          : Typ=231 Len=7: 78,75,4,5,2,35,1
  
  l_timestp_ltz               = 05*APR*2017 02:57:00 EUROPE/ZURICH CEST
  (l_timestp_ltz+l_num) DUMP  : Typ=231 Len=7: 78,75,4,5,2,3a,1
  
  l_timestp_ltz               = 05*APR*2017 02:59:00 EUROPE/ZURICH CEST
  (l_timestp_ltz+l_intv) DUMP : Typ=231 Len=7: 78,75,4,5,2,3c,1
  
  ------ 3. TIMESTAMP WITH TIME ZONE Arithmetics ------
  l_timestp_tz               = 05*APR*2017 02:52:00 EUROPE/ZURICH CEST
  l_timestp_tz DUMP          : Typ=181 Len=13: 78,75,4,5,1,35,1,0,0,0,0,86,58
  
  l_timestp_tz               = 05*APR*2017 02:57:00 EUROPE/ZURICH CEST
  (l_timestp_tz+l_num) DUMP  : Typ=181 Len=13: 78,75,4,5,1,3a,1,0,0,0,0,86,58
  
  l_timestp_tz               = 05*APR*2017 02:59:00 EUROPE/ZURICH CEST
  (l_timestp_tz+l_intv) DUMP : Typ=181 Len=13: 78,75,4,5,1,3c,1,0,0,0,0,86,58

Contrary to SQL Arithmetic (see previous Blog), PLSQL keeps the same datatype for both arithmetic wiht NUMBER and INTERVAL.


2. Substract Arithmetic


Note 2017-03-26 [02:00 03:00) 'Europe/Zurich' is DST transit time.

Run code below:

--------------- Test Code --------------- 

create or replace procedure test_subtract_plsql as
  -- 2017-03-26 [02:00, 03:00) DST transit time
  l_a1              varchar2(100) := '2017-03-26 01:52:00'; 
  l_a2              varchar2(100) := '2017-03-26 03:55:00';
  l_timestp_a1      TIMESTAMP;   
  l_timestp_a2      TIMESTAMP;   
  l_timestp_ltz_a1  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_ltz_a2  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz_a1   TIMESTAMP WITH TIME ZONE;
  l_timestp_tz_a2   TIMESTAMP WITH TIME ZONE;
  
  l_timestp_ltz_b1  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_ltz_b2  TIMESTAMP WITH LOCAL TIME ZONE;
  l_interval        INTERVAL DAY TO SECOND;
  l_dump            varchar2(50);
begin
  dbms_output.put_line('a1 = '||l_a1); 
  dbms_output.put_line('a2 = '||l_a2); 
  dbms_output.put_line('');
  
  l_timestp_a1     := to_timestamp(l_a1, 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_a2     := to_timestamp(l_a2, 'yyyy-mm-dd hh24:mi:ss');
  
  l_timestp_ltz_a1 := l_timestp_a1;
  l_timestp_ltz_a2 := l_timestp_a2;
  
  l_timestp_tz_a1  := to_timestamp_tz(l_a1||' Europe/Zurich CET',  'yyyy-mm-dd hh24:mi:ss TZR TZD');
  l_timestp_tz_a2  := to_timestamp_tz(l_a2||' Europe/Zurich CEST', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
  
  dbms_output.put_line('------ l_timestp_a2 - l_timestp_a1 ------');
  l_interval := l_timestp_a2 - l_timestp_a1;
  dbms_output.put_line('TIMESTAMP Subtract = '||l_interval); 
  dbms_output.put_line('');
  
  dbms_output.put_line('------ l_timestp_ltz_a2 - l_timestp_ltz_a1 ------');
  l_interval := l_timestp_ltz_a2 - l_timestp_ltz_a1;
  dbms_output.put_line('LOCAL TIME ZONE (a2 - a1) = '||l_interval);
  
  dbms_output.put_line('  a1 = '||to_char(l_timestp_ltz_a1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump(l_timestp_ltz_a1, 1016)) into l_dump from dual;
  dbms_output.put_line('     a1 Dump : '||l_dump);
  
  dbms_output.put_line('  a2 = '||to_char(l_timestp_ltz_a2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump(l_timestp_ltz_a2, 1016)) into l_dump from dual;
  dbms_output.put_line('     a2 Dump : '||l_dump);
  
  select (dump(l_interval, 1016)) into l_dump from dual;
  dbms_output.put_line('  (a2 - a1) Dump : '||l_dump);
  dbms_output.put_line('');
  
  dbms_output.put_line('------ l_timestp_ltz_b2 - l_timestp_ltz_b1 by TSZ assign ------');
  l_timestp_ltz_b1  := l_timestp_tz_a1;
  l_timestp_ltz_b2  := l_timestp_tz_a2;
  l_interval        := l_timestp_ltz_b2 - l_timestp_ltz_b1;
  dbms_output.put_line('LOCAL TIME ZONE by TZ Assign (b2 - b1) = '||l_interval);
  
  dbms_output.put_line('  b1 = '||to_char(l_timestp_ltz_b1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump(l_timestp_ltz_b1, 1016)) into l_dump from dual;
  dbms_output.put_line('     b1 Dump : '||l_dump);
  
  dbms_output.put_line('  b2 = '||to_char(l_timestp_ltz_b2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  select (dump(l_timestp_ltz_b2, 1016)) into l_dump from dual;
  dbms_output.put_line('     b2 Dump : '||l_dump);
  
  select (dump(l_interval, 1016)) into l_dump from dual;
  dbms_output.put_line('  (b2 - b1) Dump : '||l_dump); 
  dbms_output.put_line('');
  
  dbms_output.put_line('------ l_timestp_ltz_a1 - l_timestp_ltz_b1, l_timestp_ltz_a2 - l_timestp_ltz_b2 ------');
  l_interval := l_timestp_ltz_a1 - l_timestp_ltz_b1;
  dbms_output.put_line('(a1 - b1) = '||l_interval);
  select (dump(l_interval, 1016)) into l_dump from dual;
  dbms_output.put_line('    (a1 - b1) Dump : '||l_dump);
  
  l_interval := l_timestp_ltz_a2 - l_timestp_ltz_b2;
  dbms_output.put_line('(a2 - b2) = '||l_interval);
  select (dump(l_interval, 1016)) into l_dump from dual;
  dbms_output.put_line('    (a2 - b2) Dump : '||l_dump);
  dbms_output.put_line('');
  
  dbms_output.put_line('------ l_timestp_tz_a2 - l_timestp_tz_a1 ------');
  l_interval := l_timestp_tz_a2 - l_timestp_tz_a1;
  dbms_output.put_line('TIME ZONE (a2 - a1) = '||l_interval);
  dbms_output.put_line(''); 
  
  dbms_output.put_line('------ l_timestp_tz_a2 - l_timestp_tz_a1 by TLZ assign ------');
  l_timestp_tz_a1  := l_timestp_ltz_a1;
  l_timestp_tz_a2  := l_timestp_ltz_a2;
  l_interval := l_timestp_tz_a2 - l_timestp_tz_a1;
  dbms_output.put_line('TIME ZONE by Local Assign (a2 - a1) = '||l_interval);
  
  dbms_output.put_line('  a1 = '||to_char(l_timestp_tz_a1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  dbms_output.put_line('  a2 = '||to_char(l_timestp_tz_a2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));  
end;
/

alter session set time_zone = 'Europe/Zurich';
exec test_subtract_plsql;

--------------- Test Output ---------------

  a1 = 2017-03-26 01:52:00
  a2 = 2017-03-26 03:55:00
  
  ------ l_timestp_a2 - l_timestp_a1 ------
  TIMESTAMP Subtract = +00 02:03:00.000000
  
  ------ l_timestp_ltz_a2 - l_timestp_ltz_a1 ------
  LOCAL TIME ZONE (a2 - a1) = +00 02:03:00.000000
    a1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
       a1 Dump : Typ=231 Len=7: 78,75,3,1a,2,35,1
    a2 = 26*MAR*2017 03:55:00 EUROPE/ZURICH CEST
       a2 Dump : Typ=231 Len=7: 78,75,3,1a,3,38,1
    (a2 - a1) Dump : Typ=183 Len=11: 80,0,0,0,3e,3f,3c,80,0,0,0
  
  ------ l_timestp_ltz_b2 - l_timestp_ltz_b1 by TSZ assign ------
  LOCAL TIME ZONE by TZ Assign (b2 - b1) = +00 01:03:00.000000
    b1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
       b1 Dump : Typ=231 Len=7: 78,75,3,1a,2,35,1
    b2 = 26*MAR*2017 03:55:00 EUROPE/ZURICH CEST
       b2 Dump : Typ=231 Len=7: 78,75,3,1a,3,38,1
    (b2 - b1) Dump : Typ=183 Len=11: 80,0,0,0,3d,3f,3c,80,0,0,0
  
  ------ l_timestp_ltz_a1 - l_timestp_ltz_b1, l_timestp_ltz_a2 - l_timestp_ltz_b2
  (a1 - b1) = +00 00:00:00.000000
      (a1 - b1) Dump : Typ=183 Len=11: 80,0,0,0,3c,3c,3c,80,0,0,0
  (a2 - b2) = +00 00:00:00.000000
      (a2 - b2) Dump : Typ=183 Len=11: 80,0,0,0,3c,3c,3c,80,0,0,0
  
  ------ l_timestp_tz_a2 - l_timestp_tz_a1 ------
  TIME ZONE (a2 - a1) = +00 01:03:00.000000
  
  ------ l_timestp_tz_a2 - l_timestp_tz_a1 by TLZ assign ------
  TIME ZONE by Local Assign (a2 - a1) = +00 01:03:00.000000
    a1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
    a2 = 26*MAR*2017 03:55:00 EUROPE/ZURICH CEST

Above output shows that
  (a2 - a1) = +00 02:03:00.000000
  (b2 - b1) = +00 01:03:00.000000
although
  (a1 - b1) = +00 00:00:00.000000
  (a2 - b2) = +00 00:00:00.000000
and their dumps also confirmed the equivalence.

Oracle Datetime (5) - SQL Arithmetic

(1)-Concepts      (2)-Examples      (3)-Assignments      (4)-Comparisons      (5)-SQL Arithmetic      (6)-PLSQL Arithmetic


SQL Datetime Arithmetic operators include:
    (1) One operand is datetime, another is number or interval, for example, Addition
    (2) Both operands are datetime, for example, Subtraction
For each case, run the Test Code and look the Output.

1. Number and Interval Arithmetic


Note 2017-03-26 [02:00 03:00) 'Europe/Zurich' is DST transit time.

Run Test Code below:

--------------- Test Code --------------- 

alter session set time_zone = 'Europe/Zurich';

drop table sql_test_tab_add;
create table sql_test_tab_add (loc_ltz TIMESTAMP WITH LOCAL TIME ZONE);
insert into sql_test_tab_add values(localtimestamp);
commit;                            

drop view sql_test_tab_add_view;

create view sql_test_tab_add_view as 
with base as 
  (select 5/1440                                                                                      num
         ,numtodsinterval(7,  'Minute')                                                               intv
         ,to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss')                                timestp_base
         ,(to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss') at time zone sessiontimezone) ltz_base
         ,to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD')       tz_base 
         ,(select loc_ltz from sql_test_tab_add where rownum=1)                                       ltz_db  
   from dual
  )
  ,base_ops as
   (select base.*
          ,timestp_base + num  timestp_base_num     
          ,timestp_base + intv timestp_base_intv
          ,tz_base      + num  tz_base_num
          ,tz_base      + intv tz_base_intv
          ,ltz_db       + num  ltz_db_num
          ,ltz_db       + intv ltz_db_intv
    from base
  )
select 
  num               ,dump(num,  1016)               num_dump                -- Typ=2    SQLT_NUM
 ,intv              ,dump(intv,  1016)              intv_dump               -- Typ=190  SQLT_INTVERVAL_DS
 ,timestp_base      ,dump(timestp_base,  1016)      timestp_base_dump       -- Typ=187  SQLT_TIMESTAMP
 ,ltz_base          ,dump(ltz_base, 1016)           ltz_base_dump           -- Typ=188  SQLT_TIMESTAMP_TZ
 ,tz_base           ,dump(tz_base, 1016)            tz_base_dump            -- Typ=188  SQLT_TIMESTAMP_TZ
 ,ltz_db            ,dump(ltz_db, 1016)             ltz_db_dump             -- Typ=231  SQLT_TIMESTAMP_LTZ
 -------------------- NUMBER and INTERVAL Arithmetic  ------------------------
 ,timestp_base_num  ,dump(timestp_base_num,  1016)  timestp_base_num_dump   -- Typ=13   SQLT_DATE
 ,timestp_base_intv ,dump(timestp_base_intv,  1016) timestp_base_intv_dump  -- Typ=187  SQLT_TIMESTAMP
 ,tz_base_num       ,dump(tz_base_num, 1016)        tz_base_num_dump        -- Typ=13   SQLT_DATE
 ,tz_base_intv      ,dump(tz_base_intv, 1016)       tz_base_intv_dump       -- Typ=188  SQLT_TIMESTAMP_TZ
 ,ltz_db_num        ,dump(ltz_db_num,   1016)       ltz_db_num_dump         -- Typ=13   SQLT_DATE
 ,ltz_db_intv       ,dump(ltz_db_intv,   1016)      ltz_db_intv_dump        -- Typ=232  SQLT_TIMESTAMP_LTZ
from base_ops;

alter session set time_zone = 'Europe/London';
select * from sql_test_tab_add_view;

--------------- Test Output ---------------

  NUM                     : .003472222222222222222222222222222222222
  NUM_DUMP                : Typ=2 Len=21: bf,23,49,17,17,17,17,17,17,17,17,17,17,17,17,17,17,17,17,17,17
  INTV                    : +000000000 00:07:00.000000000
  INTV_DUMP               : Typ=190 Len=24: 0,0,0,0,0,0,0,0,7,0,0,0,0,0,0,0,0,0,0,0,a,0,0,0
  TIMESTP_BASE            : 2017*APR*05 02:52:00
  TIMESTP_BASE_DUMP       : Typ=187 Len=20: e1,7,4,5,2,34,0,0,0,0,0,0,0,0,3,0,0,0,0,0
  LTZ_BASE                : 2017-APR-05 02:52:00 EUROPE/LONDON BST
  LTZ_BASE_DUMP           : Typ=188 Len=20: e1,7,4,5,1,34,0,0,0,0,0,0,1,0,5,0,71,1,0,0
  TZ_BASE                 : 2017-APR-05 02:52:00 EUROPE/ZURICH CEST
  TZ_BASE_DUMP            : Typ=188 Len=20: e1,7,4,5,0,34,0,0,0,0,0,0,2,0,5,0,96,1,0,0
  LTZ_DB                  : 2017*MAY*02 08:39:08
  LTZ_DB_DUMP             : Typ=231 Len=11: 78,75,5,2,9,28,9,39,94,86,38
  TIMESTP_BASE_NUM        : 2017-APR-05 02:57:00
  TIMESTP_BASE_NUM_DUMP   : Typ=13 Len=8: e1,7,4,5,2,39,0,0
  TIMESTP_BASE_INTV       : 2017*APR*05 02:59:00
  TIMESTP_BASE_INTV_DUMP  : Typ=187 Len=20: e1,7,4,5,2,3b,0,0,0,0,0,0,0,0,3,0,0,0,0,0
  TZ_BASE_NUM             : 2017-APR-05 02:57:00
  TZ_BASE_NUM_DUMP        : Typ=13 Len=8: e1,7,4,5,2,39,0,0
  TZ_BASE_INTV            : 2017-APR-05 02:59:00 EUROPE/ZURICH CEST
  TZ_BASE_INTV_DUMP       : Typ=188 Len=20: e1,7,4,5,0,3b,0,0,0,0,0,0,2,0,5,0,96,1,0,0
  LTZ_DB_NUM              : 2017-MAY-02 08:44:08
  LTZ_DB_NUM_DUMP         : Typ=13 Len=8: e1,7,5,2,8,2c,8,0
  LTZ_DB_INTV             : 2017*MAY*02 08:46:08
  LTZ_DB_INTV_DUMP        : Typ=232 Len=20: e1,7,5,2,8,2e,8,0,38,86,94,39,1,0,7,0,0,0,0,0

We can see all arithmetic with INTERVAL maintains original data types, but arithmetic's with NUMBER will always convert to data type DATE. One strange case is LTZ_DB_DUMP is with Typ=231, but LTZ_DB_INTV_DUMP with Typ=232.

This is well documented in Datetime Data Types and Time Zone Support - Datetime and Interval Arithmetic:
    You can use NUMBER constants in arithmetic operations on date and timestamp values. Oracle Database internally converts timestamp values to date values before doing arithmetic operations on them with NUMBER constants.


2. Substract Arithmetic


Run code below:

--------------- Test Code --------------- 

drop table sql_test_tab_substract;
create table sql_test_tab_substract (
  a1              TIMESTAMP
 ,a2              TIMESTAMP
 ,diff_a2_a1      INTERVAL DAY TO SECOND
 ,ltz_a1          TIMESTAMP WITH LOCAL TIME ZONE
 ,ltz_a2          TIMESTAMP WITH LOCAL TIME ZONE
 ,diff_ltz_a2_a1  INTERVAL DAY TO SECOND
 ,tz_a1           TIMESTAMP WITH TIME ZONE
 ,tz_a2           TIMESTAMP WITH TIME ZONE
 ,diff_tz_a2_a1   INTERVAL DAY TO SECOND
 ,ltz_b1          TIMESTAMP WITH LOCAL TIME ZONE
 ,ltz_b2          TIMESTAMP WITH LOCAL TIME ZONE
 ,diff_ltz_b2_b1  INTERVAL DAY TO SECOND
 ,tz_b1           TIMESTAMP WITH TIME ZONE
 ,tz_b2           TIMESTAMP WITH TIME ZONE
 ,diff_tz_b2_b1   INTERVAL DAY TO SECOND
);

create or replace procedure sql_test_tab_substract_insert as
  -- 2017-03-26 [02:00, 03:00) DST transit time
  l_a1              varchar2(100) := '2017-03-26 01:52:00'; 
  l_a2              varchar2(100) := '2017-03-26 03:55:00';
  l_timestp_a1      TIMESTAMP;   
  l_timestp_a2      TIMESTAMP;   
  l_timestp_ltz_a1  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_ltz_a2  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz_a1   TIMESTAMP WITH TIME ZONE;
  l_timestp_tz_a2   TIMESTAMP WITH TIME ZONE;
  
  l_timestp_ltz_b1  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_ltz_b2  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz_b1   TIMESTAMP WITH TIME ZONE;
  l_timestp_tz_b2   TIMESTAMP WITH TIME ZONE;
  l_interval        INTERVAL DAY TO SECOND;
  l_dump            varchar2(50);
begin
  l_timestp_a1     := to_timestamp(l_a1, 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_a2     := to_timestamp(l_a2, 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_ltz_a1 := l_timestp_a1;
  l_timestp_ltz_a2 := l_timestp_a2;
  l_timestp_tz_a1  := to_timestamp_tz(l_a1||' Europe/Zurich CET',  'yyyy-mm-dd hh24:mi:ss TZR TZD');
  l_timestp_tz_a2  := to_timestamp_tz(l_a2||' Europe/Zurich CEST', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
  l_timestp_ltz_b1 := l_timestp_tz_a1;
  l_timestp_ltz_b2 := l_timestp_tz_a2;
  l_timestp_tz_b1  := l_timestp_ltz_a1;
  l_timestp_tz_b2  := l_timestp_ltz_a2; 
 
  insert into sql_test_tab_substract values (
     l_timestp_a1    
    ,l_timestp_a2
    ,(l_timestp_a2 - l_timestp_a1)         
    ,l_timestp_ltz_a1
    ,l_timestp_ltz_a2
    ,(l_timestp_ltz_a2 - l_timestp_ltz_a1) 
    ,l_timestp_tz_a1 
    ,l_timestp_tz_a2 
    ,(l_timestp_tz_a2 - l_timestp_tz_a1)   
    ,l_timestp_ltz_b1
    ,l_timestp_ltz_b2
    ,(l_timestp_ltz_b2 - l_timestp_ltz_b1) 
    ,l_timestp_tz_b1 
    ,l_timestp_tz_b2 
    ,(l_timestp_tz_b2 - l_timestp_tz_b1)   
  );
  commit;
end;
/

alter session set time_zone = 'Europe/Zurich';
exec sql_test_tab_substract_insert;

select * from sql_test_tab_substract;

--------------- Test Output ---------------

  A1               : 2017*MAR*26 01:52:00
  A2               : 2017*MAR*26 03:55:00
  DIFF_A2_A1       : +00 02:03:00.000000
  LTZ_A1           : 2017*MAR*26 01:52:00
  LTZ_A2           : 2017*MAR*26 03:55:00
  DIFF_LTZ_A2_A1   : +00 01:03:00.000000
  TZ_A1            : 2017-MAR-26 01:52:00 EUROPE/ZURICH CET
  TZ_A2            : 2017-MAR-26 03:55:00 EUROPE/ZURICH CEST
  DIFF_TZ_A2_A1    : +00 01:03:00.000000
  LTZ_B1           : 2017*MAR*26 01:52:00
  LTZ_B2           : 2017*MAR*26 03:55:00
  DIFF_LTZ_B2_B1   : +00 01:03:00.000000
  TZ_B1            : 2017-MAR-26 01:52:00 EUROPE/ZURICH CET
  TZ_B2            : 2017-MAR-26 03:55:00 EUROPE/ZURICH CEST
  DIFF_TZ_B2_B1    : +00 01:03:00.000000

The above output shows that DIFF_A2_A1 (02:03) is a purely math subtraction, and all other subtractions (01:03) are performed in natural sense.

Oracle Datetime (4) - Comparisons

(1)-Concepts      (2)-Examples      (3)-Assignments      (4)-Comparisons      (5)-SQL Arithmetic      (6)-PLSQL Arithmetic


There are 3 possible Datetime relational comparisons:
    (1) PLSQL vs. PLSQL                   
    (2) SQL   vs.   SQL                   
    (3) SQL   vs. PLSQL (or PLSQL vs. SQL)
For each combination, run the Test Code and look the Output.

1. PLSQL Comparisons


Note 2017-03-26 [02:00 03:00) 'Europe/Zurich' is DST transit time.

--------------- Test Code --------------- 

create or replace procedure test_comparison_plsql as
  l_timestp_1      TIMESTAMP;   
  l_timestp_2      TIMESTAMP;   
  l_timestp_ltz_1  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_ltz_2  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz_1   TIMESTAMP WITH TIME ZONE;
  l_timestp_tz_2   TIMESTAMP WITH TIME ZONE;
  l_interval       INTERVAL DAY TO SECOND;
  l_str            varchar2(40);
begin
  l_interval   := numtodsinterval(15,  'Minute');
  l_timestp_1  := to_timestamp('2017-03-26 01:52:00', 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_2  := l_timestp_1 + l_interval;
  dbms_output.put_line('l_timestp_1     = '||to_char(l_timestp_2,'DD*MON*YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_2     = '||to_char(l_timestp_2,'DD*MON*YYYY HH24:MI:SS'));
  
  l_timestp_ltz_1  := to_timestamp('2017-03-26 01:52:00', 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_ltz_2  := l_timestp_ltz_1 + l_interval;
  dbms_output.put_line('l_timestp_ltz_1 = '||to_char(l_timestp_ltz_1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  dbms_output.put_line('l_timestp_ltz_2 = '||to_char(l_timestp_ltz_2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  
  l_timestp_tz_1 := to_timestamp_tz('2017-03-26 01:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
  l_timestp_tz_2 := l_timestp_tz_1 + l_interval;
  dbms_output.put_line('l_timestp_tz_1  = '||to_char(l_timestp_tz_1,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  dbms_output.put_line('l_timestp_tz_2  = '||to_char(l_timestp_tz_2,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  
  dbms_output.put_line('');
  dbms_output.put_line('--------- CASE Comparisons ---------');
  case 
    when l_timestp_1     < l_timestp_2     and FALSE then dbms_output.put_line('l_timestp_1     < l_timestp_2     WIN');
    when l_timestp_ltz_1 < l_timestp_ltz_2 and FALSE then dbms_output.put_line('l_timestp_ltz_1 < l_timestp_ltz_2 WIN');
    when l_timestp_tz_1  < l_timestp_tz_2  and FALSE then dbms_output.put_line('l_timestp_tz_1  < l_timestp_tz_2  WIN');
    when l_timestp_1     < l_timestp_ltz_2 and FALSE then dbms_output.put_line('l_timestp_1     < l_timestp_ltz_2 WIN');
    when l_timestp_1     < l_timestp_tz_2  and FALSE then dbms_output.put_line('l_timestp_1     < l_timestp_tz_2  WIN');
    when l_timestp_ltz_1 < l_timestp_tz_2  and FALSE then dbms_output.put_line('l_timestp_ltz_1 < l_timestp_tz_2  WIN');
    else dbms_output.put_line('CASE Comparisons OK');
  end case;
  
  dbms_output.put_line('');
  dbms_output.put_line('--------- l_timestp_2 vs l_timestp_ltz_2 ---------');
  l_str := 'l_timestp_2 < l_timestp_ltz_2 ';
  begin
    l_interval := l_timestp_2 - l_timestp_ltz_2;           -- diff ORA-01878
    dbms_output.put_line('l_timestp_2 - l_timestp_ltz_2 = '||l_interval);
    if l_timestp_2 < l_timestp_ltz_2 then               
      dbms_output.put_line(l_str||'WIN');
    else
      dbms_output.put_line(l_str||'Lost');
    end if;  
    exception when others then dbms_output.put_line(l_str||'*** '||SQLERRM); 
  end;
   
  dbms_output.put_line('');
  dbms_output.put_line('--------- l_timestp_ltz_2 vs l_timestp_tz_2 ---------');
  l_str := 'l_timestp_ltz_2 < l_timestp_tz_2 ';
  begin
    l_interval := l_timestp_ltz_2 - l_timestp_tz_2;        -- diff OK
    dbms_output.put_line('l_timestp_ltz_2 - l_timestp_tz_2 = '||l_interval);
    if l_timestp_ltz_2 < l_timestp_tz_2 then               -- Comparison ORA-01878
      dbms_output.put_line(l_str||'WIN');
    else
      dbms_output.put_line(l_str||'Lost');
    end if;  
    exception when others then dbms_output.put_line(l_str||'*** '||SQLERRM); 
  end;
  
  dbms_output.put_line('');
  dbms_output.put_line('--------- l_timestp_tz_2 vs l_timestp_2 ---------');
  l_str := 'l_timestp_tz_2 < l_timestp_2 ';
  begin
    l_interval := l_timestp_tz_2 - l_timestp_2;            -- diff ORA-01878
    dbms_output.put_line('l_timestp_tz_2 - l_timestp_2 = '||l_interval);
    if l_timestp_tz_2 < l_timestp_2 then                    
      dbms_output.put_line(l_str||'WIN');
    else
      dbms_output.put_line(l_str||'Lost');
    end if;  
    exception when others then dbms_output.put_line(l_str||'*** '||SQLERRM); 
  end;
end;
/

alter session set time_zone = 'Europe/Zurich';
exec test_comparison_plsql;

--------------- Test Output ---------------

  l_timestp_1     = 26*MAR*2017 02:07:00
  l_timestp_2     = 26*MAR*2017 02:07:00
  l_timestp_ltz_1 = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
  l_timestp_ltz_2 = 26*MAR*2017 03:07:00 EUROPE/ZURICH CET
  l_timestp_tz_1  = 26*MAR*2017 01:52:00 EUROPE/ZURICH CET
  l_timestp_tz_2  = 26*MAR*2017 03:07:00 EUROPE/ZURICH CEST
  
  --------- CASE Comparisons ---------
  CASE Comparisons OK
  
  --------- l_timestp_2 vs l_timestp_ltz_2 ---------
  l_timestp_2 < l_timestp_ltz_2 *** ORA-01878: specified field not found in datetime or interval
  
  --------- l_timestp_ltz_2 vs l_timestp_tz_2 ---------
  l_timestp_ltz_2 - l_timestp_tz_2 = +00 00:00:00.000000
  l_timestp_ltz_2 < l_timestp_tz_2 *** ORA-01878: specified field not found in datetime or interval
  
  --------- l_timestp_tz_2 vs l_timestp_2 ---------
  l_timestp_tz_2 < l_timestp_2 *** ORA-01878: specified field not found in datetime or interval

Look the above output, we can see:
(1). For l_timestp_2 vs l_timestp_ltz_2, ORA-01878 appears when compute:
         l_interval := l_timestp_2 - l_timestp_ltz_2
     because it is trying to convert l_timestp_2 to Time Zone "EUROPE/ZURICH".
   
(2). For l_timestp_ltz_2 vs l_timestp_tz_2, compute:
         l_interval := l_timestp_ltz_2 - l_timestp_tz_2
     is OK.
   
     But the Comparison:
         l_timestp_ltz_2 < l_timestp_tz_2
     hits ORA-01878.
     
     If we look l_timestp_ltz_2: 
        l_timestp_ltz_2 = 26*MAR*2017 03:07:00 EUROPE/ZURICH CET
     it is marked with wrong Time Zone Abbreviation "CET" because "26*MAR*2017 03:07:00 EUROPE/ZURICH" has to be with "CEST".
     As we know Time Zone Abbreviation "CET" is defined as TZ Offset "+01:00".
     Probably Oracle is trying to convert "26*MAR*2017 03:07:00 EUROPE/ZURICH" to TZ Offset "+01:00", it results in a non-exist
     datetime "26*MAR*2017 02:07:00 EUROPE/ZURICH", hence ORA-01878.
     
     However, it is not clear why Arithmetic:
        l_timestp_ltz_2 - l_timestp_tz_2
     works.

(3). For l_timestp_tz_2 vs l_timestp_2, same as Point (1).


2. SQL Comparisons


TODO: Comparisons of table column values in SQL Where Clause.


3. SQL and PLSQL Comparisons


TODO: Comparisons between table column values and bind variables in SQL Where Clause.

Oracle Datetime (3) - Assignments

(1)-Concepts      (2)-Examples      (3)-Assignments      (4)-Comparisons      (5)-SQL Arithmetic      (6)-PLSQL Arithmetic


In this Blog, we will look 4 possible Datetime assignments:
    (1) PLSQL := PLSQL
    (2) SQL   :=   SQL
    (3) PLSQL :=   SQL
    (4) SQL   := PLSQL
For each combination, run the Test Code and look the Output.

1. PLSQL Assignments


--------------- Test Code --------------- 

create or replace procedure test_assignment_plsql as
  l_timestp      TIMESTAMP;   
  l_timestp_ltz  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz   TIMESTAMP WITH TIME ZONE;
begin
  dbms_output.put_line('------- From l_timestp to l_timestp_ltz, l_timestp_tz -------');
  l_timestp     := to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss');
  l_timestp_tz  := l_timestp;    -- append sessiontimezone
  l_timestp_ltz := l_timestp;    -- append sessiontimezone
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));  
  dbms_output.put_line('');
  
  dbms_output.put_line('------- From l_timestp_ltz to l_timestp, l_timestp_tz -------');
  l_timestp_ltz := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD'); -- convert to sessiontimezone
  l_timestp     := l_timestp_ltz;   -- copy Time, drop Zone
  l_timestp_tz  := l_timestp_ltz;   -- copy, l_timestp_ltz = l_timestp_tz
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));          
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD')); 
  dbms_output.put_line('');
   
  dbms_output.put_line('------- From l_timestp_tz to l_timestp, l_timestp_ltz -------');
  l_timestp_tz  := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
  l_timestp     := l_timestp_tz;    -- copy Time, drop Zone, not convertion
  l_timestp_ltz := l_timestp_tz;    -- convert to sessiontimezone, l_timestp_ltz != l_timestp_tz
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));
  dbms_output.put_line('l_timestp     = ' ||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));
end;
/

alter session set time_zone = 'Europe/London'; 
exec test_assignment_plsql;

--------------- Test Output ---------------

  ------- From l_timestp to l_timestp_ltz, l_timestp_tz -------
  l_timestp     = 05-APR-2017 02:52:00
  l_timestp_tz  = 05-APR-2017 02:52:00 EUROPE/LONDON BST
  l_timestp_ltz = 05*APR*2017 02:52:00 EUROPE/LONDON BST
  
  ------- From l_timestp_ltz to l_timestp, l_timestp_tz -------
  l_timestp_ltz = 05*APR*2017 01:52:00 EUROPE/LONDON BST
  l_timestp     = 05-APR-2017 01:52:00
  l_timestp_tz  = 05-APR-2017 01:52:00 EUROPE/LONDON BST
  
  ------- From l_timestp_tz to l_timestp, l_timestp_ltz -------
  l_timestp_tz  = 05-APR-2017 02:52:00 EUROPE/ZURICH CEST
  l_timestp     = 05-APR-2017 02:52:00
  l_timestp_ltz = 05*APR*2017 01:52:00 EUROPE/LONDON BST

In summary, we can say:
  (1). TO and FROM TIMESTAMP are pure copy of time portion, if Zone needed, take sessiontimezone as default Zone.
  (2). TIMESTAMP WITH LOCAL TIME ZONE to TIMESTAMP WITH TIME ZONE is string pure copy with sessiontimezone 
       as default Zone.
  (3). TIMESTAMP WITH TIME ZONE to TIMESTAMP WITH LOCAL TIME ZONE is conversion from Source Time Zone 
       to Target Time Zone (sessiontimezone).
       This is the only place where Time Zone is involved. All others are just pure String Shallow Copy.

2. SQL Assignments


Note 2017-03-26 [02:00 03:00) 'Europe/Zurich' is DST transit time.

--------------- Test Code --------------- 

drop table tz_test_tab;

create table tz_test_tab 
(name      VARCHAR2(15),
 loc_zone  VARCHAR2(15), 
 sys_date  DATE,
 sys_tz    TIMESTAMP WITH TIME ZONE, 
 loc_date  DATE,
 loc_ltz   TIMESTAMP WITH LOCAL TIME ZONE,
 loc_tz    TIMESTAMP WITH TIME ZONE, 
 utc       TIMESTAMP);

ALTER SESSION SET NLS_DATE_FORMAT ='YYYY-MON-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='YYYY*MON*DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='YYYY-MON-DD HH24:MI:SS TZR TZD';

col name     format a15
col loc_zone format a15
col sys_date format a25
col sys_tz   format a40
col loc_date format a25
col loc_ltz  format a35
col loc_tz   format a48
col utc      format a35

alter session set time_zone = 'Europe/Zurich';
insert into tz_test_tab values('Zurich', sessiontimezone, sysdate, systimestamp, 
                                current_date, localtimestamp, current_timestamp, sys_extract_utc(systimestamp));
                            
alter session set time_zone = 'Europe/London';
insert into tz_test_tab values('London', sessiontimezone, sysdate, systimestamp, 
                                current_date, localtimestamp, current_timestamp, sys_extract_utc(systimestamp));   

commit;

alter session set time_zone = 'Europe/Zurich';
select * from tz_test_tab;   

--------------- Test Output ---------------

  NAME             : Zurich
  LOC_ZONE         : Europe/Zurich
  SYS_DATE         : 2017-MAY-02 09:20:02
  SYS_TZ           : 2017-MAY-02 09:20:02 +02:00
  LOC_DATE         : 2017-MAY-02 09:20:02
  LOC_LTZ          : 2017*MAY*02 09:20:02
  LOC_TZ           : 2017-MAY-02 09:20:02 EUROPE/ZURICH CEST
  UTC              : 2017*MAY*02 07:20:02
  -----------------
  NAME             : London
  LOC_ZONE         : Europe/London
  SYS_DATE         : 2017-MAY-02 09:20:02
  SYS_TZ           : 2017-MAY-02 09:20:02 +02:00
  LOC_DATE         : 2017-MAY-02 08:20:02
  LOC_LTZ          : 2017*MAY*02 09:20:02
  LOC_TZ           : 2017-MAY-02 08:20:02 EUROPE/LONDON BST
  UTC              : 2017*MAY*02 07:20:02

From above output, we can see only loc_ltz is sessiontimezone sensitive, for example, for row of London, when time_zone = 'Europe/Zurich', it is converted to sessiontimezone.


3. SQL to PLSQL Assignments


Continue from above example, and assign row 'Zurich' to PLSQL.

--------------- Test Code --------------- 

create or replace procedure test_assignment_sql2plsql as
  l_timestp      DATE;   
  l_timestp_ltz  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz   TIMESTAMP WITH TIME ZONE;
begin
  dbms_output.put_line('------- From sys_date to l_timestp, l_timestp_ltz, l_timestp_tz -------');
  select sys_date, sys_date, sys_date into l_timestp, l_timestp_ltz, l_timestp_tz from tz_test_tab where name = 'Zurich';
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));  
  dbms_output.put_line('');
  
  dbms_output.put_line('------- From loc_ltz to l_timestp, l_timestp_ltz, l_timestp_tz -------');
  select loc_ltz, loc_ltz, loc_ltz into l_timestp, l_timestp_ltz, l_timestp_tz from tz_test_tab where name = 'Zurich';
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));  
  dbms_output.put_line('');
   
  dbms_output.put_line('------- From sys_tz to l_timestp, l_timestp_ltz, l_timestp_tz -------');
  select sys_tz, sys_tz, sys_tz into l_timestp, l_timestp_ltz, l_timestp_tz from tz_test_tab where name = 'Zurich';
  dbms_output.put_line('l_timestp     = '||to_char(l_timestp,'DD-MON-YYYY HH24:MI:SS'));
  dbms_output.put_line('l_timestp_tz  = '||to_char(l_timestp_tz,'DD-MON-YYYY HH24:MI:SS TZR TZD'));          
  dbms_output.put_line('l_timestp_ltz = '||to_char(l_timestp_ltz,'DD*MON*YYYY HH24:MI:SS TZR TZD'));  
  dbms_output.put_line('');
end;
/

alter session set time_zone = 'Europe/London';
exec test_assignment_sql2plsql;

--------------- Test Output ---------------

  ------- From sys_date to l_timestp, l_timestp_ltz, l_timestp_tz ------
  l_timestp     = 02-MAY-2017 09:20:02
  l_timestp_tz  = 02-MAY-2017 09:20:02 EUROPE/LONDON BST
  l_timestp_ltz = 02*MAY*2017 09:20:02 EUROPE/LONDON BST
  
  ------- From loc_ltz to l_timestp, l_timestp_ltz, l_timestp_tz -------
  l_timestp     = 02-MAY-2017 08:20:02
  l_timestp_tz  = 02-MAY-2017 08:20:02 EUROPE/LONDON BST
  l_timestp_ltz = 02*MAY*2017 08:20:02 EUROPE/LONDON BST
  
  ------- From sys_tz to l_timestp, l_timestp_ltz, l_timestp_tz -------
  l_timestp     = 02-MAY-2017 09:20:02
  l_timestp_tz  = 02-MAY-2017 09:20:02 +02:00
  l_timestp_ltz = 02*MAY*2017 08:20:02 EUROPE/LONDON BST

The above output shows that when we assign sys_tz to l_timestp_tz, time is converted from Zurich to London.


4. PLSQL to SQL Assignments


--------------- Test Code --------------- 

create or replace procedure test_assignment_plsql2sql as
  l_timestp      TIMESTAMP;   
  l_timestp_ltz  TIMESTAMP WITH LOCAL TIME ZONE;
  l_timestp_tz   TIMESTAMP WITH TIME ZONE;
begin
  dbms_output.put_line('------- From l_timestp to sys_date, loc_ltz, loc_tz -------');
  l_timestp     := to_timestamp('2017-04-05 02:52:00', 'yyyy-mm-dd hh24:mi:ss');
  insert into tz_test_tab (name, sys_date, loc_ltz, loc_tz) values('l_timestp', l_timestp, l_timestp, l_timestp);
  
  dbms_output.put_line('------- From l_timestp_ltz to sys_date, loc_ltz, loc_tz -------');
  l_timestp_ltz := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD'); 
  insert into tz_test_tab (name, sys_date, loc_ltz, loc_tz) values('l_timestp_ltz', l_timestp_ltz, l_timestp_ltz, l_timestp_ltz);
   
  dbms_output.put_line('------- From l_timestp_tz to sys_date, loc_ltz, loc_tz -------');
  l_timestp_tz  := to_timestamp_tz('2017-04-05 02:52:00 Europe/Zurich', 'yyyy-mm-dd hh24:mi:ss TZR TZD');
  insert into tz_test_tab (name, sys_date, loc_ltz, loc_tz) values('l_timestp_tz', l_timestp_tz, l_timestp_tz, l_timestp_tz);
  commit;
end;
/

truncate table tz_test_tab;

alter session set time_zone = 'Europe/Zurich';
exec test_assignment_plsql2sql;

alter session set time_zone = 'Europe/London';
select name, sys_date, loc_ltz, loc_tz from tz_test_tab;  

  --------------- Test Output ---------------
  
  NAME             : l_timestp
  SYS_DATE         : 2017-APR-05 02:52:00
  LOC_LTZ          : 2017*APR*05 01:52:00
  LOC_TZ           : 2017-APR-05 02:52:00 EUROPE/ZURICH CEST
  -----------------
  NAME             : l_timestp_ltz
  SYS_DATE         : 2017-APR-05 02:52:00
  LOC_LTZ          : 2017*APR*05 01:52:00
  LOC_TZ           : 2017-APR-05 02:52:00 EUROPE/ZURICH CEST
  -----------------
  NAME             : l_timestp_tz
  SYS_DATE         : 2017-APR-05 02:52:00
  LOC_LTZ          : 2017*APR*05 01:52:00
  LOC_TZ           : 2017-APR-05 02:52:00 EUROPE/ZURICH CEST

The above output shows that all loc_ltz are adjusted to the sessiontimezone.