Re: using temp tables for staging databases?

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" <ORACLE-L_at_fatcity.com> 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 <mark_at_cool-tools.co.uk> 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_at_cox.net at
> > rgaffuri_at_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 <tim_at_sagelogix.com>
> > >> Date: 2003/10/20 Mon AM 10:19:33 EDT
> > >> To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > >> 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_at_cox.net at
> > rgaffuri_at_cox.net 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: tim_at_sagelogix.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_at_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: Tim Gorman
> > INET: tim_at_sagelogix.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_at_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).
> > ---
> > 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: mark_at_cool-tools.co.uk
> >
> > 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: barbarabbaker_at_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_at_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: Arup Nanda
  INET: orarup_at_hotmail.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_at_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).