Implement Parallel Processing on DB Warehouse

From: Freeman, Donald <dofreeman_at_state.pa.us>
Date: Tue, 10 Feb 2004 16:17:39 -0500
Message-ID: <AFF54B073FF15849B53E32E67EE860763A7C8C@ENHBGPRI11.PA.LCL>

I am running Oracle 9i Rel 2 V. 9.2.0.4 on Windows 2000 Advanced Server = SP4.

Our database was originally created a couple of years ago on 9i Standard = by someone else. Now I'm here and we've upgraded to Enterprise Edition = and I'm trying to implement all the neat stuff available in EE. I've got = just over a year of dba experience. The db is relatively small, about = 15G, in size. We use Cognos BI for analysis and reports and Informatica = Powerplay for ETL. We have just authorized access to the remainder of = our user base, about 420 additional users, after supporting about 80 for = the first year. The new people haven't really started hitting the = database yet but will be using it for public health investigations.

I'm not presently experiencing any user complaints but want to implement = the more obvious data warehouse features to forestall a sudden drop in =

performance. Right now only four Cognos users can hit the db at one time =
for drill-through reports. The number is set by the Cognos administrator =
on the app server. They are going to cluster this server and enable more =

users.

The ETL process only takes about 3.5 hours of a 15 hour window so no = real stress there. However, that 3.5 hours is from 5pm until 8:30 and = often somebody has to stay and watch it. I want to shorten it as much as = possible.=20

We have created materialized views, enabled query rewrite, and created = indexes. All of this has resulted in some performance gains.

I want to enable parallel processing and have set the following = parameters.

parallel_adaptive_multi_user                                     boolean =
                         TRUE
parallel_automatic_tuning                                        boolean =
                         TRUE
parallel_execution_message_size                                  integer =
                         4096
parallel_instance_group                                          string
parallel_max_servers                                             integer =
                         40
parallel_min_percent                                             integer =
                         0
parallel_min_servers                                             integer =
                         0
parallel_server                                                  boolean =
                         FALSE
parallel_server_instances                                        integer =
                         1
parallel_threads_per_cpu                                         integer =
                         2
recovery_parallelism                                             integer =
                         0

The first thing I am working on is query parallelism. I set all the = tables to parallel 4 and we ran 6 production queries in parallel and = serially to compare times. We increased the processing time by about 15 = minutes running in parallel. I have now gone back and only set the = tables with > 100,000 rows to parallel 4 and all the others back to 1. I = am trying to test this and can't get it to run in parallel. I am using = the book Oracle Parallel Processing by R. Mahapatra and S.Mishra for = guidance. I know it's a bit old but I'm also using the Oracle docs.

This is what I am running for a little test.

SET timing ON

ALTER SYSTEM FLUSH SHARED_POOL; ALTER TABLE PADOHSNAP.HRA_OBS_MAP_DETAIL PARALLEL 1; SELECT COUNT(*) FROM PADOHSNAP.HRA_OBS_MAP_DETAIL; ALTER SYSTEM FLUSH SHARED_POOL; ALTER TABLE PADOHSNAP.HRA_OBS_MAP_DETAIL PARALLEL 4; SELECT /*+PARALLEL,4,1) */
COUNT(*) FROM PADOHSNAP.HRA_OBS_MAP_DETAIL;

This is what I'm getting for both executions:

Elapsed: 00:00:00.04

Execution Plan

SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D248 Card=3D1)

                   1                    0

SORT (AGGREGATE)

                   2                    1

INDEX (FAST FULL SCAN) OF 'PK_HRA_OBS_MAP_DETAIL' (UNIQUE) = (Cost=3D248 Card=3D4147413)

Statistics

       1881  recursive calls
          0  db block gets
       4707  consistent gets
          0  physical reads
          0  redo size
        387  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
          1  rows processed

I am just trying to get this little test running before I try the = informatica run again. Any advice about this is welcome Any general = advice
about what else would be helpful in my situation I would be grateful to = receive also.

Thanks,

Don Freeman
Database Administrator
Oracle 9i Database Administrator Certified Professional Bureau of Information Technology
PA Department of Health
717-783-4743 Ext 337

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Tue Feb 10 2004 - 15:17:39 CST