Oracle SQL

    Category Archives: undocumented

    Bug with integer literals in PL/SQL

    Posted on December 9, 2017 by Sayan Malakshinov Posted in curious, oracle, PL/SQL, PL/SQL optimization, undocumented Leave a comment

    This interesting question was posted on our russian forum yesterday:

    We have a huge PL/SQL package and this simple function returns wrong result when it’s located at the end of package body:

    create or replace package body PKGXXX as
      ...
      function ffff return number is
      nRes number;
      begin        
        nRes :=  268435456;
        return nRes;
      end;
    end;
    /
    

    But it works fine in any of the following cases:
    * replace 268435456 with power(2, 28), or
    * replace 268435456 with small literal like 268, or
    * move this function to the beginning of package body

    The one of the interesting findings was that the returned value is equal to the one of literals in another function.
    We can reproduce this bug even with an anonymous pl/sql block. The following test case uses 32768 integer literals from 1000001 to 1032768 and prints 5 other integers:

    declare n number;
    begin
      n:=1000001; -- this part
      n:=1000002; -- creates
      n:=1000003; -- 32768 
       ...        -- integer
      n:=1032768; -- literals
        dbms_output.put_line('100000='||100000); -- it should print: 100000=100000
        dbms_output.put_line('32766 ='||32766);
        dbms_output.put_line('32767 ='||32767);    
        dbms_output.put_line('32768 ='||32768);
        dbms_output.put_line('32769 ='||32769);
    end;
    
    Test code
    declare
       c clob:='declare n number;begin'||chr(10);
       f varchar2(100):='n:=%s;'||chr(10);
       v varchar2(32767);
       n number:=32768;
    begin
       for i in 1..n loop
          v:=v||utl_lms.format_message(f,to_char(1e7+i));
          if length(v)>30000 then
             c:=c||v;
             v:='';
          end if;
       end loop;
       v:=v||q'[
        dbms_output.put_line('100000='||100000);
        dbms_output.put_line('32766 ='||32766);
        dbms_output.put_line('32767 ='||32767);    
        dbms_output.put_line('32768 ='||32768);
        dbms_output.put_line('32769 ='||32769);
       end;
       ]';
       c:=c||v;
       execute immediate c;
    end;
    /
    

    [collapse]
    It produces the following output:

    100000=10000001
    32766 =32766
    32767 =32767
    32768 =10000002
    32769 =10000003
    

    This test case well demonstrates wrong results:
    * instead of 100000 we get 10000001, which is the value from first line after “begin”, ie 1st integer literal in the code,
    * for 32766 and 32767 oracle returns right values
    * instead of 32768 (==32767+1) it returns 10000002, which is the integer from 2nd line, ie 2nd integer literal in the code,
    * instead of 32769 (==32767+2) it returns 10000003, which is the integer from 3rd line, ie 3rd integer literal in the code
    After several tests I can make a conclusion:

    • It doesn’t matter what plsql_optimize_level or plsql_code_type you set, was debug enabled or not, the behaviour is the same.
    • It seems that this is a kind of PL/SQL optimization: during parsing, oracle leaves integer literal in place if its value is in range -32768..32767 (16bit signed int), but if its value is out of this range, oracle adds this value into array of integers’ constants and replaces the value with the index of this element in this array. But because of index value overflow in cases when a count of such integer literals becomes larger than 32768, instead of Nth element of this array, oracle returns Mth element, where M is mod(N,32767).

    So we can describe this behaviour using first test case:

    declare n number;
    begin
      n:=1000001; -- this part
      n:=1000002; -- creates
      n:=1000003; -- 32768 
       ...        -- integer
      n:=1032768; -- literals
        dbms_output.put_line('100000='||100000); -- it should print 100000, ie 32768th element of array, but prints 10000001
                                                 -- where 10000001 is the 1st element of array (1==mod(32768,32767))
        dbms_output.put_line('32766 ='||32766);  -- these 2 lines print right values,
        dbms_output.put_line('32767 ='||32767);  -- because their values are in the range of -32768..32767
        dbms_output.put_line('32768 ='||32768);  -- this line contains 32769th element and prints 2nd element of array (2==mod(32769,32767))
        dbms_output.put_line('32769 ='||32769);  -- this line contains 32770th element and prints 3nd element of array (3==mod(32770,32767))
    end;
    

    The following query can help you to find objects which can potentially have this problem:

    select
      s.owner,s.name,s.type
     ,sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)')) nums_count -- this regexp counts integer literals >= 30000
    from dba_source s 
    where 
        owner='&owner'
    and type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
    group by s.owner,s.name,s.type
    having sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)'))>32767 -- filter only objects which have >=32767 integer literal
    

    Workaround:
    You may noticed that I wrote about INTEGER literals only, so the easiest workaround is to make them FLOAT – just add “.” to the end of each literal:

    declare n number;
    begin
      n:=1000001.;
      n:=1000002.;
      n:=1000003.;
       ...       
      n:=1032768.;
        dbms_output.put_line('100000='||100000.);
        dbms_output.put_line('32766 ='||32766.);
        dbms_output.put_line('32767 ='||32767.);    
        dbms_output.put_line('32768 ='||32768.);
        dbms_output.put_line('32769 ='||32769.);
    end;
    
    Fixed test cases

    declare
       c clob:='declare n number;begin'||chr(10);
       f varchar2(100):='n:=%s.;'||chr(10); -- I've added here "."
       v varchar2(32767);
       n number:=32768;
    begin
       for i in 1..n loop
          v:=v||utl_lms.format_message(f,to_char(1e7+i));
          if length(v)>30000 then
             c:=c||v;
             v:='';
          end if;
       end loop;
       v:=v||q'[
        dbms_output.put_line('100000='||100000.); -- .
        dbms_output.put_line('32766 ='||32766.);
        dbms_output.put_line('32767 ='||32767.);    
        dbms_output.put_line('32768 ='||32768.);
        dbms_output.put_line('32769 ='||32769.);
       end;
       ]';
       c:=c||v;
       execute immediate c;
    end;
    /
    

    [collapse]
    bug oracle oracle undocumented behaviour pl/sql pl/sql optimization

    Ampersand instead of colon for bind variables

    Posted on September 27, 2017 by Sayan Malakshinov Posted in curious, oracle, SQL, SQL*Plus, undocumented 1 Comment

    I’ve troubleshooted one query today and I was very surprised that bind variables in this query were specified with &ampersand instead of :colon! I have never seen this before and I couldn’t find anything about this in documentation…
    Unfortunately SQL*Plus doesn’t support ampersand yet, even if you disable define (“set define off”),
    so I’ve tested such behaviour with this code:

    set def off serverout on
    exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
    

    And it really works! //at least on 11.2.0.2 and 12.2.0.1

    SQL> set def off serverout on
    SQL> exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
    1
    
    PL/SQL procedure successfully completed.
    
    SQL> select substr(sql_text,1,40) stext,sql_id,executions,rows_processed from v$sqlarea a where sql_text like '%dual%&var';
    
    STEXT                                 SQL_ID        EXECUTIONS ROWS_PROCESSED
    ------------------------------------- ------------- ---------- --------------
    select 1 from dual where dummy=&var   ckkw4u3atxz02          3              3
    
    SQL> select * from table(dbms_xplan.display_cursor('ckkw4u3atxz02'));
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------
    SQL_ID  ckkw4u3atxz02, child number 0
    -------------------------------------
    select 1 from dual where dummy=&var
    
    Plan hash value: 272002086
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
    |*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("DUMMY"=:VAR)
    
    
    18 rows selected.

    Update: Btw, it works for SQL only, not for PL/SQL:

    SQL> var v varchar2(1);
    SQL> begin &v = 'Z'; end;
      2  /
    begin &v = 'Z'; end;
          *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00103: Encountered the symbol "&" when expecting one of the following:
    
    SQL> exec &v := 'X';
    BEGIN &v := 'X'; END;
    
          *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00103: Encountered the symbol "&" when expecting one of the following:
    The symbol "&" was ignored.
    
    SQL> exec :v := 'X';
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from dual where dummy=&v
      2  ;
    
    D
    -
    X
    

    And we can can use mixed placeholders:

    SQL> select * from dual where dummy=&v and &v=:v;
    
    D
    -
    X
    
    ampersand bind variable colon oracle undocumented oracle

    Intra-block row chaining optimization in 12.2

    Posted on June 17, 2017 by Sayan Malakshinov Posted in 12c, oracle, trace, undocumented 3 Comments

    I’ve wrote in previous post

    4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.

    But it’s not valid anymore 🙂 Since 12.2 Oracle optimizes updates too.
    You can check it on 12.2 and previous version using example 4 from previous post:

    Test 4

    drop table test purge;
    set serverout on
    alter session set tracefile_identifier='test4';
     
    declare
       cols varchar2(32000):='c_1 char(3)';
        
       procedure print_and_exec(c varchar2) as
       begin
          dbms_output.put_line(c);
          execute immediate c;
       end;
    begin
       for i in 2..355 loop
          cols:=cols||',c_'||i||' char(3)';
       end loop;
       print_and_exec ('create table test('||cols||')');
       print_and_exec ('insert into test(c_1) values(null)');
       commit;
       for i in 256..355 loop
          execute immediate 'update test set c_'||i||'='||i;
       end loop;
       commit;
       execute immediate 'alter system flush buffer_cache';
       for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
                 from (
                       select distinct file#,block# 
                       from v$bh 
                       where class#=1 
                       and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                       order by 1,2
                      )
                )
       loop 
           execute immediate r.cmd;
       end loop;
    end;
    /
    disc;

    [collapse]

    On 12.2 you will see that it creates just 5 blocks 🙂

    PS. My presentation about Intra-block row chaining from RuOUG seminar: Intra-block row chaining(RuOUG)

    intra-block chaining row chaining

    row pieces, 255 columns, intra-block row chaining in details

    Posted on February 12, 2017 by Sayan Malakshinov Posted in oracle, trace, undocumented 10 Comments

    You may know about Intra-block row chaining, that may occur when the number of columns in a table is more than 255 columns.
    But do you know that intra-block chaining works with inserts only? not updates!

    Documentation says:

    When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

    A bit more details:
    1. One row piece can store up to 255 columns
    2. Oracle splits fields by row pieces in reverse order
    3. Oracle doesn’t store trailing null fields in a row (not in row piece)
    4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block. (not valid since 12.2)

    I’ll show in examples with dumps:
    Continue reading→

    intra-block chaining row chaining

    PRECOMPUTE_SUBQUERY hint

    Posted on August 28, 2014 by Sayan Malakshinov Posted in CBO, hints, undocumented 2 Comments

    I’ve just found out that we can specify query block for PRECOMPUTE_SUBQUERY: /*+ precompute_subquery(@sel$2) */
    So we can use it now with SQL profiles, SPM baselines and patches.

    SQL> select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in (select chr(level) from dual connect by level<=100);
    
    D
    -
    X
    
    SQL> @last
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  c437vsqj7c4jy, child number 0
    -------------------------------------
    select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in
    (select chr(level) from dual connect by level<=100)
    
    Plan hash value: 272002086
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |        |       |     2 (100)|          |
    |*  1 |  TABLE ACCESS FULL| DUAL |      1 |     2 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$1 / DUAL@SEL$1
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(("DUMMY"='' OR "DUMMY"='' OR "DUMMY"='♥' OR "DUMMY"='♦'
                  OR "DUMMY"='♣' OR "DUMMY"='♠' OR "DUMMY"='' OR "DUMMY"=' OR
                  "DUMMY"=' ' OR "DUMMY"=' ' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=' '
                  OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='â–º' OR "DUMMY"='â—„' OR
                  "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
                  OR "DUMMY"='' OR "DUMMY"='↑' OR "DUMMY"='↓' OR "DUMMY"='' OR
                  "DUMMY"=' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
                  OR "DUMMY"=' ' OR "DUMMY"='!' OR "DUMMY"='"' OR "DUMMY"='#' OR
                  "DUMMY"='$' OR "DUMMY"='%' OR "DUMMY"='&' OR "DUMMY"='''' OR
                  "DUMMY"='(' OR "DUMMY"=')' OR "DUMMY"='*' OR "DUMMY"='+' OR "DUMMY"=','
                  OR "DUMMY"='-' OR "DUMMY"='.' OR "DUMMY"='/' OR "DUMMY"='0' OR
                  "DUMMY"='1' OR "DUMMY"='2' OR "DUMMY"='3' OR "DUMMY"='4' OR "DUMMY"='5'
                  OR "DUMMY"='6' OR "DUMMY"='7' OR "DUMMY"='8' OR "DUMMY"='9' OR
                  "DUMMY"=':' OR "DUMMY"=';' OR "DUMMY"='<' OR "DUMMY"='=' OR "DUMMY"='>'
                  OR "DUMMY"='?' OR "DUMMY"='@' OR "DUMMY"='A' OR "DUMMY"='B' OR
                  "DUMMY"='C' OR "DUMMY"='D' OR "DUMMY"='E' OR "DUMMY"='F' OR "DUMMY"='G'
                  OR "DUMMY"='H' OR "DUMMY"='I' OR "DUMMY"='J' OR "DUMMY"='K' OR
                  "DUMMY"='L' OR "DUMMY"='M' OR "DUMMY"='N' OR "DUMMY"='O' OR "DUMMY"='P'
                  OR "DUMMY"='Q' OR "DUMMY"='R' OR "DUMMY"='S' OR "DUMMY"='T' OR
                  "DUMMY"='U' OR "DUMMY"='V' OR "DUMMY"='W' OR "DUMMY"='X' OR "DUMMY"='Y'
                  OR "DUMMY"='Z' OR "DUMMY"='[' OR "DUMMY"='\' OR "DUMMY"=']' OR
                  "DUMMY"='^' OR "DUMMY"='_' OR "DUMMY"='`' OR "DUMMY"='a' OR "DUMMY"='b'
                  OR "DUMMY"='c' OR "DUMMY"='d'))
    
    

    PS. I’m not sure, but as far as i remember, when I tested it on 10.2, it didn’t work with specifying a query block.
    And I have never seen such usage.

    cbo oracle undocumented behaviour precompute_subquery query optimization query optimizing undocumented oracle

    When v$sesstat statistics are updated

    Posted on March 21, 2014 by Sayan Malakshinov Posted in oracle, PL/SQL, runstats, statistics, troubleshooting, undocumented Leave a comment

    Craig Shallahamer wrote excellent article “When is v$sesstat really updated?”.
    And my today post just a little addition and correction about the difference of updating ‘Db time’ and ‘CPU used by this session’ statistics.

    Test #1

    In this test I want to show that the statistics will be updated after every fetch call.
    I have set arraysize=2, so sql*plus will fetch by 2 rows:
    (full script)

    -- Result will be fetched by 2 rows:
    set arraysize 2;
    -- this query generates CPU consumption 
    -- in the scalar subquery on fetch phase,
    -- so CPU consumption will be separated 
    -- into several periods between fetch calls:
    with gen as (
                select/*+ materialize */
                   level n, lpad(level,400) padding
                from dual
                connect by level<=200
                )
        ,stat as (
                select/*+ inline */
                   sid,name,value 
                from v$mystat st, v$statname sn
                where st.statistic#=sn.statistic#
                  and sn.name in ('DB time'
                                 ,'CPU used by this session'
                                 ,'user calls'
                                 ,'recursive calls')
                )
    --first rows just for avoiding SQL*Plus effect with fetching 1 row at start,
    -- others will be fetched by "arraysize" rows:
    select null rn,null cnt,null dbtime,null cpu,null user_calls, null recursive_calls from dual
    union all -- main query:
    select
       rownum rn
      ,(select count(*) from gen g1, gen g2, gen g3 where g1.n>g2.n and g1.n*0=main.n*0) cnt
      ,(select value from stat where sid*0=n*0 and name = 'DB time'                    ) dbtime
      ,(select value from stat where sid*0=n*0 and name = 'CPU used by this session'   ) cpu
      ,(select value from stat where sid*0=n*0 and name = 'user calls'                 ) user_calls
      ,(select value from stat where sid*0=n*0 and name = 'recursive calls'            ) recursive_calls
    from gen main
    where rownum<=10;
    set arraysize 15;
    

    Test results:

    SQL> @tests/dbtime
    
            RN        CNT     DBTIME        CPU USER_CALLS RECURSIVE_CALLS
    ---------- ---------- ---------- ---------- ---------- ---------------
    
             1    3980000      12021      11989        200             472
             2    3980000      12021      11989        200             472
             3    3980000      12121      12089        201             472
             4    3980000      12121      12089        201             472
             5    3980000      12220      12186        202             472
             6    3980000      12220      12186        202             472
             7    3980000      12317      12283        203             472
             8    3980000      12317      12283        203             472
             9    3980000      12417      12383        204             472
            10    3980000      12417      12383        204             472
    

    As you can see the statistics are updated after every fetch call.

    Test #2

    Now since we already tested simple sql query, I want to do a little bit more complicated test with PL/SQL:
    I’m going to write single PL/SQL block with next algorithm:
    1. Saving stats
    2. Executing some pl/sql code with CPU consumption
    3. Getting statistics difference
    4. Starting query from first test
    5. Fetch 10 rows
    6. Getting statistics difference
    7. Fetch next 10 rows
    8. Getting statistics difference
    9. Fetch next 10 rows
    10. Getting statistics difference
    And after executing this block, i want to check statistics.

    Full script:

    set feed off;
    
    -- saving previous values
    column st_dbtime      new_value prev_dbtime      noprint;
    column st_cpu_time    new_value prev_cputime     noprint;
    column st_user_calls  new_value prev_user_calls  noprint;
    column st_recur_calls new_value prev_recur_calls noprint;
    
    select   max(decode(sn.NAME,'DB time'                  ,st.value))*10 st_dbtime
            ,max(decode(sn.NAME,'CPU used by this session' ,st.value))*10 st_cpu_time
            ,max(decode(sn.NAME,'user calls'               ,st.value))    st_user_calls
            ,max(decode(sn.NAME,'recursive calls'          ,st.value))    st_recur_calls
    from v$mystat st, v$statname sn
    where st.statistic#=sn.statistic# 
      and sn.name in ('DB time','CPU used by this session'
                     ,'user calls','recursive calls'
                     )
    /
    -- variable for output from pl/sql block: 
    var output varchar2(4000);
    
    prompt Executing test...;
    ----- main test:
    declare
       cnt int;
       st_dbtime      number; 
       st_cpu_time    number; 
       st_user_calls  number; 
       st_recur_calls number; 
       cursor c is 
          with gen as (select/*+ materialize */
                         level n, lpad(level,400) padding
                       from dual
                       connect by level<=200)
          select
              rownum rn
            , (select count(*) from gen g1, gen g2, gen g3 where g1.n>g2.n and g1.n*0=main.n*0) cnt
          from gen main
          where rownum<=60;
       
       type ctype is table of c%rowtype;
       c_array ctype;
       
       procedure SnapStats(descr varchar2:=null)
       is
          st_new_dbtime      number;
          st_new_cpu_time    number;
          st_new_user_calls  number;
          st_new_recur_calls number;
       begin
          select   max(decode(sn.NAME,'DB time'                 ,st.value))*10 st_dbtime
                  ,max(decode(sn.NAME,'CPU used by this session',st.value))*10 st_cpu_time
                  ,max(decode(sn.NAME,'user calls'              ,st.value))    st_user_calls
                  ,max(decode(sn.NAME,'recursive calls'         ,st.value))    st_recur_calls
              into st_new_dbtime,st_new_cpu_time,st_new_user_calls,st_new_recur_calls
          from v$mystat st, v$statname sn
          where st.statistic#=sn.statistic#
            and sn.name in ('DB time','CPU used by this session'
                           ,'user calls','recursive calls'
                           );
          if descr is not null then
             :output:= :output || descr ||':'||chr(10)
                    || 'sesstat dbtime:     ' || (st_new_dbtime      - st_dbtime      )||chr(10)
                    || 'sesstat cputime:    ' || (st_new_cpu_time    - st_cpu_time    )||chr(10)
                    || 'sesstat user calls: ' || (st_new_user_calls  - st_user_calls  )||chr(10)
                    || 'sesstat recur calls:' || (st_new_recur_calls - st_recur_calls )||chr(10)
                    || '======================================'||chr(10);
          end if;
          st_dbtime      := st_new_dbtime     ;
          st_cpu_time    := st_new_cpu_time   ;
          st_user_calls  := st_new_user_calls ;
          st_recur_calls := st_new_recur_calls;
       end;
       
    begin
       -- saving previous stats:
       SnapStats;
    
       -- generating cpu load:
       for i in 1..1e7 loop
          cnt:=cnt**2+cnt**1.3-cnt**1.2;
       end loop;
       -- getting new stats:
       SnapStats('After pl/sql loop');
       
       open c;
       SnapStats('After "open c"');
       fetch c bulk collect into c_array limit 10;
       SnapStats('After fetch 10 rows');
       fetch c bulk collect into c_array limit 10;
       SnapStats('After fetch 20 rows');
       fetch c bulk collect into c_array limit 10;
       SnapStats('After fetch 30 rows');
       close c;
       SnapStats('After close c');
    end;
    / 
    
    prompt 'Delta stats after statement(ms):';
    select   max(decode(sn.NAME,'DB time'                 ,st.value))*10
              - &&prev_dbtime      as delta_dbtime
            ,max(decode(sn.NAME,'CPU used by this session',st.value))*10
              - &&prev_cputime     as delta_cpu_time
            ,max(decode(sn.NAME,'user calls'              ,st.value))  
              - &&prev_user_calls  as delta_user_calls
            ,max(decode(sn.NAME,'recursive calls'         ,st.value))  
              - &&prev_recur_calls as delta_recur_calls
    from v$mystat st, v$statname sn
    where st.statistic#=sn.statistic# 
      and sn.name in ('DB time','CPU used by this session'
                     ,'user calls','recursive calls'
                     )
    /
    prompt 'Test results:';
    col output format a40;
    print output;
    set feed off;
    

    Output:

    SQL> @tests/dbtime2
    
    Executing test...
    'Delta stats after statement(ms):'
    
    DELTA_DBTIME DELTA_CPU_TIME DELTA_USER_CALLS DELTA_RECUR_CALLS
    ------------ -------------- ---------------- -----------------
           18530          18460                5                33
    
    Test results:
    OUTPUT
    ----------------------------------------
    After pl/sql loop:
    sesstat dbtime:     0
    sesstat cputime:    4350
    sesstat user calls: 0
    sesstat recur calls:2
    ======================================
    After "open c":
    sesstat dbtime:     0
    sesstat cputime:    20
    sesstat user calls: 0
    sesstat recur calls:4
    ======================================
    After fetch 10 rows:
    sesstat dbtime:     0
    sesstat cputime:    4680
    sesstat user calls: 0
    sesstat recur calls:2
    ======================================
    After fetch 20 rows:
    sesstat dbtime:     0
    sesstat cputime:    4680
    sesstat user calls: 0
    sesstat recur calls:2
    ======================================
    After fetch 30 rows:
    sesstat dbtime:     0
    sesstat cputime:    4690
    sesstat user calls: 0
    sesstat recur calls:2
    ======================================
    After close c:
    sesstat dbtime:     0
    sesstat cputime:    0
    sesstat user calls: 0
    sesstat recur calls:3
    ======================================
    

    We can notice that “CPU time” is updated at the same time as recursive calls, but “DB time” is updated only with “User calls”. Although this difference is not so important(because in most cases we can use other statistics in sum), but i think, if you want to instrument some code, it gives reason to check out desirable statistics for update time.

    oracle pl/sql v$mystat v$sesstat

    Oracle 12c: scalar subqueries

    Posted on February 11, 2014 by Sayan Malakshinov Posted in 12c, CBO, oracle, undocumented Leave a comment

    We already know that the CBO transformation engine in 12c can unnest scalar subqueries from select-list.
    So it’s not very surprising, that CBO is now able to add scalar subqueries costs to total query cost (even if “_optimizer_unnest_scalar_sq” = false):

    Before 12.1

    SQL> explain plan for
      2  select
      3  (select count(*) from XT_TEST) cnt
      4  from dual;
    
    Explained.
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------
    Plan hash value: 2843533371
    
    ---------------------------------------------------------------------------
    | Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |           |     1 |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |           |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| IX_TEST_A | 90792 |    50   (0)| 00:00:01 |
    |   3 |  FAST DUAL            |           |     1 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    10 rows selected.
    

    [collapse]

    12.1

    SQL> alter session set "_optimizer_unnest_scalar_sq"=false;
    
    Session altered.
    
    SQL> explain plan for
      2  select
      3  (select count(*) from XT_TEST) cnt
      4  from dual;
    
    Explained.
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------
    Plan hash value: 2843533371
    
    ---------------------------------------------------------------------------
    | Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |           |     1 |    52   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |           |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| IX_TEST_A | 90792 |    50   (0)| 00:00:01 |
    |   3 |  FAST DUAL            |           |     1 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    10 rows selected.
    

    [collapse]

    But it’s interesting that correlated subquery can reference now to a column from parent tables more
    than one level above:
    Before 12.1

    SQL> with t1 as (select/*+ materialize */ 1 a from dual)
      2      ,t2 as (select/*+ materialize */ 2 b from dual)
      3      ,t3 as (select/*+ materialize */ 3 c from dual)
      4  select
      5    (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
      6  from t1;
      (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
                                                           *
    ERROR at line 5:
    ORA-00904: "T1"."A": invalid identifier
    

    [collapse]

    12.1

    SQL> with t1 as (select/*+ materialize */ 1 a from dual)
      2      ,t2 as (select/*+ materialize */ 2 b from dual)
      3      ,t3 as (select/*+ materialize */ 3 c from dual)
      4  select
      5    (select s from (select sum(b*c) s from t2,t3 where c>t1.a and c>b)) s
      6  from t1;
    
             S
    ----------
             6
    

    [collapse]

    scalar subqueries

    SYS_OP_MAP_NONNULL is in the documentation now

    Posted on February 10, 2014 by Sayan Malakshinov Posted in 12c, documentation, oracle, undocumented Leave a comment

    Interesting, that SYS_OP_MAP_NONNULL appeared in the Oracle 12c documentation: Choosing Indexes for Materialized Views

    Lazy tip: By the way, with length limitations, we can also use documented dump function:

    SQL> with
      2    t(a,b) as (
      3               select *
      4               from table(ku$_vcnt(null,'FF','A'))
      5                   ,table(ku$_vcnt(null,'FF','B'))
      6              )
      7  select
      8      a,b
      9     ,case when sys_op_map_nonnull(a) = sys_op_map_nonnull(b) then '=' else '!=' end comp1
     10     ,case when dump(a,1017)          = dump(b,1017)          then '=' else '!=' end comp2
     11     ,sys_op_map_nonnull(a) s_o_m_n_a
     12     ,sys_op_map_nonnull(b) s_o_m_n_b
     13     ,dump(a,  17) dump_a
     14     ,dump(b,  17) dump_b -- it is preferably sometimes to use 1017 - for charset showing
     15  from t;
    
    A     B     COMP1 COMP2 S_O_M_N_A  S_O_M_N_B  DUMP_A                DUMP_B
    ----- ----- ----- ----- ---------- ---------- --------------------- ---------------------
                =     =     FF         FF         NULL                  NULL
          FF    !=    !=    FF         464600     NULL                  Typ=1 Len=2: F,F
          B     !=    !=    FF         4200       NULL                  Typ=1 Len=1: B
    FF          !=    !=    464600     FF         Typ=1 Len=2: F,F      NULL
    FF    FF    =     =     464600     464600     Typ=1 Len=2: F,F      Typ=1 Len=2: F,F
    FF    B     !=    !=    464600     4200       Typ=1 Len=2: F,F      Typ=1 Len=1: B
    A           !=    !=    4100       FF         Typ=1 Len=1: A        NULL
    A     FF    !=    !=    4100       464600     Typ=1 Len=1: A        Typ=1 Len=2: F,F
    A     B     !=    !=    4100       4200       Typ=1 Len=1: A        Typ=1 Len=1: B
    
    9 rows selected.
    

    Little example of index creation on extended varchars

    Posted on November 15, 2013 by Sayan Malakshinov Posted in 12c, oracle, undocumented Leave a comment
    -- it's just for fun:
    SQL> alter system set "_scalar_type_lob_storage_threshold"=32000;
    
    System altered.
    SQL> create table t_varchar32000(v varchar2(32000 byte));
    
    Table created.
    
    SQL> insert into t_varchar32000
      2  select rpad(rownum,31999) || `x' str from dual connect by level<=1000;
    
    1000 rows created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace users;
    create index ix_t_varchar32000 on t_varchar32000(v) tablespace users
                                      *
    ERROR at line 1:
    ORA-01450: maximum key length (6398) exceeded
    
    
    SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k;
    create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k
                                      *
    ERROR at line 1:
    ORA-01450: maximum key length (26510) exceeded
    
    -- tablespace for big varchars:
    SQL> alter system set DB_32K_CACHE_SIZE=100M;
    
    System altered.
    
    SQL> CREATE TABLESPACE TS_32K DATAFILE '/u01/app/oracle/oradata/xtsql/pdb1/ts_32k_1.dbf' SIZE 150M
      2   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
      3   BLOCKSIZE 32K;
    
    Tablespace created.
    
    SQL> create table t_varchar16000(v varchar2(16000 byte)) tablespace ts_32k;
    
    Table created.
    
    SQL> insert into t_varchar16000
      2  select rpad(rownum,15999,'x' ) || 'y' from dual connect by level<=1000;
    
    1000 rows created.
    
    SQL> create index ix_t_varchar16000 on t_varchar16000(v) tablespace ts_32k;
    
    Index created.
    
    
    
    Statistics

    SQL> begin
      2     dbms_stats.gather_table_stats(
      3       ownname    => user
      4      ,tabname    => 'T_VARCHAR16000'
      5      ,method_opt => 'for all columns size auto'
      6      ,cascade => true
      7     );
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SQL> @stats/tab t_varchar16000
    
    OWNER           TABLE_NAME                     PARTITION_NAME          # ST_LOCK STALE_STA GLOBAL_ST USER_STAT   NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  AVG_SPACE LAST_ANALYZED
    --------------- ------------------------------ -------------------- ---- ------- --------- --------- --------- ---------- ---------- ------------ ----------- ---------- ---------------
    XTENDER         T_VARCHAR16000                                                   NO        YES       NO              1000       3016            0       16001          0 14-NOV-13
    
    
    OWNER           INDEX_NAME                       NUM_ROWS DISTINCT_KEYS     BLEVEL LEAF_BLOCKS  CL_FACTOR LAST_ANALYZED   GLOBAL_ST USER_STAT
    --------------- ------------------------------ ---------- ------------- ---------- ----------- ---------- --------------- --------- ---------
    XTENDER         IX_T_VARCHAR16000                    1000          1000          1        1000       1000 14-NOV-13       YES       NO
    
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | column_name                   | num_distinct| low_value         | high_value        | num_nulls | num_bucket| last_analyzed       | sample_size| globa| user_| avg_c| histogram           |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | V                             | 1000        | 1000xxxxxxxxxxxxxx| 9xxxxxxxxxxxxxxxxx| 0         | 1         | 2013-11-14 21:11    | 1000       | YES  | NO   | 16001| NONE                |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    

    [collapse]

    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

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    Russia / Moscow / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 4 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically
    • Top N biggest tables (with lobs, indexes and nested table)
    • “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Email Subscription

    Recent Comments

    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
    • Adaptive serial direct path read decision ignores object statistics since 12.1 - SSWUG.ORG on Adaptive serial direct path read decision ignores object statistics since 12.1
    • Oracle issues after upgrade to 12.2 - SSWUG.ORG on Oracle issues after upgrade to 12.2
    • Ampersand instead of colon for bind variables - SSWUG.ORG on Ampersand instead of colon for bind variables
    • Евгений Бабин on Oracle issues after upgrade to 12.2
    • Oracle SQL | How even empty trigger increases redo generation on Triggers and Redo: changes on 12.2

    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

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries RSS
    • Comments RSS
    • WordPress.org
    • 1
    • 2
    • Next
    ©Sayan Malakshinov. Oracle SQL