Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: (looong) PCTFREE, PCTUSED and ASSM

Re: (looong) PCTFREE, PCTUSED and ASSM

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Wed, 12 Nov 2003 13:44:40 -0800
Message-ID: <F001.005D6740.20031112134440@fatcity.com>


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 has to be some other reason as well.

Tanel.

> Using PQ, the segment is split into multiple sub-segments using rowid
range
> scan.
>
> 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).
Received on Wed Nov 12 2003 - 15:44:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US