Wednesday, October 27, 2010

Concurrency and parallelism

I've had a lot of discussions with some very "seasoned" professions on how to handle a high level concurrency. Most of these professions point to the new features of 11gr2 and and parallization..

True 11gr2 has added a new way of handling degree of parallization.

There are some new parameters

parallel_degree_policy
PARALLEL_MIN_TIME_THRESHOLD

These control how parallism is handled.. They can be used to actually create a funnel to ensure the system isn't flooded.

The problem is all this, is that parallelism has a price.. Take a small efficient query, and turn on these parameters.. Guess what happens when you ramp up and run 500 of the same query concurrently ? You see much lower throughput (I've seen as much as 10x lower throughput). Why ?? The overhead of parallel query can be quite high, and can consume more time than even CPU in your AWR report.



Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: JX - SQL statement queue 71 7,815 1.E+05 99.3 Scheduler
DB CPU 96 1.2
PX Nsq: PQ load info query 46 9 201 .1 Other
enq: RD - RAC load 90 2 20 .0 Other
PX Deq: reap credit 152,105 1 0 .0 Other


The moral of the story is, parallism is good for longer queries.. For shorter queries your milege may vary

3 comments:

  1. Bryan, great post. -- How did you know I would search for this answer.

    Do you happen to have any more specifics on the 'PX Nsq: PQ load info query'? I have a random query in my system that spends time in that wait event, and I haven't found a good reference for what that is measuring.

    ReplyDelete
  2. I'm glad I could help. Unfortunately I don't have amore specifics on that wait event.

    ReplyDelete
  3. The Golden Rule

    "Parallism is good for longer queries.. For shorter queries your milege may vary"

    ReplyDelete