DMLs and the Columnar Cache on ADW

Posted on by Posted in 12.2, inmemory, oracle Leave a comment

One of the main performance technologies underlying ADW is the In-Memory format column cache and a question that has come up several times is: how does the columnar cache handle DMLs and cache invalidations. This blog post attempts to answer that question.

The Two Columnar Cache Formats

The original columnar cache was an idempotent rearrangement of Hybrid Columnar Compressed blocks into pure columnar form still in the original HCC encoding. This is known internally as “CC1” and was not applicable to row format blocks. Because this is an idempotent transformation we are able to reconstitute the original raw blocks from a CC1 cache if needed.

We then introduced the In-Memory format columnar cache where, if you had an In-Memory licence, we would run the 1 MB chunks processed by Smart Scan through the In-Memory loader and write new clean columns encoded in In-Memory formats which meant that we could then use the new SIMD based predicate evaluation and other performance improvements developed for Database In-Memory. If you do not have an In-Memory licence, the original CC1 column cache is used. Another advantage of the CC2 format is that we can load row format blocks as well as HCC format blocks into pure columnar In-Memory format.  

Continue reading→

SQL: Fast ways to count unique characters in the string

Posted on by Posted in oracle, PL/SQL optimization, query optimizing, SQL Leave a comment

Test data:

create table t_str as
select round(dbms_random.value(1e10,9e10)) str from dual connect by level<=1e5
/

PL/SQL variant:

with
  function ff(s varchar2) return varchar2 
  as
      type avarchars is table of varchar2(100) index by varchar2(1);
      st  avarchars;
      idx varchar2(1);
      res varchar2(10);
      
      function iterate( idx in out nocopy varchar2, arr in out nocopy avarchars) 
         return boolean
      as --pragma inline;
      begin
         if idx is null
            then idx:=arr.first; 
            else idx:=arr.next(idx);
         end if;
         return idx is not null;
      end;  
   begin
     for i in 1..length(s) loop
        st(substr(s,i,1)):=1;
     end loop;
     while iterate(idx,st) loop
        res:=res||idx;
     end loop;
     return res;
   end;

select min(ff(str)) res
from t_str
/

SQL-only variant:

select min(fstr)
from t_str t
     cross apply (
     select listagg(c) within group (order by 1) fstr
     from (select
            distinct substr(t.str, level, 1) c
           from dual
           connect by level <= length(t.str)
          )
     )
/

Timings:

SQL> create table t_str as
  2  select round(dbms_random.value(1e10,9e10)) str from dual connect by level<=1e5
  3  /

Table created.

Elapsed: 00:00:00.55
SQL> with
  2    function ff(s varchar2) return varchar2
  3    as
  4        type avarchars is table of varchar2(100) index by varchar2(1);
  5        st  avarchars;
  6        idx varchar2(1);
  7        res varchar2(10);
  8
  9        function iterate( idx in out nocopy varchar2, arr in out nocopy avarchars)
 10           return boolean
 11        as --pragma inline;
 12        begin
 13           if idx is null
 14              then idx:=arr.first;
 15              else idx:=arr.next(idx);
 16           end if;
 17           return idx is not null;
 18        end;
 19     begin
 20       for i in 1..length(s) loop
 21          st(substr(s,i,1)):=1;
 22       end loop;
 23       while iterate(idx,st) loop
 24          res:=res||idx;
 25       end loop;
 26       return res;
 27     end;
 28
 29  select min(ff(str)) res
 30  from t_str
 31  /

RES
--------------------------------------------------------------
0123

Elapsed: 00:00:00.48
SQL> select min(fstr) res2
  2  from t_str t
  3       cross apply (
  4       select listagg(c) within group (order by 1) fstr
  5       from (select
  6              distinct substr(t.str, level, 1) c
  7             from dual
  8             connect by level <= length(t.str)
  9            )
 10       )
 11  /

RES2
--------------------------------------------------------------
0123

Elapsed: 00:00:01.01

