Concerned regarding parallel query and 10g (10.1.0.4)

[peter]

Dear all,
I'm currently supporting a mixed OLTP/DSS'ish type environment.
The environment
is 10.1.0.4 on Solaris 8 64 bit (10 cpus). The environment is
about 75% reads.
We have small oltp like transactions, but the majority of the
workload is due to
large queries that pull 10K to 5MM records at a time.

The CPU queues are typically around 7-8, so I know we have more
CPU bandwidth
available, and we are only using 3GB SGA and the machine which
hosts only the oracle
database has up to 12GB.

These large queries are typically a 2 table join per customer and
depending on what is
being selected, it could result in many rows. On average we are
joining a 5 million
record table to a 10 million record table (typically a 2/1 ratio).

I'm considering using parallel query to speed up these types of
queries, but I would like
some feedback on what things to watch out for with parallel query.
What additional
resources does it require (more memory? larger shared pool? larger
buffer cache?
larger pga aggregate target ???) contention problems ?? ).

If anyone has experience with Parallel query and the things to
watch out for I'd appreciate it.

These are my current settings, but I'm looking to increase the # of
parallel servers.

parallel_adaptive_multi_user = TRUE
parallel_automatic_tunning = FALSE
parallel_execution_message_size = 2152
parallel_max_servers = 20

The type of query that I'm referring to is something like this
SELECT ****
FROM USER_DATA U, PRODUCT_DATA P
WHERE U.USER_ID = P.USER_ID AND
P.PRODUCT_ID = X

A typical execution plan is something like this:
HASH_JOIN
INDEX FAST FULL SCAN PRODUCT_DATA
FULL TABLE SCAN USER_DATA

Currently the IFFS and FULL table scans are done with 0 parallelism,
and I just think
that using even a small degree of parallelism on the objects would make
things go faster.

I'm also considering partitioning and changing the PRODUCT_DATA to an
IOT so that
the data is ordered by PRODUCT_ID.

I'm aware that 10g will downgrade a potential parallel operation to
serial if it doesn't have enough parallel servers, so I would like to
start putting the Degree Of Parallelism
on tables/indexes and increasing parallel max servers... but I want to
know
how this will affect the shared_pool, the buffer_cache and overall
memory/contention
on the system...

Any insight would be helpful...
-peter

[Witek Swierzy]

Hello !
Three first ideas about Your problem :
1. set parallel_automatic_tuning to true
- it automatically sets most of initialization parameters regarding
pq to appropriate values
2. there is another important parameter - LARGE_POOL_SIZE - parallel
query servers use large pool for its buffers - so check utilization of
large pool in v$sgastat and if needed increase its size
3. And another parameter :-) - parallel_threads_per_cpu - its value sets
desirable parallelism degree per one CPU
regards
Witek Swierzy
wsw...@sgh.waw.pl

[peter]

Thanks for the feedback Witek. I'm currently reviewing the Data
Warehousing guide (Chpt 24).
What I'm really concerned is how adding n number of parallel servers to
faciliate some select queries is going to affect the shared_pool, the
buffer_cache and PGA configuration.... because these SELECT queries
would be initiated by database connections in shared/MTS mode... Many
times these memory
allocations are difficult to anticipate when one has both dedicated and
shared connections..
Just take a look at Jonathan Lewis's remarks at on his website (really
good stuff)
http://www.jlcomp.demon.co.uk/snark.html.

thanks for the heads up ...
-peter