Parallel Query

I have SQL optimization and tuning questions.

select a.period_id,sum(nvl(a.oem_revenue,0)) from ods_facts a,
ods_iterations b
where a.iteration_id = b.iteration_id
and b.iteration_name = 'Iteration_1'
and b.version = 'Quarterly Forecast and Actuals - Feb 2000' group by period_id

This query goes against our data warehousing database and takes approx 4 minutes
to execute. ods_facts is the fact table with number of dimensions. ods_iterations is
one such dimension table with less than 200 records. Iteration ID is the

primary key in ods_iterations table. Fact table is having more than 11 million
records and is range partitioned by iteration id. The last two where conditions in where clause uniquely identifies one iteration and thus forces access to one and only one partition in ods_facts table. This partition contains approx 300,000 rows. What are the methods of speeding up this query ? (I already have local bitmap index
on iteration_id column in ods_facts table and increased degree of parallelism of
ods_facts to 6. We have both Oracle 8.0.5 and Oracle 8.1.6 installations.)
While doing another experiment and I copied these 300,000 rows into another
table using "Create As Select" statement, created related indexes and accidentally executed similar query. In this situation it takes less than 1 minute. Since ods_facts is partitioned and my query was accessing only one partition so I was assuming the run time
of second query to be same as first one. How can I explain the difference ?

How do I confirm that the database is really using parallel query processing ? I will appreciate any input. Please copy my direct email address also (abhijit_bhattach_at_hotmail.com)

Here is the explain plan for the above query

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=12 Bytes=768 )

   1 0 SORT* (GROUP BY) (Cost=56 Card=12 Bytes=768)
:Q65002

 2 1 SORT* (GROUP BY) (Cost=56 Card=12 Bytes=768)
:Q65001

 3 2 NESTED LOOPS* (Cost=8 Card=49689 Bytes=3180096)
:Q65001

   4 3 TABLE ACCESS* (FULL) OF 'ODS_ITERATIONS' (Cost=1 Card=1 Bytes=61) :Q65000

   5 3 PARTITION RANGE* (ITERATOR) !
:Q65001

   6 5 TABLE ACCESS* (FULL) OF 'ODS_FACTS' (Cost=71 Card=12521626 Bytes=37564878) :Q65001

   1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,SUM(SYS_OP_CSR(A1

                                   .C1,0)) FROM :Q65001 A1 GROUP BY A1.

   2 PARALLEL_TO_PARALLEL          SELECT /*+ PIV_GB */ A1.C1
C0,SYS_OP_MSR(SUM
                                   (NVL(A1.C2,0))) C1 FROM (SELECT /*+

  ! ; 3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_TO_PARALLEL          SELECT /*+ NO_EXPAND ROWID(A1) */
A1."ITERAT
                                   ION_ID" C0 FROM "ODS_ITERATIONS" A1

5 PARALLEL_COMBINED_WITH_PARENT
   6 PARALLEL_COMBINED_WITH_PARENT Received on Sun Jun 10 2001 - 17:04:38 CDT