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> 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 |
------------------------------------------------------------
-------------------
