Re: (looong) PCTFREE, PCTUSED and ASSM

I invalidated the buffer cache for my tablespace, and saw all the scattered reads from start to end, but no sequential reads whatsoever. (9.2.0.4 on W2k)

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, November 13, 2003 12:04 AM

> I think it will behave the same in serial scans but we may not see any
waits
> since there is good chance the blocks are cached in buffer cache.
>
> Waleed
>
> -----Original Message-----
> Sent: Wednesday, November 12, 2003 4:45 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Yep, that's exactly what I thought, that due migration the row might go to
a
> PX granule read by another slave... but I still don't see enough reason
why
> migrated rows are handled different from serial scan. During serial scan
you
> also might have rows in beginning of segment migrating to end of it, so
> there ha

stobesomeotherreasonaswell.Tanel.-----OriginalMessage-----ToMultiplerecipien tsoflistORACLE-LORACLE-L_at_fatcity.zl6SentWednesday,November12,2003624PMUsingP Q,thesegmentissplitintomultiplesub-segmentsusingrowidrangescan.
> > Since there is no guarantee that the migrated row will be in the same
> > sub-segment that has the pointer to the migrated row, the PQ slave might
> > need to resolve the issue real time.
> >
> > I mean the migrated row might exist in a different range that will be
> > scanned by another PQ slave.
> >
> > Regards,
> >
> > Waleed
> >
> > -----Original Message-----
> > Sent: Wednesday, November 12, 2003 10:49 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > As a strange thing, from 10046 trace I saw that normal table scanning
was
> > done using direct reads, this was expected behaviour, but the lookups of
> > migrated rows were reflected as 'db file sequential reads'. And even
more,
> > there were 3 subsequent sequential read waits for the same datablock in
a
> > row, it seems that a PX slave isn't even able to cache one datablock in
> it's
> > PGA, in case of finding migrated rows... (or a wait event is registered
> for
> > reading from cache...)
> >
> > I was just wondering, why a PQ FTS requires resolving migrated rows
> > immediately, instead of reading them when scan hits their location.
Could
> it
> > be some concurrency issue, that if a row migrates to another location
> during
> > the scan, then results could get inconsistent?
> > It is not a direct read issue, because I experimented using
> > _serial_direct_read parameter, and for regular FTS, no migrated rows
were
> > resolved ahead.
> >
> > There's lot to learn...
> > Tanel.
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, November 12, 2003 4:34 PM
> >
> >
> > > I believe it's direct read from files in parallel execution, nothing
> gets
> > > read from cache.
> > >
> > > Cached blocks for the table get flushed to files before the direct
read.
> > >
> > > Regards,
> > >
> > > Waleed
> > >
> > > -----Original Message-----
> > > Sent: Wednesday, November 12, 2003 7:10 AM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Yep, the situation can get bad for parallel execution, especially if
> > blocks
> > > read aren't cached...
> > > But for serial FTS I haven't seen such a problem, I did even a test to
> > > verify it on 9.2.0.4, and did see behaviour as I expected - all blocks
> > were
> > > scanned using multiblock reads and rows were returned in order the
> > contents
> > > of them were found, instead of pointers.
> > >
> > > Tanel.
> > >
> > > ----- Original Message -----
> > > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > > Sent: Wednesday, November 12, 2003 5:14 AM
> > >
> > >
> > > > Actually row migration is a big problem for FTS also(whether
serially
> or
> > > > using PQ).
> > > > You end up waiting for too many "db file sequential read" single
block
> > > reads
> > > > instead of
> > > > MBRC in (direct path read, db file scattered read)
> > > >
> > > > Regards,
> > > >
> > > > Waleed
> > > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Tanel Poder
> > > INET: tanel.poder.003_at_mail.ee
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Khedr, Waleed
> > > INET: Waleed.Khedr_at_FMR.COM
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Tanel Poder
> > INET: tanel.poder.003_at_mail.ee
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Khedr, Waleed
> > INET: Waleed.Khedr_at_FMR.COM
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tanel Poder
> INET: tanel.poder.003_at_mail.ee
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).