Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h9LIShW00560 for ; Tue, 21 Oct 2003 13:28:43 -0500 X-ClientAddr: 66.27.56.212 Received: from www3.fatcity.com (rrcs-west-66-27-56-212.biz.rr.com [66.27.56.212]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h9LISVc00530 for ; Tue, 21 Oct 2003 13:28:34 -0500 Received: (from root@localhost) by www3.fatcity.com (8.11.6/8.11.6) id h9LFeV908566 for oracle-l@orafaq.net; Tue, 21 Oct 2003 08:40:31 -0700 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005D3DFC; Tue, 21 Oct 2003 08:39:25 -0800 Message-ID: Date: Tue, 21 Oct 2003 08:39:25 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Goulet, Dick" Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Goulet, Dick" Subject: RE: using temp tables for staging databases? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit I believe Oracle will round that block size off. What I would not be sure of is what Oracle did during database creation. I believe it should have gone with an 8K (8192 bytes) block size since the specified size of 4608 Bytes is above a 4K (4096 Bytes) block size. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -----Original Message----- Sent: Tuesday, October 21, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Too many drugs? Not enough?? Here's an exact quote from the vendor -- they placed this line in our init file. Sadly, they did not plan for any overhead . . . (the app was installed before we had an oracle db on board) ############################################################################### # The db_block_size is set at 9 multiples of 512 bytes(OpenVMS block size) # This is to accomodate the WO table. The average row length of the WO table is # 900 bytes. A 4608 parameter allows 5 rows to be stored in a single Oracle bloc k # Do not change without consulting NWI!!!!!!!!! --- Arup Nanda wrote: > 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" > > 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 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, rgaffuri@cox.net at > > > rgaffuri@cox.net 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 > > > >> Date: 2003/10/20 Mon AM 10:19:33 EDT > > > >> To: Multiple recipients of list ORACLE-L > > > > > > >> 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, rgaffuri@cox.net at > > > rgaffuri@cox.net wrote: > > > >> > > > >>> This is for non-transactional data load > > > instances. The guys here sware > > > that > > > >>> by > > > >>> using smaller temporary tables(not global > temp > === 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: barbarabbaker@yahoo.com 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: ListGuru@fatcity.com (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: Goulet, Dick INET: DGoulet@vicr.com 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: ListGuru@fatcity.com (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).