Category Archives: undocumented

When v$sesstat statistics are updated

Posted on by 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 12c: scalar subqueries

Posted on by 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]

SYS_OP_MAP_NONNULL is in the documentation now

Posted on by 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 by 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 by 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]

Too many function executions in simple query

Posted on by Posted in CBO, oracle, PL/SQL optimization, undocumented Leave a comment

Suppose we have a table with 10 rows:

SQL> select id from t10;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

And we have the query:

select *
from (
     select xf(t10.id) a
     from t10 
     )
where a*a >= 25

At first it may seem that the function should be executed as many times as rows in a table T10, i.e. 10 times.
Lets test it:

SQL> create or replace function xf(p int) return int as
  2  begin
  3    dbms_output.put_line('F fired!');
  4    return p;
  5  end;
  6  /

Function created.

SQL> set serverout on;
SQL> select *
  2  from (
  3       select xf(t10.id) a
  4       from t10
  5       )
  6  where a*a >= 25
  7  /

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired!
F fired! -- 15
F fired!
F fired!
F fired!
F fired!
F fired! -- 20
F fired!
F fired!
F fired!
F fired!
F fired! -- 25
F fired!

As you see, there are more than 10 executions, so lets see the execution plan:

SQL> @xplan +projection

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2919944937

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T10  |     1 |     3 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T10"."ID"[NUMBER,22]

Now you see that inner view was merged, and the function was executed 20 times in the filter and 6 times on the fetch after filtering(6 rows – 6 calls).
I see that often in such cases “no_merge” hint is suggested, but let’s test it:

SQL> select *
  2  from (
  3       select/*+ no_merge */ xf(t10.id) a
  4       from t10
  5       )
  6  where a*a >= 25
  7  /

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!

As you can see, the number of function calls wasn’t changed.
And if we look into the plan, we understood why:

SQL> @xplan +projection

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2027387203

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  VIEW              |      |     1 |    13 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T10  |     1 |     3 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "A"[NUMBER,22]
   2 - "T10"."ID"[NUMBER,22]

20 rows selected.

Now you see, that main problem is the “filter pushdown” transformation. Previously, if we were to disable the “filter pushdown” operation, we had to use a variety of tricks, such as “materialize” hint or adding the “rownum” in expession, etc. But all these solutions require rewriting the query.
But from 11.2.0.3 we can use “_optimizer_filter_pushdown” parameter, for example:

SQL> begin
  2    dbms_sqltune.import_sql_profile(
  3      sql_text    => 'select * from (select xf(t10.id) a from t10) where a*a >= 25'
  4     ,profile     => sys.sqlprof_attr(
  5                             q'[NO_MERGE(@SEL$2)]'
  6                            ,q'[OPT_PARAM('_OPTIMIZER_FILTER_PUSHDOWN' 'FALSE')]'
  7                          )
  8     ,category    => 'DEFAULT'
  9     ,name        => 'TEST_PROFILE'
 10     ,force_match => true
 11     ,replace     => true
 12    );
 13  end;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16
SQL> set serverout on
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired! -- extra execution because of sql*plus
Elapsed: 00:00:00.17
-- there are no extra calls when we fetches by 100 rows in pl/sql:
SQL> exec for r in (select * from (select xf(t10.id) a from t10) where a*a >= 25) loop null; end loop;
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22
With changing arraysize 

-- with fetching by 1 row:
SQL> declare
  2    cursor c is select * from (select xf(t10.id) a from t10) where a*a >= 25;
  3    n number;
  4  begin
  5    open c;
  6    loop
  7      fetch c into n;
  8      exit when c%notfound;
  9    end loop;
 10  end;
 11  /
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired!
F fired! -- 15
F fired!
F fired!
F fired!
F fired!
F fired! -- 20
F fired!
F fired! -- 22

PL/SQL procedure successfully completed.

-- with arraysize = 3
SQL> set arraysi 3
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired! -- 14
Elapsed: 00:00:00.45
SQL> set arraysi 2
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;

         A
----------
         5
         6
         7
         8
         9
        10

6 rows selected.

F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired!
F fired!
F fired! -- 16
Elapsed: 00:00:00.72

[collapse]

PS. I found that about “_optimizer_filter_pushdown” parameter already wrote Randolf Geist

Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)

Posted on by Posted in adaptive serial direct path reads, CBO, oracle, undocumented 1 Comment