And much easier variant if you need your strings contain digits only:

select min(translate('0123456789', translate('z0123456789','z'||str,'z'), chr(0)))
from t_str

Thoughts on Big Data SQL SmartScan

Posted on by Posted in cell_offload, External tables, oracle, SmartScan Leave a comment

Just a few thoughts I’ve been meaning to blog for a while:

1. Number of columns that can be offloaded

Exadata

Very early on in the Exadata Smart Scan implementation a restriction was added to prevent offloading more than 255 columns. This was done because of performance issues observed by customers. Smart Scan works on 1 MB chunks at a time and needs all the row pieces up to the highest #segcol referenced to be present in the 1 MB chunk that is being processed. When rows with more than 255 columns are first inserted all the row pieces are contiguous and hence likely to be present in the 1 MB chunk.

However, if the DBA has not provided sufficient freespace (PCTFREE) in the block when it is first loaded subsequent DMLs may end up splitting row pieces and moving pieces to blocks in the freelist that have more space available. PCTFREE specifies how much empty space to leave for future updates when a block is first populated.

Continue reading→

Reading and analyzing trace file contents using just SQL

Posted on by Posted in 12.2, oracle, trace, troubleshooting Leave a comment

Simple example: tracefiles for the last 5 days:

select fc.* 
from v$diag_trace_file f
     join v$diag_trace_file_contents fc
          on f.adr_home=fc.adr_home
          and f.trace_filename=fc.trace_filename
where f.modify_time >= systimestamp - interval'5' minute
  and fc.timestamp  >= systimestamp - interval'5' minute
  and fc.component_name = 'SQL_Trace'
  --and fc.section_name like 'kests%'
  ;
--or:
select tr.*
  from v$diag_app_trace_file tf,
       v$diag_sql_trace_records tr
 where tf.sql_trace = 'Y'
   and tf.modify_time > systimestamp - interval'5'minute
   and tr.adr_home = tf.adr_home
   and tr.trace_filename = tf.trace_filename
   and tr.timestamp > systimestamp - interval'5'minute;

Laterals: is (+) documented for laterals?

Posted on by Posted in bug, CBO, documentation, oracle Leave a comment

I know this syntax for a long time, since when lateral() was not documented yet, but recently I found a bug: the following query successfully returns 1 row:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     --left outer join c on c.c=bb.b
where a=1;

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

But doesn’t if we uncomment “left join”:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a,
     lateral(select * from b where a.a=b.b)(+) bb
     left outer join c on c.c=bb.b
where a=1;

no rows selected

And outer apply works fine:

with a as (select level a from dual connect by level<10)
    ,b as (select 0 b from dual)
    ,c as (select 0 c from dual)
select
  *
from a
     outer apply (select * from b where a.a=b.b) bb
     left outer join c on c.c=bb.b
where a=1;

         A          B          C
---------- ---------- ----------
         1

flashback_transaction_query: fixed table full table scan

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

Recently I got a question: why CBO chooses “FIXED TABLE FULL TABLE” scan instead of “FIXED INDEX” in case of a join to flashback_transaction_query view with the predicate “q.xid = a.versions_xid”:

select
  versions_xid,
  versions_startscn,
  versions_starttime,
  versions_endscn,
  versions_endtime,
  --scn_to_timestamp,
  to_char(a.classification), a.* 
from vms.advisory_arch_20190624 versions between
         timestamp timestamp'2019-06-24 13:28:00' and maxvalue a
    join flashback_transaction_query q
      on q.xid = a.versions_xid
 where a.advisoryid = 100511;

As you can see flashback_transaction_query is just a wrapper view for SYS.X$KTUQQRY, ie fixed table, which obviously has good “fixed index” for that:

And simple query with the predicate xid=hextoraw(…) uses it:

In such cases we need to check another part of the equality predicate, because usually it means that we use some function instead of literal, bind variable or SQL operator. Simple example with v$session_event:

