Mark, While waiting for Tim, I can offer another situation - in datawarehouses, where the subsequent updates are not likely to occur. Also, space is a premium and packing the blocks as densly as populated might be necessary.
I will also add to Tim's response of justifying a smaller PCTUSED. In addition to the freelist problem he mentioned, there is also a greater chance of buffer busy waits occuring when a block contains too many rows. In an OLTP database that is certainly likely to happen - another case for the default 40 setting for the parameter. In DW, however, the chances of BBW are low, hence a higher setting may be possible. HTH. Arup Nanda ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 5:19 AM > Tim, > > Can you sum up a few situations when the need *has* arisen to change these > values? > > Cheers > > Mark > > > > -----Original Message----- > Tim Gorman > Sent: 21 October 2003 06:09 > To: Multiple recipients of list ORACLE-L > > > Unless you typo'd, there are some serious problems here... > > Setting PCTFREE to 99 is not likely to "pack in the blocks". Rather the > opposite; you are instead leaving blocks 99% empty. Quite a bit of wasted > I/O in performing a FULL table scan here... :-) > > Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value > greater than 70 or 80 or so, just as a rule of thumb. Having them sum to a > value near 100 ensures that each insert, delete, or even update will > potentially cause the block to be removed or reinserted to one of the > segment's free list. Think about it: the width of a single row crossing > the boundary from "off the free list" to "on the free list". Better to > leave a bit of a "no man's land" between the two values. The default > settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings > that need little manipulation for most situations. > > > > on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: > > > we drop and recreate the temp tables every night. We also use PCTFREE > PCTUSED > > at 99 and 1 to pack in the blocks and we use very small extent sizes. then > we > > analyze with an estimate size of 20 percent which is quite fast. > > > > All of them are used for full table scans and do not have indexes. Ive > found > > that a 'create table as' is MUCH faster than inserting into global > temporary > > tables when you do not have to worry about latch contention(ie 1-3 users > > logged in at a time). > > > > anyone else notice this? Seems to go against conventional wisdom which > says > > never use them. So I want to make sure Im not missing something. > >> > >> From: Tim Gorman <[EMAIL PROTECTED]> > >> Date: 2003/10/20 Mon AM 10:19:33 EDT > >> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >> Subject: Re: using temp tables for staging databases? > >> > >> All the time. Oracle Apps's "open interfaces" are built this way, for > >> example. > >> > >> However, "the guys here" covered their bases by specifying "smaller > >> temporary tables", as if they could prevent them from becoming large. I > >> suppose they might feel that they indemnify themselves if the tables > should > >> ever become "large"? > >> > >> As with OraApps "open interface" tables, it is when a large volume of > data > >> is pushed through that the trouble starts. The "high-water marks" on all > >> the tables are pushed to a high level, thereafter causing full table > scans > >> on the interface/temporary tables to run slowly. The only way to bring > the > >> HWM back down is quiesce the interface/app and then truncate the tables. > >> > >> > >> > >> on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: > >> > >>> This is for non-transactional data load instances. The guys here sware > that > >>> by > >>> using smaller temporary tables(not global temp tables) they can increase > the > >>> speed of the data loads. > >>> > >>> Not worried about latch contention because its just for bulk loads. I > know > >>> this bad in transactional instances. Has anyone used these in > >>> non-transactional data load instances? > >> > >> -- > >> Please see the official ORACLE-L FAQ: http://www.orafaq.net > >> -- > >> Author: Tim Gorman > >> 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: Tim Gorman > 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). > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.524 / Virus Database: 321 - Release Date: 06/10/2003 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mark Leith > 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: Arup Nanda 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).