Previously i showed not obvious example with hint “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X, rows=Y)“. Strictly speaking i don’t know how exactly cbo calculates number of index leaf blocks in that case: in those examples they was 1981 for “blocks=1, rows=50” and 49525 for “blocks=5, rows=10”.
But i know that with “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X)” i can set exact blocks number.
Also those test-cases didn’t show when occurs decision changing. So todays my test will show it.

UPD: btw previously I wrote that _very_large_object_threshold is a percentage of _db_block_buffers and Igor Usoltsev have tested and confirmed my guess

Test code 

/** SQLPLUS Example of controlling adaptive serial direct path reads through SQL profiles.
*/
set serverout on termout on timing off feed off;
clear scr;
def _IF_XT_RUNSTATS_EXISTS="--"
col if_xt_runstats_exists new_value _IF_XT_RUNSTATS_EXISTS noprint;
select decode(count(*),1,'  ','--') if_xt_runstats_exists 
from all_objects where object_name='XT_RUNSTATS' and rownum=1;
/**
 * Main test.
 * You can use it also in other clients, 
 * but in that case you have to manually 
 * set substitution variable _IF_XT_RUNSTATS_EXISTS:
 *  if you have not this package - to "--" 
 *  otherwise                    - to space(" ")
 * Latest version of XT_RUNSTATS you can get from:
 *  https://github.com/xtender/xt_runstats
 */

declare

   C_SQL_TEXT      constant varchar2(300):='SELECT SUM(A) FROM XT_IFFS T';
   C_PROFILE_NAME  constant varchar2(30) :='PRF_ADPR_TEST';
   v_small_table_threshold        int;
   v_db_block_buffers             int;
   v_very_large_object_threshold  int;
   v__db_cache_size               int;
      
   procedure exec(p varchar2) is
     e_table_is_not_created_yet exception;
     e_index_is_not_created_yet exception;
     pragma exception_init(e_table_is_not_created_yet, -942);
     pragma exception_init(e_index_is_not_created_yet, -1418);
   begin
     execute immediate p;
   exception 
      when e_table_is_not_created_yet 
        or e_index_is_not_created_yet
        then null;
   end;

   /** Creating table and setting stats */   
   procedure create_table is
   begin
     exec('drop table xt_iffs purge');
     exec('create table xt_iffs as 
           with gen as(
                       select level a,mod(level,10) b,lpad(1,50,1) c
                       from dual 
                       connect by level<=1e3
           )
           select gen.* 
           from gen,gen gen2'
         );
     --dbms_stats.set_table_stats('','XT_IFFS',numrows => 1e6,numblks => 5e5,avgrlen => 800);
     dbms_stats.gather_table_stats('','XT_IFFS');
   end;
   
   /** Recreating index and setting stats */
   procedure create_index is
   begin
     exec('drop index ix_iffs');
     exec('create index ix_iffs on xt_iffs(a,b)');
     dbms_stats.set_index_stats('','IX_IFFS',numrows => 1e6,numlblks => 1e4);
   end;
   
   /** Setting index numblks for query through SQL profile */
   procedure set_numblks(p_numblks int) is
     e_profile_not_created_yet  exception;
     pragma exception_init( e_profile_not_created_yet, -13833);
   begin
     begin
        dbms_sqltune.drop_sql_profile(C_PROFILE_NAME);
     exception when e_profile_not_created_yet then null;
     end;
     dbms_sqltune.import_sql_profile(
        sql_text    => C_SQL_TEXT,
        profile     => sys.sqlprof_attr(
                                 'INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("XT_IFFS"."A"))'
                                ,'INDEX_STATS("'||user||'"."XT_IFFS", "IX_IFFS", scale, blocks='||p_numblks||')'
                       ),
        category     => 'DEFAULT',
        name        => C_PROFILE_NAME,
        force_match => false,
        replace     => true
     );
   end; 
     
   procedure test( p_description varchar2
                 , p_numblks int default null) is

     type t_seg_stat is 
        table of number 
        index by v$segstat_name.name%type;
     -- segments statistics by index:
     cursor c_stats is
        select sn.name,nvl(st.value,0) value
        from v$segstat_name sn
            ,v$segment_statistics st 
        where 
              sn.statistic#     = st.statistic#(+)
          and st.owner(+)       = user 
          and st.object_name(+) ='IX_IFFS';
     -- var for previous stats:
     v_pre     t_seg_stat;
     v_delta   number;
     n         number;
   begin
     dbms_output.put_line('-');
     dbms_output.put_line('-');
     dbms_output.put_line(lpad('-',150,'-'));
     dbms_output.put_line(lpad('-',150,'-'));
     dbms_output.put_line(  '###                ' 
                         || p_description||': '
                         ||nvl(to_char(p_numblks),'default')||' blocks');
     dbms_output.put_line('-');

     create_index;
     -- if p_numblks is null then default stats used
     if p_numblks is not null then
       set_numblks(p_numblks);
     end if;
     execute immediate C_SQL_TEXT into n;
     exec('alter system flush buffer_cache');
     -- saving segment statistics
     for r in c_stats loop
        v_pre(r.name) := r.value;
     end loop;

     &_IF_XT_RUNSTATS_EXISTS  xt_runstats.init(p_latches => false);
     -- executing query
     execute immediate C_SQL_TEXT into n;
   &_IF_XT_RUNSTATS_EXISTS  xt_runstats.snap;
   &_IF_XT_RUNSTATS_EXISTS  xt_runstats.print(
   &_IF_XT_RUNSTATS_EXISTS     p_stats_mask => '(reads (cache|direct)\.)|index fast full scans \((full|direct)'
   &_IF_XT_RUNSTATS_EXISTS    ,p_sta_diff_pct => 0);

     -- printing segments stats delta:
     for r in c_stats loop
        v_delta:= r.value - v_pre(r.name);
        if v_delta!=0 then
          dbms_output.put_line( rpad(r.name,40,'.')||v_delta );
        end if;
     end loop;
   end;

   procedure load_and_print_params is
   begin
      select
         max(decode(a.ksppinm, '_small_table_threshold'       ,b.ksppstvl)) 
        ,max(decode(a.ksppinm, '_db_block_buffers'            ,b.ksppstvl))
        ,max(decode(a.ksppinm, '_very_large_object_threshold' ,b.ksppstvl))
        ,max(decode(a.ksppinm, '__db_cache_size'              ,b.ksppstvl))
         into v_small_table_threshold,v_db_block_buffers,v_very_large_object_threshold,v__db_cache_size
      from
        sys.x$ksppi a
       ,sys.x$ksppcv b
      where
           a.indx = b.indx
       and a.ksppinm in('_small_table_threshold','_db_block_buffers','_very_large_object_threshold','__db_cache_size');
      dbms_output.put_line('_small_table_threshold       = '||v_small_table_threshold);
      dbms_output.put_line('_db_block_buffers            = '||v_db_block_buffers);
      dbms_output.put_line('_very_large_object_threshold = '||v_very_large_object_threshold);
      dbms_output.put_line('__db_cache_size              = '||v__db_cache_size);
      
   end;
