7

I'm trying out oracles parallel option on a cluster and surprisingly I am getting worse results with the parallel option. I was expecting some improvement with the parallel option but certainly not worse results. I am wondering why this is the case, and if there is anything wrong with the way I am using the parallel option on my cluster.

I am using a degree of 4 when the number of CPU's I have is 8. I have tried adding parallel to the cluster directly ALTER CLUSTER cluster PARALLEL 4 as well as at the statement on both the index /*+ PARALLEL_INDEX(clust_index, 4) */and tables /*+ PARALLEL(4) */,

Here is my trace from the parallel:

enter image description here

Without parallel:

enter image description here

Improve this question
4
  • My first thought on this that the overhead of using parallelism is larger than performing a normal hash join without parallelism. It takes extra CPU to perform parallelism, and the overhead can be greater than the benefit if you are dealing with small objects. I would try not putting hints or even turning on parallelism for the cluster. Try setting automatic parallelism on the database layer to auto and turn parallelism on for the objects.
    – Data Flux
    Oct 11, 2015 at 15:17
  • Yeah overhead would make sense. Would this explain why the CPU time is higher than the elapsed time? But this is also the case without the parallel option.
    – jn025
    Oct 11, 2015 at 21:44
  • Typically, if you are looking at DB time, CPU time is a contributing factor to the overall elapsed time. Your overall elapsed time is greater without the parallelism according to your figures. I run into this problem all the time with parallelism. Parsing and execution take no time at all, but the processing it takes to run both queries is longer for PX. You have a very simple join and unless you are working with partitioned objects that are very, very large in a cluster environment, parallelism can be a liability. I'm not saying this in every case, but from my experience.
    – Data Flux
    Oct 11, 2015 at 22:46
  • If you run in a cluster you also add liability if your interconnect is not big enough. By default parallelism will spread across nodes. You may want to read the bottom of that document: docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm
    – Nicolas de Fontenay
    Dec 30, 2015 at 18:33

2 Answers 2

Reset to default
1

If you want to run in parallel you have to design for it. Things to consider is using partitions so they can be scanned in parallel. Don't forget that parallelism in general will try to use full scans on segments. Partition pruning can help reducing the size of your scans.

If you really are using pq for this sort of small queries, make sure you have enough PQ slaves ready for use. Otherwise, the starting of new slaves is added as overhead to your query. Not that bad for dss type queries that run for hours but deadly for short running queries.

Also think about using queuing your PQ queries to guarantee that when a query starts, it has all the slaves it needs. This can be done in Oracle Resource Manager.

Improve this answer
0

From the SQL statement shown in your screen shots, you are joining two tables (customer and sales) on customer_id while restricting the customer_id to be less than 2000. Assuming that these are vanilla primary keys larger than zero and nothing fancy is going on, this will yield at max 2000 rows.

In addition as you can see the parallel statement performs full scans, while the single thread query is able to perform an index range scan.

In this case for sure all the overheads of parallelism will kick in and the statement will run slower in any cluster set-up. Try again with reasonable numbers in your tables and reasonable analytical use cases (tables containing on the order of 10 millions please and for example identifying top 10 customers by number of sales rows).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.