Concerned regarding parallel query and 10g (10.1.0.4)

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 Received on Wed Jan 25 2006 - 14:06:23 CST