Re: parallelism

The table size (bytes HWM) is the real factor here, not number of rows, and there is a different threshold for each machine configuration / Oracle version / Application combination.

The big factor is how much hardware you have to burn - CPUs, spindles, memory. The more hardware you have, the more ambitious you can be with parallelism, because any query with multiple table-joins can fire up parallel execution slaves exponentially - the degree applies to each of the tables and the intermediate join-operations, and you could swamp the machine.

The type of SQLs you have is a main factor. If it is selective and the CBO decides to use indexes, setting parallelism would have no benefits. On the other hand, if you see a lot of FTS/hash_joins, then parallelism will help.

The same SQL can and will behave differently when you run on a different version of Oracle, so you will have to start all over again.

It is easier to tune if you are focused on DW (tweak parallel parameters: automatic_tuning, adaptive_multi_user, max, min, and most importantly threads_per_cpu which controls the upper threshold of parallel slaves w.r.t to machine load). If you have mixed DW/OLTP, you need to tweak optimiser_percent_parallel. If OLTP mostly, you are unlikely to get any benefits.

>>> JTESTA_at_longaberger.com 09/05/01 01:32AM >>>
We were discussing when to set degree parallelism on a table. You experts out there, is there a number of rows that it makes sense to set it to something other than default? I know there are times when parallel can be slower than serial processing but what is that threshold. We are definitely setting it for the partitioned tables but wasnt sure if we should set it for other ones also.

thanks, joe

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Binley Lim
  INET: Binley.Lim_at_ird.govt.nz

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: 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).