Top-N Queries

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 processed

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s