Re: Not sure why parallel hint didn't work correctly

> 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.

I am not familiar with Exadata, but this description sounds like data skew in PX distribution. Do you have SQL Monitoring report for this query? It may gonna reveal it. However i can highly recommend one tool for PX analysis - XPLAN_ASH by Randolf Geist: https://raw.githubusercontent.com/randolfgeist/oracle_scripts/master/xplan_ash.sql

It needs Enterprise Edition plus the Diagnostic Pack license (for ASH analysis), but it is absolutely worth it.  

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> Sandra Becker <sbecker6925_at_gmail.com> hat am 16. Februar 2016 um 21:06 geschrieben:
>
> 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.

--
http://www.freelists.org/webpage/oracle-l