explain plan for
SELECT SUM(E.TIME_WAITED)/100 AS TIME_WAITED 
FROM V$SESSION_EVENT E 
WHERE E.SID = SYS_CONTEXT('userenv', 'sid')
AND (E.EVENT = 'TCP Socket (KGAS)' OR E.EVENT LIKE 'SQL*Net%dblink');

But it works fine with bind variables or if we use “precompute_subquery” hint to replace function call with the computed literal:

SQL> explain plan for
  2  SELECT  SUM(E.TIME_WAITED)/100 AS TIME_WAITED
  3  FROM  V$SESSION_EVENT E
  4  WHERE E.SID = :sid
  5    AND (E.EVENT = 'TCP Socket (KGAS)' OR E.EVENT LIKE 'SQL*Net%dblink');

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 404853953

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    47 |     1 (100)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    47 |            |          |
|   2 |   NESTED LOOPS            |                 |     3 |   141 |     1 (100)| 00:00:01 |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLES (ind:1) |   403 |  6851 |     1 (100)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    30 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - filter("S"."KSLESSID"=TO_NUMBER(:SID) AND "S"."KSLESWTS"<>0 AND
              "S"."INST_ID"=USERENV('INSTANCE'))
   4 - filter(("D"."KSLEDNAM"='TCP Socket (KGAS)' OR "D"."KSLEDNAM" LIKE
              'SQL*Net%dblink') AND "S"."KSLESENM"="D"."INDX")
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
  1  explain plan for
  2  SELECT SUM(E.TIME_WAITED)/100 AS TIME_WAITED
  3  FROM
  4   V$SESSION_EVENT E
  5  WHERE E.SID in (select/*+ precompute_subquery */ SYS_CONTEXT('userenv', 'sid') from dual) AND (E.EVENT =
  6*   'TCP Socket (KGAS)' OR E.EVENT LIKE 'SQL*Net%dblink')
SQL> /

Explained.

SQL> @xplan ""



PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2330447422

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    50 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    50 |            |          |
|   2 |   NESTED LOOPS            |                 |     3 |   150 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FIXED INDEX| X$KSLES (ind:1) |   514 | 10280 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    30 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - filter("S"."KSLESSID"=382 AND "S"."KSLESWTS"<>0 AND ("S"."CON_ID"=0 OR
              "S"."CON_ID"=3) AND "S"."INST_ID"=USERENV('INSTANCE'))
   4 - filter("S"."KSLESENM"="D"."INDX" AND ("D"."KSLEDNAM"='TCP Socket (KGAS)' OR
              "D"."KSLEDNAM" LIKE 'SQL*Net%dblink'))

19 rows selected.

So the same problem we got with flashback_transaction_query, because VERSIONS_XID is a pseudocolumn in fact (ie function). So we can easily hide it in inline view: 

SQL> ;
  1  explain plan for
  2  with a as (
  3  select/*+ no_merge */
  4         versions_xid, versions_startscn, versions_starttime, versions_endscn, versions_endtime, --scn_to_timestamp,
  5         to_char(x.classification),
  6         x.*
  7    from advisory_arch_20190624 versions between timestamp to_timestamp('2019-06-25 22:14:00', 'YYYY-MM-DD HH24:MI:SS') and maxvalue x
  8    where x.advisoryid = 100511
  9  )
 10  select/*+ leading(a q) use_nl(q) index(q (xid)) */ *
 11  from a
 12*      join flashback_transaction_query q on q.xid = a.versions_xid
SQL> /

Explained.

SQL> @xplan +outline

P_FORMAT
------------------------
typical +outline

