Help Understanding Remote Execution Plan
- From: "Cunningham, Mike" <mcunningham@xxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 24 Apr 2014 10:44:12 -0700
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?
SELECT "BILL_ACCT_NUM","POLICY_TERM_ID","HIST_PAY_PLAN_SEQ","CREATED_BY",
"CREATED_DATE"
FROM
"HIST_PAY_PLAN" "HIST_PAY_PLAN"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 650 0.02 0.02 0 0 0 0
Fetch 27951 304.32 310.90 0 3170051 650 906319050
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28602 304.35 310.93 0 3170051 650 906319050
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 94
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
1394337 1394337 1394337 TABLE ACCESS FULL HIST_PAY_PLAN (cr=4878 pr=0
pw=0 time=211603 us cost=2386 size=50433942 card=1327209)
Michael Cunningham
Senior Database Administrator
The Doctors' Company
707.226.0221 - desk
707.337.0184 - cell
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:
