Oracle SQL
  • LICENSE

Oracle 12c: behavior tests of the Inline functions, “Identities” and “defaults”

Posted on July 13, 2013 by Sayan Malakshinov Posted in 12c, curious, undocumented 4 Comments

I have done several minitests:
1. SQL and PL/SQL engines: which functions will be executed if there are two functions with same name as in SQL, as in PL/SQL (like “USER”, LPAD/RPAD, etc..)
– PL/SQL.

PL/SQL

SQL> @trace_on
Enter value for trace_identifier: inline
Enter value for level: 12
Tracing was enabled:

TRACEFILE_NAME
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xtsql/xtsql/trace/xtsql_ora_21599_inline.trc

SQL> with
  2  function inline_user return varchar2 is
  3  begin
  4   return user;
  5  end;
  6  select
  7    inline_user
  8  from dual
  9  /

INLINE_USER
------------------------------
XTENDER

1 row selected.

SQL> @trace_off
-- unlike SQL's "USER", PL/SQL function SYS.STANDARD.USER recursively executes "select user from sys.dual":
SQL> !grep USER /u01/app/oracle/diag/rdbms/xtsql/xtsql/trace/xtsql_ora_21599_inline.trc
SELECT USER FROM SYS.DUAL

SQL>

[collapse]

2. Will there be any context switches if we call the inline functions which contain another pl/sql functions/procedures?
– Yes

Test 1

SQL> sho parameter max_string

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
max_string_size                      string       STANDARD

SQL> @trace_pl_on

Session altered.

SQL> with
  2  function blabla(p_str varchar2) return varchar2 is
  3  begin
  4   return lpad(p_str, 5000, '*');
  5  end;
  6  select
  7    length(blabla(dummy)) lpad_plsql
  8  from dual;
  9  /
from dual
     *
ERROR at line 8:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

SQL> @trace_pl_last.sql

     RUNID  EVENT_SEQ EVENT_COMMENT                    EVENT_UNIT_OWNER   EVENT_UNIT
---------- ---------- -------------------------------- ------------------ -----------
         1          1 PL/SQL Trace Tool started
         1          2 Trace flags changed
         1          3 PL/SQL Virtual Machine started                      <anonymous>
         1          4 PL/SQL Internal Call                                <anonymous>
         1          5 PL/SQL Virtual Machine stopped

[collapse]

Test 2

SQL> @trace_pl_on

Session altered.

SQL> create or replace function f_standalone(p varchar2) return varchar2 is
  2  begin
  3    return lpad('x',3)||p;
  4  end;
  5  /

Function created.

SQL> with
  2  function blabla(p_str varchar2) return varchar2 is
  3     s varchar2(32767);
  4  begin
  5     s:=    lpad(p_str, 100, '1');
  6     s:= s||s;
  7     s:= s||lpad(p_str, 100, '3');
  8     s:= s||s;
  9     s:= s||(1+10);
 10     s:= f_standalone(s);
 11     s:= f_standalone(s);
 12     s:= f_standalone(s);
 13     return s;
 14  end;
 15  select
 16    length(blabla(dummy)) lpad_plsql
 17  from dual
 18  /

LPAD_PLSQL
----------
       611

SQL> @trace_pl_last.sql

     RUNID  EVENT_SEQ EVENT_COMMENT                    EVENT_UNIT_OWNER  EVENT_UNIT
