Re: Predicates not getting pushed down to subquery after adding JSON index on unrelated table
Date: Wed, 8 Feb 2017 12:51:28 +0000
Message-ID: <MM1P123MB0841244E7CDDB0AB983F87B4A5420_at_MM1P123MB0841.GBRP123.PROD.OUTLOOK.COM>
Patrick,
There's a lot of difference between the trace files, most of it along the lines of "why did this NOT appear", but the hint comes in the lines:
CBQT bypassed for query block SEL$1 (#0): recursive cost-based transformation. CBQT: Validity checks failed for f8j4apbwhhh8z.
Regards
Jonathan Lewis
From: Patrick Jolliffe <jolliffe_at_gmail.com> Sent: 08 February 2017 12:44:10
To: Jonathan Lewis
Cc: oracle-l
Subject: Re: Predicates not getting pushed down to subquery after adding JSON index on unrelated table
Thanks Jonathan, yes that would explain what was puzzling me which was why the addition of the index would affect transformation on completely separate part of the query. Would you mind sharing what part of trace gives you the hint that CBQT is disabled by the index? For your (and anyone else interested) reference, Oracle have opened Pre-Defect Request (I think that is euphemism for a bug!) with OOB number (bug reference number?) 25501524.
Interesting that apparently removing the TO_CHAR from around the function seems to allow the CBQT to perform. It is on my to-do list to try to identify exactly what is special about the index to disable the CBQT.
I will let you know if I get any further,
Regards
Patrick
On 8 February 2017 at 20:25, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
Patrick,
I've just found a bit of time to look at the 10053 trace.
It's all very messy, of course, but there's a slight hint that during transformation analysis the presence of the JSON_QUERY function results in the optimizer setting a flag that disables some of the cost-based query transformation features, and then forgetting to clear the flag after it's finished analyzing t4, with the result that it never gets around to considering the option for the pushed predicate plan that you get in the absence of the index.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Patrick Jolliffe <jolliffe_at_gmail.com<mailto:jolliffe_at_gmail.com>> Sent: 06 February 2017 03:29:15
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<mailto:jolliffe_at_gmail.com><mailto:jolliffe_at_gmail.com<mailto: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<http://t1_sq.id><http://t1_sq.id> = t2.id1
AND t2.id3 = t3.id<http://t3.id><http://t3.id>
AND t3.id<http://t3.id><http://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<http://t1_sq.id><http://t1_sq.id> = t2.id1
AND t2.id3 = t3.id<http://t3.id><http://t3.id>
AND t3.id<http://t3.id><http://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 Wed Feb 08 2017 - 13:51:28 CET