1 row selected.


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2200855354

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                        |     1 |  2596 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |                        |     1 |  2596 |     6   (0)| 00:00:01 |
|   2 |   VIEW                   |                        |     1 |    98 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL     | ADVISORY_ARCH_20190624 |     1 |     9 |     6   (0)| 00:00:01 |
|*  4 |   FIXED TABLE FIXED INDEX| X$KTUQQRY (ind:1)      |     1 |  2498 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$4" "X"@"SEL$4")
      USE_NL(@"SEL$62A6D27E" "X$KTUQQRY"@"SEL$3")
      LEADING(@"SEL$62A6D27E" "A"@"SEL$1" "X$KTUQQRY"@"SEL$3")
      FULL(@"SEL$62A6D27E" "X$KTUQQRY"@"SEL$3")
      NO_ACCESS(@"SEL$62A6D27E" "A"@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$3" >"SEL$2")
      OUTLINE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$335DD26A" >"SEL$1")
      OUTLINE(@"SEL$5C160134")
      OUTLINE(@"SEL$5")
      MERGE(@"SEL$5C160134" >"SEL$5")
      OUTLINE_LEAF(@"SEL$62A6D27E")
      OUTLINE_LEAF(@"SEL$4")
      ALL_ROWS
      DB_VERSION('18.1.0')
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   3 - filter("X"."ADVISORYID"=100511)
   4 - filter("XID"="A"."VERSIONS_XID" AND ("CON_ID"=0 OR "CON_ID"=3))

And in addition a couple of my scripts for fixed tables:

https://github.com/xtender/xt_scripts/blob/master/fixed_tables.sql

https://github.com/xtender/xt_scripts/blob/master/fixed_indexes.sql

SQL: Split periods

Posted on by Posted in oracle, SQL Leave a comment

Some time ago there was a question on the sql.ru about splitting validity periods for gym cards to enabled and blocked periods:

-- we have a table with gym cards (card id, validity period: from/to)
CREATE TABLE cards(id_card NUMBER, num_card VARCHAR2(100), dt_from DATE, dt_to DATE);
-- periods when cards were blocked (record id, card id, dates from/to)
CREATE TABLE blocks(id_block NUMBER, id_card NUMBER, dt_from DATE, dt_to DATE);
-- cards data:
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
INSERT INTO cards (id_card, num_card, dt_from, dt_to) VALUES (3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY'));
-- blocks data:
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY'));
INSERT INTO blocks (id_block, id_card, dt_from, dt_to) VALUES (201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY'));
COMMIT;

Just for fun, I’ve solved it using match_recognise:

with 
 cards (id_card, num_card, dt_from, dt_to) as (
   select 1,'111111', TO_DATE('01.09.2016','DD.MM.YYYY'), TO_DATE('01.09.2019','DD.MM.YYYY') from dual union all
   select 2,'222222', TO_DATE('06.10.2017','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual union all
   select 3,'333333', TO_DATE('09.05.2019','DD.MM.YYYY'), TO_DATE('31.12.2050','DD.MM.YYYY') from dual 
),blocks (id_block, id_card, dt_from, dt_to) as (
   select 101, 1, TO_DATE('20.03.2017','DD.MM.YYYY'), TO_DATE('09.04.2017','DD.MM.YYYY') from dual union all
   select 102, 1, TO_DATE('14.01.2018','DD.MM.YYYY'), TO_DATE('03.02.2018','DD.MM.YYYY') from dual union all
   select 103, 1, TO_DATE('18.02.2019','DD.MM.YYYY'), TO_DATE('10.03.2019','DD.MM.YYYY') from dual union all
   select 201, 2, TO_DATE('02.08.2018','DD.MM.YYYY'), TO_DATE('10.11.2018','DD.MM.YYYY') from dual
)
,data as (
   select *
   from (
         select * 
         from (
            select id_card,dt_from,dt_to,'c' t from cards
            union all
            select id_card,dt_to,dt_from,'b' from blocks
         ) v
         --where v.id_card=1 -- here we can filter card ID if needed
        ) piv
   unpivot(dt for x in (dt_from as 'dt_from', dt_to as 'dt_to'))
)
select * 
from data
match_recognize (
   partition by id_card
   order by dt /* we can modify add "t" into "order by clause" to make it more deterministic */
   measures
      match_number() as N,
      first(dt) as dt_from,
      last(dt) as dt_to
   pattern (x_start+ x_end)
   define
      x_start as x = 'dt_from'
     ,x_end   as x = 'dt_to'
);

Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions

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

You may know that table() (kokbf$ collection functions), xmltable and json_table functions block Join-Predicate PushDown(JPPD).

Simple example:

DDL 

create table xtest(a, b, c) as
select mod(level,1000),level,rpad('x',100,'x')
from dual
connect by level<=1e4
/
create index itest on xtest(a)
/
create or replace view vtest as
select a,count(b) cnt
from xtest
group by a
/
call dbms_stats.gather_table_stats(user,'xtest');
/

[collapse]

select distinct v.* 
from table(sys.odcinumberlist(1,2,3)) c, vtest v
where v.a = c.column_value;

Plan hash value: 699667151

-------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |       |     1 |    19 |    80   (4)| 00:00:01 |
|   1 |  HASH UNIQUE                            |       |     1 |    19 |    80   (4)| 00:00:01 |
|*  2 |   HASH JOIN                             |       |     1 |    19 |    79   (3)| 00:00:01 |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|       |     1 |     2 |    29   (0)| 00:00:01 |
|   4 |    VIEW                                 | VTEST |  1000 | 17000 |    49   (3)| 00:00:01 |
|   5 |     HASH GROUP BY                       |       |  1000 |  8000 |    49   (3)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                  | XTEST | 10000 | 80000 |    48   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   2 - access("V"."A"=VALUE(KOKBF$))

same for json_table 

select/*+ cardinality(c 1) use_nl(v) push_pred(v) */ * 
from json_table('{"a":[1,2,3]}', '$.a[*]' COLUMNS (a int PATH '$')) c
    ,vtest v
where c.a = v.a;

Plan hash value: 664523328

--------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    28 |    78   (2)| 00:00:01 |
|   1 |  NESTED LOOPS          |       |     1 |    28 |    78   (2)| 00:00:01 |
|   2 |   JSONTABLE EVALUATION |       |       |       |            |          |
|*  3 |   VIEW                 | VTEST |     1 |    26 |    49   (3)| 00:00:01 |
|   4 |    SORT GROUP BY       |       |  1000 |  8000 |    49   (3)| 00:00:01 |
|   5 |     TABLE ACCESS FULL  | XTEST | 10000 | 80000 |    48   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   3 - filter("V"."A"="P"."A")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$F534CA49 / V@SEL$1
         U -  push_pred(v)

[collapse]
same for xmltable 

select/*+ leading(c v) cardinality(c 1) use_nl(v) push_pred(v) */ v.*
from  xmltable('(1,3)' columns a int path '.') c,vtest v
where  c.a = v.a(+);

Plan hash value: 564839666

------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |     1 |    28 |    78   (2)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER                |                       |     1 |    28 |    78   (2)| 00:00:01 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |     1 |     2 |    29   (0)| 00:00:01 |
|*  3 |   VIEW                             | VTEST                 |     1 |    26 |    49   (3)| 00:00:01 |
|   4 |    SORT GROUP BY                   |                       |  1000 |  8000 |    49   (3)| 00:00:01 |
|   5 |     TABLE ACCESS FULL              | XTEST                 | 10000 | 80000 |    48   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   3 - filter("V"."A"(+)=CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,54525952,0),
              50,1,2)) AS int ))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$6722A2F6 / V@SEL$1
         U -  push_pred(v)

