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" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED],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" <[EMAIL PROTECTED]> > > 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" <[EMAIL PROTECTED]> > > > 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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
