Re: FTS waiting on db file sequential reads?

From: Kenny Payton <k3nnyp_at_gmail.com>
Date: Thu, 18 Sep 2014 16:15:35 -0400
Message-ID: <CAEidWqNPVVikfdTkki7s5KGZ88P0eHeRNdDLmuZp=GjRQ6tjuA_at_mail.gmail.com>

I mixed up issues slightly. Intra-block chaining with tables with more than 255 columns is the issue I was thinking of. They would show up with analyze table list chained rows and exhibit this behavior since they are chained.
On Sep 18, 2014 4:07 PM, "Riyaj Shamsudeen" <riyaj.shamsudeen_at_gmail.com> wrote:

> Hello Chris
> Snapper should also spit out statistics for 'table fetch continued row'
> if you use gather=stw. Could you post the full output of snapper with
> gather=stw? That will give us more data.
>
> Second, AFAIK, full table scan should not follow the chain for migrated
> rows, except in the case of inmemory population:
> https://orainternals.wordpress.com/2014/09/11/inmemory-why-didnt-that-table-was-populated-in-the-column-store/
>
> Yes, dbms_stats doesn't populate chain_cnt. You must use analyze table..
>
> Another possibility is that may be the buffer cache already has blocks
> of the table cached and so, single block reads are performed.
>
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com - Specialists in Performance,
> RAC and EBS
> Blog: http://orainternals.wordpress.com/
> Oracle ACE Director and OakTable member <http://www.oaktable.com/>
>
> Co-author of the books: Expert Oracle Practices
> <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL,
> <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC
> Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices
> <http://tinyurl.com/book-expert-plsql-practices>
>
> <http://tinyurl.com/book-expert-plsql-practices>
>
>
> On Thu, Sep 18, 2014 at 12:31 PM, Kenny Payton <k3nnyp_at_gmail.com> wrote:
>
>> Look at the "analyze table list chained rows" command. It could be
>> chained or migrated rows.
>>
>> We have pretty bad re-occurring migrated rows from time to time and have
>> to reorg occasionally to get rid of them. Oracle added a feature to 12.?
>> and 11.2.0.4 that gives you some control over migrated rows. The problem
>> apparently raised its head during some Exadata performance proofing.
>>
>> Kenny
>>
>>
>>
>> On Thu, Sep 18, 2014 at 3:25 PM, Chris Taylor <
>> christopherdtaylor1994_at_gmail.com> wrote:
>>
>>> The last analyzed date (yesterday) shows CHAIN_CNT = 0 but I'm not
>>> positive that is accurate. (Seems like I remember you have to check a
>>> different way to get an accurate picture of chained or migrated rows)
>>>
>>> Chris
>>>
>>> On Thu, Sep 18, 2014 at 2:18 PM, Wolfgang Breitling <
>>> breitliw_at_centrexcc.com> wrote:
>>>
>>>>
>>>> - chained rows
>>>> - delayed block clean out
>>>>
>>>>
>>>>
>>>>
>>
>

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

Received on Thu Sep 18 2014 - 22:15:35 CEST