RE: Not sure why parallel hint didn't work correctly
- From: "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx>
- To: Sandra Becker <sbecker6925@xxxxxxxxx>
- Date: Thu, 18 Feb 2016 01:26:56 +0000
If you include Table Aliases in the query you must specify the Alias in the
PARALLEL Hint.
Hemant K Chitale
From: Sandra Becker [mailto:sbecker6925@xxxxxxxxx]
Sent: Wednesday, February 17, 2016 11:34 PM
To: Chitale, Hemant K
Cc: oracle-l
Subject: Re: Not sure why parallel hint didn't work correctly
Got a chance to rerun with parallel(alias 48). It performed as well as the
test not using a hint. Curiously, everything showed it requested and actual
DOP of 32 and not 48. 32 is what we have on the large table. Duration is just
over 12 minutes. Without the alias, the second server sessions were inactive
the entire run. Of the four tables in the query, the two smallest have a degree
of 1. The other two have a degree of 32. Parallel query was used only on the
one table without the hint and using (alias 48). The run with just
parallel(48) does not show parallel query being used for any of the tables.
v$sessstat shows all the parallel sessions doing roughly the same amount of
activity.
Unfortunately, I am not at liberty to share the SQL statement in this instance
(company policy). I was just curious as to what would cause this and possible
resolution other than what I did. The runtime without the hint is acceptable
to the enduser. He was under the impression he always had to use a hint and
the higher the parallel value, the faster his query would run.
Sandy
On Tue, Feb 16, 2016 at 7:20 PM, Chitale, Hemant K
<Hemant-K.Chitale@xxxxxx<mailto:Hemant-K.Chitale@xxxxxx>> wrote:
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.
What was the duration of the query execution ? Did you see INACTIVE and ACTIVE
for the entire duration (i.e. repeated queries on V$SESSION) ? Did V$SESSTAT
statistics indicate the level of activity for each of the sessions ?
along with some joins to much smaller tables to get to the right set of rows
Did it show parallel query being used for the joined tables ? In all three
cases (User query, unhinted query, query with parallel (48)) ?
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx<
mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[
mailto:oracle-l-bounce@xxxxxxxxxxxxx<
mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Sandra Becker
Sent: Wednesday, February 17, 2016 4:06 AM
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.
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html
--
Sandy B.
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html
Other related posts:
