Table Stats::
Table: TAB Alias: TAB
#Rows: 6508761 #Blks: 1988479 AvgRowLen: 50.00 ChainCnt: 0.00
If you have 6.5M rows and 2M blocks you're averaging less than 4 rows per block
- even though your average row length is 50 bytes.
Sayan was suggesting that over time you've deleted a lot of data from the table
leaving a huge number of empty blocks before you find the blocks that hold the
first 5 rows. I'll go a little further - I'll guess that your code always uses
"insert /*+ append */" so you're always inserting above the high water mark
and unable to reuse the freespace that exists below the HWM. That might help
to explain why a relatively small volume of deletes could leave you with a very
large but empty table and a tablescan that takes hundreds of thousands of
blocks before it finds the first extant rows.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Henry Poras [henry.poras@xxxxxxxxx]
Sent: 23 September 2016 21:17
To: oracle-l@xxxxxxxxxxxxx
Subject: Stopkey not stopping FTS
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
***************************************
