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).
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Yes you can move within the same tablespace. I had to do this when one of the other developers created a table with the default pctfree. Updates were causing rows to chain, so I issued the alter table move command with a new pctfree. Rebuilt the indexes and analyzed the table again and so far everything is working great. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 9/26/2002 12:33 PM, Hemant K Chitale <[EMAIL PROTECTED]> wrote: > >Well, yes, that is one option. Can I issue an ALTER TABLE >MOVE to >the same tablespace, I wonder. > >Thanks >Hemant >At 08:23 AM 25-09-02 -0800, you wrote: >>Hemant, >> >>If I understand your question correctly, trying using the alter >table move >>command and specify new values for pctfree and pctused. This should > >>affect existing blocks. Make sure to rebuild any indicies. >> >>HTH, >>-- >>Alan Davey >>[EMAIL PROTECTED] >> >> >> >>On 9/25/2002 11:38 AM, Hemant K Chitale <[EMAIL PROTECTED]> >wrote: >> > >> >Let me clarify my original question. >> > >> >I do not expect the FreeList for a table to get updated instantaneously >> >after I change the PCTFREE/PCTUSED. >> >What I meant by "is the effect ... immediate" is that do the >> >new values come into play immediately -- even for existing blocks. >> > >> >Suppose I have a table where PCTFREE was high (40) >> >and PCTUSED high (50 or 60). Thus, ignoring deletes [and overheads], >> >there would have been about 60% [100-40] usage in the block -- >> >these could mean a large number of rows. >> >Now, I want to reduce the number of rows in a block -- the >> >particular table is a "hot" table where some blocks become >> >"very hot" spots [extremely high rate of updates to existing rows, >> >updates which do not increase the size of existing rows]. >> >My "cache buffer chains latch" contention is high. >> >I further introduce the possibility of deletes [e.g. a purge job >> >running daily]. Because PCTUSED is high, and not very many >> >rows in a block get deleted at each purge, the block is unlikely >> >to come into the FreeList early. It would be a number of days >> >before enough rows are deleted from the block. >> > >> >Therefore, to reduce the contention for the "hot blocks", I decide >> >to have only 1 row in each block. Normally, with a *NEW* table, >> >PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per >block. >> >But if I have a large number of blocks in a few extents created >when >> >PCTFREE was 40 and PCTUSED 50 or 60. When would these >> >existing blocks start behaving as if they were created with >> >PCTFREE 99 and PCTUSED 1 ? >> >How about new blocks ? Would new [empty] blocks in existing >> >extents immediately behave such that they allow only one row >> >per block ? Or would only new blocks in new extents take >> >the PCTFREE 99 and PCTUSED 1 attributes ? >> > >> >Hemant >> > >> > >> > >> >At 03:53 PM 24-09-02 -0800, you wrote: >> >>I replied too soon earlier, I think. >> >> >> >>Yes, what you state is correct. >> >> >> >>Jraed >> >> >> >> >> >> >> >> >> >> >> >> >> >>[EMAIL PROTECTED] >> >>Sent by: [EMAIL PROTECTED] >> >> 09/24/2002 09:08 AM >> >> Please respond to ORACLE-L >> >> >> >> >> >> To: Multiple recipients of list ORACLE-L >> <[EMAIL PROTECTED]> >> >> cc: >> >> Subject:RE: Is the effect of modifying PCTFREE/PCTUSED >> > >> >> immediate ? >> >> >> >> >> >>Well I was sure about it until you had the temerity to question >> >me :) >> >>I think we agree on extents sizes not being changed after the >event >> >so it >> >>is >> >>now a discussion on whether changes to a pctfree/pctused are >> >>retrospective. >> >> >> >>I contend that if a table is fully loaded upto its pctfree/pctused >> >limits >> >>and there are no available blocks on the freelist then by changing >> >the >> >>pctfree/pctused values no additional blocks will suddenly appear >> >on the >> >>freelist. >> >>I do agree however that if a block is am
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Yes, I think I need to test it for myself and see what I get. As for the busy table in a production environment [24x7, so there's no time to rebuild the table normally], I've put in a request for 30minutes downtime [I am allowed an occassional 15 to 30 minutes every two months] to rebuild the table. Hemant At 08:33 AM 25-09-02 -0800, you wrote: >Hemant wrote > >> >Therefore, to reduce the contention for the "hot blocks", I decide >to have only 1 row in each block. Normally, with a *NEW* table, >PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. >But if I have a large number of blocks in a few extents created when >PCTFREE was 40 and PCTUSED 50 or 60. When would these >existing blocks start behaving as if they were created with >PCTFREE 99 and PCTUSED 1 ? >How about new blocks ? Would new [empty] blocks in existing >extents immediately behave such that they allow only one row >per block ? Or would only new blocks in new extents take >the PCTFREE 99 and PCTUSED 1 attributes ? > >> > >I would guess the following Hemant, >After changing the PCTFREE/PCTUSED values I do not think existing blocks >will be changed until sufficient rows are deleted so that a block will be >available on the freelist. >If as you say there is only 1 row to a block then that would need to be >deleted so that the block became available on the freelist >and the new values would apply once a new row was inserted. >Moving on from there it seems logical to me (that may be where I am going >wrong!!) that an existing empty block will have it's values changed and >will remain on the freelist but with revised parameters. I cannot say for >certain without performing some tests but that is my gut feel > >HTH > >John > > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: > 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). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale 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: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Yes, you can! - Kirti -Original Message- Sent: Thursday, September 26, 2002 11:34 AM To: Multiple recipients of list ORACLE-L Well, yes, that is one option. Can I issue an ALTER TABLE MOVE to the same tablespace, I wonder. Thanks Hemant At 08:23 AM 25-09-02 -0800, you wrote: >Hemant, > >If I understand your question correctly, trying using the alter table move >command and specify new values for pctfree and pctused. This should >affect existing blocks. Make sure to rebuild any indicies. > >HTH, >-- >Alan Davey >[EMAIL PROTECTED] > > > >On 9/25/2002 11:38 AM, Hemant K Chitale <[EMAIL PROTECTED]> wrote: > > > >Let me clarify my original question. > > > >I do not expect the FreeList for a table to get updated instantaneously > >after I change the PCTFREE/PCTUSED. > >What I meant by "is the effect ... immediate" is that do the > >new values come into play immediately -- even for existing blocks. > > > >Suppose I have a table where PCTFREE was high (40) > >and PCTUSED high (50 or 60). Thus, ignoring deletes [and overheads], > >there would have been about 60% [100-40] usage in the block -- > >these could mean a large number of rows. > >Now, I want to reduce the number of rows in a block -- the > >particular table is a "hot" table where some blocks become > >"very hot" spots [extremely high rate of updates to existing rows, > >updates which do not increase the size of existing rows]. > >My "cache buffer chains latch" contention is high. > >I further introduce the possibility of deletes [e.g. a purge job > >running daily]. Because PCTUSED is high, and not very many > >rows in a block get deleted at each purge, the block is unlikely > >to come into the FreeList early. It would be a number of days > >before enough rows are deleted from the block. > > > >Therefore, to reduce the contention for the "hot blocks", I decide > >to have only 1 row in each block. Normally, with a *NEW* table, > >PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. > >But if I have a large number of blocks in a few extents created when > >PCTFREE was 40 and PCTUSED 50 or 60. When would these > >existing blocks start behaving as if they were created with > >PCTFREE 99 and PCTUSED 1 ? > >How about new blocks ? Would new [empty] blocks in existing > >extents immediately behave such that they allow only one row > >per block ? Or would only new blocks in new extents take > >the PCTFREE 99 and PCTUSED 1 attributes ? > > > >Hemant > > > > > > > >At 03:53 PM 24-09-02 -0800, you wrote: > >>I replied too soon earlier, I think. > >> > >>Yes, what you state is correct. > >> > >>Jraed > >> > >> > >> > >> > >> > >> > >>[EMAIL PROTECTED] > >>Sent by: [EMAIL PROTECTED] > >> 09/24/2002 09:08 AM > >> Please respond to ORACLE-L > >> > >> > >> To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > >> cc: > >> Subject:RE: Is the effect of modifying PCTFREE/PCTUSED > > > >> immediate ? > >> > >> > >>Well I was sure about it until you had the temerity to question > >me :) > >>I think we agree on extents sizes not being changed after the event > >so it > >>is > >>now a discussion on whether changes to a pctfree/pctused are > >>retrospective. > >> > >>I contend that if a table is fully loaded upto its pctfree/pctused > >limits > >>and there are no available blocks on the freelist then by changing > >the > >>pctfree/pctused values no additional blocks will suddenly appear > >on the > >>freelist. > >>I do agree however that if a block is amended by having a row deleted > >or a > >>row updated then the new values come into play and the blockcould > >then be > >>available on the freelist. > >> > >>I think I am correct on this but as with anything I am always ready > >to be > >>proved wrong - it has happened before and wil lhappen may times > >in the > >>future > >> > >>John > >> > >> > >>-Original Message- > >>Sent: 24 September 2002 15:47 > >>To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > >> > >> > >> > >>Are you sure about that John? > >> > >>On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wr
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Well, yes, that is one option. Can I issue an ALTER TABLE MOVE to the same tablespace, I wonder. Thanks Hemant At 08:23 AM 25-09-02 -0800, you wrote: >Hemant, > >If I understand your question correctly, trying using the alter table move >command and specify new values for pctfree and pctused. This should >affect existing blocks. Make sure to rebuild any indicies. > >HTH, >-- >Alan Davey >[EMAIL PROTECTED] > > > >On 9/25/2002 11:38 AM, Hemant K Chitale <[EMAIL PROTECTED]> wrote: > > > >Let me clarify my original question. > > > >I do not expect the FreeList for a table to get updated instantaneously > >after I change the PCTFREE/PCTUSED. > >What I meant by "is the effect ... immediate" is that do the > >new values come into play immediately -- even for existing blocks. > > > >Suppose I have a table where PCTFREE was high (40) > >and PCTUSED high (50 or 60). Thus, ignoring deletes [and overheads], > >there would have been about 60% [100-40] usage in the block -- > >these could mean a large number of rows. > >Now, I want to reduce the number of rows in a block -- the > >particular table is a "hot" table where some blocks become > >"very hot" spots [extremely high rate of updates to existing rows, > >updates which do not increase the size of existing rows]. > >My "cache buffer chains latch" contention is high. > >I further introduce the possibility of deletes [e.g. a purge job > >running daily]. Because PCTUSED is high, and not very many > >rows in a block get deleted at each purge, the block is unlikely > >to come into the FreeList early. It would be a number of days > >before enough rows are deleted from the block. > > > >Therefore, to reduce the contention for the "hot blocks", I decide > >to have only 1 row in each block. Normally, with a *NEW* table, > >PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. > >But if I have a large number of blocks in a few extents created when > >PCTFREE was 40 and PCTUSED 50 or 60. When would these > >existing blocks start behaving as if they were created with > >PCTFREE 99 and PCTUSED 1 ? > >How about new blocks ? Would new [empty] blocks in existing > >extents immediately behave such that they allow only one row > >per block ? Or would only new blocks in new extents take > >the PCTFREE 99 and PCTUSED 1 attributes ? > > > >Hemant > > > > > > > >At 03:53 PM 24-09-02 -0800, you wrote: > >>I replied too soon earlier, I think. > >> > >>Yes, what you state is correct. > >> > >>Jraed > >> > >> > >> > >> > >> > >> > >>[EMAIL PROTECTED] > >>Sent by: [EMAIL PROTECTED] > >> 09/24/2002 09:08 AM > >> Please respond to ORACLE-L > >> > >> > >> To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > >> cc: > >> Subject:RE: Is the effect of modifying PCTFREE/PCTUSED > > > >> immediate ? > >> > >> > >>Well I was sure about it until you had the temerity to question > >me :) > >>I think we agree on extents sizes not being changed after the event > >so it > >>is > >>now a discussion on whether changes to a pctfree/pctused are > >>retrospective. > >> > >>I contend that if a table is fully loaded upto its pctfree/pctused > >limits > >>and there are no available blocks on the freelist then by changing > >the > >>pctfree/pctused values no additional blocks will suddenly appear > >on the > >>freelist. > >>I do agree however that if a block is amended by having a row deleted > >or a > >>row updated then the new values come into play and the blockcould > >then be > >>available on the freelist. > >> > >>I think I am correct on this but as with anything I am always ready > >to be > >>proved wrong - it has happened before and wil lhappen may times > >in the > >>future > >> > >>John > >> > >> > >>-Original Message- > >>Sent: 24 September 2002 15:47 > >>To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > >> > >> > >> > >>Are you sure about that John? > >> > >>On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: > >> > No, it is not retrospective. > >> > You are setting parameters to be used when the next extent
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Hemant wrote >> Therefore, to reduce the contention for the "hot blocks", I decide to have only 1 row in each block. Normally, with a *NEW* table, PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. But if I have a large number of blocks in a few extents created when PCTFREE was 40 and PCTUSED 50 or 60. When would these existing blocks start behaving as if they were created with PCTFREE 99 and PCTUSED 1 ? How about new blocks ? Would new [empty] blocks in existing extents immediately behave such that they allow only one row per block ? Or would only new blocks in new extents take the PCTFREE 99 and PCTUSED 1 attributes ? >> I would guess the following Hemant, After changing the PCTFREE/PCTUSED values I do not think existing blocks will be changed until sufficient rows are deleted so that a block will be available on the freelist. If as you say there is only 1 row to a block then that would need to be deleted so that the block became available on the freelist and the new values would apply once a new row was inserted. Moving on from there it seems logical to me (that may be where I am going wrong!!) that an existing empty block will have it's values changed and will remain on the freelist but with revised parameters. I cannot say for certain without performing some tests but that is my gut feel HTH John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Hemant, If I understand your question correctly, trying using the alter table move command and specify new values for pctfree and pctused. This should affect existing blocks. Make sure to rebuild any indicies. HTH, -- Alan Davey [EMAIL PROTECTED] On 9/25/2002 11:38 AM, Hemant K Chitale <[EMAIL PROTECTED]> wrote: > >Let me clarify my original question. > >I do not expect the FreeList for a table to get updated instantaneously >after I change the PCTFREE/PCTUSED. >What I meant by "is the effect ... immediate" is that do the >new values come into play immediately -- even for existing blocks. > >Suppose I have a table where PCTFREE was high (40) >and PCTUSED high (50 or 60). Thus, ignoring deletes [and overheads], >there would have been about 60% [100-40] usage in the block -- >these could mean a large number of rows. >Now, I want to reduce the number of rows in a block -- the >particular table is a "hot" table where some blocks become >"very hot" spots [extremely high rate of updates to existing rows, >updates which do not increase the size of existing rows]. >My "cache buffer chains latch" contention is high. >I further introduce the possibility of deletes [e.g. a purge job >running daily]. Because PCTUSED is high, and not very many >rows in a block get deleted at each purge, the block is unlikely >to come into the FreeList early. It would be a number of days >before enough rows are deleted from the block. > >Therefore, to reduce the contention for the "hot blocks", I decide >to have only 1 row in each block. Normally, with a *NEW* table, >PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. >But if I have a large number of blocks in a few extents created when >PCTFREE was 40 and PCTUSED 50 or 60. When would these >existing blocks start behaving as if they were created with >PCTFREE 99 and PCTUSED 1 ? >How about new blocks ? Would new [empty] blocks in existing >extents immediately behave such that they allow only one row >per block ? Or would only new blocks in new extents take >the PCTFREE 99 and PCTUSED 1 attributes ? > >Hemant > > > >At 03:53 PM 24-09-02 -0800, you wrote: >>I replied too soon earlier, I think. >> >>Yes, what you state is correct. >> >>Jraed >> >> >> >> >> >> >>[EMAIL PROTECTED] >>Sent by: [EMAIL PROTECTED] >> 09/24/2002 09:08 AM >> Please respond to ORACLE-L >> >> >> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >> cc: >> Subject:RE: Is the effect of modifying PCTFREE/PCTUSED > >> immediate ? >> >> >>Well I was sure about it until you had the temerity to question >me :) >>I think we agree on extents sizes not being changed after the event >so it >>is >>now a discussion on whether changes to a pctfree/pctused are >>retrospective. >> >>I contend that if a table is fully loaded upto its pctfree/pctused >limits >>and there are no available blocks on the freelist then by changing >the >>pctfree/pctused values no additional blocks will suddenly appear >on the >>freelist. >>I do agree however that if a block is amended by having a row deleted >or a >>row updated then the new values come into play and the blockcould >then be >>available on the freelist. >> >>I think I am correct on this but as with anything I am always ready >to be >>proved wrong - it has happened before and wil lhappen may times >in the >>future >> >>John >> >> >>-Original Message- >>Sent: 24 September 2002 15:47 >>To: [EMAIL PROTECTED]; [EMAIL PROTECTED] >> >> >> >>Are you sure about that John? >> >>On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: >> > No, it is not retrospective. >> > You are setting parameters to be used when the next extent is >created. >> > A better example is when setting next extent size to be different >than >>the >> > existing extent size (dictionary managed tablespaces only). >> > It does not alter all the existing extents it only works on the >next one >> > that is created. >> > >> > HTH >> > >> > John >> > >> > -Original Message- >> > Sent: 24 September 2002 10:58 >> > To: Multiple recipients of list ORACLE-L >> > >> > >> > >> > Is the effect of modifying PCTFREE/PCTUSED immediate ? >> > >> > >> > If I do an "ALTER TABLE PCTFREE 99 PCTUSED1&
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Let me clarify my original question. I do not expect the FreeList for a table to get updated instantaneously after I change the PCTFREE/PCTUSED. What I meant by "is the effect ... immediate" is that do the new values come into play immediately -- even for existing blocks. Suppose I have a table where PCTFREE was high (40) and PCTUSED high (50 or 60). Thus, ignoring deletes [and overheads], there would have been about 60% [100-40] usage in the block -- these could mean a large number of rows. Now, I want to reduce the number of rows in a block -- the particular table is a "hot" table where some blocks become "very hot" spots [extremely high rate of updates to existing rows, updates which do not increase the size of existing rows]. My "cache buffer chains latch" contention is high. I further introduce the possibility of deletes [e.g. a purge job running daily]. Because PCTUSED is high, and not very many rows in a block get deleted at each purge, the block is unlikely to come into the FreeList early. It would be a number of days before enough rows are deleted from the block. Therefore, to reduce the contention for the "hot blocks", I decide to have only 1 row in each block. Normally, with a *NEW* table, PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. But if I have a large number of blocks in a few extents created when PCTFREE was 40 and PCTUSED 50 or 60. When would these existing blocks start behaving as if they were created with PCTFREE 99 and PCTUSED 1 ? How about new blocks ? Would new [empty] blocks in existing extents immediately behave such that they allow only one row per block ? Or would only new blocks in new extents take the PCTFREE 99 and PCTUSED 1 attributes ? Hemant At 03:53 PM 24-09-02 -0800, you wrote: >I replied too soon earlier, I think. > >Yes, what you state is correct. > >Jraed > > > > > > >[EMAIL PROTECTED] >Sent by: [EMAIL PROTECTED] > 09/24/2002 09:08 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject:RE: Is the effect of modifying PCTFREE/PCTUSED > immediate ? > > >Well I was sure about it until you had the temerity to question me :) >I think we agree on extents sizes not being changed after the event so it >is >now a discussion on whether changes to a pctfree/pctused are >retrospective. > >I contend that if a table is fully loaded upto its pctfree/pctused limits >and there are no available blocks on the freelist then by changing the >pctfree/pctused values no additional blocks will suddenly appear on the >freelist. >I do agree however that if a block is amended by having a row deleted or a >row updated then the new values come into play and the blockcould then be >available on the freelist. > >I think I am correct on this but as with anything I am always ready to be >proved wrong - it has happened before and wil lhappen may times in the >future > >John > > >-Original Message- >Sent: 24 September 2002 15:47 >To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > >Are you sure about that John? > >On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: > > No, it is not retrospective. > > You are setting parameters to be used when the next extent is created. > > A better example is when setting next extent size to be different than >the > > existing extent size (dictionary managed tablespaces only). > > It does not alter all the existing extents it only works on the next one > > that is created. > > > > HTH > > > > John > > > > -Original Message- > > Sent: 24 September 2002 10:58 > > To: Multiple recipients of list ORACLE-L > > > > > > > > Is the effect of modifying PCTFREE/PCTUSED immediate ? > > > > > > If I do an "ALTER TABLE PCTFREE 99 PCTUSED1", > > does this take effect immediately, even for existing blocks. > > [If so, existing blocks would not get new rows inserted]. > > Or is it effective only in new Extents ? In that case, > > existing blocks in existing Extents still use the old > > PCTFREE/PCTUSED parameters and keep re-entering the > > FreeList. > > > > Hemant K Chitale > > http://hkchital.tripod.com >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: > 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
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
I replied too soon earlier, I think. Yes, what you state is correct. Jraed [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/24/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Is the effect of modifying PCTFREE/PCTUSED immediate ? Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: > No, it is not retrospective. > You are setting parameters to be used when the next extent is created. > A better example is when setting next extent size to be different than the > existing extent size (dictionary managed tablespaces only). > It does not alter all the existing extents it only works on the next one > that is created. > > HTH > > John > > -Original Message- > Sent: 24 September 2002 10:58 > To: Multiple recipients of list ORACLE-L > > > > Is the effect of modifying PCTFREE/PCTUSED immediate ? > > > If I do an "ALTER TABLE PCTFREE 99 PCTUSED1", > does this take effect immediately, even for existing blocks. > [If so, existing blocks would not get new rows inserted]. > Or is it effective only in new Extents ? In that case, > existing blocks in existing Extents still use the old > PCTFREE/PCTUSED parameters and keep re-entering the > FreeList. > > Hemant K Chitale > http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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.com -- Author: 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: Is the effect of modifying PCTFREE/PCTUSED immediate ?
John, You are right, I just find out note 1029850.6 on metalink : "A block is relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTUSED." --- [EMAIL PROTECTED] a écrit : > Well I was sure about it until you had the temerity > to question me :) > I think we agree on extents sizes not being changed > after the event so it is > now a discussion on whether changes to a > pctfree/pctused are retrospective. > > I contend that if a table is fully loaded upto its > pctfree/pctused limits > and there are no available blocks on the freelist > then by changing the > pctfree/pctused values no additional blocks will > suddenly appear on the > freelist. > I do agree however that if a block is amended by > having a row deleted or a > row updated then the new values come into play and > the blockcould then be > available on the freelist. > > I think I am correct on this but as with anything I > am always ready to be > proved wrong - it has happened before and wil > lhappen may times in the > future > > John > > > -Original Message- > Sent: 24 September 2002 15:47 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > > Are you sure about that John? > > On Tuesday 24 September 2002 04:28, > [EMAIL PROTECTED] wrote: > > No, it is not retrospective. > > You are setting parameters to be used when the > next extent is created. > > A better example is when setting next extent size > to be different than the > > existing extent size (dictionary managed > tablespaces only). > > It does not alter all the existing extents it only > works on the next one > > that is created. > > > > HTH > > > > John > > > > -Original Message- > > Sent: 24 September 2002 10:58 > > To: Multiple recipients of list ORACLE-L > > > > > > > > Is the effect of modifying PCTFREE/PCTUSED > immediate ? > > > > > > If I do an "ALTER TABLE PCTFREE 99 > PCTUSED1", > > does this take effect immediately, even for > existing blocks. > > [If so, existing blocks would not get new rows > inserted]. > > Or is it effective only in new Extents ? In that > case, > > existing blocks in existing Extents still use the > old > > PCTFREE/PCTUSED parameters and keep re-entering > the > > FreeList. > > > > Hemant K Chitale > > http://hkchital.tripod.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: > 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Jared, So, that means that to remedy a case of data block fragmentation we just need to increase the pctused for the fragmented tables. Of course, things won't change as fast as an export/import but it's certainly less work to do. --- [EMAIL PROTECTED] a écrit : > John, > > Someone asked a question a month or so ago about > changing > PCTUSED and PCTFREE: When do the blocks go back on > the > free list, when the 'ALTER TABLE ... PCTFREE N' > command was > issued, or did the blocks go back on the free list > when the next > insert was issued. > > I don't remember what my conclusion was, and IIRC, > it wasn't > definite. But, testing shows that blocks do go back > on the free list > when PCTUSED is increased to a a value greater than > the amount > of data in the block. > > This was on 8.1.7 on Linux. > > It's in the archives if you care to look for it. > > Jared > > > > > > > [EMAIL PROTECTED] > Sent by: [EMAIL PROTECTED] > 09/24/2002 09:08 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:RE: Is the effect of > modifying PCTFREE/PCTUSED immediate ? > > > Well I was sure about it until you had the temerity > to question me :) > I think we agree on extents sizes not being changed > after the event so it > is > now a discussion on whether changes to a > pctfree/pctused are > retrospective. > > I contend that if a table is fully loaded upto its > pctfree/pctused limits > and there are no available blocks on the freelist > then by changing the > pctfree/pctused values no additional blocks will > suddenly appear on the > freelist. > I do agree however that if a block is amended by > having a row deleted or a > row updated then the new values come into play and > the blockcould then be > available on the freelist. > > I think I am correct on this but as with anything I > am always ready to be > proved wrong - it has happened before and wil > lhappen may times in the > future > > John > > > -Original Message- > Sent: 24 September 2002 15:47 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > > Are you sure about that John? > > On Tuesday 24 September 2002 04:28, > [EMAIL PROTECTED] wrote: > > No, it is not retrospective. > > You are setting parameters to be used when the > next extent is created. > > A better example is when setting next extent size > to be different than > the > > existing extent size (dictionary managed > tablespaces only). > > It does not alter all the existing extents it only > works on the next one > > that is created. > > > > HTH > > > > John > > > > -Original Message- > > Sent: 24 September 2002 10:58 > > To: Multiple recipients of list ORACLE-L > > > > > > > > Is the effect of modifying PCTFREE/PCTUSED > immediate ? > > > > > > If I do an "ALTER TABLE PCTFREE 99 > PCTUSED1", > > does this take effect immediately, even for > existing blocks. > > [If so, existing blocks would not get new rows > inserted]. > > Or is it effective only in new Extents ? In that > case, > > existing blocks in existing Extents still use the > old > > PCTFREE/PCTUSED parameters and keep re-entering > the > > FreeList. > > > > Hemant K Chitale > > http://hkchital.tripod.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: > 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.com > -- > Author: > 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
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
John, Someone asked a question a month or so ago about changing PCTUSED and PCTFREE: When do the blocks go back on the free list, when the 'ALTER TABLE ... PCTFREE N' command was issued, or did the blocks go back on the free list when the next insert was issued. I don't remember what my conclusion was, and IIRC, it wasn't definite. But, testing shows that blocks do go back on the free list when PCTUSED is increased to a a value greater than the amount of data in the block. This was on 8.1.7 on Linux. It's in the archives if you care to look for it. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/24/2002 09:08 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Is the effect of modifying PCTFREE/PCTUSED immediate ? Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: > No, it is not retrospective. > You are setting parameters to be used when the next extent is created. > A better example is when setting next extent size to be different than the > existing extent size (dictionary managed tablespaces only). > It does not alter all the existing extents it only works on the next one > that is created. > > HTH > > John > > -Original Message- > Sent: 24 September 2002 10:58 > To: Multiple recipients of list ORACLE-L > > > > Is the effect of modifying PCTFREE/PCTUSED immediate ? > > > If I do an "ALTER TABLE PCTFREE 99 PCTUSED1", > does this take effect immediately, even for existing blocks. > [If so, existing blocks would not get new rows inserted]. > Or is it effective only in new Extents ? In that case, > existing blocks in existing Extents still use the old > PCTFREE/PCTUSED parameters and keep re-entering the > FreeList. > > Hemant K Chitale > http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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.com -- Author: 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: Is the effect of modifying PCTFREE/PCTUSED immediate ?
I'm not sure either as I am rereading a document by Craig Shallamaher where he is saying to change pctused and pctfree in order to reduce data block fragmentation. I have to test that. At my new job, the DBAs are doing massive export/import to reduce fragmentation... (with their dictionnary managed tablespace) --- Jared Still <[EMAIL PROTECTED]> a écrit : > > Are you sure about that John? > > On Tuesday 24 September 2002 04:28, > [EMAIL PROTECTED] wrote: > > No, it is not retrospective. > > You are setting parameters to be used when the > next extent is created. > > A better example is when setting next extent size > to be different than the > > existing extent size (dictionary managed > tablespaces only). > > It does not alter all the existing extents it only > works on the next one > > that is created. > > > > HTH > > > > John > > > > -Original Message- > > Sent: 24 September 2002 10:58 > > To: Multiple recipients of list ORACLE-L > > > > > > > > Is the effect of modifying PCTFREE/PCTUSED > immediate ? > > > > > > If I do an "ALTER TABLE PCTFREE 99 > PCTUSED1", > > does this take effect immediately, even for > existing blocks. > > [If so, existing blocks would not get new rows > inserted]. > > Or is it effective only in new Extents ? In that > case, > > existing blocks in existing Extents still use the > old > > PCTFREE/PCTUSED parameters and keep re-entering > the > > FreeList. > > > > Hemant K Chitale > > http://hkchital.tripod.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jared Still > 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Well I was sure about it until you had the temerity to question me :) I think we agree on extents sizes not being changed after the event so it is now a discussion on whether changes to a pctfree/pctused are retrospective. I contend that if a table is fully loaded upto its pctfree/pctused limits and there are no available blocks on the freelist then by changing the pctfree/pctused values no additional blocks will suddenly appear on the freelist. I do agree however that if a block is amended by having a row deleted or a row updated then the new values come into play and the blockcould then be available on the freelist. I think I am correct on this but as with anything I am always ready to be proved wrong - it has happened before and wil lhappen may times in the future John -Original Message- Sent: 24 September 2002 15:47 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: > No, it is not retrospective. > You are setting parameters to be used when the next extent is created. > A better example is when setting next extent size to be different than the > existing extent size (dictionary managed tablespaces only). > It does not alter all the existing extents it only works on the next one > that is created. > > HTH > > John > > -Original Message- > Sent: 24 September 2002 10:58 > To: Multiple recipients of list ORACLE-L > > > > Is the effect of modifying PCTFREE/PCTUSED immediate ? > > > If I do an "ALTER TABLE PCTFREE 99 PCTUSED1", > does this take effect immediately, even for existing blocks. > [If so, existing blocks would not get new rows inserted]. > Or is it effective only in new Extents ? In that case, > existing blocks in existing Extents still use the old > PCTFREE/PCTUSED parameters and keep re-entering the > FreeList. > > Hemant K Chitale > http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Are you sure about that John? On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] wrote: > No, it is not retrospective. > You are setting parameters to be used when the next extent is created. > A better example is when setting next extent size to be different than the > existing extent size (dictionary managed tablespaces only). > It does not alter all the existing extents it only works on the next one > that is created. > > HTH > > John > > -Original Message- > Sent: 24 September 2002 10:58 > To: Multiple recipients of list ORACLE-L > > > > Is the effect of modifying PCTFREE/PCTUSED immediate ? > > > If I do an "ALTER TABLE PCTFREE 99 PCTUSED1", > does this take effect immediately, even for existing blocks. > [If so, existing blocks would not get new rows inserted]. > Or is it effective only in new Extents ? In that case, > existing blocks in existing Extents still use the old > PCTFREE/PCTUSED parameters and keep re-entering the > FreeList. > > Hemant K Chitale > http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: Is the effect of modifying PCTFREE/PCTUSED immediate ?
No, it is not retrospective. You are setting parameters to be used when the next extent is created. A better example is when setting next extent size to be different than the existing extent size (dictionary managed tablespaces only). It does not alter all the existing extents it only works on the next one that is created. HTH John -Original Message- Sent: 24 September 2002 10:58 To: Multiple recipients of list ORACLE-L Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an "ALTER TABLE PCTFREE 99 PCTUSED1", does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: hkchital 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.com -- Author: 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).
Is the effect of modifying PCTFREE/PCTUSED immediate ?
Is the effect of modifying PCTFREE/PCTUSED immediate ? If I do an "ALTER TABLE PCTFREE 99 PCTUSED1", does this take effect immediately, even for existing blocks. [If so, existing blocks would not get new rows inserted]. Or is it effective only in new Extents ? In that case, existing blocks in existing Extents still use the old PCTFREE/PCTUSED parameters and keep re-entering the FreeList. Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: hkchital 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
why does this number not surprise me ??? lol -Original Message- Sent: Thursday, September 05, 2002 1:18 PM To: Multiple recipients of list ORACLE-L 42 ;>) -Original Message- Sent: Thursday, September 05, 2002 3:05 PM To: Multiple recipients of list ORACLE-L Hi Can some one suggest what would be normal PCTFREE and PCTUSED for following type of tables? TYPE A: High rate of insert/delete but less update TYPE B: High rate of update but less insert/delete TYPE C: Large objects used for read mostly less DML operations Type D: High rate of DML operations Thanks in advance -seema _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
42 ;>) -Original Message- Sent: Thursday, September 05, 2002 3:05 PM To: Multiple recipients of list ORACLE-L Hi Can some one suggest what would be normal PCTFREE and PCTUSED for following type of tables? TYPE A: High rate of insert/delete but less update TYPE B: High rate of update but less insert/delete TYPE C: Large objects used for read mostly less DML operations Type D: High rate of DML operations Thanks in advance -seema _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
PCTFREE & PCTUSED
Hi Can some one suggest what would be normal PCTFREE and PCTUSED for following type of tables? TYPE A: High rate of insert/delete but less update TYPE B: High rate of update but less insert/delete TYPE C: Large objects used for read mostly less DML operations Type D: High rate of DML operations Thanks in advance -seema _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: 8i Concept PCTFREE & PCTUSED are 0
I don't think this is true anymore. At one time, PCTUSED had to be set to "1", but as of 8i, at least, it will be put back on the freelist when empty if PCTUSED=0. (I haven't tested it myself, but I believe Dave Ensor.) -Don Granaman [OraSaurus] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, February 18, 2002 6:38 AM > Hello Sinardy > > PCTUSED means that: > After a block was filled with records and the free space in the block > is less then pctfree the block is no longer available for further inserts. > If however, the data falls beneath PCTUSED the block became again > available for inserts. > > In your example, block will never again became available, even if empty, > because: After a data block becomes full as determined by PCTFREE, Oracle > does not consider the block for the insertion of new rows until the > percentage of the block being used falls below the parameter PCTUSED. > > > Yechiel Adar, Mehish Computer Services > [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: 8i Concept PCTFREE & PCTUSED are 0
Hi Long,
Do you mean Oracle should come with AI capability ?
then Oracle will be very buggy and Oracle Concept Doc will be the last SOS.
With current version I think Oracle is good in general but certainly not
ready for AI.
Sinardy
-Original Message-
[EMAIL PROTECTED]
Sent: 19 February 2002 09:54
To: Multiple recipients of list ORACLE-L
Hi,
This is an offside comment rather than a direct reply to Sinard's question.
Oracle has made many config parameters tuneable and this makes the software
both flexible and complex. On the other hand MS's SQLServer only has a small
number of
config parameters that could be changed by DBA (about 5 or so in SQLServer
7) which makes the software easier to manage but not very tuneable. I woule
prefer to see s'where in the middle where Oracle could reduce the number of
config
parameters so its environment is easier to manage. For example it is all
good to have pctfree and pctused parameters to play with, but I just wonder
what is the percentage of developers who have actually thought about these
in advance and
set them up "appropriately" for their applications. And also once they have
set the values for pctused and pctfree, did they actually monitor the space
usage to see if the values used are in fact appropriate? Why don't Oracle
work more
intelligent and manage space smartly without the requirement of developers
setting these parameters. Another example is the pinning of dbms_standard
package. If it is commonly used then Oracle should pin it automatically
every time the db
is started up instead of leaving this decision in the hand of DBAs.
My 2c comments.
What do you reckon?
Long
-Original Message-
Sent: Monday, 18 February 2002 11:38 PM
To: Multiple recipients of list ORACLE-L
Hello Sinardy
PCTUSED means that:
After a block was filled with records and the free space in the block
is less then pctfree the block is no longer available for further inserts.
If however, the data falls beneath PCTUSED the block became again
available for inserts.
In your example, block will never again became available, even if empty,
because: After a data block becomes full as determined by PCTFREE, Oracle
does not consider the block for the insertion of new rows until the
percentage of the block being used falls below the parameter PCTUSED.
Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]
> -Original Message-
> From: Sinard Xing [SMTP:[EMAIL PROTECTED]]
> Sent: Mon, February 18, 2002 11:08 AM
> To: Multiple recipients of list ORACLE-L
> Subject: 8i Concept PCTFREE & PCTUSED are 0
>
> Hi all,
>
> Oracle 8i (81600)
>
> I create user Panadol and give unlimited quota on TBS.
>
> as Panadol I create 1 table like this:
>
> SQL> CREATE TABLE mytable(
> 2 col1 char(3),
> 3 col2 char(4),
> 4 col4 varchar2(4)
> 5 )
> 6 PCTFREE 0
> 7 PCTUSED 0
> 8 STORAGE (
> 9INITIAL 20K
> 10NEXT 20K
> 11PCTINCREASE 0
> 12MAXEXTENTS 10
> 13 )
> 14 ;
>
> Table created.
>
> SQL> insert into mytable values('abc', 'abcd', 'a');
>
> 1 row created.
>
> SQL> commit;
>
>
> I don't understand That table PCTUSED is 0 ? where Oracle keep those
> data ?
>
> Look at this:
>
> SQL> select * from mytable;
>
> COL COL2 COL4
> ---
> abc abcd a
>
>
>
> Sinardy
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sinard Xing
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> 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).
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> This e-mail was scanned by the eSafe Mail Gateway
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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 conta
RE: 8i Concept PCTFREE & PCTUSED are 0
Hi,
This is an offside comment rather than a direct reply to Sinard's question. Oracle has
made many config parameters tuneable and this makes the software both flexible and
complex. On the other hand MS's SQLServer only has a small number of
config parameters that could be changed by DBA (about 5 or so in SQLServer 7) which
makes the software easier to manage but not very tuneable. I woule prefer to see
s'where in the middle where Oracle could reduce the number of config
parameters so its environment is easier to manage. For example it is all good to have
pctfree and pctused parameters to play with, but I just wonder what is the percentage
of developers who have actually thought about these in advance and
set them up "appropriately" for their applications. And also once they have set the
values for pctused and pctfree, did they actually monitor the space usage to see if
the values used are in fact appropriate? Why don't Oracle work more
intelligent and manage space smartly without the requirement of developers setting
these parameters. Another example is the pinning of dbms_standard package. If it is
commonly used then Oracle should pin it automatically every time the db
is started up instead of leaving this decision in the hand of DBAs.
My 2c comments.
What do you reckon?
Long
-Original Message-
Sent: Monday, 18 February 2002 11:38 PM
To: Multiple recipients of list ORACLE-L
Hello Sinardy
PCTUSED means that:
After a block was filled with records and the free space in the block
is less then pctfree the block is no longer available for further inserts.
If however, the data falls beneath PCTUSED the block became again
available for inserts.
In your example, block will never again became available, even if empty,
because: After a data block becomes full as determined by PCTFREE, Oracle
does not consider the block for the insertion of new rows until the
percentage of the block being used falls below the parameter PCTUSED.
Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]
> -Original Message-
> From: Sinard Xing [SMTP:[EMAIL PROTECTED]]
> Sent: Mon, February 18, 2002 11:08 AM
> To: Multiple recipients of list ORACLE-L
> Subject: 8i Concept PCTFREE & PCTUSED are 0
>
> Hi all,
>
> Oracle 8i (81600)
>
> I create user Panadol and give unlimited quota on TBS.
>
> as Panadol I create 1 table like this:
>
> SQL> CREATE TABLE mytable(
> 2 col1 char(3),
> 3 col2 char(4),
> 4 col4 varchar2(4)
> 5 )
> 6 PCTFREE 0
> 7 PCTUSED 0
> 8 STORAGE (
> 9INITIAL 20K
> 10NEXT 20K
> 11PCTINCREASE 0
> 12MAXEXTENTS 10
> 13 )
> 14 ;
>
> Table created.
>
> SQL> insert into mytable values('abc', 'abcd', 'a');
>
> 1 row created.
>
> SQL> commit;
>
>
> I don't understand That table PCTUSED is 0 ? where Oracle keep those
> data ?
>
> Look at this:
>
> SQL> select * from mytable;
>
> COL COL2 COL4
> ---
> abc abcd a
>
>
>
> Sinardy
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sinard Xing
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> 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).
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> This e-mail was scanned by the eSafe Mail Gateway
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (8
RE: 8i Concept PCTFREE & PCTUSED are 0
Hello Sinardy
PCTUSED means that:
After a block was filled with records and the free space in the block
is less then pctfree the block is no longer available for further inserts.
If however, the data falls beneath PCTUSED the block became again
available for inserts.
In your example, block will never again became available, even if empty,
because: After a data block becomes full as determined by PCTFREE, Oracle
does not consider the block for the insertion of new rows until the
percentage of the block being used falls below the parameter PCTUSED.
Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]
> -Original Message-
> From: Sinard Xing [SMTP:[EMAIL PROTECTED]]
> Sent: Mon, February 18, 2002 11:08 AM
> To: Multiple recipients of list ORACLE-L
> Subject: 8i Concept PCTFREE & PCTUSED are 0
>
> Hi all,
>
> Oracle 8i (81600)
>
> I create user Panadol and give unlimited quota on TBS.
>
> as Panadol I create 1 table like this:
>
> SQL> CREATE TABLE mytable(
> 2 col1 char(3),
> 3 col2 char(4),
> 4 col4 varchar2(4)
> 5 )
> 6 PCTFREE 0
> 7 PCTUSED 0
> 8 STORAGE (
> 9INITIAL 20K
> 10NEXT 20K
> 11PCTINCREASE 0
> 12MAXEXTENTS 10
> 13 )
> 14 ;
>
> Table created.
>
> SQL> insert into mytable values('abc', 'abcd', 'a');
>
> 1 row created.
>
> SQL> commit;
>
>
> I don't understand That table PCTUSED is 0 ? where Oracle keep those
> data ?
>
> Look at this:
>
> SQL> select * from mytable;
>
> COL COL2 COL4
> ---
> abc abcd a
>
>
>
> Sinardy
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sinard Xing
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> 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).
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> This e-mail was scanned by the eSafe Mail Gateway
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
8i Concept PCTFREE & PCTUSED are 0
Hi all,
Oracle 8i (81600)
I create user Panadol and give unlimited quota on TBS.
as Panadol I create 1 table like this:
SQL> CREATE TABLE mytable(
2 col1 char(3),
3 col2 char(4),
4 col4 varchar2(4)
5 )
6 PCTFREE 0
7 PCTUSED 0
8 STORAGE (
9INITIAL 20K
10NEXT 20K
11PCTINCREASE 0
12MAXEXTENTS 10
13 )
14 ;
Table created.
SQL> insert into mytable values('abc', 'abcd', 'a');
1 row created.
SQL> commit;
I don't understand That table PCTUSED is 0 ? where Oracle keep those
data ?
Look at this:
SQL> select * from mytable;
COL COL2 COL4
---
abc abcd a
Sinardy
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sinard Xing
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Setting PCTFREE & PCTUSED in a Datawarehouse
In a datawarehouse, unless your ETL processes are very
slow and your loading window is quite small, you
should always favor the reading performance over the
loading performance.
I want to have the maximum rows per block so my
pctfree is always near 0 and pctused high.
You do not delete often in a DW but you truncate
tables and partition.
--- "Koivu, Lisa" <[EMAIL PROTECTED]> a
écrit : > Hi Vikas,
>
> The docs should have discussed the reason why. High
> pctused can lead to a
> block going on and off the freelist frequently as
> deletes/inserts are
> processed, and also can leave the amount of free
> space inadequate for a new
> insert (your avg_rowlen comes into play here). So
> what you'd end up with is
> several blocks on the freelist that don't have
> enough free space for an
> insert, and every time an insert is processed it
> goes thorugh the freelist
> and checks for space in the block. The performance
> hit comes when too many
> blocks have to be checked for adequate space. Does
> that make sense?
>
> Now some people on this list have debated that
> PCTFREE/PCTUSED is so low
> level and that performance really is not affected
> that much. Of course Ross
> was one of those people ("amen, my brother")
>
> HTH
> Lisa Koivu
> Data Bored Administrator
> Ft. Lauderdale, FL, USA
>
>
> > -Original Message-
> > From: Vikas Kawatra [SMTP:[EMAIL PROTECTED]]
> > Sent: Friday, June 29, 2001 11:42 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject:Setting PCTFREE & PCTUSED in a
> Datawarehouse
> >
> > I read in the Oracle docs that setting a high
> PCTUSED ( such as 60/70)
> > would
> > increase the cost of INSERTS to the table. Can
> someone explain this ,
> > pleas
> > e!
> >
> > thanks
> >
> > vikas
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Vikas Kawatra
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051
> FAX: (858) 538-5051
> > San Diego, California-- Public Internet
> access / Mailing Lists
> >
>
> > 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).
>
=
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
[EMAIL PROTECTED]
___
Do You Yahoo!? -- Pour faire vos courses sur le Net,
Yahoo! Shopping : http://fr.shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Setting PCTFREE & PCTUSED in a Datawarehouse
Thanks Lisa !
-Original Message-
Sent: Friday, June 29, 2001 10:21 AM
To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Hi Vikas,
The docs should have discussed the reason why. High pctused can lead to a
block going on and off the freelist frequently as deletes/inserts are
processed, and also can leave the amount of free space inadequate for a new
insert (your avg_rowlen comes into play here). So what you'd end up with is
several blocks on the freelist that don't have enough free space for an
insert, and every time an insert is processed it goes thorugh the freelist
and checks for space in the block. The performance hit comes when too many
blocks have to be checked for adequate space. Does that make sense?
Now some people on this list have debated that PCTFREE/PCTUSED is so low
level and that performance really is not affected that much. Of course Ross
was one of those people ("amen, my brother")
HTH
Lisa Koivu
Data Bored Administrator
Ft. Lauderdale, FL, USA
-Original Message-
Sent: Friday, June 29, 2001 11:42 AM
To: Multiple recipients of list ORACLE-L
I read in the Oracle docs that setting a high PCTUSED ( such as
60/70) would
increase the cost of INSERTS to the table. Can someone explain this , pleas
e!
thanks
vikas
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>
--
Author: Vikas Kawatra
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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.com
--
Author: Vikas Kawatra
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Setting PCTFREE & PCTUSED in a Datawarehouse
Title: RE: Setting PCTFREE & PCTUSED in a Datawarehouse
Hi Vikas,
The docs should have discussed the reason why. High pctused can lead to a block going on and off the freelist frequently as deletes/inserts are processed, and also can leave the amount of free space inadequate for a new insert (your avg_rowlen comes into play here). So what you'd end up with is several blocks on the freelist that don't have enough free space for an insert, and every time an insert is processed it goes thorugh the freelist and checks for space in the block. The performance hit comes when too many blocks have to be checked for adequate space. Does that make sense?
Now some people on this list have debated that PCTFREE/PCTUSED is so low level and that performance really is not affected that much. Of course Ross was one of those people ("amen, my brother")
HTH
Lisa Koivu
Data Bored Administrator
Ft. Lauderdale, FL, USA
-Original Message-
From: Vikas Kawatra [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 29, 2001 11:42 AM
To: Multiple recipients of list ORACLE-L
Subject: Setting PCTFREE & PCTUSED in a Datawarehouse
I read in the Oracle docs that setting a high PCTUSED ( such as 60/70) would
increase the cost of INSERTS to the table. Can someone explain this , pleas
e!
thanks
vikas
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vikas Kawatra
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
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).
Setting PCTFREE & PCTUSED in a Datawarehouse
I read in the Oracle docs that setting a high PCTUSED ( such as 60/70) would increase the cost of INSERTS to the table. Can someone explain this , pleas e! thanks vikas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Kawatra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
MY DB_BLOCK_SIZE = 8K - Original Message - From: Mark Leith To: Multiple recipients of list ORACLE-L Sent: Friday, May 18, 2001 12:30 PM Subject: RE: PCTFREE/PCTUSED What is your DB_BLOCK_SIZE? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bunyamin K. KaradenizSent: Thursday, May 17, 2001 04:51To: Multiple recipients of list ORACLE-LSubject: PCTFREE/PCTUSED PCT_USED = PCT_FREE + 1 row sizePCT_USED + PCT_FREE < 100Are the rules . But what does 1 row size mean here? For ex . My pctfree=30 1 row size (avg.) = 150 bytes . Then what is PCT_USED ? TIA
RE: PCTFREE/PCTUSED
What is your DB_BLOCK_SIZE? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bunyamin K. KaradenizSent: Thursday, May 17, 2001 04:51To: Multiple recipients of list ORACLE-LSubject: PCTFREE/PCTUSED PCT_USED = PCT_FREE + 1 row sizePCT_USED + PCT_FREE < 100Are the rules . But what does 1 row size mean here? For ex . My pctfree=30 1 row size (avg.) = 150 bytes . Then what is PCT_USED ? TIA
PCTFREE/PCTUSED
PCT_USED = PCT_FREE + 1 row sizePCT_USED + PCT_FREE < 100 Are the rules . But what does 1 row size mean here? For ex . My pctfree=30 1 row size (avg.) = 150 bytes . Then what is PCT_USED ? TIA
