Help Understanding Remote Execution Plan

From: Cunningham, Mike <mcunningham_at_thedoctors.com>
Date: Thu, 24 Apr 2014 10:44:12 -0700
Message-ID: <C8FDF1081BF81B418F937FE98B7B3CC78D9C1C_at_NPEXCHMB102.tdc.internal>

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 24 2014 - 19:44:12 CEST