Re: (looong) PCTFREE, PCTUSED and ASSM
Hi Tanel, I think there's a logical reason why ASSM is designed to behave as you describe below. There's a bit of a balancing act going on here between nice, efficient performance of inserts vs. nice compact, efficient use of storage within a segment. The issue that Oracle has is that it has no real ideal of the average row sizes to be inserted in the future. An insert will only be attempted in a block where it *could* fit, however if it still fails, then we've wasted valuable resources and have to perform additional I/O to find an appropriate block.Yuck !! So should Oracle just treat it as bad luck and "hope" that an appropriate row will eventually turn up or risk the case where the same failure occurs again and again and again with the same block ? Who know which of the two might happen ? Oracle has chosen the side of caution. It's given the insert some kinda chance by selecting a block where it could fit in, but has decided to make the block full and ensure that similarly size rows don't fall for the same trap again. Personally, I think an improvement could be made to "relegate" such blocks that fail on insert to the next lowest free boundary so that the same size row won't be tempted by the same block. Also, a change in behaviour in how Oracle deals with a row which it knows "must" fit in a block vs. where it "might" fit in a block might be beneficial and how it classes a particular row size. For example, if a row is 45% of a block, should it consider the 25-50 free space blocks where it "might" fit or only 50% or more free space blocks where it "will" fit. What about rows that are 26% of a block in size, should it consider the 25-50% free space blocks will it will "very likely" fit or the 50% or more free space blocks where again it will definitely fit ? By relegating a block, you help narrow down similarly based previous failures as Oracle goes for the could fit solution. As it stands, making a block (potentially prematurely) full to prevent similar failures in the near future makes some kinda sense. Cheers Richard - Original Message - > In ASSM, as you said, we have currently 6 different freeness statuses for > table blocks, 0 = unformatted, 1 = logically full, 2 = 0-25% free, 3 = > 25-50% free, 4 = 50%-75% free, 5= 75-100% free. When a datablock is filled > to some level, it's corresponding bit vector in it's level 1 bitmap block is > updated to reflect its "freeness". When a block is 90% full for example, > it's FS will be set to 0-25% free. However, if the block freeness drops > below PCTFREE, it's freeness state will go to "FULL" regardless what's the > percentage of free space in your block - it's PCTFREE that matters. You can > easily trace it using events 10612 and 10613. At least in 9.2 it seems that > also these blocks are marked FULL, which are rejected for an insert because > the new row would have caused the free space drop below PCTFREE. Even if the > existing block is 99% free and could accommodate several smaller rows in the > future. This seemed a bit odd for me, because I thought that ASSM was > supposed to eliminate the problem with inserting heavily varying sized rows, > but few of my tests didn't show that good results. Maybe we'll see this > improved in future versions. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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).
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" <[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
RE: (looong) PCTFREE, PCTUSED and ASSM
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 has to be some other reason as well. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, November 12, 2003 6:24 PM > 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" <[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 in
Re: (looong) PCTFREE, PCTUSED and ASSM
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. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, November 12, 2003 6:24 PM > 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" <[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
Re: (looong) PCTFREE, PCTUSED and ASSM
Maybe, the process that read the block use the same logic to pass the rows out, no matter if it FTS or direct block read. So this logic resolved migrated rows in the block that was passed to him. Yechiel Adar Mehish - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, November 12, 2003 5:49 PM > 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: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Maili
RE: (looong) PCTFREE, PCTUSED and ASSM
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" <[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 ---
RE: (looong) PCTFREE, PCTUSED and ASSM
Tanel, Maybe a PQ FTS needs to resolve migrated rows immediately as it's possible that the migrated row is located in a block that's allocated to a different PQ slave. Chris -Original Message- Sent: 12 November 2003 15:49 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: Dunscombe, Chris 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
Re: (looong) PCTFREE, PCTUSED and ASSM
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).
RE: (looong) PCTFREE, PCTUSED and ASSM
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).
Re: (looong) PCTFREE, PCTUSED and ASSM
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).
RE: (looong) PCTFREE, PCTUSED and ASSM
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 -Original Message- Sent: Tuesday, November 11, 2003 9:09 PM To: Multiple recipients of list ORACLE-L > From that, it would follow that free lists as such are gone in ASSM tablespaces > and are replaced by bitmaps. As Richard Foote has shown, PCTFREE is not ignored, > but without free lists, it doesn't make much sense. Does any of the gurus (Cary, Steve, > Jonathan, Wolfgang, Pete Sharman) have any knowledge of how exactly PCTFREE is > implemented in the ASSM situation? I can see and verify that it is not ignored, but > I can't figure how does it work. Mladen, The reason for existence of PCTFREE parameter is completely irrelevant to free space management issues, regardless whether we use FLM or ASSM. The only reason for PCTFREE is preventing/reducing row migration, through reserving space for row growth. As you know, row migration is bad for performance because reading migrated rows using index rowid access means reading datablock from original location of the row (the rowid stored in index), then the actual block where row has migrated (original block contains pointer to new location). Migrated rows don't cause that much of a problem for full table scans because all blocks are read anyway, but for index range scans they do. The problem comes from that whenever a row is migrated, corresponding index entries are not updated with the new location of a row, a pointer to it is left behind into datablock instead. This is not because of lazyness of a kernel programmer, this is intended behaviour, imagine if we'd have to scan and update all indexes on a table whenever it migrates due even 1-byte growth... So, it's cheaper to reserve some space in a block for future growth of a row, instead of trying to keep blocks 100% full and maintain all indexes on a table (plus find a new block for the row) every time row increases even a bit. So, hopefully this clears a bit, why we still need PCTFREE, regardless of free space management mechanism we use. In ASSM, as you said, we have currently 6 different freeness statuses for table blocks, 0 = unformatted, 1 = logically full, 2 = 0-25% free, 3 = 25-50% free, 4 = 50%-75% free, 5= 75-100% free. When a datablock is filled to some level, it's corresponding bit vector in it's level 1 bitmap block is updated to reflect its "freeness". When a block is 90% full for example, it's FS will be set to 0-25% free. However, if the block freeness drops below PCTFREE, it's freeness state will go to "FULL" regardless what's the percentage of free space in your block - it's PCTFREE that matters. You can easily trace it using events 10612 and 10613. At least in 9.2 it seems that also these blocks are marked FULL, which are rejected for an insert because the new row would have caused the free space drop below PCTFREE. Even if the existing block is 99% free and could accommodate several smaller rows in the future. This seemed a bit odd for me, because I thought that ASSM was supposed to eliminate the problem with inserting heavily varying sized rows, but few of my tests didn't show that good results. Maybe we'll see this improved in future versions. PCTUSED, however, is purely a space management parameter, which helped to reduce hit on freelists when block free space was fluctuating on PCTFREE level. Since freelists are implemented as linked lists of regular datablocks, it could get quite problematic if we'd have several blocks on freelists which actually don't have any real space for inserts. Although you can't specify PCTUSED in ASSM table, it exists there internally as well - a block is cleared the "logically full" status if it's freeness state drops below the next level under PCTFREE. This means if PCTFREE is 10%, then block is going to have FULL bit until it's free space increases at least to 25% (Remember the 0-25, 25-50, 50-75 and 75-100% freeness states) So, we could somewhat say that Oracle has hardcoded PCTUSED into it's ASSM mechanism, but one shouldn't consider ASSM an improved version of Freelists, it's completely different approach for managing free space and it has really lot's of optimizations in it. I now realized how much time it is, so I won't continue on how Oracle searches for free blocks, I found it to be way more complex (and fascinating :) when I did some research on it. The guy's at Oracle have really spent a lot of effort on figuring out a good solution for high-concurrency environments. Even though I don't have a whitepaper there, you could get some more information out from my presentation: http://integrid.info/Poder_Freelists_vs_ASSM.ppt Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 htt
Re: (looong) PCTFREE, PCTUSED and ASSM
> From that, it would follow that free lists as such are gone in ASSM tablespaces > and are replaced by bitmaps. As Richard Foote has shown, PCTFREE is not ignored, > but without free lists, it doesn't make much sense. Does any of the gurus (Cary, Steve, > Jonathan, Wolfgang, Pete Sharman) have any knowledge of how exactly PCTFREE is > implemented in the ASSM situation? I can see and verify that it is not ignored, but > I can't figure how does it work. Mladen, The reason for existence of PCTFREE parameter is completely irrelevant to free space management issues, regardless whether we use FLM or ASSM. The only reason for PCTFREE is preventing/reducing row migration, through reserving space for row growth. As you know, row migration is bad for performance because reading migrated rows using index rowid access means reading datablock from original location of the row (the rowid stored in index), then the actual block where row has migrated (original block contains pointer to new location). Migrated rows don't cause that much of a problem for full table scans because all blocks are read anyway, but for index range scans they do. The problem comes from that whenever a row is migrated, corresponding index entries are not updated with the new location of a row, a pointer to it is left behind into datablock instead. This is not because of lazyness of a kernel programmer, this is intended behaviour, imagine if we'd have to scan and update all indexes on a table whenever it migrates due even 1-byte growth... So, it's cheaper to reserve some space in a block for future growth of a row, instead of trying to keep blocks 100% full and maintain all indexes on a table (plus find a new block for the row) every time row increases even a bit. So, hopefully this clears a bit, why we still need PCTFREE, regardless of free space management mechanism we use. In ASSM, as you said, we have currently 6 different freeness statuses for table blocks, 0 = unformatted, 1 = logically full, 2 = 0-25% free, 3 = 25-50% free, 4 = 50%-75% free, 5= 75-100% free. When a datablock is filled to some level, it's corresponding bit vector in it's level 1 bitmap block is updated to reflect its "freeness". When a block is 90% full for example, it's FS will be set to 0-25% free. However, if the block freeness drops below PCTFREE, it's freeness state will go to "FULL" regardless what's the percentage of free space in your block - it's PCTFREE that matters. You can easily trace it using events 10612 and 10613. At least in 9.2 it seems that also these blocks are marked FULL, which are rejected for an insert because the new row would have caused the free space drop below PCTFREE. Even if the existing block is 99% free and could accommodate several smaller rows in the future. This seemed a bit odd for me, because I thought that ASSM was supposed to eliminate the problem with inserting heavily varying sized rows, but few of my tests didn't show that good results. Maybe we'll see this improved in future versions. PCTUSED, however, is purely a space management parameter, which helped to reduce hit on freelists when block free space was fluctuating on PCTFREE level. Since freelists are implemented as linked lists of regular datablocks, it could get quite problematic if we'd have several blocks on freelists which actually don't have any real space for inserts. Although you can't specify PCTUSED in ASSM table, it exists there internally as well - a block is cleared the "logically full" status if it's freeness state drops below the next level under PCTFREE. This means if PCTFREE is 10%, then block is going to have FULL bit until it's free space increases at least to 25% (Remember the 0-25, 25-50, 50-75 and 75-100% freeness states) So, we could somewhat say that Oracle has hardcoded PCTUSED into it's ASSM mechanism, but one shouldn't consider ASSM an improved version of Freelists, it's completely different approach for managing free space and it has really lot's of optimizations in it. I now realized how much time it is, so I won't continue on how Oracle searches for free blocks, I found it to be way more complex (and fascinating :) when I did some research on it. The guy's at Oracle have really spent a lot of effort on figuring out a good solution for high-concurrency environments. Even though I don't have a whitepaper there, you could get some more information out from my presentation: http://integrid.info/Poder_Freelists_vs_ASSM.ppt Tanel. -- 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