[collapse]

And compare with this:

create global temporary table temp_collection(a number);

insert into temp_collection select * from table(sys.odcinumberlist(1,2,3));

select/*+ cardinality(c 1) no_merge(v) */
   distinct v.* 
from temp_collection c, vtest v
where v.a = c.a;

Plan hash value: 3561835411

------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |     1 |    26 |    41   (3)| 00:00:01 |
|   1 |  HASH UNIQUE                             |                 |     1 |    26 |    41   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                           |                 |     1 |    26 |    40   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                     | TEMP_COLLECTION |     1 |    13 |    29   (0)| 00:00:01 |
|   4 |    VIEW PUSHED PREDICATE                 | VTEST           |     1 |    13 |    11   (0)| 00:00:01 |
|*  5 |     FILTER                               |                 |       |       |            |          |
|   6 |      SORT AGGREGATE                      |                 |     1 |     8 |            |          |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| XTEST           |    10 |    80 |    11   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN                  | ITEST           |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   5 - filter(COUNT(*)>0)
   8 - access("A"="C"."A")

You can see that JPPD works fine in case of global temporary tables and, obviously, the first workaround is to avoid such functions with complex views.
But in such simple queries you have 2 other simple options:
1. you can avoid JPPD and get optimal plans using CVM(complex view merge) by just simply rewriting the query using IN or EXISTS:

