Re: Predicates not getting pushed down to subquery after adding JSON index on unrelated table

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Mon, 6 Feb 2017 11:29:15 +0800
Message-ID: <CABx0cSXBw5L5dFG1coDR7ST8JKUeex0G73MWRTq0Axyq3KrL6w_at_mail.gmail.com>



Reproduced on Oracle Live SQL (so I presume that means same behavior in 12.2)
https://livesql.oracle.com/apex/livesql/file/content_EJDIWSIK1AE1M5YR1DFJ1ZABO.html Seems pretty cool for such things, I haven't played with it much up til now. Still waiting on Oracle for update, will try to investigate what is particular about this index if I get time.

On 31 January 2017 at 17:51, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:

> We had a performance regression recently, and eventually identified that
> it was caused by the addition of an index on one of the tables in the
> query.
> However the index is not on any fields used by the query, and the problem,
> namely predicates not getting pushed down to the subquery, seemed to be on
> a different block of the query than the table with the added index.
> I have managed to simplify down to the test-case below, which replicates
> for me on 12.1.0.2 with Jan 2017 DBBP.
> Note in particular, table access to T1 has switched from index lookup to
> full table scan.
> We have removed the index and performance is back to normal.
> Does anybody have any further insights?
> Thanks in advance
> Patrick
>
>
>
> drop table t1;
> drop table t2;
> drop table t3;
> drop table t4;
>
> create table t1(id number, ref varchar2(255));
> create index i1 on t1(id);
> insert into t1 select level, 'X' from dual connect by level < 10000;
> commit;
> create table t2(id1 number, id3 number, ref varchar2(255));
> create table t3(id number, ref varchar2(255));
> create table t4(id number, ref varchar2(255));
>
> alter session set optimizer_adaptive_features=false;
>
> exec dbms_stats.gather_table_stats(null, 't1');
> exec dbms_stats.gather_table_stats(null, 't2');
> exec dbms_stats.gather_table_stats(null, 't3');
> exec dbms_stats.gather_table_stats(null, 't4');
>
> select * from table(dbms_xplan.display);
> explain plan for
> SELECT
> (
> SELECT count(*)
> FROM
> (
> SELECT /*+ NO_MERGE*/
> id
> FROM
> t1
> ) t1_sq,
> t2,
> t3
> WHERE
> t1_sq.id = t2.id1
> AND t2.id3 = t3.id
> AND t3.id = 1
> ),
> (
> SELECT
> COUNT(*)
> FROM
> t4
> )
> FROM
> dual;
>
> select * from table(dbms_xplan.display);
>
> ------------------------------------------------------------
> ---------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> ------------------------------------------------------------
> ---------------------
> | 0 | SELECT STATEMENT | | 1 | | 9 (0)|
> 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 | 41 |
> | |
> | 2 | NESTED LOOPS | | 1 | 41 | 5 (0)|
> 00:00:01 |
> |* 3 | HASH JOIN | | 1 | 39 | 4 (0)|
> 00:00:01 |
> |* 4 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)|
> 00:00:01 |
> |* 5 | TABLE ACCESS FULL | T3 | 1 | 13 | 2 (0)|
> 00:00:01 |
> | 6 | VIEW PUSHED PREDICATE | | 1 | 2 | 1 (0)|
> 00:00:01 |
> |* 7 | INDEX RANGE SCAN | I1 | 1 | 4 | 1 (0)|
> 00:00:01 |
> | 8 | SORT AGGREGATE | | 1 | |
> | |
> | 9 | TABLE ACCESS FULL | T4 | 1 | | 2 (0)|
> 00:00:01 |
> | 10 | FAST DUAL | | 1 | | 2 (0)|
> 00:00:01 |
> ------------------------------------------------------------
> ---------------------
>
> select * from table(dbms_xplan.display);
> create index i4 on t4 to_char(JSON_QUERY(ref, '$'));
>
> explain plan for
> SELECT
> (
> SELECT count(*)
> FROM
> (
> SELECT /*+ NO_MERGE*/
> id
> FROM
> t1
> ) t1_sq,
> t2,
> t3
> WHERE
> t1_sq.id = t2.id1
> AND t2.id3 = t3.id
> AND t3.id = 1
> ),
> (
> SELECT
> COUNT(*)
> FROM
> t4
> )
> FROM
> dual;
>
> select * from table(dbms_xplan.display);
> ------------------------------------------------------------
> -------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> ------------------------------------------------------------
> -------------------
> | 0 | SELECT STATEMENT | | 1 | | 15 (0)|
> 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 | 52 |
> | |
> |* 2 | HASH JOIN | | 1 | 52 | 11 (0)|
> 00:00:01 |
> | 3 | MERGE JOIN CARTESIAN| | 1 | 26 | 9 (0)|
> 00:00:01 |
> |* 4 | TABLE ACCESS FULL | T3 | 1 | 13 | 2 (0)|
> 00:00:01 |
> | 5 | BUFFER SORT | | 9999 | 126K| 7 (0)|
> 00:00:01 |
> | 6 | VIEW | | 9999 | 126K| 7 (0)|
> 00:00:01 |
> | 7 | TABLE ACCESS FULL| T1 | 9999 | 39996 | 7 (0)|
> 00:00:01 |
> |* 8 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)|
> 00:00:01 |
> | 9 | SORT AGGREGATE | | 1 | |
> | |
> | 10 | TABLE ACCESS FULL | T4 | 1 | | 2 (0)|
> 00:00:01 |
> | 11 | FAST DUAL | | 1 | | 2 (0)|
> 00:00:01 |
> ------------------------------------------------------------
> -------------------
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 06 2017 - 04:29:15 CET

Original text of this message