Re: Implement Parallel Processing on DB Warehouse

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Tue, 10 Feb 2004 16:50:40 -0500
Message-ID: <20040210215040.GA3759@mladen.wangtrading.com>

On 02/10/2004 04:17:39 PM, "Freeman, Donald" wrote:
> SET timing ON
>
> ALTER SYSTEM FLUSH SHARED_POOL;
Flushing is harmful.

>
> ALTER TABLE PADOHSNAP.HRA_OBS_MAP_DETAIL PARALLEL 1;
> SELECT COUNT(*) FROM PADOHSNAP.HRA_OBS_MAP_DETAIL;
>
> ALTER SYSTEM FLUSH SHARED_POOL;
Ditto.

>
> ALTER TABLE PADOHSNAP.HRA_OBS_MAP_DETAIL PARALLEL 4;
>
> SELECT /*+PARALLEL,4,1) */
Hint is ignored, because of the incorrect syntax. This is how the thing should be done"

select /*+ parallel(tab,4) */ count(*)
from PADOHSNAP.HRA_OBS_MAP_DETAIL tab;

> COUNT(*) FROM PADOHSNAP.HRA_OBS_MAP_DETAIL;
>
> ********************************************************************
>
> This is what I'm getting for both executions:
>
> Elapsed: 00:00:00.04
>
> Execution Plan
> ----------------------------------------------------------
> 0
> 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)

It's not going parallel because your optimizer decided that PK is cheaper. You should also issue command:

alter index PADOHSNAP.PK_HRA_OBS_MAP_DETAIL parallel 4;

if it isn't already set to parallel 4. Check DBA_INDEXES.

> 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,

With how many zeroes do you spell "grateful"?

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:50:40 CST