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: PCTFREE, PCTUSED and ASSM
Hi Mladen, Don't desert me now, I thought we've come a long way !! Although, I'm not a listed Oracle guru, let me try and explain further ;) PCTFREE works in almost exactly the same way as it does for non-ASSM objects. PCTFREE determines how much of the block we want to reserve for subsequent update growth. An insert that would violate this figure would result in the block being taken off the freelist (and other block being considered). However, with ASSM we don't have freelists, instead the corresponding BMB is updated to now reflect the block as being full. So an insert that would violate pctfree causes the block to be considered full and a full block is longer considered for subsequent inserts. Note an advantage of ASSM is that blocks can be filled more effectively because we know the relative free space in a given block thanks to the BMBs. So for example, if we have a row that is greater than 25% of a block in length, those blocks with less than 25% free space are not considered for inserts as we know such inserts would not succeed (in these blocks). Only blocks with over 25% free space are therefore considered. This way, rows of a relatively large size are less likely to prematurely make a block unavailable for inserts as is possible with non-ASSM tables. Thus, tables with widely variable row lengths are more suited to ASSM and could pack data in more tightly. For a block to be made available again for inserts, we need the available free space to be reduced below one of the free space boundaries. So for example, if a PCTFREE of 10% is used, once we have more than 25% free space (ie. the free space is between 25% and 50%) the block becomes "unfull" and the corresponding BMB is updated. Therefore the effective PCTUSED in this case is 75%. If however the PCTFREE were set to 30%, then we need to get below the 50% free space boundary (ie. free space is between 50-75%) in order to be insertable again, an effective PCTUSED of 50%. So in summary, PCTFREE is effectively used in a similar manner with ASSM, but rather than being taken off a freelist, the block is marked as full by the corresponding BMB. The PCTFREE value also has an influence on what with effective "PCTUSED" is used within the block as well. Hope this clears things up a bit(map ;) Cheers Richard (Non Oracle Guru / All Round Nice Guy ) - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, November 12, 2003 6:24 AM > Int the note 247752.1 named "Oracle9i Space Management Demystified" oracle says > the following: > -- - > AUTOMATIC SEGMENT SPACE MANAGEMENT > ARCHITECTURE Oracle9i introduces a new way of managing free space within a > segment using bitmaps. In the new scheme, a set of bits describes the space > utilization for each block in a segment as well as whether it is formatted or > not. Using these Paper # 32707 Oracle9i Database bits, for example, it is > possible to represent the state of any given data block as follows: " Free > space in the block is less than 25% " Free space in the block is greater than > 25% and less than 50% " Free Space in the block is greater than 50% but less > than 75% " Free space in the block is more than 75% " The block is FULL i.e. > there is no free space in the block " The block is unformatted. It can be noted > here that unlike freelists, where a block was either available for new rows or > not, bitmaps provide a more granular and accurate idea of space utilization > within blocks of segment. For LOBs and indexes, the bitmap just indicate " > Whether the block is formatted or not and, " Whether the block is considered > free or not. The Automatic Segment Space Management feature can only be used > with Locally Managed Tablespaces. BITMAP BLOCKS The bitmaps are contained in a > set of meta-data blocks known as bitmap blocks or BMBs. The number of BMBs in > a segment depends on its size and the space consumed by the bitmap blocks is > typically a very small part (less than 1%) of the total segment size for any > reasonable sized segment. As shown in the table below, the space overhead of > bitmap blocks decreases as the segment grows and becomes close to negligible > for large segments. > Segment Size Block Size No. Of BMBs Space Used by BMBs BMB Space Overhead > 25 MB2 KB 201 400 KB .8% > 500 MB 8 KB 251 2 MB .4% > 100 TB 16KB 6555941 100 GB .05% > -- -- > > 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 > i
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
PCTFREE, PCTUSED and ASSM
Int the note 247752.1 named "Oracle9i Space Management Demystified" oracle says the following: --- AUTOMATIC SEGMENT SPACE MANAGEMENT ARCHITECTURE Oracle9i introduces a new way of managing free space within a segment using bitmaps. In the new scheme, a set of bits describes the space utilization for each block in a segment as well as whether it is formatted or not. Using these Paper # 32707 Oracle9i Database bits, for example, it is possible to represent the state of any given data block as follows: " Free space in the block is less than 25% " Free space in the block is greater than 25% and less than 50% " Free Space in the block is greater than 50% but less than 75% " Free space in the block is more than 75% " The block is FULL i.e. there is no free space in the block " The block is unformatted. It can be noted here that unlike freelists, where a block was either available for new rows or not, bitmaps provide a more granular and accurate idea of space utilization within blocks of segment. For LOBs and indexes, the bitmap just indicate " Whether the block is formatted or not and, " Whether the block is considered free or not. The Automatic Segment Space Management feature can only be used with Locally Managed Tablespaces. BITMAP BLOCKS The bitmaps are contained in a set of meta-data blocks known as bitmap blocks or BMBs. The number of BMBs in a segment depends on its size and the space consumed by the bitmap blocks is typically a very small part (less than 1%) of the total segment size for any reasonable sized segment. As shown in the table below, the space overhead of bitmap blocks decreases as the segment grows and becomes close to negligible for large segments. Segment Size Block Size No. Of BMBs Space Used by BMBs BMB Space Overhead 25 MB2 KB 201 400 KB .8% 500 MB 8 KB 251 2 MB .4% 100 TB 16KB 6555941 100 GB .05% >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 Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
