Home » RDBMS Server » Performance Tuning » Parallel Threads Waiting
Parallel Threads Waiting [message #621039] Thu, 07 August 2014 17:23 Go to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Hi all,

I have a query running in parallel. It's running much slower that I expected (hoped?) it would. When I look at v$session, I see that all but one session is WAITING. Please see below. I was wondering if anybody can tell me if that really means that all those parallel threads are actually not doing anything until the one that is not WAITING completes. If so, what controls the number of parallel sessions that actually do something simultaneously? Or is this just something that I have to live with? I am looking at How Parallel Execution Works in Oracle® Database VLDB and Partitioning Guide, but I am not seeing anything the clearly answers my question.


Thanks in advance,
Scott

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 

SQL> SELECT sid,
  2         serial#,
  3         status,
  4         program,
  5         module,
  6         state,
  7         process
  8    FROM v$session
  9   WHERE username IS NOT NULL
 10     AND status = 'ACTIVE'
 11   ORDER BY program;
 
 SID    SERIAL# STATUS   PROGRAM                     MODULE      STATE             PROCESS
---- ---------- -------- --------------------------- ----------- ----------------- --------
 483       4107 ACTIVE   oracle@server (P000)        EVENTS.LOAD WAITING           22040
 510      25415 ACTIVE   oracle@server (P001)        EVENTS.LOAD WAITING           22044
 ...
1393      52879 ACTIVE   oracle@server (P118)        EVENTS.LOAD WAITING           22520
1419      43859 ACTIVE   oracle@server (P119)        EVENTS.LOAD WAITING           22524
 652      28123 ACTIVE   sqlplus@server (TNS V1-V3)  EVENTS.LOAD WAITED SHORT TIME 80268
Re: Parallel Threads Waiting [message #621048 is a reply to message #621039] Fri, 08 August 2014 00:55 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How do you know it is running in parallel? A parallel plan does not guarantee parallel execution. I had this problem once - I thought it was running in parallel but I had a (I think) non-deterministic that was forcing it to serial execution.

Ross Leishman
Re: Parallel Threads Waiting [message #621049 is a reply to message #621039] Fri, 08 August 2014 01:16 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
One point that sometimes slips through: the name of that module, events.load, implies that it is doing DML. Have you enabled parallel DML for the session?

But generally speaking, if you are trying to tune parallel processing manually you may have allocated far too many PX servers. I see that so often. Much better to enable parallel_degree_policy=auto, and let Uncle Oracle decide.
Re: Parallel Threads Waiting [message #621062 is a reply to message #621048] Fri, 08 August 2014 03:04 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I don't know that it is running in parallel. That's what I was hoping one of you all could help me with. I have always been assuming that when I see the (Pxxx), that is an indication of one query's parallel execution. I am not actually focusing on the module name to determine that they are the same query. I actually look at these in PL/SQL Developer's Session window. It actually shows the query that it is executing. They are all the same and I know there is only one of them running at any given time. I am just trying to find out if WAITING means they are not doing anything and what I can do about it.
Re: Parallel Threads Waiting [message #621063 is a reply to message #621062] Fri, 08 August 2014 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can have a look at v$pq_sysstat to know if you ever execute in parallel.
You can query v$px_session to know if some sessions are currently running in parallel and with which degree.
You can query v$pq_tqstat to know what happened during the latest parallel operations.

[Updated on: Fri, 08 August 2014 03:30]

Report message to a moderator

Re: Parallel Threads Waiting [message #621064 is a reply to message #621063] Fri, 08 August 2014 03:35 Go to previous messageGo to next message
Roachcoach
Messages: 1215
Registered: May 2010
Location: UK
Senior Member
In addition to the above, bring EVENT into your query - you need to know what it is waiting on in order to start to diagnose.

You're definitely getting PX though - but perhaps too many going by the program names (PXXX) unless there are a bunch of people using slaves - in which case it's maybe just mugging the box.
Re: Parallel Threads Waiting [message #621109 is a reply to message #621064] Sat, 09 August 2014 02:30 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Thanks much Roachcoach,

I added EVENT into my query and saw that all my session were "PX Deq Credit: send blkd". I googled that and found that it comes up when the table into which you are inserting is not parallel enabled. I checked, and my target table had a degree of 1. I altered the table and now everything is running in parallel over 10 times faster than before.
Re: Parallel Threads Waiting [message #621111 is a reply to message #621109] Sat, 09 August 2014 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

John Watson wrote on Fri, 08 August 2014 08:16
... Have you enabled parallel DML for the session?
...


Re: Parallel Threads Waiting [message #621128 is a reply to message #621111] Sat, 09 August 2014 12:20 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Yes, parallel DML had always been enabled for the session. That's what through me off. I assumed it was sufficient, but I needed to change the degree of parallel for the table.
Previous Topic: Queries "break" after automatic statistic gathering.
Next Topic: How to calculate ROI on performance gain
Goto Forum:
  


Current Time: Wed Oct 01 11:34:49 CDT 2014

Total time taken to generate the page: 0.59349 seconds