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@xxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Patrick Jolliffe <jolliffe@xxxxxxxxx>
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@xxxxxxxxx<mailto:
jolliffe@xxxxxxxxx>> 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> = t2.id1
AND t2.id3 = t3.id<http://t3.id>
AND 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> = t2.id1
AND t2.id3 = t3.id<http://t3.id>
AND 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 |
------------------------------------------------------------
-------------------
