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.
>>> [EMAIL PROTECTED] 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: [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).