begin
  create_table;
  load_and_print_params;

  test( '_very_large_object_threshold + 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
  test( '_very_large_object_threshold'
       ,v_db_block_buffers * v_very_large_object_threshold/100     );
  test( '_very_large_object_threshold - 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 - 1 );

  test( '_db_block_buffers + 1'
       ,v_db_block_buffers + 1 );
  test( '_db_block_buffers - 1'
       ,v_db_block_buffers - 1 );
  
  test( '_small_table_threshold * 5 + 1'
       ,v_small_table_threshold * 5 + 1 );
  test( '_small_table_threshold * 5 - 1'
       ,v_small_table_threshold * 5 - 1 );

  test( ' 1 block ', 1);
  test( ' Default ', null);

  test( ' Again _very_large_object_threshold + 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
  test( ' Again _very_large_object_threshold'
       ,v_db_block_buffers * v_very_large_object_threshold/100     );

end;
/

[collapse]
Test results 
_small_table_threshold       = 166
_db_block_buffers            = 8347
_very_large_object_threshold = 500
__db_cache_size              = 4194304
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _very_large_object_threshold + 1: 41736 blocks
-
################     Results:      ##################
Run #  01 ran in 11 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |              1
physical reads direct................... |          2,491
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              1
##########################################################
-
logical reads...........................2496
physical reads..........................2492
physical read requests..................86
physical reads direct...................2491
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _very_large_object_threshold: 41735 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2496
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _very_large_object_threshold - 1: 41734 blocks
-
################     Results:      ##################
Run #  01 ran in 11 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          3,386
physical reads direct................... |              1
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _db_block_buffers + 1: 8348 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _db_block_buffers - 1: 8346 blocks
-
################     Results:      ##################
Run #  01 ran in 8 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _small_table_threshold * 5 + 1: 831 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                _small_table_threshold * 5 - 1: 829 blocks
-
################     Results:      ##################
Run #  01 ran in 7 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2496
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                 1 block : 1 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2512
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                 Default : default blocks
-
################     Results:      ##################
Run #  01 ran in 7 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2496
physical reads..........................2494
physical read requests..................95
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                 Again _very_large_object_threshold + 1: 41736 blocks
-
################     Results:      ##################
Run #  01 ran in 6 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |              1
physical reads direct................... |          2,491
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              1
##########################################################
-
logical reads...........................2496
physical reads..........................2492
physical read requests..................86
physical reads direct...................2491
segment scans...........................1
-
-
------------------------------------------------------------------------------
------------------------------------------------------------------------------
###                 Again _very_large_object_threshold: 41735 blocks
-
################     Results:      ##################
Run #  01 ran in 7 hsecs
##########################################################
Statistics                               | Run # 1
##########################################################
physical reads cache.................... |          2,494
physical reads direct................... |              0
index fast full scans (full)............ |              1
index fast full scans (direct read)..... |              0
##########################################################
-
logical reads...........................2496
physical reads..........................2494
physical read requests..................95
segment scans...........................1

PL/SQL procedure successfully completed.

[collapse]

Old test script for Index Fast Full Scan: controlling_direct_reads_with_profiles.sql

2015-05-26. Update #2: Thanks to Advait Deo for correcting common mistake about wrong calculation of the lower limit for direct path read decision: the right limit is just “_small_table_threshold” ( “_small_table_threshold” * 5 )

I have simplified the test for checking this behaviour:

Simplified test(table scan) 

/** SQLPLUS Example of controlling adaptive serial direct path reads through SQL profiles.
*/
set serverout on termout on timing off feed off;
clear scr;
/**
 * Main test.
 */
 
declare
 
   C_SQL_TEXT      constant varchar2(300):='SELECT SUM(A) FROM XT_IFFS T';
   v_small_table_threshold        int;
   v_db_block_buffers             int;
   v_very_large_object_threshold  int;
   v__db_cache_size               int;
       
   procedure exec(p varchar2) is
     e_table_is_not_created_yet exception;
     e_index_is_not_created_yet exception;
     pragma exception_init(e_table_is_not_created_yet, -942);
     pragma exception_init(e_index_is_not_created_yet, -1418);
   begin
     execute immediate p;
   exception 
      when e_table_is_not_created_yet 
        or e_index_is_not_created_yet
        then null;
   end;
 
   /** Creating table and setting stats */   
   procedure create_table is
   begin
     exec('drop table xt_iffs purge');
     exec('create table xt_iffs as 
           with gen as(
                       select level a,mod(level,10) b,lpad(1,50,1) c
                       from dual 
                       connect by level<=100
           )
           select gen.* 
           from gen,gen gen2'
         );
     dbms_stats.gather_table_stats('','XT_IFFS',no_invalidate => false);
     dbms_stats.set_table_stats('','XT_IFFS',numrows => 1e6,numblks => 5e5,avgrlen => 800,no_invalidate => false);
   end;
    
   /** Setting index numblks for query through SQL profile */
   procedure set_numblks(p_numblks int) is
     e_profile_not_created_yet  exception;
     pragma exception_init( e_profile_not_created_yet, -13833);
   begin
     dbms_stats.set_table_stats(user,'XT_IFFS',numrows => 1e6,numblks => p_numblks, avgrlen => 800,no_invalidate => false);
   end; 
      
   procedure test( p_description varchar2
                 , p_numblks int default null) is
     v_delta   number;
     n         number;
     
     type t_ses_stat is
        table of number 
        index by v$segstat_name.name%type;

     -- var for previous stats:
     v_pre     t_ses_stat;

     -- segments statistics by segment name:
     cursor c_stats is
        select name,value 
        from v$mystat st join v$statname sn using(STATISTIC#)
        where regexp_like(name,'^physical reads (cache|direct)$');
     
     procedure ses_stats_save is
     begin
       for r in c_stats loop
          v_pre(r.name) := r.value;
       end loop;
     end ses_stats_save;
     
     procedure ses_stats_print is
     begin
       for r in c_stats loop
          v_delta:= r.value - v_pre(r.name);
          if v_delta!=0 then
            dbms_output.put_line( rpad(r.name,40,'.')||v_delta );
          end if;
       end loop;
     end ses_stats_print;
     
   begin
     dbms_output.put_line(chr(160));
     dbms_output.put_line(lpad('-',150,'-'));
     dbms_output.put_line(  '###                '
                         || p_description||': '
                         ||nvl(to_char(p_numblks),'default')||' blocks');
     dbms_output.put_line(chr(160));
 
     --create_index;
     -- if p_numblks is null then default stats used
     if p_numblks is not null then
       set_numblks(p_numblks);
     end if;
     execute immediate C_SQL_TEXT into n;
     exec('alter system flush buffer_cache');
     -- saving segment statistics
     ses_stats_save;
 
     -- executing query
     execute immediate C_SQL_TEXT into n;
 
     -- printing segments stats delta:
     ses_stats_print;
   end;
 
   procedure load_and_print_params is
   begin
      select
         max(decode(a.ksppinm, '_small_table_threshold'       ,b.ksppstvl)) 
        ,max(decode(a.ksppinm, '_db_block_buffers'            ,b.ksppstvl))
        ,max(decode(a.ksppinm, '_very_large_object_threshold' ,b.ksppstvl))
        ,max(decode(a.ksppinm, '__db_cache_size'              ,b.ksppstvl))
         into v_small_table_threshold,v_db_block_buffers,v_very_large_object_threshold,v__db_cache_size
      from
        sys.x$ksppi a
       ,sys.x$ksppcv b
      where
           a.indx = b.indx
       and a.ksppinm in('_small_table_threshold','_db_block_buffers','_very_large_object_threshold','__db_cache_size');
      dbms_output.put_line('_small_table_threshold       = '||v_small_table_threshold);
      dbms_output.put_line('_db_block_buffers            = '||v_db_block_buffers);
      dbms_output.put_line('_very_large_object_threshold = '||v_very_large_object_threshold);
      dbms_output.put_line('__db_cache_size              = '||v__db_cache_size);
       
   end;
begin
  create_table;
  load_and_print_params;
 
  test( '_very_large_object_threshold + 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
  test( '_very_large_object_threshold'
       ,v_db_block_buffers * v_very_large_object_threshold/100     );
  test( '_very_large_object_threshold - 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 - 1 );
 
  test( '_db_block_buffers + 1'
       ,v_db_block_buffers + 1 );
  test( '_db_block_buffers - 1'
       ,v_db_block_buffers - 1 );
   
  test( '_small_table_threshold * 5 + 1'
       ,v_small_table_threshold * 5 + 1 );
  test( '_small_table_threshold * 5 - 1'
       ,v_small_table_threshold * 5 - 1 );

  test( '_small_table_threshold + 1'
       ,v_small_table_threshold + 1 );
  test( '_small_table_threshold'
       ,v_small_table_threshold     );
  test( '_small_table_threshold - 1'
       ,v_small_table_threshold - 1 );
 
  test( ' 1 block ', 1);
  test( ' Default ', null);
 
  test( ' Again _very_large_object_threshold + 1'
       ,v_db_block_buffers * v_very_large_object_threshold/100 + 1 );
  test( ' Again _very_large_object_threshold'
       ,v_db_block_buffers * v_very_large_object_threshold/100     );
 
end;
/

[collapse]

Results 

_small_table_threshold       = 2246
_db_block_buffers            = 112347
_very_large_object_threshold = 500
__db_cache_size              = 939524096

------------------------------------------------------------------------
###                _very_large_object_threshold + 1: 561736 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _very_large_object_threshold: 561735 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _very_large_object_threshold - 1: 561734 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _db_block_buffers + 1: 112348 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _db_block_buffers - 1: 112346 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _small_table_threshold * 5 + 1: 11231 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _small_table_threshold * 5 - 1: 11229 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _small_table_threshold + 1: 2247 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                _small_table_threshold - 1: 2245 blocks

physical reads cache....................88

------------------------------------------------------------------------
###                 1 block : 1 blocks

physical reads cache....................88

------------------------------------------------------------------------
###                 Default : default blocks

physical reads cache....................88

------------------------------------------------------------------------
###                 Again _very_large_object_threshold + 1: 561736 blocks

physical reads cache....................1
physical reads direct...................87

------------------------------------------------------------------------
###                 Again _very_large_object_threshold: 561735 blocks

physical reads cache....................1
physical reads direct...................87

[collapse]

Full test script: test_table.sql

Materialization in subquery factoring without hint “materialize” can be considered only when exists at least one predicate

Posted on by Posted in CBO, oracle, query optimizing, undocumented 6 Comments

I found just now that materialization can not be considered by optimizer if there are no predicates in subquery factoring clause. Of course, i mean cases without forcing materialization through hint “materialize”.
Simple example:

Spoiler 


SQL> create table tt1 as select 1 id from dual;

Table created.

SQL> exec dbms_stats.gather_table_stats('','TT1');

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  with gen as (select * from tt1)
  3  select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 486748850

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     6 |     6   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |     1 |     6 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | TT1  |     1 |     3 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | TT1  |     1 |     3 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL> explain plan for
  2  with gen as (select * from tt1 where 1=1)
  3  select * from gen,gen g2;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 2673059801

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |     1 |    26 |     7   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |       |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6610_6641830 |       |       |            |       |
|   3 |    TABLE ACCESS FULL       | TT1                        |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |   MERGE JOIN CARTESIAN     |                            |     1 |    26 |     4   (0)| 00:00:01 |
|   5 |    VIEW                    |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6610_6641830 |     1 |     3 |     2   (0)| 00:00:01 |
|   7 |    BUFFER SORT             |                            |     1 |    13 |     4   (0)| 00:00:01 |
|   8 |     VIEW                   |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   9 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6610_6641830 |     1 |     3 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

[collapse]

Update: I did some additional tests and found:

  1. with “table()” but without “xmltable” materialization occurs always regardless of existence of predicates or another tables in subquery factoring clause
  2. with “xmltable” behavior is very strange – decision about materialization depends on ‘columns …’ clause: when it exists – materialization occurs, if not – doesn’t.
  3. with “selects a subset of table columns” as David Aldridge said – decision still depends from predicates existence
Tests with table() were like this: 
with t as (select * from table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;
with t as (select * from t10,table(cast(:a as sys.ku$_vcnt)) /*where 1=0*/ )
select count(*) from t,t t2;

[collapse]

Tests with xmltable() were like this: 

with t as (select * from t10,xmltable(:a ) ttt where 1=1)
select count(*)
from t, t t1;
with t as (select * from t10,xmltable(:a columns n int) ttt where 1=0)
select count(*)
from t, t t1;
with t as (select/*+ no_merge */ * from table(cast(:a as sys.ku$_vcnt)),xmltable(:a) where 1=0 )
select count(*) from t,t t2;

[collapse]

Test with 2 from 1000 columns 

declare 
  c  varchar2(32767):='create table t_1000_cols as select ';
  c2 varchar2(32767);
begin
  for i in 1..1000 loop
    c2:=c2||',lpad(1,4000,1) c'||i;
  end loop;
  c:=c||ltrim(c2,',')||' from dual connect by level<=100';
  execute immediate c;
end;
/
exec dbms_stats.gather_table_stats('','T_1000_COLS');
alter session set tracefile_identifier = mat1000;
alter session set events='10053 trace name context forever, level 1';

with t as (select c1,c2 from t_1000_cols)
select count(*)
from t, t t2;

with t as (select c1,c2 from t_1000_cols where 1=1)
select count(*)
from t, t t2;

[collapse]

Bug in documentation about dbms_stats.gather_schema_stats or in the dbms_stats itself

Posted on by Posted in bug, CBO, documentation, oracle, statistics, undocumented 2 Comments

Recently I had to gather pending stats with Object Filter List by several objects, so I copied example from documentation and was surprised: instead of gather stats by specified filter list, oracle started to gather stats for all tables in current_schema! And ‘filter list’ applies only with dbms_stats.GATHER_DATABASE_STATS

UPD: Jared Still gave link to registered bug id, which i couldn’t find in MOS before:

Bug 12754926 – DBMS_STATS.gather_schema_stats does not work with an obj_filter_list parameter specified [ID 12754926.8]
Modified:04-Jan-2012 Type:PATCH Status:PUBLISHED
This issue is fixed in 11.2.0.4 (Future Patch Set)

Little example 

SQL> exec dbms_stats.delete_schema_stats('HR');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.31
SQL> begin
  2    for r in (select table_name
  3              from dba_tables t
  4              where t.owner='XTENDER'
  5              and table_name like 'TMP%'
  6    )loop
  7       dbms_stats.delete_table_stats('XTENDER',r.TABLE_NAME);
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> select
  2    owner
  3   ,table_name
  4   ,num_rows
  5   ,last_analyzed
  6  from dba_tab_statistics t
  7  where owner='XTENDER' and table_name like 'TMP%'
  8        or owner='HR';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HR                             COUNTRIES
HR                             DEPARTMENTS
HR                             EMPLOYEES
HR                             JOBS
HR                             JOB_HISTORY
HR                             LOCATIONS
HR                             REGIONS
XTENDER                        TMP
XTENDER                        TMP1
XTENDER                        TMP_CONTENT
XTENDER                        TMP_DATA
XTENDER                        TMP_DOCUMENT
XTENDER                        TMP_EXCH

13 rows selected.

Elapsed: 00:00:00.11
SQL> col dt new_value dt
SQL> select sysdate dt from dual;

DT
-------------------
2013-01-27 00:30:21

1 row selected.

Elapsed: 00:00:00.00
SQL> DECLARE
  2        filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
  3      BEGIN
  4        filter_lst.extend(2);
  5        filter_lst(1).ownname := 'XTENDER';
  6        filter_lst(1).objname := 'TMP%';
  7        filter_lst(2).ownname := 'HR';
  8        DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst,
  9                                       options => 'gather');
 10      END;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:16.89
SQL> select owner,table_name
  2  from dba_tables t
  3  where t.LAST_ANALYZED>timestamp'&dt'+0;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
XTENDER                        TT
XTENDER                        TR_LOG
XTENDER                        IOT1
...[skipped 171 rows]...
XTENDER                        DEPARTMENTS

175 rows selected.

Elapsed: 00:00:01.04

[collapse]

PS. Also there is a typo in the example from oracle documentation: ‘gather_stale’ instead of ‘gather stale’ – underline instead of blank space.

Differences between integer(int) in SQL and PL/SQL

Posted on by Posted in oracle, undocumented Leave a comment

Very simple example:

create table t_integer(i integer);
insert into t_integer values(1e125);
select * from t_integer;

declare
  i1 integer;
begin 
  select i into i1 from t_integer;
end;
/
declare
  i2 t_integer.i%type;
begin 
  select i into i2 from t_integer;
end;
/

Although variable and the column specified as “integer”, but in both blocks you will get error: ORA-06502: PL/SQL: numeric or value error: number precision too large.
Same error will be with “int“.
Describe shows wrong datatype:

> desc t_integer;
 Name                        Null?    Type
 --------------------------- -------- ----------
 I                                    NUMBER(38)

Really it would be number without precision and scale=0. You can see it in dba_tab_columns.
sys.standard:

  subtype INTEGER is NUMBER(38,0);
  subtype INT is INTEGER;

Also a couple simple good-known but often forgotten things:
1. integer as parameter type or return type:

SQL> create function f_integer(i integer)
  2    return integer is
  3  begin
  4    return i;
  5  end;
  6  /

Function created.

SQL> select f_integer(1/3) from dual;

F_INTEGER(1/3)
--------------
    .333333333

1 row selected.

2. Old finding from sql.ru – no casting in assignments (from 10.2 till 11.2, was found by Elic):

declare
  numberVar  number        := 1/3;
  numberVar2 number(20,10) := 1/3;
  procedure Test(Label varchar2, Value int)
  is
    LocalVar int := Value;
  begin
    dbms_output.put_line(Label || '         : ' || Value);
    dbms_output.put_line(Label || ' assigned: ' || LocalVar);
  end Test;
begin
  Test('|                 const', 1/3);
  Test('|            number var', numberVar);
  Test('|constrained number var', numberVar2);
end;
/
|                 const         : .3333333333333333333333333333333333333333
|                 const assigned: .3333333333333333333333333333333333333333
|            number var         : .3333333333333333333333333333333333333333
|            number var assigned: .3333333333333333333333333333333333333333
|constrained number var         : .3333333333
|constrained number var assigned: .3333333333

PL/SQL procedure successfully completed.