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).