Stopkey not stopping FTS

From: Henry Poras <henry.poras_at_gmail.com>
Date: Fri, 23 Sep 2016 16:17:04 -0400
Message-ID: <CAK5zhLK9YtgpXbkLEwN5Xc+KQr2E-GJJGti3TT-TyWqcFDRZ=A_at_mail.gmail.com>

I am running a

SELECT * FROM tab WHERE rownum <=5;

and expecting a few blocks to be read and the data returned. Instead, the query is doing a FTS of the entire table before returning the 5 records.

A few details:

-db_version : 11.2.0.4
-The optimizer's plan is to read just a few blocks (as seen in execution
plan obtained from dbms_xplan after executing query and from 10053 trace. Will display later). But it's executing a FTS.
-8K block size and avg_row_length of 50, 1988479 blocks
-autotrace shows 1989873 physical reads, 10046 trace has ~3700 direct
path reads, most with 128 blocks. Those reads consume the bulk of the run time

Why am I reading the whole table instead of just the first few blocks?

dbms_explan.display_cursor returns:

---

| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |

---

|   0 | SELECT STATEMENT   |               |      1 |         |     2
(100)|      5 |00:00:58.93 |    1985K|   1988K|

|*  1 |  COUNT STOPKEY       |               |      1 |         |
 |      5 |00:00:58.93 |    1985K|   1988K|

|   2 |   TABLE ACCESS FULL | TAB        |      1 |      5 |     2   (0)|

   5 |00:00:58.93 | 1985K| 1988K|

---

Predicate Information (identified by operation id):

---

   1 - filter(ROWNUM<=5)

10053 trace includes:

BASE STATISTICAL INFORMATION

---

Table Stats::
  Table: TAB Alias: TAB
    #Rows: 6508761 #Blks: 1988479 AvgRowLen: 50.00 ChainCnt: 0.00 Access path analysis for TAB

---

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for TAB[TAB]   Table: TAB Alias: TAB
    Card: Original: 6508761.000000 Rounded: 6508761 Computed: 6508761.00  Non Adjusted: 6508761.00
  Access Path: TableScan
    Cost: 539651.75 Resp: 539651.75 Degree: 0

      Cost_io: 538548.00  Cost_cpu: 15788024140
      Resp_io: 538548.00  Resp_cpu: 15788024140
  Best:: AccessPath: TableScan
         Cost: 539651.75  Degree: 1  Resp: 539651.75  Card: 6508761.00

 Bytes: 0

---

OPTIMIZER STATISTICS AND COMPUTATIONS

---

GENERAL PLANS

---

Considering cardinality-based initial join order. Permutations for Starting Table :0
Join order[1]: TAB[TAB]#0

---

Best so far: Table#: 0 cost: 539651.7502 card: 6508761.0000 bytes: 325438050

---

First K Rows: K = 5.00, N = 6508761.00
First K Rows: old pf = -1.0000000, new pf = 0.0000008 Access path analysis for TAB

---

SINGLE TABLE ACCESS PATH (First K Rows)
  Single Table Cardinality Estimation for TAB[TAB]   Table: TAB Alias: TAB
    Card: Original: 5.000000 Rounded: 5 Computed: 5.00 Non Adjusted: 5.00   Access Path: TableScan
    Cost: 2.00 Resp: 2.00 Degree: 0

      Cost_io: 2.00  Cost_cpu: 15493
      Resp_io: 2.00  Resp_cpu: 15493
  Best:: AccessPath: TableScan
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 5.00  Bytes: 50

First K Rows: unchanged join prefix len = 1 Join order[1]: TAB[TAB]#0

---

Best so far: Table#: 0 cost: 2.0011 card: 5.0000 bytes: 250

---

(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

What am I missing here?

Henry

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 23 2016 - 22:17:04 CEST