This is definitely one for the Hall of [F|Sh]ame! 4608 byte block size! But how did someone arrive at that number - Typo? Wheel of Fortune? DBMS_RANDOM?
Arup ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 11:19 AM > Hi, Mark. > I'm not Tim, but I did encounter such a situation. > This was not a temp table, but a permanent one. > > We have a db with a very strange block size of 4608 > (actually Tim is painfully aware of this one). We have > a very large table in this database. It was expanding > at about 200 megs per week -- way out of control for a > relataively small database. > > The database was not reusing blocks. Oracle > recommends that (100% - (pctfree+pcused)) be greater > than the maximum sie of a row. So we did an exact > calculation of the blocksize less %free+%used > > 1% of a block is 46.08 > 80% of a block is 3686.4 > 4608 - (46.08 + 3686.4) = 875.52 > > > our largest row length is 860 > > So we set pctfree at 1% and pctused at 80% > One of the reasons we can get by with this is because > the vendor designed the database with all char (not > varchar2), so we pretty much know exactly what each > row is going to consume. (It's a Cobol app) > > After this change, the database stopped it's wild > expansion. > > Not a normal situation, but then nothing here is > normal. (Kids -- don't try this at home!) > > Barb > > > > --- Mark Leith <[EMAIL PROTECTED]> wrote: > > 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 > === message truncated === > > > __________________________________ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Barbara Baker > 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).