select * 
from vtest v
where v.a in (select/*+ cardinality(c 1) */ c.* from table(sys.odcinumberlist(1,2,3)) c);

Plan hash value: 1474391442

---------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |       |    10 |   100 |    42   (5)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT                     |       |    10 |   100 |    42   (5)| 00:00:01 |
|   2 |   NESTED LOOPS                            |       |    10 |   100 |    41   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                           |       |    10 |   100 |    41   (3)| 00:00:01 |
|   4 |     SORT UNIQUE                           |       |     1 |     2 |    29   (0)| 00:00:01 |
|   5 |      COLLECTION ITERATOR CONSTRUCTOR FETCH|       |     1 |     2 |    29   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                      | ITEST |    10 |       |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID            | XTEST |    10 |    80 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   6 - access("A"=VALUE(KOKBF$))

the same with json_table and xmltable 

select * 
from vtest t
where t.a in (select/*+ cardinality(v 1) */ v.a from json_table('{"a":[1,2,3]}', '$.a[*]' COLUMNS (a int PATH '$')) v);

Plan hash value: 2910004067

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    10 |   100 |    42   (5)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT         |       |    10 |   100 |    42   (5)| 00:00:01 |
|   2 |   NESTED LOOPS                |       |    10 |   100 |    41   (3)| 00:00:01 |
|   3 |    NESTED LOOPS               |       |    10 |   100 |    41   (3)| 00:00:01 |
|   4 |     SORT UNIQUE               |       |       |       |            |          |
|   5 |      JSONTABLE EVALUATION     |       |       |       |            |          |
|*  6 |     INDEX RANGE SCAN          | ITEST |    10 |       |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID| XTEST |    10 |    80 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   6 - access("A"="P"."A")

select v.*
from  vtest v
where exists(select/*+ cardinality(c 1) */ 1 from xmltable('(1,3)' columns a int path '.') c where c.a = v.a);

Plan hash value: 1646016183

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                       |    10 |   100 |    42   (5)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT                 |                       |    10 |   100 |    42   (5)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                       |    10 |   100 |    41   (3)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                       |    10 |   100 |    41   (3)| 00:00:01 |
|   4 |     SORT UNIQUE                       |                       |     1 |     2 |    29   (0)| 00:00:01 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |     1 |     2 |    29   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | ITEST                 |    10 |       |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID        | XTEST                 |    10 |    80 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   6 - access("A"=CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,54525952,0),50,1,2)) AS int ))

[collapse]

2. Avoid JPPD using lateral():

select/*+ cardinality(c 1) no_merge(lat) */
   distinct lat.* 
from table(sys.odcinumberlist(1,2,3)) c, 
     lateral(select * from vtest v where v.a = c.column_value) lat;

