RE: Help Understanding Remote Execution Plan

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 24 Apr 2014 18:32:58 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DEAA8A_at_exmbx05.thus.corp>


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle



From: Cunningham, Mike [mcunningham_at_thedoctors.com] Sent: 24 April 2014 19:29
To: Jonathan Lewis; oracle-l_at_freelists.org Subject: RE: Help Understanding Remote Execution Plan

Jonathan, thanks for the reply. I originally looked at the NL and it wasn稚 making sense which is the source of my confusion. I知 sorry I didn稚 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知 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_at_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知 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_at_jlcomp.demon.co.uk] Sent: Thursday, April 24, 2014 11:00 AM
To: Cunningham, Mike; oracle-l_at_freelists.org 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
_at_jloracle



From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org] on behalf of Cunningham, Mike [mcunningham_at_thedoctors.com] Sent: 24 April 2014 18:44
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Help Understanding Remote Execution Plan I知 tuning a query and I知 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知 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.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 24 2014 - 20:32:58 CEST

Original text of this message