Parallel Query and CPUs

[DianeWh]

I know this is a really stupid question but - is it possible to run a query using parallel slaves when you only have one cpu available?

[jmodic]

Yes.

[DaPi]

. . . and, if you have RAID (I'm sure you do), it does make sense. BUT beware of diminishing returns and the risk of the parallel query eating up all your machine.

[julian]

Originally posted by DianeWh
I know this is a really stupid question but - is it possible to run a query using parallel slaves when you only have one cpu available?

But I don't see much point of running parallel queries on 1-CPU machines...

[jmodic]

Originally posted by julian
But I don't see much point of running parallel queries on 1-CPU machines...

Hm, performing a massive full table scan in parallel on a single CPU machine could be much more efficient than a nonparallel FTS. Reading data from disks is not CPU-bound operation, multiple slave processes can read simultaneously from different disks without much need of that single CPU.

Or creating an index on a large table. Multiple slaves can red table simultaneously (I/O-bound), while one slave may perform in-memory sorting (CPU-bound).

[pando]

well considering a CPU does not run a process only or sequentially I dont see what can go wrong using PQ in single CPU servers

if one CPU runs processes sequentially that's end of I.T!

[julian]

Originally posted by jmodic
Hm, performing a massive full table scan in parallel on a single CPU machine could be much more efficient than a nonparallel FTS. Reading data from disks is not CPU-bound operation, multiple slave processes can read simultaneously from different disks without much need of that single CPU.

Or creating an index on a large table. Multiple slaves can red table simultaneously (I/O-bound), while one slave may perform in-memory sorting (CPU-bound).

In theory yes. But I have tested that on a (partitioned) relatively large table in 8i. Not much difference. I think you may have improvements but it depends on distribution of data on disks.

Try for yourself and let us know if you achive better results. I used parallel_automatic_tuning = TRUE

BTW: starting 9iR2 you can run parallel DML on non-partitioned tables.

[jmodic]

Originally posted by julian
Try for yourself and let us know if you achive better results. I used parallel_automatic_tuning = TRUE

Ok, I performed a "quick and dirty" test on my home PC - one CPU and two (slow) disks (standalone, no RAID), both on the same controler. Created a table TEST_RANGE, consisting of tow partitions, each residing on different disk, and loaded it with some data, so that the size of the whole table was arround 800 MB. PARALLEL_AUTOMATIC_TUNING was set to TRUE. Then run COUNT(*) on that table several times, first without parallelism and then with parallelism set to 2.

Here are the results for non-parallel executions:

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Prod
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set timing on
SQL> select count(*) from test_range;

  COUNT(*)
----------
   7175424

Elapsed: 00:01:10.08
SQL> /

  COUNT(*)
----------
   7175424

Elapsed: 00:01:09.08
SQL> /

  COUNT(*)
----------
   7175424

Elapsed: 00:01:11.05

And here are the results for the same query that was executed in parallel:

SQL> select /*+parallel (test_range,2)*/ count(*)
  2  from test_range;

  COUNT(*)
----------
   7175424

Elapsed: 00:00:51.01
SQL> /

  COUNT(*)
----------
   7175424

Elapsed: 00:00:51.04
SQL> /

  COUNT(*)
----------
   7175424

Elapsed: 00:00:51.00
SQL>

As can be seen, the serial version nedeed around 71 seconds to complete, while parallel one nedeed only 51 secs. So the serial FTS in this example took almost 40% more time compared to a parallel one!

[julian]

Originally posted by jmodic

As can be seen, the serial version nedeed around 71 seconds to complete, while parallel one nedeed only 51 secs. So the serial FTS in this example took almost 40% more time compared to a parallel one!

Very good. 40% is good, even very good. I got even less than 10% when I performed my tests. When I did it there were about 10 other users in the system. You were the only one I guess. Do you think that might influence on the results somehow?

[jmodic]

Originally posted by julian
When I did it there were about 10 other users in the system. You were the only one I guess. Do you think that might influence on the results somehow?

I don't think so. Of course, I'm just speculating, I could be wrong, but I realy don't see any reason why performing FTS in parallel on a single CPU machine could not be substantialy faster even on a bussy system.