---------- ---------- -------------------------------- ----------------- ------------
         2          1 PL/SQL Trace Tool started
         2          2 Trace flags changed
         2          3 PL/SQL Virtual Machine started                     <anonymous>
         2          4 PL/SQL Internal Call                               <anonymous>
         2          5 PL/SQL Virtual Machine stopped
         2          6 PL/SQL Virtual Machine started                     <anonymous>
         2          7 PL/SQL Virtual Machine started                     <anonymous>
         2          8 PL/SQL Internal Call                               <anonymous>
         2          9 PL/SQL Virtual Machine stopped
         2         10 PL/SQL Virtual Machine stopped
         2         11 PL/SQL Virtual Machine started                     <anonymous>
         2         12 PL/SQL Virtual Machine started                     <anonymous>
         2         13 PL/SQL Internal Call                               <anonymous>
         2         14 PL/SQL Virtual Machine stopped
         2         15 PL/SQL Virtual Machine stopped
         2         16 PL/SQL Virtual Machine started                     <anonymous>
         2         17 PL/SQL Internal Call                               <anonymous>
         2         18 PL/SQL Internal Call                               <anonymous>
         2         19 Procedure Call                                     <anonymous>
         2         20 PL/SQL Internal Call             XTENDER           F_STANDALONE
         2         21 Return from procedure call       XTENDER           F_STANDALONE
         2         22 Procedure Call                                     <anonymous>
         2         23 PL/SQL Internal Call             XTENDER           F_STANDALONE
         2         24 Return from procedure call       XTENDER           F_STANDALONE
         2         25 Procedure Call                                     <anonymous>
         2         26 PL/SQL Internal Call             XTENDER           F_STANDALONE
         2         27 Return from procedure call       XTENDER           F_STANDALONE
         2         28 PL/SQL Virtual Machine stopped

28 rows selected.

[collapse]

Test 3

SQL> @trace_pl_on

Session altered.

SQL> with
  2  function blabla(p_str varchar2) return varchar2 is
  3     s varchar2(32767);
  4  begin
  5     s:=    lpad(p_str, 100, '1');
  6     s:= s||s;
  7     s:= s||lpad(p_str, 100, '3');
  8     s:= s||s;
  9     s:= s||(1+10);
 10     return s;
 11  end;
 12  select
 13    length(blabla(dummy)) lpad_plsql
 14  from dual
 15  /

LPAD_PLSQL
----------
       602

1 row selected.

SQL> @trace_pl_last.sql

     RUNID  EVENT_SEQ EVENT_COMMENT                    EVENT_UNIT_OWNER   EVENT_UNIT
---------- ---------- -------------------------------- ------------------ ------------
         3          1 PL/SQL Trace Tool started
         3          2 Trace flags changed
         3          3 PL/SQL Virtual Machine started                      <anonymous>
         3          4 PL/SQL Internal Call                                <anonymous>
         3          5 PL/SQL Internal Call                                <anonymous>
         3          6 PL/SQL Virtual Machine stopped

6 rows selected.

[collapse]

3. How IDENTITY works?
For all identity columns Oracle creates a sequence with name like “ISEQ$$_XXX”, where “XXX” is the object_id of the table. All identities we can get through DBA_TAB_IDENTITY_COLS.
All Identity sequences:

select i.*
      ,tab.owner       tab_owner
      ,tab.object_name tab_name
      ,sq.object_name  sequence_name
from sys.idnseq$ i
    ,dba_objects tab
    ,dba_objects sq
where tab.object_id=i.obj#
  and sq.object_id = i.seqobj#

And we can see usage of this sequence in plans:

SQL_ID  fn5tjw6hu0dtn, child number 0
-------------------------------------
insert into xt_identity (description) values('1')

Plan hash value: 3838626111

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | Cost  | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |      1 |     1 |      0 |00:00:00.01 |      35 |
|   1 |  LOAD TABLE CONVENTIONAL |              |      1 |       |      0 |00:00:00.01 |      35 |
|   2 |   SEQUENCE               | ISEQ$$_91720 |      1 |       |      1 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------

[collapse]

4. When executes “default seq.nextval”?
Test

SQL> create sequence xt_sq1;
SQL> create sequence xt_sq2;
SQL> create table xt_default(
  2      id1 int default xt_sq1.nextval
  3    , pad varchar2(30)
  4    , id2 int default xt_sq2.nextval
  5  );

Table created.

SQL> insert into xt_default(pad) values('1');

1 row created.

SQL> select xt_sq1.currval, xt_sq2.currval from dual;

   CURRVAL    CURRVAL
---------- ----------
         1          1

SQL> insert into xt_default(pad) values(1/0);
insert into xt_default(pad) values(1/0)
                                    *
ERROR at line 1:
ORA-01476: divisor is equal to zero


SQL> select xt_sq1.currval, xt_sq2.currval from dual;

   CURRVAL    CURRVAL
---------- ----------
         2          2

[collapse]

12c undocumented oracle
« Oracle 12c: Extended varchars
Just link to my old package for os commands execution »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL