Were the execution plans the same - in particular the distribution methods ?
Your unhinted query would be effectively the same as hinting "parallel(alias
32)" rather than "parallel(32)" - so you might try hinting "parallel(alias 48)"
to see if the same anomaly appears (I would be a little surprised if it did).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Sandra Becker [sbecker6925@xxxxxxxxx]
Sent: 16 February 2016 20:06
To: oracle-l
Subject: Not sure why parallel hint didn't work correctly
Oracle EE 11.2.0.4
This is an Exadata with RAC database.
Database servers - 48 cores
I am just starting to learn about Exadata so I may be asking "silly" questions.
User had a query with hint parallel(48) and looking only at a single partition
with 1.6 billion rows in that partition, along with some joins to much smaller
tables to get to the right set of rows. It was not returning results in a
timely manner. The user indicated he let it run for over an hour without any
results being returned so he killed it.
I did some testing and looked v$px_session, v$session_wait, got the execution
plan, etc. to see what was going on. The 1.6 billion row table has a degree of
32. My test run without the hint completed in just over 12 minutes using
parallel 32, 16 parallel sessions showing ACTIVE status on each node.
v$session_wait showed 16 sessions on each node doing actual work.
When I put the hint back in, v$px_session showed 48 slaves, 24 on each of the
nodes. All the sessions on one node showed status of INACTIVE, but the 24 on
the other node were ACTIVE. v$session_wait showed only one session actively
doing any work for this query.
No one on the team understands what is happening here. Why did the
parallel(48) hint cause this behavior. Any insight would be greatly
appreciated. I'm not sure if there is more information you need that I didn't
supply.
Thank you.
--
Sandy B.
