Oracle 12c TOP N Pagination performance considerations.

In my post on TOP N and Pagination in Oracle 12c highlighted features introduce.

In current post will try to compare performance and cardinality estimations for a scenario between INLINE ORDER BY + OUTER ROWNUM and TOP N IN 12C.

Let’s start with field work.
Test DB Version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production.
Optimizer_mode: ALL_ROWS

Consider a case when data need to be fetched on order define by indexed column and projecting top rows.

Let build dataset.
SQL> create table TAB_TEST_TOPN as
2 (select * from dba_objects where object_id is not null);
-- Need to defined not null constraint on object_id.
Table created.

SQL> insert into TAB_TEST_TOPN
2 (select * from TAB_TEST_TOPN);
93011 rows created.

SQL> insert into TAB_TEST_TOPN
2 (select * from TAB_TEST_TOPN);
186022 rows created.

SQL> insert into TAB_TEST_TOPN
2 (select * from TAB_TEST_TOPN);
372044 rows created.

SQL> insert into TAB_TEST_TOPN
2 (select * from TAB_TEST_TOPN);
744088 rows created.

SQL> insert into TAB_TEST_TOPN
2 (select * from TAB_TEST_TOPN);
1488176 rows created.
...
....

SQL> commit;
Commit complete.

Create index on object_id.
SQL> create index idx_topn_test on tab_test_topn(object_id);
Index created.

Gathered stats for table.
SQL> begin
2 SYS.dbms_stats.gather_table_stats(user,'TAB_TEST_TOPN', CASCADE => TRUE , method_opt => 'FOR ALL COLUMNS SIZE 1');
3 end;
4 /
PL/SQL procedure successfully completed.

SQL> alter table tab_test_topn modify object_id number not null;
Table altered.

SQL> desc tab_test_topn;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)

So we have a table, with normal btree index define on not null column.

Let’s try to fetch top 10 rows with TOP N 12c Feature.

SQL> select *
2 from TAB_TEST_TOPN
3 order by object_id
4 fetch first 10 rows only;

10 rows selected.

Elapsed: 00:00:01.52

Execution Plan
----------------------------------------------------------
Plan hash value: 3372920324
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2976K| 1118M| | 90108(1)| 00:00:04 |
|* 1 | VIEW | | 2976K| 1118M| | 90108(1)| 00:00:04 |
|* 2 | WINDOW SORT PUSHED RANK| | 2976K| 323M| 447M| 90108(1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | TAB_TEST_TOPN | 2976K| 323M| | 13599(1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "TAB_TEST_TOPN"."OBJECT_ID")<=10)

Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
49806 consistent gets
0 physical reads
0 redo size
2046 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

Observations :
1. Though we have indexed on object_id column, but same is not considered for sorting.
2. Estimated Cardinality mismatch (2976K) is far offset, then actual rows (10).
Table total rows: 2976352
3. First row optimization is not invoked (as in the case of Rownum conditions)
4. Window sort operation is performed on complete table data.

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
10 10 10 VIEW (cr=49798 pr=0 pw=0 time=1405779 us card=2976352)
10 10 10 WINDOW SORT PUSHED RANK (cr=49798 pr=0 pw=0 time=1405773 us card=2976352)
2976352 2976352 2976352 TABLE ACCESS FULL TAB_TEST_TOPN (cr=49798 pr=0 pw=0 time=641700 us card=2976352)

Later based on Filter only 10 rows is projected.
5. Logical reads (LIO) imposed is same as total table blocks..

Lets run same functionality with old methods. (ORDER BY INLINE + ROWNUM OUTER)

SQL> select *
2 from (select * from tab_test_topn order by object_id)
3 where rownum < 11;

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1371261212
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 3680 | 14 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 10 | 3680 | 14 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TAB_TEST_TOPN | 2976K| 323M| 14 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | IDX_TOPN_TEST | 10 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<11)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
1 physical reads
0 redo size
2046 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

Observations :
1. No explicit Sort Operation, same is achieved with an index.
2. Estimated Cardinality estimation as per Actual Rows.
3. First Rows optimization was invoked.
4. Total LIO is only 14.

Clearly performance was far better for older methods as compared to New TOP N Features.

Before using new feature for ease of coding, just test with the old method also for performance considerations.
We need to test both method and, choose what fit best for our data, our DB and configurations.

Further observations :
Let run TOP N Features with First_rows mode.

SQL> alter session set optimizer_mode = FIRST_ROWS;
Session altered.

SQL> sho parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string FIRST_ROWS

SQL> SET ARRAYSIZE 1000;
SQL>
SQL> SET AUTOTRACE TRACEONLY;
SQL>
SQL> SET TIMING ON;

SQL> select *
2 from TAB_TEST_TOPN
3 order by object_id
4 fetch first 10 rows only;

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3339162364
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2976K| 1118M| 3091K(1)| 00:02:01 |
|* 1 | VIEW | | 2976K| 1118M| 3091K(1)| 00:02:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 2976K| 323M| 3091K(1)| 00:02:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TAB_TEST_TOPN | 2976K| 323M| 3091K(1)| 00:02:01 |
| 4 | INDEX FULL SCAN | IDX_TOPN_TEST | 2976K| | 6841 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "TAB_TEST_TOPN"."OBJECT_ID")<=10)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
2046 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

Though cardinality estimations is incorrect, but performance wise SQL run better due to FIRST_ROWS Influence.
Due to index access, explicit sorting was not considered.
"WINDOW NOSORT STOPKEY"

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle 12c New Features, Oracle Internal and tagged , , , , , . Bookmark the permalink.

One Response to Oracle 12c TOP N Pagination performance considerations.

  1. Pingback: TOP N and Pagination in Oracle 12c | Oracle Insight and Performance concepts

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s