-
Parallel Query and CPUs
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?
-
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
. . . 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.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Re: Parallel Query and CPUs
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...
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Re: Re: Parallel Query and CPUs
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).
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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!
-
Re: Re: Re: Parallel Query and CPUs
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Re: Re: Re: Re: Parallel Query and CPUs
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:
Code:
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:
Code:
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!
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Re: Re: Re: Re: Re: Parallel Query and CPUs
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?
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Re: Re: Re: Re: Re: Re: Parallel Query and CPUs
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|