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 |
-------------------------------------------------------------------------------
--
//www.freelists.org/webpage/oracle-l
