Re: Not sure why parallel hint didn't work correctly
Date: Thu, 18 Feb 2016 09:30:01 -0000
Message-ID: <A845B3EB3B63470EAF5618D66900B1A0_at_Primary>
Hemant,
The parallel hint is now like the dynamic_sampling hint: with an alias in place it refers to the table referenced, but with no alias it is more like a cursor hint.
I have to say that I am slightly surprised at the description of what servers and how many there are in the OP's experiments - but I can imagine (unlikely) events that could explain them but I'm not keen to start writing up all the guesses.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Chitale, Hemant K" <Hemant-K.Chitale_at_sc.com> To: "Sandra Becker" <sbecker6925_at_gmail.com> Cc: "oracle-l" <oracle-l_at_freelists.org> Sent: Thursday, February 18, 2016 1:26 AM Subject: RE: Not sure why parallel hint didn't work correctly
|
| 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_at_gmail.com]
| 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_at_sc.com<mailto:Hemant-K.Chitale_at_sc.com>> 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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>
[mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>]
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
|
|
| -----
| No virus found in this message.
| Checked by AVG - www.avg.com
| Version: 2016.0.7442 / Virus Database: 4530/11647 - Release Date:
02/17/16
|
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7442 / Virus Database: 4530/11647 - Release Date: 02/17/16
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 18 2016 - 10:30:01 CET