I have a strange behavior in our production machine and let's take a look at
this example (done in TEST and PRODUCTION machine).

See the value 'redo size' (in trace stat) and exec time.



*****************************************
TEST machine (Unix Aix 4.3.3 with 4 CPUs)
*****************************************
SQL> select count(*) from ztest.ag_magasins;

COUNT(*)
----------
42482128

Ecoulé : 00 :04 :40.08

Plan d'exécution
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29865 Card=1)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q912100
0

3 2 TABLE ACCESS* (FULL) OF 'AG_MAGASINS' (Cost=29865 Card :Q912100
=42433432) 0



2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ NO_EXPAND ROWID(A2)

3 PARALLEL_COMBINED_WITH_PARENT


Statistiques
----------------------------------------------------------
261 recursive calls
171 db block gets
392504 consistent gets
196771 physical reads
692 redo size
216 bytes sent via SQL*Net to client
134 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed


*****************************************
Production machine (Unix Aix with 2 CPUs)
*****************************************

SQL> select count(*) from ztest.ag_magasins;

COUNT(*)
----------
42482128

Ecoulé : 00 :07 :59.00

Plan d'exécution
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29865 Card=1)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q188050
00

3 2 TABLE ACCESS* (FULL) OF 'AG_MAGASINS' (Cost=29865 Card :Q188050
=42433432) 00



2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ NO_EXPAND ROWID(A2)

3 PARALLEL_COMBINED_WITH_PARENT


Statistiques
----------------------------------------------------------
239 recursive calls
16 db block gets
392501 consistent gets
196769 physical reads
11742392 redo size
216 bytes sent via SQL*Net to client
135 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processed



Any idea/explanation ?