Oracle SQL
  • LICENSE

Monthly Archives: July 2013

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

Posted on July 13, 2013 by Sayan Malakshinov Posted in 12c 9 Comments

Tim Hall perfectly describes in his excellent post how new extended datatypes are stored on Oracle 12c.
I just found interesting parameter “_scalar_type_lob_storage_threshold“ – “threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB” – This parameter is the max size in bytes, at which these data types will be stored “inline” as simple datatypes, without creation of the lob segments.
See little example:

Controlling store extended varchars as lob

SQL> @param_ _scalar_type_lob_storage_threshold;

NAME                                     VALUE        DEFLT        TYPE         DESCRIPTION
---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------
_scalar_type_lob_storage_threshold       4000         TRUE         number       threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB

SQL> select * from user_lobs;

no rows selected

SQL> create table T_4000(
  2         i int generated always as identity
  3        ,v1000 varchar2(1000)
  4        ,v4000 varchar2(4000)
  5        ,v4500 varchar2(4500)
  6       );

Table created.

SQL> alter system set "_scalar_type_lob_storage_threshold"=5000;

System altered.

SQL> create table T_5000(
  2         i int generated always as identity
  3        ,v1000 varchar2(1000)
  4        ,v4000 varchar2(4000)
  5        ,v4500 varchar2(4500)
  6       );

Table created.

SQL> select table_name,column_name ,data_type,data_type_mod,data_length,char_col_decl_length,char_length,char_used
  2  from user_tab_columns;

TABLE_NAME COLUMN_NAM DATA_TYPE  DAT DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C
---------- ---------- ---------- --- ----------- -------------------- ----------- -
T_4000     V4500      VARCHAR2              4500                 4500        4500 B
T_4000     V4000      VARCHAR2              4000                 4000        4000 B
T_4000     V1000      VARCHAR2              1000                 1000        1000 B
T_4000     I          NUMBER                  22                                0
T_5000     V4500      VARCHAR2              4500                 4500        4500 B
T_5000     V4000      VARCHAR2              4000                 4000        4000 B
T_5000     V1000      VARCHAR2              1000                 1000        1000 B
T_5000     I          NUMBER                  22                                0

8 rows selected.

SQL> select table_name,column_name,chunk,retention,cache,logging,encrypt,compression,deduplication,in_row,securefile
  2  from user_lobs;

TABLE_NAME COLUMN_NAM      CHUNK  RETENTION CACHE      LOGGING ENCR COMPRE DEDUPLICATION   IN_ SEC
---------- ---------- ---------- ---------- ---------- ------- ---- ------ --------------- --- ---
T_4000     V4500            8192            YES        YES     NO   NO     NO              YES YES

[collapse]

Note that there are no lobs for table t_5000!

Oracle 12c: Lateral, row_limiting_clause

Posted on July 5, 2013 by Sayan Malakshinov Posted in 12c, CBO, query optimizing 3 Comments

Previously i showed how we can optimize getting TopN rows sorted by field “B” for each distinct value “A” with undocumented “lateral” in previous versions of Oracle RDBMS.
But now it is documented!
Very simple example:

with t as (select level a from dual connect by level&amp;lt;=10)
select *
from t
    ,lateral(
             select *
             from dba_objects o
             where object_id=t.a
            )
;


Moreover, we can make now this optimization more stable and simple with row_limiting_clause:

With row_limiting_clause and multiset:

with t_unique( a ) as (
              select min(t1.a)
              from xt_test t1
              union all
              select (select min(t1.a) from xt_test t1 where t1.a&amp;gt;t.a)
              from t_unique t
              where a is not null
)
select/*+ use_nl(rids tt) */ *
from t_unique v
    ,table(
          cast(
               multiset(
                        select/*+ index_desc(tt ix_xt_test_ab) */ tt.rowid rid
                        from xt_test tt
                        where tt.a=v.a
                        order by tt.b desc
                        fetch first 5 rows only
                       )
               as sys.odcivarchar2list
              )
          ) rids
    ,xt_test tt
where tt.rowid=rids.column_value
order by tt.a,tt.b desc

[collapse]
With row_limiting_clause and lateral:

with t_unique( a ) as (
              select min(t1.a)
              from xt_test t1
              union all
              select next_a
              from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a&amp;gt;t.a) r
              where t.a is not null
)
select/*+ use_nl(v r t) leading(v r t) */  t.*
from t_unique v
    ,lateral(
              select/*+ index_desc(tt ix_xt_test_ab) */ rowid rid
              from xt_test tt
              where tt.a=v.a
              order by b desc
              fetch first 5 rows only
     ) r
    ,xt_test t
where r.rid=t.rowid

[collapse]

Unfortunately, the recursive_subquery_clause with scalar subqueries sometimes doesn’t work:

Spoiler

SQL> with t_unique( a ) as (
  2                select min(t1.a)
  3                from xt_test t1
  4                union all
  5                select (select min(t1.a) from xt_test t1 where t1.a&amp;gt;t.a)
  6                from t_unique t
  7                where a is not null
  8  )
  9  select/*+ use_nl(v r) */  *
 10  from t_unique v
 11      ,lateral(
 12                select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
 13                from xt_test tt
 14                where tt.a=v.a
 15                order by tt.a, b desc
 16                fetch first 5 rows only
 17       ) r
 18  order by r.a,r.b desc;
              from xt_test t1
                   *
