Oracle SQL

    Triggers and Redo: changes on 12.2

    Posted on November 19, 2017 by Sayan Malakshinov Posted in 12c, oracle, trace 1 Comment

    In one of the previous posts I showed How even empty trigger increases redo generation, but running the test from that post, I have found that this behaviour a bit changed on 12.2:
    In my old test case, values of column A were equal to values of B, and on previous oracle versions including 12.1.0.2 we can see that even though “update … set B=A” doesn’t change really “B”, even empty trigger greatly increases redo generation.
    But on 12.2.0.1 in case of equal values, the trigger doesn’t increase redo, so we can see small optimization here, though in case of different values, the trigger still increases reado generation greatly.

    same_dumpredo.sql
    set feed on;
    drop table xt_curr1 purge;
    drop table xt_curr2 purge;
    -- simple table:
    create table xt_curr1 as select '2' a, '2' b from dual connect by level<=1000;
    -- same table but with empty trigger:
    create table xt_curr2 as select '2' a, '2' b from dual connect by level<=1000;
    create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
    begin
      null;
    end;
    /
    -- objectID and SCN:
    col obj1 new_val obj1;
    col obj2 new_val obj2;
    col scn  new_val scn;
    select 
      (select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR1') obj1
     ,(select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR2') obj2
     ,d.CURRENT_SCN scn
    from v$database d
    /
    -- logfile1:
    alter system switch logfile;
    col member new_val logfile;
    SELECT member
    FROM v$logfile
    WHERE 
         is_recovery_dest_file='NO'
     and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT')
     and rownum=1;
    -- update1:
    set autot trace stat;
    update xt_curr1 set b=a;
    set autot off;
    commit;
    -- dump logfile1:
    alter session set tracefile_identifier='log1_same';
    ALTER SYSTEM DUMP LOGFILE '&logfile' SCN MIN &scn OBJNO &obj1;
    
    -- logfile2:
    alter system switch logfile;
    col member new_val logfile;
    SELECT member
    FROM v$logfile
    WHERE 
         is_recovery_dest_file='NO'
     and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT')
     and rownum=1;
    -- update2:
    set autot trace stat;
    update xt_curr2 set b=a;
    set autot off;
    commit;
    -- dump logfile2:
    alter session set tracefile_identifier='log2_same';
    ALTER SYSTEM DUMP LOGFILE '&logfile' OBJNO &obj2;
    alter session set tracefile_identifier='off';
    disc;
    
    

    [collapse]

    diff_dumpredo.sql

    set feed on;
    drop table xt_curr1 purge;
    drop table xt_curr2 purge;
    -- simple table:
    create table xt_curr1 as select '1' a, '2' b from dual connect by level<=1000;
    -- same table but with empty trigger:
    create table xt_curr2 as select '1' a, '2' b from dual connect by level<=1000;
    create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
    begin
      null;
    end;
    /
    -- objectID and SCN:
    col obj1 new_val obj1;
    col obj2 new_val obj2;
    col scn  new_val scn;
    select 
      (select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR1') obj1
     ,(select o.OBJECT_ID from user_objects o where o.object_name='XT_CURR2') obj2
     ,d.CURRENT_SCN scn
    from v$database d
    /
    -- logfile1:
    alter system switch logfile;
    col member new_val logfile;
    SELECT member
    FROM v$logfile
    WHERE 
         is_recovery_dest_file='NO'
     and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT')
     and rownum=1;
    -- update1:
    set autot trace stat;
    update xt_curr1 set b=a;
    set autot off;
    commit;
    -- dump logfile1:
    alter session set tracefile_identifier='log1_diff';
    ALTER SYSTEM DUMP LOGFILE '&logfile' SCN MIN &scn OBJNO &obj1;
    
    -- logfile2:
    alter system switch logfile;
    col member new_val logfile;
    SELECT member
    FROM v$logfile
    WHERE 
         is_recovery_dest_file='NO'
     and group#=(SELECT group# FROM v$log WHERE status = 'CURRENT')
     and rownum=1;
    -- update2:
    set autot trace stat;
    update xt_curr2 set b=a;
    set autot off;
    commit;
    -- dump logfile2:
    alter session set tracefile_identifier='log2_diff';
    ALTER SYSTEM DUMP LOGFILE '&logfile' OBJNO &obj2;
    alter session set tracefile_identifier='off';
    disc;
    
    

    [collapse]

    Equal values:
    12.1.0.2:

    12.2.0.1:

    Different values:
    12.1.0.2:

    12.2.0.1:

    We can easily find that trigger disables batched “Array update”:

    redo triggers

    Adaptive serial direct path read decision ignores object statistics since 12.1

    Posted on November 19, 2017 by Sayan Malakshinov Posted in 12c, adaptive serial direct path reads, oracle 1 Comment

    On versions 11.2.0.2 – 11.2.0.4 Oracle uses objects’ statistics to make direct path reads decision (of course, if “_direct_read_decision_statistics_driven” haven’t changed it to “false”), and we can force serial direct reads on statement level using sql profiles with hints INDEX_STATS/TABLES_STATS, but since at least 12.1.0.2 this decision ignores statistics.

    Btw, thanks to Jure Bratina, we know now, that we need to repeat hint TABLE_STATS at least twice to make it work 🙂 And from the following test case we know that it takes parameters from second one 🙂

    Compare trace files of the following test cases:

    table_stats2.sql

    drop table t1;
    
    create table t1 as select * from dual;
    ----------------------------
    pro ######################################;
    exec dbms_stats.gather_table_stats('','T1');
    exec dbms_stats.set_table_stats(user,'T1',numblks => 33333333);
    
    col value new_val oldval noprint;
    select value from v$statname n, v$mystat s
    where n.statistic#=s.statistic# and n.name ='physical reads direct';
    
    ----------------------------
    alter session set tracefile_identifier='table_stats2';
    --alter session set events '10053 trace name context forever, level 1';
    alter session set events 'trace[nsmtio] disk highest';
    select/*+ table_stats(t1, scale, blocks=66666666 rows=2222222222) 
              table_stats(t1, scale, blocks=99999999 rows=4444444444)
           */ 
           * 
    from t1;
    select value-&oldval directreads, value from v$statname n, v$mystat s
    where n.statistic#=s.statistic# and n.name ='physical reads direct';
    
    

    [collapse]

    You can see that our hint successfully changed number of blocks and forced direct path reads on 11.2.0.4:
    Oracle 11.2.0.4:

    NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT]
    NSMTIO: Additional Info: VLOT=797445
    Object# = 78376, Object_Size = 66666666 blocks
    SqlId = 7naawntkc57yx, plan_hash_value = 3617692013, Partition# = 0
    

    [collapse]

    But on 12.1.0.2 and 12.2.0.1 we can see 2 lines with “NSMTIO: kcbism” with the different types(2 and 3) and different number of blocks, and the direct path read decision was based on second one – from segment header:

    Oracle 12.1.0.2:

    NSMTIO: kcbism: islarge 1 next 0 nblks 66666666 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 4096 kcbstt 983 keep_nb 0 kcbnbh 59010 kcbnwp 1
    NSMTIO: kcbism: islarge 0 next 0 nblks 4 type 2, bpid 3, kcbisdbfc 0 kcbnhl 4096 kcbstt 983 keep_nb 0 kcbnbh 59010 kcbnwp 1
    NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 4 (blocks), Threshold: MTT(4917 blocks),
    _object_statistics: enabled, Sage: enabled, 
    Direct Read for serial qry: enabled(::::::), Ascending SCN table scan: FALSE 
    flashback_table_scan: FALSE, Row Versions Query: FALSE
    SqlId: 7naawntkc57yx, plan_hash_value: 3617692013, Object#: 302342, Parition#: 0 DW_scan: disabled
    

    [collapse]

    Oracle 12.2.0.1:

    NSMTIO: kcbism: islarge 1 next 0 nblks 66666666 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 4096 kcbstt 1214 keep_nb 0 kcbnbh 45026 kcbnwp 1
    NSMTIO: kcbism: islarge 0 next 0 nblks 4 type 2, bpid 3, kcbisdbfc 0 kcbnhl 4096 kcbstt 1214 keep_nb 0 kcbnbh 45026 kcbnwp 1
    NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 4 (blocks), Threshold: MTT(6072 blocks),
    _object_statistics: enabled, Sage: enabled, 
    Direct Read for serial qry: enabled(:::::::), Ascending SCN table scan: FALSE 
    flashback_table_scan: FALSE, Row Versions Query: FALSE
    SqlId: 7naawntkc57yx, plan_hash_value: 3617692013, Object#: 174411, Parition#: 0 DW_scan: disabled
    

    [collapse]

    And similar example, but for IFFS(index fast full scan):
    nb: I set the number of index blocks using dbms_stats to 33333000 and hinted the query with 77777700

    index_stats2.sql

    drop table t2 purge;
    ALTER SESSION SET optimizer_dynamic_sampling = 0;
    ALTER SESSION SET "_optimizer_use_feedback" = FALSE;
    ALTER SESSION SET optimizer_adaptive_features = FALSE;
    ALTER SESSION SET optimizer_adaptive_plans=FALSE;
    
    create table t2(x) as select level from dual connect by level<=1000;
    create index t2_ix on t2(1,x,rpad(x,100));
    
    begin
       dbms_stats.gather_table_stats('','T2',cascade => true);
       dbms_stats.set_table_stats(user,'T2'   ,numblks => 33333333);
       dbms_stats.set_index_stats(user,'T2_IX',numlblks => 33333000);
    end;
    /
    
    col value new_val oldval noprint;
    select value from v$statname n, v$mystat s
    where n.statistic#=s.statistic# and n.name ='physical reads direct';
    
    alter session set tracefile_identifier='index_stats2';
    alter session set events 'trace[nsmtio] disk highest';
    select/*+ index_stats(t2, t2_ix, scale, blocks=7777700)
              index_ffs(t2 t2_ix)
              dynamic_sampling(0)
           */ 
           count(*) cnt2
    from t2;
    select value-&oldval directreads, value from v$statname n, v$mystat s
    where n.statistic#=s.statistic# and n.name ='physical reads direct';
    
    disc;
    

    [collapse]

    You can see that on 11.2.0.4 oracle gets number of blocks from the hint (7777700)
    Oracle 11.2.0.4 - index_stats:

    NSMTIO: qerixFetchFastFullScan:DirectRead[OBJECT_SIZE > VLOT]:
    NSMTIO: AdditionalInfo: Object_size: 7777700 (blocks), vlot=797445
    SqlId=by2zv0k566hj5, plan_hash_value=3419274230,Object#=78375
    

    [collapse]

    From the first line we can see that kcbism takes the hinted number of blocks, but later kcbivlo rewrites it with the number from segment header:

    Oracle 12.1.0.2 - index_stats:

    NSMTIO: kcbism: islarge 1 next 0 nblks 7777700 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 4096 kcbstt 983 keep_nb 0 kcbnbh 59010 kcbnwp 1
    NSMTIO: kcbivlo: nblks 22 vlot 500 pnb 49175 kcbisdbfc 0 is_large 0
    NSMTIO: qerixFetchFastFullScan:[MTT < OBJECT_SIZE < VLOT]:NSMTIO: AdditionalInfo: Object_size: 22 (blocks), vlot=245875
    SqlId=by2zv0k566hj5, plan_hash_value=3419274230,Object#=302347
    

    [collapse]

    Oracle 12.2.0.1 - index_stats:

    NSMTIO: kcbism: islarge 1 next 0 nblks 7777700 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 4096 kcbstt 1214 keep_nb 0 kcbnbh 45026 kcbnwp 1
    NSMTIO: kcbivlo: nblks 22 vlot 500 pnb 60729 kcbisdbfc 0 is_large 0
    NSMTIO: qerixFetchFastFullScan:[MTT < OBJECT_SIZE < VLOT]:NSMTIO: AdditionalInfo: Object_size: 22 (blocks), vlot=303645
    SqlId=by2zv0k566hj5, plan_hash_value=3419274230,Object#=174409
    

    [collapse]

    So we have 2 options to force direct reads:
    1. to execute alter session set “_serial_direct_read”=’ALWAYS’;
    2. or to force parallel plan to get parallel direct path reads (we can do it with even with dop=1)

    12c direct path reads

    Easy(lazy) way to check which programs have properly configured FetchSize

    Posted on November 16, 2017 by Sayan Malakshinov Posted in oracle, SQL, statistics Leave a comment
    select 
       s.module
      ,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch
    from v$sql s
    where 
        s.rows_processed>100
    and s.executions    >1
    and s.fetches       >1
    and s.module is not null
    and s.command_type  = 3    -- SELECTs only
    and s.program_id    = 0    -- do not account recursive queries from stored procs
    and s.parsing_schema_id!=0 -- <> SYS
    group by s.module
    order by rows_per_fetch desc nulls last
    /
    
    arraysize fetchsize troubleshooting

    PL/SQL functions: Iterate and keys for associative arrays

    Posted on October 2, 2017 by Sayan Malakshinov Posted in oracle, PL/SQL, PL/SQL optimization Leave a comment

    Unfortunately associative arrays still require more “coding”:
    we still can’t use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don’t have convinient iterators and even function to get all keys…
    That’s why I want to show my templates for such things like iterator and keys function. You can adopt these functions and create them on schema level.

    declare 
       type numbers  is table of number;
       type anumbers is table of number index by pls_integer;
       a anumbers;
       i pls_integer;
    
       function iterate( idx in out nocopy pls_integer, arr in out nocopy anumbers) 
          return boolean
       as pragma inline;
       begin
          if idx is null 
             then idx:=arr.first; 
             else idx:=arr.next(idx);
          end if;
          return idx is not null;
       end;
    
      function keys(a in out nocopy anumbers) return numbers as
         res numbers:=numbers();
         idx number;
         pragma inline;
      begin
         while iterate(idx,a) loop
            res.extend;
            res(res.count):=idx;
         end loop;
         return res;
      end;
    
    begin
       a(1):=10;
       a(3):=30;
       a(5):=50;
       a(8):=80;
       -- iterate:
       while iterate(i,a) loop
          dbms_output.put_line(a(i));
       end loop;
    
       -- keys:
       for i in 1..keys(a).count loop
          dbms_output.put_line(a(keys(a)(i)));
       end loop;
    
    end;
    

    How to group connected elements (or pairs)

    Posted on September 29, 2017 by Sayan Malakshinov Posted in oracle, PL/SQL, PL/SQL optimization, query optimizing, SQL 7 Comments

    I see quite often when developers ask questions about connected components:

    Table “MESSAGES” contains fields “SENDER” and “RECIPIENT”, which store clients id.
    How to quickly get all groups of clients who are connected even through other clients if the table has X million rows?
    So for this table, there should be 4 groups:

    • (1, 2, 4, 8, 16)
    • (3, 6, 12)
    • (5, 10, 20)
    • (7, 14)
    • (9, 18)
    SENDERRECIPIENT
    12
    24
    36
    48
    510
    612
    714
    816
    918
    1020

    Of course, we can solve this problem using SQL only (model, recursive subquery factoring or connect by with nocycle), but such solutions will be too slow for huge tables.

    Example of SQL solution

    with 
       t(sender,recipient) as (select level,level*2 from dual connect by level<=10)
    ,  v1 as (select rownum id,t.* from t)
    ,  v2 as (select id, account
              from v1
               unpivot (
                 account for x in (sender,recipient)
               ))
    , v3 as (
               select
                  id
                 ,account
                 ,dense_rank()over(order by account) account_n
                 ,count(*)over() cnt
               from v2)
    , v4 as (
               select distinct grp,account
               from v3
               model
                    dimension by (id,account_n)
                    measures(id grp,account,cnt)
                    rules
                    iterate(1e6)until(iteration_number>cnt[1,1])(
                       grp[any,any] = min(grp)[any,cv()]
                      ,grp[any,any] = min(grp)[cv(),any]
                    )
    )
    select
       listagg(account,',')within group(order by account) s
    from v4
    group by grp
    

    [collapse]

    In such situations it’s much better to adopt standard algorithms like Quick-find or Weighted quick-union for PL/SQL.
    The first time I wrote such solution about 5 years ago and I even posted here one of the latest solutions, but all of them were not universal, so I’ve created the package today with a couple of functions for most common problems: XT_CONNECTED_COMPONENTS

    It contains 2 functions based on Weighted quick-find quick-union algorithm:

    • function get_strings(cur in sys_refcursor, delim varchar2:=’,’) return strings_array pipelined;
      It accepts a cursor and returns found connected components as table of varchar2(v_size). You can change v_size in the package definition.
      Input cursor should contain one Varchar2 column with linked strings, for example: ‘a,b,c’.
      You can also specify list delimiter, by default it is comma.
      Examples:

      select * from table(xt_connected_components.get_strings( cursor(select ELEM1||','||ELEM2 from TEST));
      select * 
      from
       table(
         xt_connected_components.get_strings( 
           cursor(select 'a,b,c' from dual union all
                  select 'd,e,f' from dual union all
                  select 'e,c'   from dual union all
                  select 'z'     from dual union all
                  select 'X,Y'   from dual union all
                  select 'Y,Z'   from dual)));
      COLUMN_VALUE
      -----------------------------------------
      STRINGS('X', 'Y', 'Z')
      STRINGS('a', 'b', 'c', 'd', 'e', 'f')
      STRINGS('z')
      
      
    • function get_numbers(cur in sys_refcursor) return numbers_array pipelined;
      This function also returns connected components, but for numbers.
      Input cursor should contain two columns with linked numbers.
      Examples:

      select * 
      from table(
              xt_connected_components.get_numbers( 
                cursor(
                  select sender_id, recipient_id from messages
              )));
      select * 
      from
        table(
          xt_connected_components.get_numbers( 
             cursor(
                select level   account1
                     , level*2 account2 
                from dual 
                connect by level<=10
          )));
      SQL> select *
        2  from
        3    table(
        4      xt_connected_components.get_numbers(
        5         cursor(
        6            select level   account1
        7                 , level*2 account2
        8            from dual
        9            connect by level<=10
       10*     )))
      SQL> /
      
      COLUMN_VALUE
      ------------------------
      NUMBERS(1, 2, 4, 8, 16)
      NUMBERS(3, 6, 12)
      NUMBERS(5, 10, 20)
      NUMBERS(7, 14)
      NUMBERS(9, 18)
      

    How to install:
    Download all files from Github and execute “@install” in SQL*Plus or execute them in another tool in the following order:
    xt_connected_components_types.sql
    xt_connected_components_pkg.sql
    xt_connected_components_bdy.sql

    Download URL: https://github.com/xtender/xt_scripts/tree/master/extra/xt_connected_components

    query 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

    Simple regexp to check that string contains word1 and word2 and doesn’t contain word3

    Posted on September 14, 2017 by Sayan Malakshinov Posted in oracle, SQL 1 Comment
    with tst as (
      select 'qwe word1 asd ...............' s from dual
      union all
      select 'qwe word1 asd word2 .........' s from dual
      union all
      select 'qwe word1 asd word2 zxc word3' s from dual
      union all
      select 'qwe word2 asd word1 zxc word4' s from dual
    )
    select
      s
      ,regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') subst
      ,case when regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$') then 'matched' end tst2
    from tst
    where 1=1
    --and regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$')
    

    Book advice: ORACLE SQL & PL/SQL Golden Diary by Asim Chowdhury

    Posted on August 10, 2017 by Sayan Malakshinov Posted in oracle 1 Comment

    I’ve reviewed this book recently, and I highly recommend it as it has almost all that needed to become strong Oracle developer. You can check at least the table of contents:

    ORACLE SQL & PL/SQL Golden Diary: by Asim Chowdhury
    New Book Demystifies Complex Cross-Version Oracle Problem Solving

    Compiled by veteran computer scientist and data modeler, Asim Chowdhury, ‘ORACLE SQL & PL/SQL Golden Diary: Refactoring, Interoperability of Versions & Integration of related concepts for High Performance’ is the first book on the market that comprehensively allows data architects to unravel any concepts in SQL and PL/sql till oracle 12c. It’s poised to remove much confusion from the many versions of Oracle SQL now on the market; a Godsend for the computer science industry.

    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

    SQL validation during PL/SQL compilation

    Posted on May 1, 2017 by Sayan Malakshinov Posted in oracle, PL/SQL, SQL Leave a comment

    A recent posting on SQL.RU asked why Oracle doesn’t raise such errors like “ORA-00979 not a group by expression” during PL/SQL compilation. Since I couldn’t find a link to the answer (though I read about it many years ago, but I don’t remember where…), I’ve decided to post short answer:

    During PL/SQL compilation Oracle checks static SQL using only:

    1. Syntactic analysis – Oracle verifies that keywords, object names, operators, delimiters, and so on are placed correctly in your SQL statement. So such queries like “select * foRm dual” will fail during this validation. For example, we can get here such errors like:
      ORA-00900: invalid SQL statement
      ORA-00923: FROM keyword not found where expected
      ORA-00924: missing BY keyword
      ORA-00933: SQL command not properly ended
      …
    2. Semantic analysis – it verifies that references to host variables and database objects are valid(including their grants) and that host-variable datatypes are correct. For example, “select * from nonexisting_table” will fail this validation.

    And since Oracle doesn’t validate all other types of errors during Syntactic and Semantic analysis, we can detect them only during optimization* or execution*.
    For example, Oracle detects “ORA-00979 not a group by expression” during optimization phase.

    * Read “Chapter Chapter 7: Parsing and Optimizing” in Oracle Core by Jonathan Lewis

    NB. It doesn’t not apply to CREATE or ALTER VIEW, because Oracle executes optimization step for them. You can check it using trace 10053.

    Simple example:

    -- fORm instead of "from" - syntactic validation fails:
    SQL> create table t(a int, b int, c int);
    SQL> create or replace procedure p_syntactic is
      2     cursor c is select a,b,sum(c) sum_c fORm t group by a;
      3  begin
      4     null;
      5  end;
      6  /
    
    Warning: Procedure created with compilation errors.
    
    SQL> sho error;
    Errors for PROCEDURE P_SYNTACTIC:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/16     PL/SQL: SQL Statement ignored
    2/40     PL/SQL: ORA-00923: FROM keyword not found where expected
    
    -- semantic validation fails:
    SQL> create or replace procedure p_semantic is
      2     cursor c is select a,b,sum(blabla) sum_c from t group by a;
      3  begin
      4     null;
      5  end;
      6  /
    
    Warning: Procedure created with compilation errors.
    
    SQL> sho error;
    Errors for PROCEDURE P_SEMANTIC:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/16     PL/SQL: SQL Statement ignored
    2/31     PL/SQL: ORA-00904: "BLABLA": invalid identifier
    
    -- As you can see this procedure passes successfully both syntactic and semantic analysis,
    -- though query is not valid: it should raise "ORA-00979: not a GROUP BY expression"
    SQL> create or replace procedure p_valid is
      2     cursor c is select a,b,sum(c) sum_c from t group by a;
      3  begin
      4     null;
      5  end;
      6  /
    
    SQL> sho error;
    No errors.
    
    -- Oracle checks such errors for "CREATE VIEW", because it runs optimization for the query text:
    SQL> create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a;
    create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a
                                     *
    ERROR at line 1:
    ORA-00979: not a GROUP BY expression
    

    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

    • Another bug with lateral
    • Lateral view decorrelation(VW_DCL) causes wrong results with rownum
    • Oracle Linux hangs after “probing EDD” in Oracle Cloud
    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically

    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
    • Prev
    • 1
    • 2
    • 3
    • 4
    • …
    • 10
    • Next
    ©Sayan Malakshinov. Oracle SQL