| Parallel operations not executing with expected degree [message #549614] |
Mon, 02 April 2012 04:48  |
 |
orapratap
Messages: 134
Registered: November 2011
Location: Canada
|
Senior Member |
|
|
Hello
I am executing a sql statement which is doing FTS in parallel mode
The server has 8 cpus and threads_per_cpu is 2
The v$sql shows PX_SERVERS_EXECUTIONS as 8
select PX_SERVERS_EXECUTIONS, sql_text from v$sql where sql_id='0q0nk5117yth2'
8, select /*+ full(a) parallel(a)....
however the px_sessions shows 17 sessions (16 parallel session + 1 parent session (where sid = qcsid)
Now in px_sessions, these 16 parallel session are divided in 2 server sets 1 and 2
and values for degree and required degree are 8 and 16 respectively
However, all the time, only 8 sessions which belong to server set = 1, were active
though its state was waiting with event "PX Deq Credit: send blkd"
The other session which belong to server set = 2 were never active and always had waint event ='PX Deq: Execution Msg'
what could be the reason that 16 parallel session could not be started though I am the only person using the server, there aren't any batch jobs, dbms_jobs,even archivelogs (not a prod system)?
Note that paralel_max_servers setting is 16
Another issue being the duing start of the query approximately 100-115 blocks were read for the query (checked from longops)
however after 60-70% blocks are read the number of blocks read / seconds falls down to 10-20 blocks / second across all parallel sessions.
What could be the reason? Is there any workaround for this behavior?
Thanks and Regards
Orapratap
|
|
|
|
|
|
| Re: Parallel operations not executing with expected degree [message #549619 is a reply to message #549614] |
Mon, 02 April 2012 06:01  |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
1. the first issue.
I don't see any problem here. You got your 16 slaves as gv$px_session showed. Regarding server sets you can read in Oracle Concepts.
2. the second issue.
It can be for ex., if you have selects in the select list, i.e.
select a, (select b from t) as b from ...
But it is only one possible reason. If you want to know exactly, then you have to supply additional information (sql text, execution plan with runtime stats, etc.).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
