Parallel Query and CPUs

[adewri]

I'm not sure if this was supposed to be the output. I agree i have never worked on parellel execution before but this was not what we expected.

The table was analyzed, no indexes, parallel_automatic_tuning is set to true. But still...

SQL> conn scott/tiger
Connected.
SQL> select count(*) from my_test;

  COUNT(*)
----------
    361374

Elapsed: 00:00:04.08
SQL> select /*+parallel (my_test,2)*/ count(*) from my_test;

  COUNT(*)
----------
    361374

Elapsed: 00:00:05.07
SQL> set autot trace exp
SQL> select count(*) from my_test;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=431 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MY_TEST' (Cost=431 Card=361374)



SQL> select /*+parallel (my_test,2)*/ count(*) from my_test;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=216 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                        :Q5000
   3    2       TABLE ACCESS* (FULL) OF 'MY_TEST' (Cost=216 Card=36137 :Q5000
          4)



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

   3 PARALLEL_COMBINED_WITH_PARENT

SQL>

I'm in doubt ?? Why this one behave differently than the one you showed Jurij. I'll try with larger amount of data...

[jmodic]

What is your disk layout? How is the MY_TEST distributed on disks? Different extents on different disks? Is it partitioned table? Partitions on different disks?

If the whole table is located on one disk then FTS in parallel will definitely be slower than the serial one, even if you have many CPUs.

[adewri]

Originally posted by jmodic
If the whole table is located on one disk then FTS in parallel will definitely be slower than the serial one, even if you have many CPUs.

That solves my querry , yes the datafile is on one disk, and its a non partitioned table.

Thanks