Here is explain plan for single date please suggest any performance improvements..
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock PROD_T.DS_OPS_A2K_WORKFLOW_SS in view PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS for access, we lock PROD_T.DS_OPS_A2K_WF_SS_DELTA in
view PROD_V.DS_VW_OPS_A2K_WF_SS_DELTA for access, and we lock SYS_CALENDAR.CALDATES in view CALENDARTMP for access.
| |
| | 2) | Next, we execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from PROD_T.DS_OPS_A2K_WORKFLOW_SS in view PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS by way of an all-rows scan
with a condition of ("PROD_T.DS_OPS_A2K_WORKFLOW_SS in view PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS.Create_User = 'ULR8'") into Spool 2 (all_amps), which
is built locally on the AMPs. Then we do a SORT to order Spool 2 by the hash code of (CAST((PROD_T.DS_OPS_A2K_WORKFLOW_SS.Source_Create_Ts) AS
DATE)). The size of Spool 2 is estimated with high confidence to be 4,022 rows (301,650 bytes). The estimated time for this step is 4.08
seconds.
| | |
| | |
| | 2) | We do a single-AMP RETRIEVE step from SYS_CALENDAR.CALDATES in view PROD_V.DS_VW1_AVIVA_CALENDAR by way of the unique primary index
"SYS_CALENDAR.CALDATES in view PROD_V.DS_VW1_AVIVA_CALENDAR.cdate = DATE '2013-02-22'" with no residual conditions into Spool 3 (all_amps), which
is duplicated on all AMPs. Then we do a SORT to order Spool 3 by the hash code of (SYS_CALENDAR.CALDATES.cdate). The size of Spool 3 is
estimated with high confidence to be 96 rows (1,632 bytes). The estimated time for this step is 0.00 seconds.
| | |
| | 3) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to Spool 3 (Last Use) by way of a
RowHash match scan. Spool 2 and Spool 3 are joined using a merge join, with a join condition of ("cdate = (CAST(({LeftTable}.Source_Create_Ts) AS
DATE))"). The result goes into Spool 4 (all_amps), which is redistributed by the hash code of (PROD_T.DS_OPS_A2K_WORKFLOW_SS.WPS_Id,
PROD_T.DS_OPS_A2K_WORKFLOW_SS.Policy_No, PROD_T.DS_OPS_A2K_WORKFLOW_SS.Work_Flow_Queue, PROD_T.DS_OPS_A2K_WORKFLOW_SS.Status,
SYS_CALENDAR.CALDATES.cdate) to all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with index join
confidence to be 1 row (76 bytes). The estimated time for this step is 0.02 seconds.
| |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 4 by way of an all-rows scan into Spool 5 (all_amps), which is duplicated on all AMPs. The size
of Spool 5 is estimated with index join confidence to be 96 rows (7,296 bytes). The estimated time for this step is 0.01 seconds.
| |
| | 5) | We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an all-rows scan, which is joined to PROD_T.DS_OPS_A2K_WF_SS_DELTA in view
PROD_V.DS_VW_OPS_A2K_WF_SS_DELTA by way of an all-rows scan with no residual conditions. Spool 5 and PROD_T.DS_OPS_A2K_WF_SS_DELTA are joined
using a product join, with a join condition of ("WPS_Id = PROD_T.DS_OPS_A2K_WF_SS_DELTA.Current_WPS_Id"). The result goes into Spool 6
(all_amps), which is redistributed by the hash code of (SYS_CALENDAR.CALDATES.cdate, PROD_T.DS_OPS_A2K_WORKFLOW_SS.Status,
PROD_T.DS_OPS_A2K_WORKFLOW_SS.Work_Flow_Queue, PROD_T.DS_OPS_A2K_WORKFLOW_SS.Policy_No, PROD_T.DS_OPS_A2K_WORKFLOW_SS.WPS_Id) to all AMPs. Then
we do a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated with index join confidence to be 3 rows (135 bytes).
| |
| | 6) | We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan, which is joined to Spool 6 (Last Use) by way of a
RowHash match scan. Spool 4 and Spool 6 are left outer joined using a merge join, with a join condition of ("Field_1 = Field_1"). The result
goes into Spool 7 (all_amps), which is redistributed by the hash code of (PROD_T.DS_OPS_A2K_WF_SS_DELTA.Previous_WPS_Id) to all AMPs. Then we do
a SORT to order Spool 7 by row hash. The size of Spool 7 is estimated with index join confidence to be 3 rows (204 bytes). The estimated time for
this step is 2.34 seconds.
| |
| | 7) | We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of a RowHash match scan, which is joined to PROD_T.DS_OPS_A2K_WORKFLOW_SS in
view PROD_V.DS_VW_OPS_A2K_WORKFLOW_SS by way of a RowHash match scan with no residual conditions. Spool 7 and PROD_T.DS_OPS_A2K_WORKFLOW_SS are
left outer joined using a merge join, with condition(s) used for non-matching on left table ("NOT (Previous_WPS_Id IS NULL)"), with a join
condition of ("Previous_WPS_Id = PROD_T.DS_OPS_A2K_WORKFLOW_SS.WPS_Id"). The result goes into Spool 1 (group_amps), which is built locally on the
AMPs. The size of Spool 1 is estimated with index join confidence to be 3 rows (285 bytes). The estimated time for this step is 0.04 seconds.
| |
| | 8) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1.
| |
Thank you.
