Index scan results is lot of physical reads (Oracle)

There have two tables with more than 20 million records. The database version is Oracle 11.2.0.3.13 Standard Edition

SQL> select count(*) from atsd_tra_trade_print;

  COUNT(*)
----------
  20000000

SQL> select count(*) from atsd_mob_allocation_pos;

  COUNT(*)
----------
  20551780

We run the following queries on the tables. Query structures are similar. Query is intended for pagination. The sort column on both the queries are indexed and dont contain any NULL values. The queries and the corresponding autotrace output / plan is given below.

Index INDX_ATTP_6 is created on ATSD_TRA_TRADE_PRINT(TRANSACT_TIME)

Index INDX_AMAP_6 is created on ATSD_MOB_SETTLEMENT_POS(DATE_TIME)

Cardinality of the indexes are as follows

INDX_ATTP_6 – 5972

INDX_AMAP_6 – 5974

Both columns above are of datatype varchar2.

Both queries were run after flushing the buffer_cache.

SELECT * FROM ( SELECT T2.*, ROWNUM AS RN FROM (
SELECT *
FROM ATSDN_TRA_TRADE_PRINT T
WHERE 1=1 
ORDER BY TRANSACT_TIME) T2)
WHERE RN > 0 and ROWNUM <= 28;

Output:

28 rows selected.

Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 3823692003

-------------------------------------------------------------------------    -------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost     (%CPU)| Time     |
-------------------------------------------------------------------------    -------------------------------
|   0 | SELECT STATEMENT        |              |    28 | 60060 |     8       (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |              |       |       |        |              |
|*  2 |   VIEW              |              |    28 | 60060 |     8   (0)|     00:00:01 |
|   3 |    COUNT            |              |       |       |        |             |
|   4 |     VIEW            |              |    28 | 59696 |     8   (0)|     00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| ATSD_TRA_TRADE_PRINT |  7488K|      3234M|     8   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN       | INDX_ATTP_6          |    57 |           |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------    -------------------------------

Predicate Information (identified by operation id):
 ---------------------------------------------------

   1 - filter(ROWNUM<=28)
   2 - filter("RN">0)
   5 - filter("LATEST"=1)


Statistics
----------------------------------------------------------
    571  recursive calls
      0  db block gets
    948  consistent gets
     49  physical reads
      0  redo size
  18586  bytes sent via SQL*Net to client
    535  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
     29  sorts (memory)
      0  sorts (disk)
     28  rows processed

The above query executes quickly as expected. But the query on the other table results in a long execution time. The only observation I could see was there is a large amount of physical reads. Both query execution plans look similar too.

SELECT * FROM ( SELECT T2.*, ROWNUM as RN FROM ( 
SELECT *
FROM ATSD_MOB_ALLOCATION_POS AL
WHERE  LATEST = 1
ORDER BY DATE_TIME) T2)
WHERE RN > 0 and ROWNUM <= 28;

Output:

28 rows selected.

Elapsed: 00:01:52.28

Execution Plan
----------------------------------------------------------
Plan hash value: 884170602

-------------------------------------------------------------------------    ----------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost     (%CPU)| Time     |
-------------------------------------------------------------------------    ----------------------------------
|   0 | SELECT STATEMENT        |             |    28 | 38892 | 7   (0)|     00:00:01 |
|*  1 |  COUNT STOPKEY          |             |   |   |        |      |
|*  2 |   VIEW              |             |    28 | 38892 | 7   (0)|     00:00:01 |
|   3 |    COUNT            |             |   |   |        |      |
|   4 |     VIEW            |             |    28 | 38528 | 7   (0)|     00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| ATSD_MOB_ALLOCATION_POS |        14M|  5863M| 7   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN       | INDX_AMAP_6         |    39 |   | 4       (0)| 00:00:01 |
-------------------------------------------------------------------------    ----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=28)
   2 - filter("RN">0)
   5 - filter("LATEST"=1)


Statistics
----------------------------------------------------------
    281  recursive calls
      0  db block gets
 335395  consistent gets
 332763  physical reads
    116  redo size
  13582  bytes sent via SQL*Net to client
    535  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
     17  sorts (memory)
      0  sorts (disk)
     28  rows processed

Can someone please enlighten me why this is happening. Thanks in advance … 🙂


Source: sql

Leave a Reply