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 <orarup_at_hotmail.com> 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"
> <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
>
=== 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).
Received on Tue Oct 21 2003 - 10:54:44 CDT
