RE: Help Understanding Remote Execution Plan
- From: "Cunningham, Mike" <mcunningham@xxxxxxxxxxxxxx>
- To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 24 Apr 2014 13:59:21 -0700
That’s it Jonathan, you nailed it on the head. I wish I could do that. I
missed it completely.
When I added line 13 predicate to the query (without the part from lines 12-16)
the result set dropped to 650.
Thanks a bunch for helping me to understand this.
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FEE"."FEE_WAIVED_FLAG"="F1"."FLAG_CODE"(+))
6 - access("FEE"."FEE_TYPE_CODE"="FT"."FEE_TYPE_CODE"(+))
9 - access("PP"."POLICY_TERM_ID"="FEE"."POLICY_TERM_ID"(+) AND
"PP"."BILL_ACCT_NUM"="FEE"."BILL_ACCT_NUM"(+))
13 - filter("PP"."AUTO_PRINT_FLAG" IS NOT NULL)
14 - filter("PPH"."DR"=1 AND "PPH"."BILL_ACCT_NUM"="PP"."BILL_ACCT_NUM" AND
"PPH"."POLICY_TERM_ID"="PP"."POLICY_TERM_ID")
15 - filter(DENSE_RANK() OVER ( PARTITION BY
"HIST_PAY_PLAN"."BILL_ACCT_NUM","HIST_PAY_PLAN"."POLI
D" ORDER BY
INTERNAL_FUNCTION("HIST_PAY_PLAN"."HIST_PAY_PLAN_SEQ") DESC )<=1)
Michael Cunningham
Senior Database Administrator
The Doctors' Company
707.226.0221 - desk
707.337.0184 - cell
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Thursday, April 24, 2014 11:35 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Help Understanding Remote Execution Plan
It would be more helpful to see the predicate section of the plan.
A simple explanation of the 1M rows vs. 650 executions is that the FILTER at
line 13 is pre-emptive and stops lines 14 - 16 from running. For example is may
be a predicate of the form: :bind_variable = {constant}
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Cunningham, Mike [mcunningham@xxxxxxxxxxxxxx]
Sent: 24 April 2014 19:29
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: Help Understanding Remote Execution Plan
Jonathan, thanks for the reply. I originally looked at the NL and it wasn’t
making sense which is the source of my confusion. I’m sorry I didn’t mention
this before, but when I run the query without the part from lines 12-16 it does
indeed return 1,082,779 rows which is represented on line 2. I’m sure if the
NL caused that many executions of the second table it would never complete.
Just in case it helps, here is part of the query (lines 12-16)
select bill_acct_num,
policy_term_id,
created_by as apf_last_updated_by,
created_date as apf_last_updated_date,
dense_rank ()
over ( partition by bill_acct_num, policy_term_id
order by hist_pay_plan_seq desc ) dr
from hist_pay_plan@rmt;
Here is what was shown in the remote database as the executed query.
SELECT "BILL_ACCT_NUM","POLICY_TERM_ID","HIST_PAY_PLAN_SEQ","CREATED_BY",
"CREATED_DATE"
FROM
"HIST_PAY_PLAN" "HIST_PAY_PLAN"
I’m interested in the outcome of this misunderstanding of mine. I will try a
HASH hint and see what that produces.
Michael Cunningham
Senior Database Administrator
The Doctors' Company
707.226.0221 - desk
707.337.0184 - cell
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, April 24, 2014 11:00 AM
To: Cunningham, Mike; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: Help Understanding Remote Execution Plan
It's executed 650 times because it's (ignoring the fiddly bits around the
edges) the second table in a nested loop join.
Line 2 presumably returns 650 rows, so line 12 and its sub-plan have been
executed 650 times.
It's curious that Oracle did this rather than working out some way of doing a
hash join for that part of the query, but perhaps there a limitation imposed by
the analytic function you're using and the (derived?) columns used in the join.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Cunningham, Mike
[mcunningham@xxxxxxxxxxxxxx]
Sent: 24 April 2014 18:44
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Help Understanding Remote Execution Plan
I’m tuning a query and I’m having trouble understanding why part of a REMOTE
execution plan is executing 650 times. Can anyone shed some light on this?
I hope the plan is enough to help shed light on this as I’m not sure it would
be ok to share the query. However, the query runs for 50 minutes as is.
Adding the driving_site hint got it to under 2 minutes, but I still want to
understand what was going on in the original execution.
Here is the plan from the main database (not the remote db)
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
26G(100)| | | |
| 1 | NESTED LOOPS OUTER | | 1437G| 503T| |
26G (1)|999:59:59 | | |
| 2 | VIEW | | 1082K| 365M| |
1780 (4)| 00:00:22 | | |
|* 3 | HASH JOIN RIGHT OUTER | | 1082K| 372M| |
1780 (4)| 00:00:22 | | |
| 4 | REMOTE | LU_FLAG | 2 | 58 | |
4 (0)| 00:00:01 | RMT~ | R->S |
| 5 | VIEW | | 1082K| 342M| |
1769 (4)| 00:00:22 | | |
|* 6 | HASH JOIN RIGHT OUTER | | 1082K| 350M| |
1769 (4)| 00:00:22 | | |
| 7 | REMOTE | LU_FEE_TYPE | 2 | 170 | |
4 (0)| 00:00:01 | RMT~ | R->S |
| 8 | VIEW | | 1082K| 262M| |
1758 (4)| 00:00:22 | | |
|* 9 | HASH JOIN RIGHT OUTER| | 1082K| 134M| |
1758 (4)| 00:00:22 | | |
| 10 | REMOTE | PAY_PLAN_FEE | 2416 | 96640 | |
16 (0)| 00:00:01 | RMT~ | R->S |
| 11 | REMOTE | PAY_PLAN | 1082K| 92M| |
1734 (3)| 00:00:21 | RMT~ | R->S |
| 12 | VIEW | | 1327K| 39M| |
24076 (1)| 00:04:49 | | |
|* 13 | FILTER | | | | |
| | | |
|* 14 | VIEW | | 1327K| 88M| |
24076 (1)| 00:04:49 | | |
|* 15 | WINDOW SORT PUSHED RANK| | 1327K| 88M| 106M|
24076 (1)| 00:04:49 | | |
| 16 | REMOTE | HIST_PAY_PLAN | 1327K| 88M| |
2181 (3)| 00:00:27 | RMT~ | R->S |
---------------------------------------------------------------------------------------------------------------------
The remote database shows that each table is queried individually as I would
expect. The first four tables have the FULL scan query executed one time each.
The final table, HIST_PAY_PLAN, is where the confusion comes in. There are
650 Executions on the remote database show in the tkprof. There are 1,394,337
rows in the HIST_PAY_PLAN table, but I see that 906,319,050 (1394337 * 650)
were fetched through the database link. Can anyone help me understand why this
query would have been executed 650 times?
Confidentiality Notice: This message and any attachments hereto may contain
confidential and privileged communications or information and/or attorney
client communications or work-product protected by law. The information
contained herein is transmitted for the sole use of the intended recipient(s).
If you are not the intended recipient or designated agent of the recipient of
such information, you are hereby notified that any use, dissemination, copying
or retention of this e-mail or the information contained herein is strictly
prohibited and may subject you to penalties under federal and/or state law. If
you received this e-mail in error, please notify the sender immediately and
permanently delete this e-mail.
Confidentiality Notice: This message and any attachments hereto may contain
confidential and privileged communications or information and/or attorney
client communications or work-product protected by law. The information
contained herein is transmitted for the sole use of the intended recipient(s).
If you are not the intended recipient or designated agent of the recipient of
such information, you are hereby notified that any use, dissemination, copying
or retention of this e-mail or the information contained herein is strictly
prohibited and may subject you to penalties under federal and/or state law. If
you received this e-mail in error, please notify the sender immediately and
permanently delete this e-mail.
Other related posts:
