DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: parallel query

  1. #1
    Join Date
    Jun 2001
    i have a table named test.
    which occupy around 400m.

    i run two query as the following:
    select * from test order by a;

    select /*+PARALLEL(test,3)*/ * from test order by a;

    for no parallel one , if data is inside SGA, it will read from SGA, for parallel one, even if data is inside SGA, it still read from disk.
    am i right?

    the reason i am asking this is because
    i run both query several times, each time
    i run first one, i didn't notice much disk activity.
    but when i run second one, i notice a lot of disk activity.

    also , i notice that parallel one is actually SLOWER than the
    one without parallel.
    guru is on the way!!!!

  2. #2
    Join Date
    Aug 2001
    Can you check the database buffer before and after the first query?
    Did you check the explain plan for both?
    If there is an index on a, the optimiser might choose to do a full index scan, hence the faster return. (this is especially true for aggregrate functions)
    The parallel hint may be forcing the optimiser to choose a full table scan. The overhead of splitting and recombining the query results for parallel might actually make you machine CPU bound (not knowing the no of CPUs you have) and slow the whole process down, or if they are hitting the same disk I/O bound??.
    Even with enough CPU, parallel queries work better with larger datasets.

    Put up the explain plan for both and we can see what is happening, also set AUTOTRACE on, so we grab the stats as well.

    Have Fun
    Performance... Push the envelope!

  3. #3
    Join Date
    Nov 2000
    Baltimore, MD USA
    Everything roobaron said is true. I might also add that 3 is an extremely small degree of parallelism. There is a lot of overhead to making a query parallel. One cannot expect a sufficient return on that investment with only 3 threads. I would up that to 5 * #processors as a start.

    - Chris

  4. #4
    Join Date
    May 2000
    How many CPUs does the box have?
    Is the data file striped across many disks?

  5. #5
    Join Date
    Jan 2000
    san jose
    Can you check the database buffer before and after the first query?

    i have 8 cpu and data is striped by two disk

    don't you think i should start with
    5*#cpu which is 40 degree

  6. #6
    Join Date
    May 2000
    When the data is striped across 2 disks, more than 2 Parallel Query Processes is a waste. In fact, They will slow down the entire process.

    Parallel Query Execution Plan =
    Minimum ( number of CPUs or number of data files on which the table is striped)

    Also remember that the number of extents created for the object should be the degree of parallelism. For example, you want to create an index with degree of parallelism 4. Assume that the index data file is striped across 4 disks. And the box has 8 CPUs. In order to use Parallel index creation, the object must be created with atleast 4 extents of any size. Then only you would gain the performance of parallelism, because each Parallel Query Process uses one extent, and later they will be merged.

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.