Re: Stopkey not stopping FTS

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_at_gmail.com> 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
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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