| Parallel Threads Waiting [message #621039] |
Thu, 07 August 2014 17:23  |
scottwmackey
Messages: 515
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  |
rleishman
Messages: 3728
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 |
John Watson
Messages: 8962
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 |
scottwmackey
Messages: 515
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 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
Roachcoach
Messages: 1576
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 |
scottwmackey
Messages: 515
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
