Tag Archives: query optimization

How to speed up slow unicode migration of a table with xmltype columns

Posted on by Posted in oracle, parallel, query optimizing, troubleshooting Leave a comment

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update  /*+ PARALLEL(A,16)*/ "RRR"."T_XMLDATA" A  set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16')

“SYS_NC00011$” was internal hidden CLOB column used to store XMLTYPE. As you can see DMU added PARALLEL hint, but though oracle can use parallel dml for xmltype since 12.1.0.1, we can’t use it because of its’ limitations:

Changes in Oracle Database 12c Release 1 (12.1.0.1) for Oracle XML DB

Parallel DML Support for XMLType
Support for parallel DML has been improved for XMLType storage model binary XML using SecureFiles LOBs. The performance and scalability have been improved for both CREATE TABLE AS SELECT and INSERT AS SELECT.

Restrictions on Parallel DML

Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

Btw, Oracle didn’t support parallel dml for xmltype on previous releases:

No Parallel DML for XMLType – DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)

So I had to use manual parallelization:
1. Monitor “Convert application tables” step through “View Table Conversion progress” and press “Stop” button during conversion of this table.
2. Create table with ROWIDs of this table and split them into 16 groups:

create table tmp_rids as 
select rowid rid, ntile(16)over(order by rowid) grp 
from t_xmldata;

3. Execute 

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1,FOREVER';

to avoid “ORA-22839: Direct updates on SYS_NC columns are disallowed”
4. Start 16 sessions and each of them have to update own part:

update t_xmldata A 
set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16') 
where rowid in (select rid from tmp_rids where grp=&grp);
commit;

5. Disable event 22838:

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT OFF';

6. Open “View Table Conversion progress” window, click on this table and change “Retry” to “Skip” option for the update step.

This simple method allowed to make unicode migration about 16 times faster.

WINDOW NOSORT STOPKEY + RANK()

Posted on by Posted in CBO, oracle, query optimizing, SQL Leave a comment

Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:

rnk1 

create table test(n not null) as 
  with gen as (select level n from dual connect by level<=100)
  select g2.n as n
  from gen g1, gen g2
  where g1.n<=10
/
create index ix_test on test(n)
/
exec dbms_stats.gather_table_stats('','TEST');
select/*+ gather_plan_statistics */ n
from (select rank()over(order by n) rnk
            ,n
      from test)
where rnk<=3
/
select * from table(dbms_xplan.display_cursor('','','allstats last'));
drop table test purge;

[collapse]
Output 

         N
----------
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1

10 rows selected.

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  8tbq95dpw0gw7, child number 0
-------------------------------------
select/*+ gather_plan_statistics */ n from (select rank()over(order by
n) rnk             ,n       from test) where rnk<=3

Plan hash value: 1892911073

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |     10 |00:00:00.01 |       3 |       |       |          |
|*  1 |  VIEW                  |         |      1 |   1000 |     10 |00:00:00.01 |       3 |       |       |          |
|*  2 |   WINDOW NOSORT STOPKEY|         |      1 |   1000 |     30 |00:00:00.01 |       3 | 73728 | 73728 |          |
|   3 |    INDEX FULL SCAN     | IX_TEST |      1 |   1000 |     31 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RNK"<=3)
   2 - filter(RANK() OVER ( ORDER BY "N")<=3)

[collapse]

As you can see, A-Rows in plan step 2 = 30 – ie, that is the number of rows where 

DENSE_RANK<=3

but not 

RANK<=3

The more effective way will be to stop after first 10 rows, because 11th row already has RANK more than 3!
But we can create own STOPKEY version with PL/SQL:

PLSQL STOPKEY version 

create or replace type rowids_table is table of varchar2(18);
/
create or replace function get_rowids_by_rank(
      n          int
     ,max_rank   int
   ) 
   return rowids_table pipelined