Plan hash value: 18036714

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |    10 |   190 |    41   (3)| 00:00:01 |
|   1 |  HASH UNIQUE                            |                 |    10 |   190 |    41   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                          |                 |    10 |   190 |    40   (0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|                 |     1 |     2 |    29   (0)| 00:00:01 |
|   4 |    VIEW                                 | VW_LAT_4DB60E85 |    10 |   170 |    11   (0)| 00:00:01 |
|   5 |     SORT GROUP BY                       |                 |    10 |    80 |    11   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| XTEST           |    10 |    80 |    11   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN                  | ITEST           |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   7 - access("A"=VALUE(KOKBF$))

Let’s see a bit more complex query:

Test tables 2 

create table xtest1(id primary key, a) as
  select level,level from dual connect by level<=1000;

create table xtest2(a, b, c) as
   select mod(level,1000),level,rpad('x',100,'x')
   from dual
   connect by level<=1e4
/
create index itest2 on xtest2(a)
/
create or replace view vtest2 as
select a,count(b) cnt
from xtest2
group by a
/

[collapse]

select v.* 
from xtest1 t1,
     vtest2 v
where t1.id in (select/*+ cardinality(c 1) */ * from table(sys.odcinumberlist(1,2,3)) c)
  and v.a = t1.a;

Plan hash value: 4293766070

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |              |     1 |    36 |    80   (3)| 00:00:01 |
|*  1 |  HASH JOIN                                 |              |     1 |    36 |    80   (3)| 00:00:01 |
|   2 |   JOIN FILTER CREATE                       | :BF0000      |     1 |    10 |    31   (4)| 00:00:01 |
|   3 |    NESTED LOOPS                            |              |     1 |    10 |    31   (4)| 00:00:01 |
|   4 |     NESTED LOOPS                           |              |     1 |    10 |    31   (4)| 00:00:01 |
|   5 |      SORT UNIQUE                           |              |     1 |     2 |    29   (0)| 00:00:01 |
|   6 |       COLLECTION ITERATOR CONSTRUCTOR FETCH|              |     1 |     2 |    29   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                     | SYS_C0026365 |     1 |       |     0   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID            | XTEST1       |     1 |     8 |     1   (0)| 00:00:01 |
|   9 |   VIEW                                     | VTEST2       |  1000 | 26000 |    49   (3)| 00:00:01 |
|  10 |    HASH GROUP BY                           |              |  1000 |  8000 |    49   (3)| 00:00:01 |
|  11 |     JOIN FILTER USE                        | :BF0000      | 10000 | 80000 |    48   (0)| 00:00:01 |
|* 12 |      TABLE ACCESS FULL                     | XTEST2       | 10000 | 80000 |    48   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - access("V"."A"="T1"."A")
   7 - access("T1"."ID"=VALUE(KOKBF$))
  12 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"A"))

As you see, CVM can’t help in this case, but we can use lateral():

select/*+ no_merge(lat) */ lat.* 
from xtest1 t1,
     lateral(select * from vtest2 v where v.a = t1.a) lat
where t1.id in (select/*+ cardinality(c 1) */ * from table(sys.odcinumberlist(1,2,3)) c);

Plan hash value: 1798023704

------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |    10 |   360 |    42   (3)| 00:00:01 |
|   1 |  NESTED LOOPS                            |                 |    10 |   360 |    42   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                           |                 |     1 |    10 |    31   (4)| 00:00:01 |
|   3 |    SORT UNIQUE                           |                 |     1 |     2 |    29   (0)| 00:00:01 |
|   4 |     COLLECTION ITERATOR CONSTRUCTOR FETCH|                 |     1 |     2 |    29   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID           | XTEST1          |     1 |     8 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN                    | SYS_C0026365    |     1 |       |     0   (0)| 00:00:01 |
|   7 |   VIEW                                   | VW_LAT_A18161FF |    10 |   260 |    11   (0)| 00:00:01 |
|   8 |    SORT GROUP BY                         |                 |    10 |    80 |    11   (0)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID BATCHED  | XTEST2          |    10 |    80 |    11   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN                    | ITEST2          |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   6 - access("T1"."ID"=VALUE(KOKBF$))
  10 - access("A"="T1"."A")

There is also another workaround with non-documented “precompute_subquery” hint:

