Parallel Query and CPUs
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Parallel Query and CPUs

  1. #1
    Join Date
    Mar 2003
    Location
    West Sussex UK
    Posts
    2

    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?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    . . . 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

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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!

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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
  •  


Click Here to Expand Forum to Full Width