This is an old story. It started in 2015 when I found a problem with TOP-N query performance. I wrote a blog post about it and later an update. Here I want to show the full testcase and some updates after a twitter discussion.
Background
I don’t want to spend too much time writing the whole story (mainly because I already wrote it in the posts I mentioned above), but here it is in a very short version:
I found a performance issue with the new TOP-N query format. I opened an SR about it and Oracle closed it and explained that this cannot be easily done or something like that. Then I happened to meet one of the support VPs and told her about this. She re-opened the SR and demanded a better resolution, so Oracle opened a bug (Bug 22174392 – INCORRECT INDEX COSTING CAUSES INEFFICIENT QUERY PLANS FOR OFFSET M ROWS FETCH N). That was in November of 2015.
Update
During the mentioned twitter discussion, Oren brought to my attention that this has actually been solved (and it’s the same bug number) and Nigel Bayliss wrote a post about it. I checked and I could see patches for 12.1.0.2 and 12.2.0.1. So back to the VMs, let’s try to reproduce this issue and check if the patch really fixes it (and what happens in 18c).
Test Case – Preparation
Preparing the table:
create table large_tab as select rownum id, dbms_random.value(1,10000) num from (select * from dual connect by level<=500000); create index tab_idx on large_tab(num); exec dbms_stats.gather_table_stats(user,'LARGE_TAB');Test Case – 12.2 pre-patch
First, let’s see how 12.2.0.1 behaves
SQL> create table large_tab as 2 select rownum id, 3 dbms_random.value(1,10000) num 4 from (select * from dual connect by level<=500000); Table created. SQL> create index tab_idx on large_tab(num); Index created. SQL> exec dbms_stats.gather_table_stats(user,'LARGE_TAB'); PL/SQL procedure successfully completed. SQL> select * 2 from (select id,num, rownum r 3 from (select id, num 4 from large_tab 5 where num is not null 6 order by num desc 7 ) 8 where rownum<=10 9 ) 10 where r>=6; Execution Plan ---------------------------------------------------------- Plan hash value: 2038705269 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 390 | 13 (0)| 00:00:01 | |* 1 | VIEW | | 10 | 390 | 13 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 10 | 260 | 13 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| LARGE_TAB | 500K| 12M| 13 (0)| 00:00:01 | |* 5 | INDEX FULL SCAN DESCENDING| TAB_IDX | 10 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("R">=6) 2 - filter(ROWNUM<=10) 5 - filter("NUM" IS NOT NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 877 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed SQL> select * 2 from large_tab 3 where num is not null 4 order by num desc 5 offset 5 rows fetch next 5 rows only; Execution Plan ---------------------------------------------------------- Plan hash value: 742619145 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 500K| 24M| | 4412 (1)| 00:00:01 | |* 1 | VIEW | | 500K| 24M| | 4412 (1)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK| | 500K| 12M| 17M| 4412 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | LARGE_TAB | 500K| 12M| | 616 (1)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$_rownumber"<=CASE WHEN (5>=0) THEN 5 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$_rownumber">5) 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("NUM") DESC )<=CASE WHEN (5>=0) THEN 5 ELSE 0 END +5) 3 - filter("NUM" IS NOT NULL) Statistics ---------------------------------------------------------- 12 recursive calls 11 db block gets 2222 consistent gets 0 physical reads 2028 redo size 799 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 5 rows processedAs you can see, the first query is using ROWNUM and the plan shows that Oracle uses the index. The second query uses the new Top-N feature. As you can see, Oracle doesn’t use the index and the amount of “consistent gets” is considerably higher.
Test Case – 18.4
I also wanted to check the latest 18c (18.4 currently), so see how it behaves:
SQL> select * 2 from (select id,num, rownum r 3 from (select id, num 4 from large_tab 5 where num is not null 6 order by num desc 7 ) 8 where rownum<=10 9 ) 10 where r>=6 Execution Plan ---------------------------------------------------------- Plan hash value: 2038705269 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 390 | 13 (0)| 00:00:01 | |* 1 | VIEW | | 10 | 390 | 13 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 10 | 260 | 13 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| LARGE_TAB | 500K| 12M| 13 (0)| 00:00:01 | |* 5 | INDEX FULL SCAN DESCENDING| TAB_IDX | 10 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("R">=6) 2 - filter(ROWNUM<=10) 5 - filter("NUM" IS NOT NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 901 bytes sent via SQL*Net to client 623 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed SQL> select * 2 from large_tab 3 where num is not null 4 order by num desc 5 offset 5 rows fetch next 5 rows only; Execution Plan ---------------------------------------------------------- Plan hash value: 742619145 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 500K| 24M| | 4409 (1)| 00:00:01 | |* 1 | VIEW | | 500K| 24M| | 4409 (1)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK| | 500K| 12M| 17M| 4409 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | LARGE_TAB | 500K| 12M| | 616 (1)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$_rownumber"<=CASE WHEN (5>=0) THEN 5 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$_rownumber">5) 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("NUM") DESC )<=CASE WHEN (5>=0) THEN 5 ELSE 0 END +5) 3 - filter("NUM" IS NOT NULL) Statistics ---------------------------------------------------------- 11 recursive calls 12 db block gets 2221 consistent gets 0 physical reads 2028 redo size 815 bytes sent via SQL*Net to client 623 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 5 rows processedAs you can see, both plans are exactly as in 12.2
Test Case – patched 12.2
Now, let’s install patch 22174392 on top of the 12.2 database, and see the plan of the Top-N query:
SQL> select * 2 from large_tab 3 where num is not null 4 order by num desc 5 offset 5 rows fetch next 5 rows only; Execution Plan ---------------------------------------------------------- Plan hash value: 742619145 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 520 | | 4412 (1)| 00:00:01 | |* 1 | VIEW | | 10 | 520 | | 4412 (1)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK| | 500K| 12M| 17M| 4412 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | LARGE_TAB | 500K| 12M| | 616 (1)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$_rownumber"<=10 AND "from$_subquery$_002"."rowlimit_$_rownumber">5) 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("NUM") DESC )<=10) 3 - filter("NUM" IS NOT NULL) Statistics ---------------------------------------------------------- 73 recursive calls 0 db block gets 2347 consistent gets 2214 physical reads 0 redo size 799 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 5 rows processedAs you can see, the estimated number of rows is now 10 (instead of 500K like before), but this is only in the “view” part and not for the table access, so Oracle still decides to perform full table scan rather than index scan. so unfortunately, it seems that the issue is not fixed… 🙁
Conclusion
Seems like this issue is still happening in 18.4, and the patch (at least on top of 12.2.0.1) didn’t really fixed the issue.
I will try to check this out and will keep you posted.