RE: Predicates not getting pushed down to subquery after adding JSON index on unrelated table
Date: Tue, 7 Feb 2017 08:45:54 -0500
Message-ID: <018c01d28148$8116a8e0$8343faa0$_at_rsiz.com>
To really find out, you need to do a Wolfgang trace before and after adding the index and with similar stuff in the sga as to when you got each plan.
I *suspect* that the chosen plan (whether or not actuals show it to be ultimately more efficient) will have estimated as cheaper or equal to the pushed predicate plan. The new index may just have caused the CBO to search alternative plans in a different order reversing the choice on a tie, or pushing the pushed predicate plan past the number of alternative plans considered.
Or, of course, with all the dynamic “I might make it better, but holy cow watch out” parameters something askew from the originally chosen plan may have taken place after execution began.
So NOW, with the various adaptive bits and pieces, even a pair of Wolfgangs cannot completely demonstrate the plan evolution. A Millsap trace probably shows if the plan dynamically changed, or there might be another trace that watches dynamic plan alterations. I try not to allow that and the goal for me is a stable decent plan rather than the absolute perfect plan. Probably Chris Antognini or JL or Wolfgang have references to any such trace option (if it exists).
Good luck!
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Patrick Jolliffe
Sent: Sunday, February 05, 2017 10:29 PM
To: oracle-l
Subject: Re: Predicates not getting pushed down to subquery after adding JSON index on unrelated table
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-lReceived on Tue Feb 07 2017 - 14:45:54 CET