Re: using temp tables for staging databases?

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" <ORACLE-L_at_fatcity.com> 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, 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 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).