Hi Henry,
Are you sure that there wasn't huge delete from this table?
Have you monitored this execution with dbms_sqltune.report_sql_monitor?
How much block were scanned before first row was found? (2nd,3rd,...5th?)
Have you checked number of rows?
Could you show statistics of the query from this block:
declare
cursor c is select/*+ test_query*/ * from tab;
type tc is table of c%rowtype;
vc tc;
begin
open c;
fetch c bulk collect into vc limit 5;
close c;
end;
/
On Fri, Sep 23, 2016 at 11:17 PM, Henry Poras <henry.poras@xxxxxxxxx> wrote:
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
