Do not increase the sort_area_size at the database
level but at the session level. When I have big batch
jobs running alone at night I increased the
hash_area_size and the sort_area_size of the session
running the job.
--- CC Harvest <[EMAIL PROTECTED]> a écrit : >
Kirti:
> The server is solely used for the Oracle Database
> and it has 2GB Ram. I didn't increase sort_area_size
> too much because I thought the table is so big, and
> probablay I could not run them in the memory. That's
> why I just try the temp tablespace.
>
> Thanks,
>
> Chris
>
> --- "Deshpande, Kirti" <[EMAIL PROTECTED]>
> wrote:
> > I would suggest that you increase (as much as
> > possible) sort_area_size and
> > sort_area_retained_size for your session when
> > building indexes to minimize
> > temporary tablespace use. Making temporary
> > tablepspace of type temporary and
> > adjusting default initial & next extent size can
> > also help.
> > HTH..
> > - Kirti Deshpande
> > Verizon Information Services
> > http://www.superpages.com
> >
> > > -----Original Message-----
> > > From: CC Harvest [SMTP:[EMAIL PROTECTED]]
> > > Sent: Thursday, April 12, 2001 11:23 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Re:Temporary Tablespace Design
> > >
> > > Thanks Dick and Lisa for answering my question.
> I
> > > think I am going to either let the file
> > auto-extend,
> > > or will try a smaller file as a start. I found
> my
> > temp
> > > tablespace is too small(1GB) because it seems
> like
> > > takes forever to rebuild an index with
> nologging.
> > I
> > > have 11 indexes on this tables, and it took me
> > tons
> > > hours to do the index rebuilding. My application
> > is
> > > a mixed system with 10% batch processing, and
> 90%
> > > OLAP.
> > > But we need the 10% batch processing part should
> > be
> > > really fast.
> > >
> > > Thanks,
> > >
> > > Chris
> > >
> > > --- [EMAIL PROTECTED] wrote:
> > > > Chris,
> > > >
> > > > First let me say that I have a TON of
> > respect
> > > > for Mike and count him as a
> > > > friend. That said, I also take exception to
> > many of
> > > > his pronouncements from a
> > > > practical, not theoretical, point of view.
> > Given
> > > > infinite resources, like disk
> > > > space and memory and CPU, he does have it
> > absolutely
> > > > right. But in the real
> > > > world there is infinite nothing.
> > > >
> > > > The first item on my list here it to look
> at
> > > > what temp space is used for.
> > > > It's mainly used for sorting, grouping, and
> > distinct
> > > > operations. These are the
> > > > normal things that involve temp segments, and
> in
> > a
> > > > day to day operation that
> > > > will consume an amount of space. The other
> item
> > > > their used for is index
> > > > building, which is not a normal day to day
> > > > operation. Therefore the need for an
> > > > extremely large temp tablespace is a sporadic
> > and
> > > > plan able event. Second,
> > > > comes the question of the purpose of the
> > database.
> > > > If your building an OLTP
> > > > system then temp usage is going to be even
> less
> > > > since the majority of actions
> > > > will affect few rows at one time. If it's a
> > data
> > > > warehouse on the other hand
> > > > then data mining operations tend to make
> extreme
> > use
> > > > of temp for group and sort
> > > > operations, but even so the amount of data
> being
> > > > processed will not hit the
> > > > extremes and when it does it's most likely
> bogus
> > in
> > > > the first place. My
> > > > favorite in this vein is our CIO who let loose
> a
> > > > Cartesian product query just
> > > > because he forgot to join the fact table to
> the
> > > > other tables. In this case the
> > > > lack of temp space brought the query to a halt
> > > > quickly and mercifully.
> > > >
> > > > OK, so where should you go? Well, I'll
> get
> > into
> > > > our DB's which range from
> > > > our 150GB data warehouse to our 200GB
> > operational
> > > > data store. The former has
> > > > 1GB of temp storage for normal operations.
> The
> > > > latter gets along very well on
> > > > 400MB of temp space. Both have a 14GB disk
> area
> > > > that they share as required for
> > > > those monster index rebuilds.
> > > >
> > > > Where you go from here is a lot of
> personal
> > > > decision. I recommend starting
> > > > small & working your way up as necessary. The
> > > > easiest way to do that is to
> > > > enable auto-extend.
> > > >
> > > > Dick Goulet
> > > >
> > > > ____________________Reply
> > > > Separator____________________
> > > > Author: CC Harvest <[EMAIL PROTECTED]>
> > > > Date: 4/12/2001 12:05 AM
> > > >
> > > > What's your experience about the temporary
> table
> > > > design? I read Michael Ault's Orcale8
> > Administartion
> > > > and Management , it says "For Cost-based
> > > > optimization,
> > > > it should be 4 times of the largest table". I
> > have a
> > > > table of 60 Million records, and it costs
> 16GB,
> > > > should
> > > > I have a 64GB temp tablespace(I don't think
> so,
> > > > though
> > > > it's a 100GB database, and I have a 300GB of
> > > > diskspace).
> > > >
> > > > Thanks for your advice.
> > > >
> > > > Chris
> > > >
> > > >
> > __________________________________________________
> > > > Do You Yahoo!?
> > > > Get email at your own domain with Yahoo! Mail.
>
> > > > http://personal.mail.yahoo.com/
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.com
> > > > --
> > > > Author: CC Harvest
> > > > INET: [EMAIL PROTECTED]
> > > >
> > > > Fat City Network Services -- (858) 538-5051
>
> > FAX:
> > > > (858) 538-5051
> > > > San Diego, California -- Public
> Internet
> > > > access / Mailing Lists
> > > >
> > >
> >
>
--------------------------------------------------------------------
> > > > To REMOVE yourself from this mailing list,
> send
> > an
> > > > E-Mail message
>
=== message truncated ===
=====
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
[EMAIL PROTECTED]
___________________________________________________________
Do You Yahoo!? -- Pour dialoguer en direct avec vos amis,
Yahoo! Messenger : http://fr.messenger.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