as
begin
   for r in (
      select/*+ index_rs_asc(t (n))  */ rowidtochar(rowid) chr_rowid, rank()over(order by n) rnk
      from test t
      where t.n > get_rowids_by_rank.n
      order by n
   )
   loop
      if r.rnk <= max_rank then
         pipe row (r.chr_rowid);
      else
         exit;
      end if;
   end loop;
   return;
end;
/
select/*+ leading(r t) use_nl(t) */
   t.*
from table(get_rowids_by_rank(1, 3)) r
    ,test t
where t.rowid = chartorowid(r.column_value)
/

[collapse]
In that case the fetch from a table will stop when rnk will be larger than max_rank

PRECOMPUTE_SUBQUERY hint

Posted on by Posted in CBO, hints, undocumented 3 Comments

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

SQL> select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in (select chr(level) from dual connect by level<=100);

D
-
X

SQL> @last

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c437vsqj7c4jy, child number 0
-------------------------------------
select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in
(select chr(level) from dual connect by level<=100)

Plan hash value: 272002086

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

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

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

   1 - filter(("DUMMY"='' OR "DUMMY"='' OR "DUMMY"='♥' OR "DUMMY"='♦'
              OR "DUMMY"='♣' OR "DUMMY"='♠' OR "DUMMY"='' OR "DUMMY"=' OR
              "DUMMY"=' ' OR "DUMMY"=' ' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=' '
              OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='►' OR "DUMMY"='◄' OR
              "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"='' OR "DUMMY"='↑' OR "DUMMY"='↓' OR "DUMMY"='' OR
              "DUMMY"=' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
              OR "DUMMY"=' ' OR "DUMMY"='!' OR "DUMMY"='"' OR "DUMMY"='#' OR
              "DUMMY"='$' OR "DUMMY"='%' OR "DUMMY"='&' OR "DUMMY"='''' OR
              "DUMMY"='(' OR "DUMMY"=')' OR "DUMMY"='*' OR "DUMMY"='+' OR "DUMMY"=','
              OR "DUMMY"='-' OR "DUMMY"='.' OR "DUMMY"='/' OR "DUMMY"='0' OR
              "DUMMY"='1' OR "DUMMY"='2' OR "DUMMY"='3' OR "DUMMY"='4' OR "DUMMY"='5'
              OR "DUMMY"='6' OR "DUMMY"='7' OR "DUMMY"='8' OR "DUMMY"='9' OR
              "DUMMY"=':' OR "DUMMY"=';' OR "DUMMY"='<' OR "DUMMY"='=' OR "DUMMY"='>'
              OR "DUMMY"='?' OR "DUMMY"='@' OR "DUMMY"='A' OR "DUMMY"='B' OR
              "DUMMY"='C' OR "DUMMY"='D' OR "DUMMY"='E' OR "DUMMY"='F' OR "DUMMY"='G'
              OR "DUMMY"='H' OR "DUMMY"='I' OR "DUMMY"='J' OR "DUMMY"='K' OR
              "DUMMY"='L' OR "DUMMY"='M' OR "DUMMY"='N' OR "DUMMY"='O' OR "DUMMY"='P'
              OR "DUMMY"='Q' OR "DUMMY"='R' OR "DUMMY"='S' OR "DUMMY"='T' OR
              "DUMMY"='U' OR "DUMMY"='V' OR "DUMMY"='W' OR "DUMMY"='X' OR "DUMMY"='Y'
              OR "DUMMY"='Z' OR "DUMMY"='[' OR "DUMMY"='\' OR "DUMMY"=']' OR
              "DUMMY"='^' OR "DUMMY"='_' OR "DUMMY"='`' OR "DUMMY"='a' OR "DUMMY"='b'
              OR "DUMMY"='c' OR "DUMMY"='d'))

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

Strange moving filter predicates from index to table

Posted on by Posted in CBO, curious, oracle 9 Comments

It seems strange to me:
When all needed columns are in the index, filter predicates are expectedly applied to the index

select a,b from xt_test where a=1 and
(:b is null or b = :b)

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |      1 |        |      1 |00:00:00.01 |       2 |
|*  1 |  INDEX RANGE SCAN| PK_XT_TEST |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------

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

   1 - access("A"=1)
       filter((:B IS NULL OR "B"=:B))

But if I add another column “PAD”, the filter moves to the table filters:

select a,b,pad from xt_test where a=1
and (:b is null or b = :b)

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |        |      1 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| XT_TEST    |      1 |      1 |      1 |       4 |
|*  2 |   INDEX RANGE SCAN          | PK_XT_TEST |      1 |     10 |     10 |       2 |
---------------------------------------------------------------------------------------

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

   1 - filter((:B IS NULL OR "B"=:B))
   2 - access("A"=1)

As workaround we can use something like that:

select--+ NO_ELIMINATE_JOIN(t) NO_ELIMINATE_JOIN(t2@sel$2) gather_plan_statistics
   a,b,pad
from xt_test t
where t.rowid in ( select t2.rowid
                   from xt_test t2
                   where a=1
                     and (:b is null or b = :b)
                 );

Plan hash value: 1464320522

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |        |      1 |      3 |
|   1 |  NESTED LOOPS               |            |      1 |      1 |      1 |      3 |
|*  2 |   INDEX RANGE SCAN          | PK_XT_TEST |      1 |      1 |      1 |      2 |
|   3 |   TABLE ACCESS BY USER ROWID| XT_TEST    |      1 |      1 |      1 |      1 |
--------------------------------------------------------------------------------------

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

   2 - access("A"=1)
       filter((:B IS NULL OR "B"=:B))
Full test case 
create table xt_test(a,b,pad,constraint pk_xt_test primary key(a,b))
as select 
      mod(rownum,10) a
     ,rownum         b
     ,rpad(rownum,10) pad
   from dual
   connect by level<=100;
call dbms_stats.gather_table_stats('','XT_TEST');
var b number;
exec :b:=1;
select/*+ gather_plan_statistics */
  a,b,pad
from xt_test 
where a=1 and (:b is null or b = :b);
select * from table(dbms_xplan.display_cursor('','','allstats last'));

select/*+ gather_plan_statistics */
  a,b
from xt_test 
where a=1 and (:b is null or b = :b);
select * from table(dbms_xplan.display_cursor('','','allstats last'));

select--+ NO_ELIMINATE_JOIN(t) NO_ELIMINATE_JOIN(t2@sel$2) gather_plan_statistics
   a,b,pad 
from xt_test t
where t.rowid in ( select t2.rowid 
                   from xt_test t2 
                   where a=1 
                     and (:b is null or b = :b)
                 );
select * from table(dbms_xplan.display_cursor('','','allstats last'));

[collapse]

Update:
I just forgot to mention that there is another workaround – to force concatenation:

SQL> select--+ use_concat(or_predicates(2))
  2     a,b,pad
  3  from xt_test where a=1 and (:b is null or b = :b);

         A          B PAD
---------- ---------- ----------------------------------------
         1          1 1

Plan hash value: 3582916188

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |     3 (100)|      1 |00:00:00.01 |       2 |      2 |
|   1 |  CONCATENATION                |            |      1 |        |            |      1 |00:00:00.01 |       2 |      2 |
|   2 |   TABLE ACCESS BY INDEX ROWID | XT_TEST    |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |      2 |
|*  3 |    INDEX UNIQUE SCAN          | PK_XT_TEST |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |      1 |
|*  4 |   FILTER                      |            |      1 |        |            |      0 |00:00:00.01 |       0 |      0 |
|   5 |    TABLE ACCESS BY INDEX ROWID| XT_TEST    |      0 |     10 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |
|*  6 |     INDEX RANGE SCAN          | PK_XT_TEST |      0 |     10 |     1   (0)|      0 |00:00:00.01 |       0 |      0 |
----------------------------------------------------------------------------------------------------------------------------

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

   3 - access("A"=1 AND "B"=:B)
   4 - filter(:B IS NULL)
   6 - access("A"=1)
       filter(LNNVL("B"=:B))

Unresolved quiz: Avoiding in-list iterator

Posted on by Posted in CBO, oracle, query optimizing 11 Comments

A couple days ago i had very interesting quiz, which is not resolved yet.
Look at this simplified query:

  select *
  from xt1,xt2
  where
       xt1.b=10
   and xt1.a=xt2.a
   and xt2.b in (1,2);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2715236140

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |   100 | 36900 |   501   (0)| 00:00:07 |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   501   (0)| 00:00:07 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:02 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|   5 |    INLIST ITERATOR            |        |       |       |            |          |
|*  6 |     INDEX RANGE SCAN          | IX_XT2 |     1 |       |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("XT1"."B"=10)
   6 - access("XT1"."A"="XT2"."A" AND ("XT2"."B"=1 OR "XT2"."B"=2))
Full test case 
create table xt1 as
select 
   level a
 , mod(level,1000) b
 , lpad(1,300,1) padding 
from dual
connect by level<=1e5;

create index ix_xt1 on xt1(b);

create table xt2 as
select 
   level        a
 , mod(level,5) b
 , lpad(1,50,1) padding 
from dual
connect by level<=1e6;

alter table xt2 
  add constraint uq_xt2
  unique (a)
  using index(create index ix_xt2 on xt2(a,b));

exec dbms_stats.gather_table_stats('','XT1',cascade=>true);
exec dbms_stats.gather_table_stats('','XT2',cascade=>true);

explain plan for 
select *
from xt1,xt2
where 
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2);

@?/rdbms/admin/utlxpls.sql

[collapse]

As you see, in such queries cbo always generating plans with INLIST ITERATOR, and it is reasonably in cases when there are many rows with different values of field B for most values of A, and this number is much larger than number of values in the “INLIST”. But in such case as shown, will be better to use index range scan with access by A and filter by B:

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2);

no rows selected

Statistics
----------------------------------------------------------
        ...
        505  consistent gets
SQL> -- without inlist iterator:
SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b+0 in (1,2);

no rows selected

Statistics
----------------------------------------------------------
        ...
        305  consistent gets

But how we can do it? I know 5 options:
1. Trace event 10157
2. Rewrite code. for example replacing “b in (1,2)” to “b+0 in (1,2)”
3. Changing query with “Advanced query rewrite” (DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE)
4. Recreating index from xt2(a,b) to xt2(a,1,b)
5. Changing optimizer_mode to “rule” through hint or SQL profile/baseline

But unfortunately all of them are inapplicable for the my real problem, because i cannot for some reasons rewrite query or change query with advanced rewrite, cannot recreate/add index, and can’t change optimizer_mode, because execution plan for the real query will become worst than plan generated with CBO with inlist iterator(some operations aren’t exists in RBO).

Could anybody suggest any another solution?

UPDATE #1:
This bug is fixed now in 12.2, and patch 16516751 is available now for 11.2.0.3 Solaris64.
Changes:
1. CBO can consider filters in such cases now
2. Hint NUM_INDEX_KEYS fixed and works fine

UPDATE #2:
Very interesting solution by Igor Usoltsev(in russian):
Ignored hint USE_CONCAT(OR_PREDICATES(N)) allows to avoid inlist iterator.
Example:

select--+ USE_CONCAT(OR_PREDICATES(32767))
 * from xt1,xt2
where
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2)
/

Plan hash value: 2884586137          -- good plan:
 
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2))

From 10053 trace on nonpatched 11.2.0.3:
inlist_concat_diff_10053

About unnecessary work with predicate “field=:bind_variable” where bind_variable is null

Posted on by Posted in CBO, oracle, query optimizing Leave a comment

Although we know that in the case when we do select from a table and one of the predicates is “field=:bind_variable” and :bind_variable is null, we should not get the rows, oracle not always “think” the same and don’t add upper filter predicate “:bind_variable is not null”, so it can do unnecessary work. It is completely depends from a plan, which will be chosen. Of course this applies to other predicates like >, < or != too. For example, there will not be any reads only if it is an access predicate on index range/unique scan or full table scan on 11.2.0.3 and with gathered stats. If it is FTS on previous versions, then only segment header is read. In others cases oracle will do useless scans. So if bind variable can be null and you want to be sure that oracle will not do futile work in such cases, just add predicate ":bind_variable is not null". This note is just aggregated info from recent question from our forum where i participated

Update:The explanation about segment header reads you can listen from Enkitec.tv by Tanel Poder

Full test code you can download as file – test.sql.

Little example #1 

set timing off feed off

create table XT_NULLS_TEST(a not null, b not null,c)
   as 
      select
            level a
         ,  mod(level,100) b
         ,  lpad(1,100,1)  c 
      from dual 
      connect by level<=1e5;

create index IX_NULLS_TEST on XT_NULLS_TEST(a);

exec dbms_stats.gather_table_stats('','XT_NULLS_TEST');


------------------- Main test with statistics: ----------------------------------------------
-- Variable with null:
var v_null number;

exec xt_runstats.init(p_latches => false);

-- INDEX RANGE SCAN:
select/*+ INDEX(XT_NULLS_TEST IX_NULLS_TEST)       */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('IRS');

-- FULL TABLE SCAN:
select/*+ FULL(XT_NULLS_TEST)                      */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('FTS');

-- INDEX FAST FULL SCAN:
select/*+ INDEX_FFS(XT_NULLS_TEST IX_NULLS_TEST)   */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('IFFS');

--Results
set serveroutput on
exec xt_runstats.print(p_stats_mask => 'reads|buff.*gets|consistent gets',p_sta_diff_pct => 1);

drop table xt_nulls_test purge;

Results:

SQL> @test.sql.txt

       CNT
----------
         0

       CNT
----------
         0

       CNT
----------
         0
################     Results:      ##################
Run #  01 ran in 0 hsecs
Run #  02 ran in 0 hsecs
Run #  03 ran in 1 hsecs
############################################################################################
Statistics                               | IRS            | FTS            | IFFS
############################################################################################
session logical reads................... |              0 |              1 |            230
consistent gets......................... |              0 |              1 |            230
consistent gets from cache.............. |              0 |              1 |            230
consistent gets from cache (fastpath)... |              0 |              1 |            230
############################################################################################

[collapse]

Example #2
DDL 

SQL> alter session set optimizer_dynamic_sampling=0;
SQL> alter session set statistics_level=all;
SQL> create table xt_test as select 1 i from dual;

Table created.

[collapse]
NULL 1 

SQL> -- NULL 1:
SQL> var a number;

SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a

Plan hash value: 3713359643

---------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |      0 |00:00:00.01 |       1 |
|*  1 |  TABLE ACCESS FULL| XT_TEST |      1 |      3 |      0 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("I"=:A)

[collapse]
Not NULL 1 

SQL> -- NOT NULL 1:
SQL> exec :a := 0;

PL/SQL procedure successfully completed.

SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a

Plan hash value: 3713359643

------------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |      0 |00:00:00.01 |       3 |      1 |
|*  1 |  TABLE ACCESS FULL| XT_TEST |      1 |      3 |      0 |00:00:00.01 |       3 |      1 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("I"=:A)

[collapse]
Null 2 

SQL> alter table xt_test add primary key(i);

Table altered.

SQL> exec :a := null;

PL/SQL procedure successfully completed.

SQL> -- NULL 2:
SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a

Plan hash value: 136758570

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |      1 |        |      0 |00:00:00.01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_C00161305 |      1 |      1 |      0 |00:00:00.01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("I"=:A)

[collapse]
Not NULL 2 

SQL> exec :a := 0;

PL/SQL procedure successfully completed.

SQL> -- NOT NULL 2:
SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a

Plan hash value: 136758570

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |      1 |        |      0 |00:00:00.01 |       1 |
|*  1 |  INDEX UNIQUE SCAN| SYS_C00161305 |      1 |      1 |      0 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("I"=:A)

[collapse]