Oracle-L: RE: Parallel degree on tables and indexes

From: Koivu, Lisa <Lisa.Koivu_at_efairfield.com>
Date: Mon, 16 Dec 2002 08:53:58 -0800
Message-ID: <F001.0051A53D.20021216085358@fatcity.com>

Govind,

a statement like 'use parallel = 4 for tables over 100,000 rows' is like saying drive to Iowa, go that way.

The parallel degree should at least take into account the resources on your box. It can be larger than the number of CPU's on your box as long as it doesn't start thrashing and swapping.

Partitioned objects are usually good candidates for a parallel degree > 1.

Also a bit of knowledge as to when the tables/indexes are used and for what purpose helps. The answer to this question may be different for different times of the day - for example, daily user interaction and night batch execution.

Sorry, seems to me there's no quick and easy answer. Tuning is an ongoing process that isn't completed overnight.

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063

-----Original Message-----
Sent: Monday, December 16, 2002 10:39 AM To: Multiple recipients of list ORACLE-L

List,

Is there a 'magic' number for num_rows that you use in order to set the parallel degree on tables and/or indexes?

We were told to use parallel degree of 4 for tables having more than 100,000 rows. Some of the on-line queries are running in parallel mode thus making batch jobs to go serial for lack of parallel slaves.

Upon a closer look, we see tables/indexes having millions of rows having parallel degree set at 1 and tables/indexes having fewer than 100,000 rows are having parallel degree set at 4 etc.,

Is there a number for num_rows that you have been very successful to determine the parallel degree? Some of the indexes were re-built using parallel degree of 3 to reduce the 'create index' time but were not changed back to 'noparallel' recently and I need to answer these questions.

TIA. Govind
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com <http://www.orafaq.com>
--

Author: <Govind.Arumugam_at_alltel.com
INET: Govind.Arumugam_at_alltel.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com <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.com
--

Author: Koivu, Lisa
INET: Lisa.Koivu_at_efairfield.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------