select v.* 
from xtest1 t1,
     vtest2 v 
where t1.id in (select/*+ precompute_subquery */ * from table(sys.odcinumberlist(1,2,3)) c)
and v.a = t1.a;

Plan hash value: 1964829099

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |    30 |   480 |    37   (3)| 00:00:01 |
|   1 |  HASH GROUP BY                  |              |    30 |   480 |    37   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                  |              |    30 |   480 |    36   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |              |    30 |   480 |    36   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR             |              |       |       |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| XTEST1       |     3 |    24 |     3   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | SYS_C0026365 |     3 |       |     2   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN            | ITEST2       |    10 |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID  | XTEST2       |    10 |    80 |    11   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   6 - access("T1"."ID"=1 OR "T1"."ID"=2 OR "T1"."ID"=3)
   7 - access("A"="T1"."A")

It can help even in most difficult cases, for example if you can’t rewrite query (in this case you can create sql patch or sql profile with “precompute_subquery”), but I wouldn’t suggest it since “precompute_subquery” is non-documented, it can be used only with simple collections and has limitation in 1000 values.
I’d suggest to use the workaround with lateral, since it’s most reliable and very simple.

v$sql_hint.target_level

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

Today I wanted to give a link to the description of v$sql_hint.target_level to show that no_parallel can be specified for statement or object, and though it’s pretty obvious, but surprisingly I haven’t found any articles or posts about it, so this short post describes it.
v$sql_hint.target_level is a bitset, where
1st bit set to 1 means that the hint can be specified on statement level,
2nd – on query block level,
3rd – on object level,
4th – on join level(for multiple objects).
Short example:

   select name,sql_feature
          ,class,inverse
          ,version,version_outline
          ,target_level
         ,decode(bitand(target_level,1),0,'no','yes') Statement_level
         ,decode(bitand(target_level,2),0,'no','yes') Query_block_level
         ,decode(bitand(target_level,4),0,'no','yes') Object_level
         ,decode(bitand(target_level,8),0,'no','yes') Join_level
   from v$sql_hint h;

with hints as (
   select name,sql_feature
          ,class,inverse
          ,version,version_outline
          ,target_level
         ,decode(bitand(target_level,1),0,'no','yes') Statement_level
         ,decode(bitand(target_level,2),0,'no','yes') Query_block_level
         ,decode(bitand(target_level,4),0,'no','yes') Object_level
         ,decode(bitand(target_level,8),0,'no','yes') Join_level
   from v$sql_hint h
)
select *
from hints
where statement_level='yes'
  and to_number(regexp_substr(version,'^\d+')) >= 18
order by version;

Result:

NAME              SQL_FEATURE     CLASS                VERSION  TARGET_LEVEL STATEMENT_LEVEL QUERY_BLOCK_LEVEL OBJECT_LEVEL JOIN_LEVEL
----------------- --------------- -------------------- -------- ------------ --------------- ----------------- ------------ ----------
PDB_LOCAL_ONLY    QKSFM_DML       PDB_LOCAL_ONLY       18.1.0              1 yes             no                no           no
SUPPRESS_LOAD     QKSFM_DDL       SUPPRESS_LOAD        18.1.0              1 yes             no                no           no
SYSTEM_STATS      QKSFM_ALL       SYSTEM_STATS         18.1.0              1 yes             no                no           no
MEMOPTIMIZE_WRITE QKSFM_EXECUTION MEMOPTIMIZE_WRITE    18.1.0              1 yes             no                no           no
SKIP_PROXY        QKSFM_ALL       SKIP_PROXY           18.1.0              1 yes             no                no           no
CURRENT_INSTANCE  QKSFM_ALL       CURRENT_INSTANCE     18.1.0              1 yes             no                no           no
JSON_LENGTH       QKSFM_EXECUTION JSON_LENGTH          19.1.0              1 yes             no                no           no
QUARANTINE        QKSFM_EXECUTION QUARANTINE           19.1.0              1 yes             no                no           no