Home » RDBMS Server » Performance Tuning » Parallel operations not executing with expected degree (Oracle 10.2.0.4)
Parallel operations not executing with expected degree [message #549614] Mon, 02 April 2012 04:48 Go to next message
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 #549618 is a reply to message #549614] Mon, 02 April 2012 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Parallelism clause applies to each current step (operation) of the plan in execution.

Regards
Michel
Re: Parallel operations not executing with expected degree [message #549619 is a reply to message #549614] Mon, 02 April 2012 06:01 Go to previous message
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.).

Previous Topic: Distributed query running slow
Next Topic: speeding up scanning of internal temporary table (of materialize hint)
Goto Forum:
  


Current Time: Thu Mar 28 09:07:32 CDT 2024