ERROR at line 3:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []

[collapse]

But I think oracle will fix it soon, because this ORA-600 can be solved easily with hint “materialize”, but it’s not so good:

Spoiler

SQL> with t_unique( a ) as (
  2                select min(t1.a)
  3                from xt_test t1
  4                union all
  5                select (select min(t1.a) from xt_test t1 where t1.a&amp;gt;t.a)
  6                from t_unique t
  7                where a is not null
  8  ), v as (
  9                select--+ materialize
 10                  *
 11                from t_unique
 12  )
 13  select/*+ use_nl(v r) */  *
 14  from v
 15      ,lateral(
 16                select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
 17                from xt_test tt
 18                where tt.a=v.a
 19                order by tt.a, b desc
 20                fetch first 5 rows only
 21       ) r
 22  order by r.a,r.b desc;

150 rows selected.

Elapsed: 00:00:01.01

Statistics
----------------------------------------------------------
         10  recursive calls
          8  db block gets
      11824  consistent gets
          1  physical reads
        624  redo size
       4608  bytes sent via SQL*Net to client
        462  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
         64  sorts (memory)
          0  sorts (disk)
        150  rows processed

[collapse]

UPDATE: There is a better solution:

Spoiler

SQL> with t_unique( a ) as (
  2                select min(t1.a)
  3                from xt_test t1
  4                union all
  5                select (select min(t1.a) from xt_test t1 where t1.a&amp;gt;t.a)
  6                from t_unique t
  7                where a is not null
  8  ), v as (
  9                select * from t_unique
 10                  union all
 11                select null from dual where 1=0 -- &amp;lt;&amp;lt;-- workaround
 12  )
 13  select/*+ use_nl(v r) */  *
 14  from v
 15      ,lateral(
 16                select/*+ index_desc(tt ix_xt_test_ab) */ tt.*
 17                from xt_test tt
 18                where tt.a=v.a
 19                order by tt.a, b desc
 20                fetch first 5 rows only
 21       ) r
 22  order by r.a,r.b desc;

[collapse]

And note that we can’t use now row_limiting_clause in cursor’s:

cursor(...row_limiting_clause)

SQL> with
  2    t_unique( a ) as (
  3                select min(t1.a)
  4                from xt_test t1
  5                union all
  6                select next_a
  7                from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a&amp;gt;t.a) r
  8                where t.a is not null
  9  )
 10  select
 11    cursor(
 12         select *
 13         from xt_test t
 14         where t.a=v.a
 15         order by a,b desc
 16         fetch first 5 rows only
 17    ) c
 18  from t_unique v
 19  ;
with
*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-00600: internal error code, arguments: [kokbcvb1], [], [], [], [], [], [], [], [], [], [], []

[collapse]
And, just for fun, with inline pl/sql function(inconsistent):

SQL> with
  2    function f(v_a int)
  3      return sys.ku$_vcnt
  4    as
  5      res sys.ku$_vcnt;
  6    begin
  7      select tt.rowid as rid
  8          bulk collect into res
  9      from xt_test tt
 10      where tt.a = v_a
 11      order by a,b desc
 12      fetch first 5 rows only;
 13      return res;
 14    end;
 15
 16    t_unique( a ) as (
 17                select min(t1.a)
 18                from xt_test t1
 19                union all
 20                select next_a
 21                from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a&amp;gt;t.a) r
 22                where t.a is not null
 23    )
 24  select/*+ use_nl(v r t) leading(v r t) */  t.*
 25  from t_unique v
 26      ,table(f(v.a)) r
 27      ,xt_test t
 28  where r.column_value=t.rowid;
 29  /

150 rows selected.

Elapsed: 00:00:00.06

Statistics
----------------------------------------------------------
         31  recursive calls
          0  db block gets
        173  consistent gets
          0  physical reads
          0  redo size
       5657  bytes sent via SQL*Net to client
        642  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
         32  sorts (memory)
          0  sorts (disk)
        150  rows processed

[collapse]
lateral oracle undocumented behaviour recursive_subquery_clause row_limiting_clause undocumented oracle

Oracle 12c: Inconsistency of Inline “with” functions

Posted on July 3, 2013 by Sayan Malakshinov Posted in 12c, oracle, PL/SQL 2 Comments

I was hoping that if inline “with” functions are in the query, so their results will be consistent with it (as operators), but unfortunately such functions returns also inconsistent results as standalone pl/sql functions.

SQL> create table t as select 1 a from dual;
Table created.

SQL> declare
  2    j binary_integer;
  3  begin
  4    dbms_job.submit( j
  5                    ,'begin
  6                        for i in 1..10 loop
  7                          dbms_lock.sleep(1);
  8                          update t set a=a+1;
  9                          commit;
 10                        end loop;
 11                      end;'
 12                   );
 13    commit;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL> with
  2     function f return int is
  3       res int;
  4     begin
  5       dbms_lock.sleep(1);
  6       select a into res from t;
  7       return res;
  8     end;
  9  select
 10     f
 11  from dual
 12  connect by level<=10;
 13  /

         F
----------
         1
         1
         1
         2
         3
         4
         5
         6
         7
         8

10 rows selected.

Interesting: Jonathan Lewis wrote that inline “deterministic” functions doesn’t use caching mechanism as standalone deterministic functions.

12c consistency deterministic functions pl/sql functions
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