I did forget to mention that an RMAN backup was running and is still
running. It's a 30T data warehouse so backups take quite awhile. They
have only 4 threads for the backup. They are working on a new backup
solution so hopefully we'll get that taken care of in the next 6 weeks.
Could the backup play a part in this?
I don't have a SQL Monitoring report. I'll see what I can get by running
tests later today. Busy time of day for our app users right now and I
don't want to take a chance on causing any issues since I'm not sure what's
going on.
The execution plans were the same, except for the cost. It was about a
third higher for the parallel(48) run. I will try the parallel(alias 48)
to see what that shows me.
Sandy
On Tue, Feb 16, 2016 at 1:29 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx
wrote:
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.
