Re: Stopkey not stopping FTS

Sayan,

I'm not at my computer right now, but I thought about other txns and delayed block clean out. That isn't it.

No current transactions, the query behavior is reproducible, all object I'd access shown in 10046 is from my table, no undo. I can check. $mystats later to confirm.

Henry

On Sep 23, 2016 4:35 PM, "Sayan Malakshinov" <xt.and.r_at_gmail.com> wrote:

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