This discussion is archived
4 Replies Latest reply: Jan 28, 2013 11:59 PM by Mohamed Houri RSS

Need Help in sql tuning in EXADATA environment

949187 Newbie
Currently Being Moderated
I am uploadin the sql with its current explain plan, this sql in Prd database is executing in 6-10 mins and we need to improve this sql perf to 1-2 mins as the requirement from business team.
I am giving some backgroud about this sql tuning requirement, this sql is newly developed in and currently is in UAT phase, we don't have much option for tuning code here since the sql is tool generated through COGNOS DataMart tool, options are there to look into from DBA end with plan level or creating indexes on suitible columns to reduce i/o in minimizing the rows traversing by the sql.
Is anybody can help me out here?
WITH "WCRS_CLAIM_DETAIL_VW5"
AS (SELECT "WCRS_CLAIM_DETAIL_VW"."CLAIM_DETAIL_PK_ID"
"CLAIM_DETAIL_PK_ID",
"WCRS_CLAIM_DETAIL_VW"."CLAIM_ID" "CLAIM_ID",
"WCRS_CLAIM_DETAIL_VW"."CURRENT_SNAPSHOT_IND"
"CURRENT_SNAPSHOT_IND",
"WCRS_CLAIM_DETAIL_VW"."LOSS_DT" "LOSS_DT",
"WCRS_CLAIM_DETAIL_VW"."REGULATORY_STATE_CD"
"REGULATORY_STATE_CD"
FROM "CDW_DLV_IDS"."WCRS_CLAIM_DETAIL_VW" "WCRS_CLAIM_DETAIL_VW"
WHERE "WCRS_CLAIM_DETAIL_VW"."CURRENT_SNAPSHOT_IND" = 'Y'),
"WCRS_POLICY_DETAIL_VW7"
AS (SELECT "WCRS_POLICY_DETAIL_VW"."CLAIM_NBR" "CLAIM_NBR",
"WCRS_POLICY_DETAIL_VW"."CLAIM_SYMBOL_CD" "CLAIM_SYMBOL_CD",
"WCRS_POLICY_DETAIL_VW"."CURRENT_SNAPSHOT_IND"
"CURRENT_SNAPSHOT_IND",
"WCRS_POLICY_DETAIL_VW"."KEY_OFFICE_CD" "KEY_OFFICE_CD",
"WCRS_POLICY_DETAIL_VW"."POLICY_NBR" "POLICY_NBR"
FROM "CDW_DLV_IDS"."WCRS_POLICY_DETAIL_VW" "WCRS_POLICY_DETAIL_VW"
WHERE "WCRS_POLICY_DETAIL_VW"."CURRENT_SNAPSHOT_IND" = 'Y')
SELECT /*+ gather_plan_statistics monitor bind_aware */
/* ^^unique_id */
"T0"."C0" "Account_Name",
"T0"."C1" "Accident_State_Cd",
"T0"."C2" "c3",
"T0"."C3" "PPO_Name",
"T0"."C4" "Invc_Classification_Type_Desc",
FIRST_VALUE (
"T0"."C5")
OVER (
PARTITION BY "T0"."C0",
"T0"."C1",
"T0"."C2",
"T0"."C3",
"T0"."C4",
"T0"."C6")
"Claim_Cnt___Distinct",
"T0"."C7" "Invc_Control_Number",
"T0"."C8" "Invc_Allowance_Amt",
"T0"."C9" "Invc_Charge_Amt",
"T0"."C10" "c10",
"T0"."C11" "PPO_Reduction_Amt",
"T0"."C12" "Dup_Ln_Save_Amt",
"T0"."C13" "c13",
"T0"."C14" "Sub_Total",
"T0"."C15" "c15",
"T0"."C6" "c16"
FROM (SELECT "T1"."C0" "C0",
"T1"."C1" "C1",
"T1"."C2" "C2",
"T1"."C3" "C3",
"T1"."C4" "C4",
"T1"."C6" "C5",
"T1"."C5" "C6",
"T0"."C0" "C7",
"T1"."C7" "C8",
"T1"."C8" "C9",
"T1"."C9" "C10",
"T1"."C10" "C11",
"T1"."C11" "C12",
"T1"."C12" "C13",
"T1"."C13" "C14",
"T1"."C14" "C15"
FROM (SELECT COUNT (DISTINCT "INVC_DIM_VW"."INVC_ID") "C0"
FROM "CDW_DLV_IDS"."WCRS_POLICY_GROUPING_VW" "WCRS_POLICY_GROUPING_VW",
"WCRS_CLAIM_DETAIL_VW5",
"EDW_DM"."INVC_DIM_VW" "INVC_DIM_VW",
"EDW_DM"."PROVIDER_NETWORK_DIM_VW" "PROVIDER_NETWORK_DIM_VW",
"EDW_DM"."INVC_ACTY_SNPSHT_FACT_VW" "INVC_ACTY_SNPSHT_FACT_VW6",
"EDW_DM"."CURRENT_MED_INVC_RPT_DT_VW" "CURRENT_MED_INVC_RPT_DT_VW",
"EDW_DM"."ALL_INVC_SNPSHT_FACT_VW" "ALL_INVC_SNPSHT_FACT_VW",
"CDW_DLV_IDS"."WCRS_CURRENT_CLAIM_RPT_DT_VW" "WCRS_CURRENT_CLAIM_RPT_DT_VW",
"CDW_DLV_IDS"."WCRS_CLAIM_FACT_VW" "WCRS_CLAIM_FACT_VW",
"WCRS_POLICY_DETAIL_VW7",
"EDW_DM"."INVC_CLAIM_DTL_BRDG_FACT_VW" "INVC_CLAIM_DTL_BRDG_FACT_VW"
WHERE "INVC_DIM_VW"."INVC_DELETION_IND" <> 'Y'
AND "INVC_DIM_VW"."INVC_CONSIDRTN_TYPE_DESC" =
'Original'
AND "CURRENT_MED_INVC_RPT_DT_VW"."CURRENT_MONTH_RPT_DT" =
"ALL_INVC_SNPSHT_FACT_VW"."AS_OF_MONTH_END_DT_PK_ID"
AND "WCRS_CURRENT_CLAIM_RPT_DT_VW"."CURRENT_CLAIM_RPT_DT" =
"WCRS_CLAIM_FACT_VW"."CLAIM_REPORTING_DT"
AND "WCRS_POLICY_GROUPING_VW"."ACCOUNT_NM" =
CAST ('1ST TEAM STAFFING SERVICES, INC.' AS VARCHAR (50 CHAR))
AND "WCRS_POLICY_DETAIL_VW7"."POLICY_NBR" =
"WCRS_POLICY_GROUPING_VW"."POLICY_IDENTIFIER"
AND "WCRS_POLICY_DETAIL_VW7"."CLAIM_NBR" =
"WCRS_CLAIM_FACT_VW"."CLAIM_NBR"
AND "WCRS_POLICY_DETAIL_VW7"."CLAIM_SYMBOL_CD" =
"WCRS_CLAIM_FACT_VW"."CLAIM_SYMBOL_CD"
AND "WCRS_POLICY_DETAIL_VW7"."KEY_OFFICE_CD" =
"WCRS_CLAIM_FACT_VW"."KEY_OFFICE_CD"
AND "WCRS_POLICY_DETAIL_VW7"."CURRENT_SNAPSHOT_IND" =
'Y'
AND "WCRS_CLAIM_FACT_VW"."CLAIM_DETAIL_PK_ID" =
"WCRS_CLAIM_DETAIL_VW5"."CLAIM_DETAIL_PK_ID"
AND "WCRS_CLAIM_DETAIL_VW5"."CURRENT_SNAPSHOT_IND" =
'Y'
AND "WCRS_CLAIM_FACT_VW"."CLAIM_GID" =
"INVC_CLAIM_DTL_BRDG_FACT_VW"."CLM_GID"
AND "INVC_CLAIM_DTL_BRDG_FACT_VW"."INVC_GID" =
"INVC_DIM_VW"."INVC_GID"
AND "INVC_DIM_VW"."INVC_PK_ID" =
"INVC_ACTY_SNPSHT_FACT_VW6"."INVC_PK_ID"
AND "ALL_INVC_SNPSHT_FACT_VW"."MONTH_END_DT_PK_ID" =
"INVC_ACTY_SNPSHT_FACT_VW6"."MONTH_END_DT_PK_ID"
AND "ALL_INVC_SNPSHT_FACT_VW"."INVC_GID" =
"INVC_ACTY_SNPSHT_FACT_VW6"."INVC_GID"
AND "PROVIDER_NETWORK_DIM_VW"."PROVIDER_NETWORK_PK_ID" =
"INVC_ACTY_SNPSHT_FACT_VW6"."PPO_PROVIDER_NETWORK_PK_ID"
AND "WCRS_CURRENT_CLAIM_RPT_DT_VW"."CURRENT_CLAIM_RPT_DT" =
"WCRS_CLAIM_FACT_VW"."CLAIM_REPORTING_DT") "T0",
( SELECT "WCRS_POLICY_GROUPING_VW"."ACCOUNT_NM" "C0",
"WCRS_CLAIM_DETAIL_VW5"."REGULATORY_STATE_CD" "C1",
CASE
WHEN "INVC_DIM_VW"."INVC_IN_OUT_OF_NETWORK_IND" =
'Y'
THEN
'In - Network'
WHEN "INVC_DIM_VW"."INVC_IN_OUT_OF_NETWORK_IND" =
'N'
THEN
'Out - Network'
ELSE
'Others'
END
"C2",
"PROVIDER_NETWORK_DIM_VW"."PROVIDER_NETWORK_NM" "C3",
"INVC_DIM_VW"."INVC_CLASS_TYPE_DESC" "C4",
CASE
WHEN (EXTRACT (
YEAR FROM ("WCRS_CLAIM_DETAIL_VW5"."LOSS_DT"))
IS NULL)
OR (EXTRACT (
MONTH FROM ("WCRS_CLAIM_DETAIL_VW5"."LOSS_DT"))
IS NULL)
THEN
NULL
ELSE
( EXTRACT (
YEAR FROM ("WCRS_CLAIM_DETAIL_VW5"."LOSS_DT"))
|| EXTRACT (
MONTH FROM ("WCRS_CLAIM_DETAIL_VW5"."LOSS_DT")))
END
"C5",
COUNT (DISTINCT "WCRS_CLAIM_DETAIL_VW5"."CLAIM_ID")
"C6",
SUM ("INVC_ACTY_SNPSHT_FACT_VW6"."INVC_ALWC_AMT") "C7",
SUM ("INVC_ACTY_SNPSHT_FACT_VW6"."INVC_CHRGS_AMT")
"C8",
SUM (
"INVC_ACTY_SNPSHT_FACT_VW6"."TOT_INVC_REVIEW_RULE_ALWC_AMT")
"C9",
SUM ("INVC_ACTY_SNPSHT_FACT_VW6"."INVC_PPO_REDUC_AMT")
"C10",
SUM ("INVC_ACTY_SNPSHT_FACT_VW6"."INVC_DUP_REDUC_AMT")
"C11",
SUM ("INVC_ACTY_SNPSHT_FACT_VW6"."INVC_OSR_REDUC_AMT")
"C12",
( SUM (
"INVC_ACTY_SNPSHT_FACT_VW6"."INVC_CHRGS_AMT")
- SUM ("INVC_ACTY_SNPSHT_FACT_VW6"."INVC_ALWC_AMT"))
- SUM (
"INVC_ACTY_SNPSHT_FACT_VW6"."INVC_DUP_REDUC_AMT")
"C13",
SUM (
"INVC_ACTY_SNPSHT_FACT_VW6"."INVC_CLNT_SPEC_REDUC_AMT")
"C14"
FROM "CDW_DLV_IDS"."WCRS_POLICY_GROUPING_VW" "WCRS_POLICY_GROUPING_VW",
"WCRS_CLAIM_DETAIL_VW5",
"EDW_DM"."INVC_DIM_VW" "INVC_DIM_VW",
"EDW_DM"."PROVIDER_NETWORK_DIM_VW" "PROVIDER_NETWORK_DIM_VW",
"EDW_DM"."INVC_ACTY_SNPSHT_FACT_VW" "INVC_ACTY_SNPSHT_FACT_VW6",
"EDW_DM"."CURRENT_MED_INVC_RPT_DT_VW" "CURRENT_MED_INVC_RPT_DT_VW",
"EDW_DM"."ALL_INVC_SNPSHT_FACT_VW" "ALL_INVC_SNPSHT_FACT_VW",
"CDW_DLV_IDS"."WCRS_CURRENT_CLAIM_RPT_DT_VW" "WCRS_CURRENT_CLAIM_RPT_DT_VW",
"CDW_DLV_IDS"."WCRS_CLAIM_FACT_VW" "WCRS_CLAIM_FACT_VW",
"WCRS_POLICY_DETAIL_VW7",
"EDW_DM"."INVC_CLAIM_DTL_BRDG_FACT_VW" "INVC_CLAIM_DTL_BRDG_FACT_VW"
WHERE "INVC_DIM_VW"."INVC_DELETION_IND" <> 'Y'
AND "INVC_DIM_VW"."INVC_CONSIDRTN_TYPE_DESC" =
'Original'
AND "CURRENT_MED_INVC_RPT_DT_VW"."CURRENT_MONTH_RPT_DT" =
"ALL_INVC_SNPSHT_FACT_VW"."AS_OF_MONTH_END_DT_PK_ID"
AND "WCRS_CURRENT_CLAIM_RPT_DT_VW"."CURRENT_CLAIM_RPT_DT" =
"WCRS_CLAIM_FACT_VW"."CLAIM_REPORTING_DT"
AND "WCRS_POLICY_GROUPING_VW"."ACCOUNT_NM" =
CAST ('1ST TEAM STAFFING SERVICES, INC.' AS VARCHAR (50 CHAR))
AND "WCRS_POLICY_DETAIL_VW7"."POLICY_NBR" =
"WCRS_POLICY_GROUPING_VW"."POLICY_IDENTIFIER"
AND "WCRS_POLICY_DETAIL_VW7"."CLAIM_NBR" =
"WCRS_CLAIM_FACT_VW"."CLAIM_NBR"
AND "WCRS_POLICY_DETAIL_VW7"."CLAIM_SYMBOL_CD" =
"WCRS_CLAIM_FACT_VW"."CLAIM_SYMBOL_CD"
AND "WCRS_POLICY_DETAIL_VW7"."KEY_OFFICE_CD" =
"WCRS_CLAIM_FACT_VW"."KEY_OFFICE_CD"
AND "WCRS_POLICY_DETAIL_VW7"."CURRENT_SNAPSHOT_IND" =
'Y'
AND "WCRS_CLAIM_FACT_VW"."CLAIM_DETAIL_PK_ID" =
"WCRS_CLAIM_DETAIL_VW5"."CLAIM_DETAIL_PK_ID"
AND "WCRS_CLAIM_DETAIL_VW5"."CURRENT_SNAPSHOT_IND" =
'Y'
AND "WCRS_CLAIM_FACT_VW"."CLAIM_GID" =
"INVC_CLAIM_DTL_BRDG_FACT_VW"."CLM_GID"
AND "INVC_CLAIM_DTL_BRDG_FACT_VW"."INVC_GID" =
"INVC_DIM_VW"."INVC_GID"
AND "INVC_DIM_VW"."INVC_PK_ID" =
"INVC_ACTY_SNPSHT_FACT_VW6"."INVC_PK_ID"
AND "ALL_INVC_SNPSHT_FACT_VW"."MONTH_END_DT_PK_ID" =
"INVC_ACTY_SNPSHT_FACT_VW6"."MONTH_END_DT_PK_ID"
AND "ALL_INVC_SNPSHT_FACT_VW"."INVC_GID" =
"INVC_ACTY_SNPSHT_FACT_VW6"."INVC_GID"
AND "PROVIDER_NETWORK_DIM_VW"."PROVIDER_NETWORK_PK_ID" =
"INVC_ACTY_SNPSHT_FACT_VW6"."PPO_PROVIDER_NETWORK_PK_ID"
AND "WCRS_CURRENT_CLAIM_RPT_DT_VW"."CURRENT_CLAIM_RPT_DT" =
"WCRS_CLAIM_FACT_VW"."CLAIM_REPORTING_DT"
GROUP BY "WCRS_POLICY_GROUPING_VW"."ACCOUNT_NM",
"WCRS_CLAIM_DETAIL_VW5"."REGULATORY_STATE_CD",
CASE
WHEN "INVC_DIM_VW"."INVC_IN_OUT_OF_NETWORK_IND" =
'Y'
THEN
'In - Network'
WHEN "INVC_DIM_VW"."INVC_IN_OUT_OF_NETWORK_IND" =
'N'
THEN
'Out - Network'
ELSE
'Others'
END,
"PROVIDER_NETWORK_DIM_VW"."PROVIDER_NETWORK_NM",
"INVC_DIM_VW"."INVC_CLASS_TYPE_DESC",
CASE
WHEN (EXTRACT (
YEAR FROM ("WCRS_CLAIM_DETAIL_VW5"."LOSS_DT"))
IS NULL)
OR (EXTRACT (
MONTH FROM ("WCRS_CLAIM_DETAIL_VW5"."LOSS_DT"))
IS NULL)
THEN
NULL
ELSE
( EXTRACT (
YEAR FROM ("WCRS_CLAIM_DETAIL_VW5"."LOSS_DT"))
|| EXTRACT (
MONTH FROM ("WCRS_CLAIM_DETAIL_VW5"."LOSS_DT")))
END) "T1") "T0"
ORDER BY "Account_Name" ASC NULLS LAST,
"Accident_State_Cd" ASC NULLS LAST,
"c3" ASC NULLS LAST,
"PPO_Name" ASC NULLS LAST,
"Invc_Classification_Type_Desc" ASC NULLS LAST
  • 1. Re: Need Help in sql tuning in EXADATA environment
    949187 Newbie
    Currently Being Moderated
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 838 | 1079K (1)| 00:00:34 | | |
    | 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
    | 2 | PX COORDINATOR | | | | | | | |
    | 3 | PX SEND QC (RANDOM) | :TQ10000 | 10M| 317M| 848K (1)| 00:00:27 | | |
    | 4 | LOAD AS SELECT | SYS_TEMP_0FD9D677A_286AAA2E | | | | | | |
    | 5 | PX BLOCK ITERATOR | | 10M| 317M| 848K (1)| 00:00:27 | | |
    |* 6 | TABLE ACCESS STORAGE FULL | WCRS_CLAIM_DETAIL | 10M| 317M| 848K (1)| 00:00:27 | | |
    | 7 | PX COORDINATOR | | | | | | | |
    | 8 | PX SEND QC (RANDOM) | :TQ20000 | 10M| 268M| 44875 (1)| 00:00:02 | | |
    | 9 | LOAD AS SELECT | SYS_TEMP_0FD9D677B_286AAA2E | | | | | | |
    | 10 | PX BLOCK ITERATOR | | 10M| 268M| 44875 (1)| 00:00:02 | | |
    |* 11 | TABLE ACCESS STORAGE FULL | WCRS_POLICY_DETAIL | 10M| 268M| 44875 (1)| 00:00:02 | | |
    | 12 | PX COORDINATOR | | | | | | | |
    | 13 | PX SEND QC (ORDER) | :TQ40017 | 1 | 838 | 186K (2)| 00:00:06 | | |
    | 14 | WINDOW SORT | | 1 | 838 | 186K (2)| 00:00:06 | | |
    | 15 | PX RECEIVE | | 1 | 838 | 186K (2)| 00:00:06 | | |
    | 16 | PX SEND RANGE | :TQ40016 | 1 | 838 | 186K (2)| 00:00:06 | | |
    | 17 | NESTED LOOPS | | 1 | 838 | 186K (2)| 00:00:06 | | |
    | 18 | BUFFER SORT | | | | | | | |
    | 19 | PX RECEIVE | | | | | | | |
    | 20 | PX SEND BROADCAST | :TQ40001 | | | | | | |
    | 21 | VIEW | | 1 | 13 | 93216 (2)| 00:00:03 | | |
    | 22 | SORT GROUP BY | | 1 | 393 | | | | |
    | 23 | PX COORDINATOR | | | | | | | |
    | 24 | PX SEND QC (RANDOM) | :TQ30015 | 1 | 393 | | | | |
    | 25 | SORT GROUP BY | | 1 | 393 | | | | |
    | 26 | PX RECEIVE | | 1 | 393 | | | | |
    | 27 | PX SEND HASH | :TQ30014 | 1 | 393 | | | | |
    | 28 | SORT GROUP BY | | 1 | 393 | | | | |
    |* 29 | HASH JOIN ANTI | | 1 | 393 | 93216 (2)| 00:00:03 | | |
    | 30 | PX RECEIVE | | 1 | 376 | 85197 (2)| 00:00:03 | | |
    | 31 | PX SEND HASH | :TQ30012 | 1 | 376 | 85197 (2)| 00:00:03 | | |
    | 32 | BUFFER SORT | | 1 | 838 | | | | |
    | 33 | NESTED LOOPS | | 1 | 376 | 85197 (2)| 00:00:03 | | |
    | 34 | NESTED LOOPS | | 1 | 358 | 85197 (2)| 00:00:03 | | |
    | 35 | NESTED LOOPS | | 1 | 348 | 85197 (2)| 00:00:03 | | |
    |* 36 | HASH JOIN ANTI | | 1 | 316 | 85179 (2)| 00:00:03 | | |
    | 37 | PX RECEIVE | | 4 | 1156 | 77161 (2)| 00:00:03 | | |
    | 38 | PX SEND HASH | :TQ30010 | 4 | 1156 | 77161 (2)| 00:00:03 | | |
    |* 39 | HASH JOIN ANTI BUFFERED | | 4 | 1156 | 77161 (2)| 00:00:03 | | |
    | 40 | PX RECEIVE | | 371 | 94605 | 69142 (2)| 00:00:03 | | |
    | 41 | PX SEND HASH | :TQ30008 | 371 | 94605 | 69142 (2)| 00:00:03 | | |
    |* 42 | HASH JOIN | | 371 | 94605 | 69142 (2)| 00:00:03 | | |
    | 43 | PX RECEIVE | | 350 | 77000 | 36642 (1)| 00:00:02 | | |
    | 44 | PX SEND BROADCAST | :TQ30007 | 350 | 77000 | 36642 (1)| 00:00:02 | | |
    |* 45 | HASH JOIN | | 350 | 77000 | 36642 (1)| 00:00:02 | | |
    | 46 | PX RECEIVE | | 140 | 25200 | 28624 (1)| 00:00:01 | | |
    | 47 | PX SEND BROADCAST | :TQ30006 | 140 | 25200 | 28624 (1)| 00:00:01 | | |
    |* 48 | HASH JOIN | | 140 | 25200 | 28624 (1)| 00:00:01 | | |
    | 49 | PX RECEIVE | | 140 | 22820 | 25169 (1)| 00:00:01 | | |
    | 50 | PX SEND BROADCAST | :TQ30005 | 140 | 22820 | 25169 (1)| 00:00:01 | | |
    |* 51 | HASH JOIN BUFFERED | | 140 | 22820 | 25169 (1)| 00:00:01 | | |
    | 52 | BUFFER SORT | | | | | | | |
    | 53 | PX RECEIVE | | 9 | 306 | 5 (0)| 00:00:01 | | |
    | 54 | T PX SEND BROADCAS | :TQ30000 | 9 | 306 | 5 (0)| 00:00:01 | | |
    | 55 | INDEX ROWID TABLE ACCESS BY | WCRS_POLICY_GROUPING | 9 | 306 | 5 (0)| 00:00:01 | | |
    |* 56 | AN INDEX RANGE SC | SUK3 | 9 | | 1 (0)| 00:00:01 | | |
    |* 57 | HASH JOIN | | 9699K| 1193M| 25149 (1)| 00:00:01 | | |
    | 58 | PX RECEIVE | | 9699K| 434M| 22205 (1)| 00:00:01 | | |
    | 59 | PX SEND HASH | :TQ30003 | 9699K| 434M| 22205 (1)| 00:00:01 | | |
    | 60 | NESTED LOOPS | | 9699K| 434M| 22205 (1)| 00:00:01 | | |
    | 61 | BUFFER SORT | | | | | | | |
    | 62 | PX RECEIVE | | | | | | | |
    | 63 | DCAST PX SEND BROA | :TQ30002 | | | | | | |
    | 64 | CARTESIAN MERGE JOIN | | 1 | 14 | 13 (0)| 00:00:01 | | |
    | 65 | TERATOR PX BLOCK I | | 1 | 8 | 10 (0)| 00:00:01 | | |
    | 66 | ESS STORAGE FULL TABLE ACC | CURRENT_MED_INVC_RPT_DT | 1 | 8 | 10 (0)| 00:00:01 | | |
    | 67 | T BUFFER SOR | | 1 | 6 | 3 (0)| 00:00:01 | | |
    | 68 | E PX RECEIV | | 1 | 6 | 2 (0)| 00:00:01 | | |
    | 69 | BROADCAST PX SEND | :TQ30001 | 1 | 6 | 2 (0)| 00:00:01 | | |
    | 70 | K ITERATOR PX BLOC | | 1 | 6 | 2 (0)| 00:00:01 | | |
    | 71 | ACCESS STORAGE FULL TABLE | WCRS_CURRENT_CLAIM_RPT_DT | 1 | 6 | 2 (0)| 00:00:01 | | |
    | 72 | TOR PX BLOCK ITERA | | 9699K| 305M| 22192 (1)| 00:00:01 | KEY | KEY |
    |* 73 | STORAGE FULL TABLE ACCESS | WCRS_CURRENT_CLAIM_FACT | 9699K| 305M| 22192 (1)| 00:00:01 | KEY | KEY |
    | 74 | PX RECEIVE | | 10M| 785M| 2907 (2)| 00:00:01 | | |
    | 75 | PX SEND HASH | :TQ30004 | 10M| 785M| 2907 (2)| 00:00:01 | | |
    |* 76 | VIEW | | 10M| 785M| 2907 (2)| 00:00:01 | | |
    | 77 | TOR PX BLOCK ITERA | | 10M| 268M| 2907 (2)| 00:00:01 | | |
    | 78 | STORAGE FULL TABLE ACCESS | SYS_TEMP_0FD9D677B_286AAA2E | 10M| 268M| 2907 (2)| 00:00:01 | | |
    |* 79 | VIEW | | 10M| 168M| 3439 (2)| 00:00:01 | | |
    | 80 | PX BLOCK ITERATOR | | 10M| 317M| 3439 (2)| 00:00:01 | | |
    | 81 | E FULL TABLE ACCESS STORAG | SYS_TEMP_0FD9D677A_286AAA2E | 10M| 317M| 3439 (2)| 00:00:01 | | |
    | 82 | PX BLOCK ITERATOR | | 15M| 599M| 7994 (1)| 00:00:01 | | |
    | 83 | LL TABLE ACCESS STORAGE FU | INVC_CLAIM_DTL_BRDG_FACT | 15M| 599M| 7994 (1)| 00:00:01 | | |
    | 84 | PX BLOCK ITERATOR | | 15M| 521M| 32477 (2)| 00:00:02 | | |
    |* 85 | TABLE ACCESS STORAGE FULL | INVC_DIM | 15M| 521M| 32477 (2)| 00:00:02 | | |
    | 86 | PX RECEIVE | | 15M| 509M| 7994 (1)| 00:00:01 | | |
    | 87 | PX SEND HASH | :TQ30009 | 15M| 509M| 7994 (1)| 00:00:01 | | |
    | 88 | PX BLOCK ITERATOR | | 15M| 509M| 7994 (1)| 00:00:01 | | |
    | 89 | TABLE ACCESS STORAGE FULL | INVC_CLAIM_DTL_BRDG_FACT | 15M| 509M| 7994 (1)| 00:00:01 | | |
    | 90 | PX RECEIVE | | 15M| 404M| 7994 (1)| 00:00:01 | | |
    | 91 | PX SEND HASH | :TQ30011 | 15M| 404M| 7994 (1)| 00:00:01 | | |
    | 92 | PX BLOCK ITERATOR | | 15M| 404M| 7994 (1)| 00:00:01 | | |
    | 93 | TABLE ACCESS STORAGE FULL | INVC_CLAIM_DTL_BRDG_FACT | 15M| 404M| 7994 (1)| 00:00:01 | | |
    | 94 | TABLE ACCESS BY INDEX ROWID | INVC_ACTY_SNPSHT_FACT | 1 | 32 | 18 (0)| 00:00:01 | | |
    |* 95 | INDEX RANGE SCAN | IFK_XPKINVOICE_ACTIVITY_SNAPSH | 1 | | 1 (0)| 00:00:01 | | |
    |* 96 | INDEX UNIQUE SCAN | IFK_XPKPROVIDER_NETWORK_DIM | 1 | 10 | 0 (0)| 00:00:01 | | |
    |* 97 | INDEX RANGE SCAN | IFK_XPKALL_INVC_SNPSHT_FACT | 1 | 18 | 1 (0)| 00:00:01 | | |
    | 98 | PX RECEIVE | | 15M| 254M| 7994 (1)| 00:00:01 | | |
    | 99 | PX SEND HASH | :TQ30013 | 15M| 254M| 7994 (1)| 00:00:01 | | |
    | 100 | PX BLOCK ITERATOR | | 15M| 254M| 7994 (1)| 00:00:01 | | |
    | 101 | TABLE ACCESS STORAGE FULL | INVC_CLAIM_DTL_BRDG_FACT | 15M| 254M| 7994 (1)| 00:00:01 | | |
    | 102 | VIEW | | 1 | 825 | | | | |
    | 103 | SORT GROUP BY | | 1 | 430 | 93216 (2)| 00:00:03 | | |
    | 104 | BUFFER SORT | | | | | | | |
    | 105 | PX RECEIVE | | 1 | 430 | 93216 (2)| 00:00:03 | | |
    | 106 | PX SEND HASH | :TQ40015 | 1 | 430 | 93216 (2)| 00:00:03 | | |
    |*107 | HASH JOIN ANTI BUFFERED | | 1 | 430 | 93216 (2)| 00:00:03 | | |
    | 108 | PX RECEIVE | | 1 | 413 | 85198 (2)| 00:00:03 | | |
    | 109 | PX SEND HASH | :TQ40013 | 1 | 413 | 85198 (2)| 00:00:03 | | |
    | 110 | BUFFER SORT | | 1 | 838 | | | | |
    | 111 | NESTED LOOPS | | 1 | 413 | 85198 (2)| 00:00:03 | | |
    | 112 | NESTED LOOPS | | 1 | 395 | 85197 (2)| 00:00:03 | | |
    | 113 | NESTED LOOPS | | 1 | 369 | 85197 (2)| 00:00:03 | | |
    |*114 | HASH JOIN ANTI | | 1 | 311 | 85179 (2)| 00:00:03 | | |
    | 115 | PX RECEIVE | | 4 | 1136 | 77161 (2)| 00:00:03 | | |
    | 116 | PX SEND HASH | :TQ40011 | 4 | 1136 | 77161 (2)| 00:00:03 | | |
    |*117 | HASH JOIN ANTI BUFFERED | | 4 | 1136 | 77161 (2)| 00:00:03 | | |
    | 118 | PX RECEIVE | | 371 | 92750 | 69143 (2)| 00:00:03 | | |
    | 119 | PX SEND HASH | :TQ40009 | 371 | 92750 | 69143 (2)| 00:00:03 | | |
    |*120 | HASH JOIN | | 371 | 92750 | 69143 (2)| 00:00:03 | | |
    | 121 | PX RECEIVE | | 350 | 72450 | 36642 (1)| 00:00:02 | | |
    | 122 | PX SEND BROADCAST | :TQ40008 | 350 | 72450 | 36642 (1)| 00:00:02 | | |
    |*123 | HASH JOIN | | 350 | 72450 | 36642 (1)| 00:00:02 | | |
    | 124 | PX RECEIVE | | 140 | 23380 | 28624 (1)| 00:00:01 | | |
    | 125 | PX SEND BROADCAST | :TQ40007 | 140 | 23380 | 28624 (1)| 00:00:01 | | |
    |*126 | HASH JOIN | | 140 | 23380 | 28624 (1)| 00:00:01 | | |
    | 127 | PX RECEIVE | | 140 | 15540 | 25169 (1)| 00:00:01 | | |
    | 128 | PX SEND BROADCAST | :TQ40006 | 140 | 15540 | 25169 (1)| 00:00:01 | | |
    |*129 | HASH JOIN BUFFERED | | 140 | 15540 | 25169 (1)| 00:00:01 | | |
    | 130 | BUFFER SORT | | | | | | | |
    | 131 | PX RECEIVE | | 9 | 306 | 5 (0)| 00:00:01 | | |
    | 132 | PX SEND BROADCAST | :TQ40000 | 9 | 306 | 5 (0)| 00:00:01 | | |
    | 133 | ROWID TABLE ACCESS BY INDEX | WCRS_POLICY_GROUPING | 9 | 306 | 5 (0)| 00:00:01 | | |
    |*134 | INDEX RANGE SCAN | SUK3 | 9 | | 1 (0)| 00:00:01 | | |
    |*135 | HASH JOIN | | 9699K| 712M| 25149 (1)| 00:00:01 | | |
    | 136 | PX RECEIVE | | 10M| 287M| 2907 (2)| 00:00:01 | | |
    | 137 | PX SEND HASH | :TQ40004 | 10M| 287M| 2907 (2)| 00:00:01 | | |
    |*138 | VIEW | | 10M| 287M| 2907 (2)| 00:00:01 | | |
    | 139 | PX BLOCK ITERATOR | | 10M| 268M| 2907 (2)| 00:00:01 | | |
    | 140 | E FULL TABLE ACCESS STORAG | SYS_TEMP_0FD9D677B_286AAA2E | 10M| 268M| 2907 (2)| 00:00:01 | | |
    | 141 | PX RECEIVE | | 9699K| 434M| 22205 (1)| 00:00:01 | | |
    | 142 | PX SEND HASH | :TQ40005 | 9699K| 434M| 22205 (1)| 00:00:01 | | |
    | 143 | NESTED LOOPS | | 9699K| 434M| 22205 (1)| 00:00:01 | | |
    | 144 | BUFFER SORT | | | | | | | |
    | 145 | PX RECEIVE | | | | | | | |
    | 146 | PX SEND BROADCAST | :TQ40003 | | | | | | |
    | 147 | IAN MERGE JOIN CARTES | | 1 | 14 | 13 (0)| 00:00:01 | | |
    | 148 | R PX BLOCK ITERATO | | 1 | 8 | 10 (0)| 00:00:01 | | |
    | 149 | ORAGE FULL TABLE ACCESS ST | CURRENT_MED_INVC_RPT_DT | 1 | 8 | 10 (0)| 00:00:01 | | |
    | 150 | BUFFER SORT | | 1 | 6 | 3 (0)| 00:00:01 | | |
    | 151 | PX RECEIVE | | 1 | 6 | 2 (0)| 00:00:01 | | |
    | 152 | AST PX SEND BROADC | :TQ40002 | 1 | 6 | 2 (0)| 00:00:01 | | |
    | 153 | ATOR PX BLOCK ITER | | 1 | 6 | 2 (0)| 00:00:01 | | |
    | 154 | STORAGE FULL TABLE ACCESS | WCRS_CURRENT_CLAIM_RPT_DT | 1 | 6 | 2 (0)| 00:00:01 | | |
    | 155 | PX BLOCK ITERATOR | | 9699K| 305M| 22192 (1)| 00:00:01 | KEY | KEY |
    |*156 | E FULL TABLE ACCESS STORAG | WCRS_CURRENT_CLAIM_FACT | 9699K| 305M| 22192 (1)| 00:00:01 | KEY | KEY |
    |*157 | VIEW | | 10M| 555M| 3439 (2)| 00:00:01 | | |
    | 158 | PX BLOCK ITERATOR | | 10M| 317M| 3439 (2)| 00:00:01 | | |
    | 159 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D677A_286AAA2E | 10M| 317M| 3439 (2)| 00:00:01 | | |
    | 160 | PX BLOCK ITERATOR | | 15M| 599M| 7994 (1)| 00:00:01 | | |
    | 161 | TABLE ACCESS STORAGE FULL | INVC_CLAIM_DTL_BRDG_FACT | 15M| 599M| 7994 (1)| 00:00:01 | | |
    | 162 | PX BLOCK ITERATOR | | 15M| 641M| 32477 (2)| 00:00:02 | | |
    |*163 | TABLE ACCESS STORAGE FULL | INVC_DIM | 15M| 641M| 32477 (2)| 00:00:02 | | |
    | 164 | PX RECEIVE | | 15M| 509M| 7994 (1)| 00:00:01 | | |
    | 165 | PX SEND HASH | :TQ40010 | 15M| 509M| 7994 (1)| 00:00:01 | | |
    | 166 | PX BLOCK ITERATOR | | 15M| 509M| 7994 (1)| 00:00:01 | | |
    | 167 | TABLE ACCESS STORAGE FULL | INVC_CLAIM_DTL_BRDG_FACT | 15M| 509M| 7994 (1)| 00:00:01 | | |
    | 168 | PX RECEIVE | | 15M| 404M| 7994 (1)| 00:00:01 | | |
    | 169 | PX SEND HASH | :TQ40012 | 15M| 404M| 7994 (1)| 00:00:01 | | |
    | 170 | PX BLOCK ITERATOR | | 15M| 404M| 7994 (1)| 00:00:01 | | |
    | 171 | TABLE ACCESS STORAGE FULL | INVC_CLAIM_DTL_BRDG_FACT | 15M| 404M| 7994 (1)| 00:00:01 | | |
    | 172 | TABLE ACCESS BY INDEX ROWID | INVC_ACTY_SNPSHT_FACT | 1 | 58 | 18 (0)| 00:00:01 | | |
    |*173 | INDEX RANGE SCAN | IFK_XPKINVOICE_ACTIVITY_SNAPSH | 1 | | 1 (0)| 00:00:01 | | |
    | 174 | TABLE ACCESS BY INDEX ROWID | PROVIDER_NETWORK_DIM | 1 | 26 | 0 (0)| 00:00:01 | | |
    |*175 | INDEX UNIQUE SCAN | IFK_XPKPROVIDER_NETWORK_DIM | 1 | | 0 (0)| 00:00:01 | | |
    |*176 | INDEX RANGE SCAN | IFK_XPKALL_INVC_SNPSHT_FACT | 1 | 18 | 1 (0)| 00:00:01 | | |
    | 177 | PX RECEIVE | | 15M| 254M| 7994 (1)| 00:00:01 | | |
    | 178 | PX SEND HASH | :TQ40014 | 15M| 254M| 7994 (1)| 00:00:01 | | |
    | 179 | PX BLOCK ITERATOR | | 15M| 254M| 7994 (1)| 00:00:01 | | |
    | 180 | TABLE ACCESS STORAGE FULL | INVC_CLAIM_DTL_BRDG_FACT | 15M| 254M| 7994 (1)| 00:00:01 | | |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
  • 2. Re: Need Help in sql tuning in EXADATA environment
    949187 Newbie
    Currently Being Moderated
    Predicate Information (identified by operation id):
    ---------------------------------------------------

    6 - storage("CURRENT_SNAPSHOT_IND"='Y')
    filter("CURRENT_SNAPSHOT_IND"='Y')
    11 - storage("CURRENT_SNAPSHOT_IND"='Y')
    filter("CURRENT_SNAPSHOT_IND"='Y')
    29 - access("A"."INVC_GID"="B"."INVC_GID")
    filter("B"."SRCE_EFF_START_TMSP">"A"."SRCE_EFF_START_TMSP")

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    36 - access("A"."INVC_GID"="C"."INVC_GID" AND "C"."SRCE_EFF_START_TMSP"="A"."SRCE_EFF_START_TMSP")
    filter("C"."INVC_PK_ID">"A"."INVC_PK_ID")
    39 - access("A"."INVC_GID"="D"."INVC_GID" AND "D"."SRCE_EFF_START_TMSP"="A"."SRCE_EFF_START_TMSP" AND "D"."INVC_PK_ID"="A"."INVC_PK_ID")
    filter("D"."CLAIM_DETAIL_PK_ID">"A"."CLAIM_DETAIL_PK_ID")
    42 - access("INVC_GID"="INVC_GID")
    45 - access("CLAIM_GID"="CLM_GID")
    48 - access("CLAIM_DETAIL_PK_ID"="WCRS_CLAIM_DETAIL_VW5"."CLAIM_DETAIL_PK_ID")
    51 - access("WCRS_POLICY_DETAIL_VW7"."POLICY_NBR"="POLICY_IDENTIFIER")
    56 - access("ACCOUNT_NM"=CAST('1ST TEAM STAFFING SERVICES, INC.' AS VARCHAR (50 CHAR)) AND "CURRENT_SNAPSHOT_IND"='Y')
    57 - access("WCRS_POLICY_DETAIL_VW7"."CLAIM_NBR"="CLAIM_NBR" AND "WCRS_POLICY_DETAIL_VW7"."CLAIM_SYMBOL_CD"="CLAIM_SYMBOL_CD" AND
    "WCRS_POLICY_DETAIL_VW7"."KEY_OFFICE_CD"="KEY_OFFICE_CD")
    73 - storage("CURRENT_CLAIM_RPT_DT"="CLAIM_REPORTING_DT")
    filter("CURRENT_CLAIM_RPT_DT"="CLAIM_REPORTING_DT")
    76 - filter("WCRS_POLICY_DETAIL_VW7"."CURRENT_SNAPSHOT_IND"='Y')
    79 - filter("WCRS_CLAIM_DETAIL_VW5"."CURRENT_SNAPSHOT_IND"='Y')
    85 - storage("INVC_CONSIDRTN_TYPE_DESC"='Original' AND "INVC_DELETION_IND"<>'Y' AND SYS_OP_BLOOM_FILTER(:BF0000,"INVC_GID"))
    filter("INVC_CONSIDRTN_TYPE_DESC"='Original' AND "INVC_DELETION_IND"<>'Y' AND SYS_OP_BLOOM_FILTER(:BF0000,"INVC_GID"))
    95 - access("INVC_PK_ID"="INVC_PK_ID")
    96 - access("PROVIDER_NETWORK_PK_ID"="PPO_PROVIDER_NETWORK_PK_ID")
    97 - access("INVC_GID"="INVC_GID" AND "MONTH_END_DT_PK_ID"="MONTH_END_DT_PK_ID" AND
    "AS_OF_MONTH_END_DT_PK_ID"=TO_NUMBER(TO_CHAR(LAST_DAY(INTERNAL_FUNCTION("DT_CURRENT_INVC_RPT_DT")),'YYYYMMDD')))
    filter("AS_OF_MONTH_END_DT_PK_ID"=TO_NUMBER(TO_CHAR(LAST_DAY(INTERNAL_FUNCTION("DT_CURRENT_INVC_RPT_DT")),'YYYYMMDD')) AND
    "MONTH_END_DT_PK_ID"="MONTH_END_DT_PK_ID")
    107 - access("A"."INVC_GID"="B"."INVC_GID")
    filter("B"."SRCE_EFF_START_TMSP">"A"."SRCE_EFF_START_TMSP")
    114 - access("A"."INVC_GID"="C"."INVC_GID" AND "C"."SRCE_EFF_START_TMSP"="A"."SRCE_EFF_START_TMSP")
    filter("C"."INVC_PK_ID">"A"."INVC_PK_ID")
    117 - access("A"."INVC_GID"="D"."INVC_GID" AND "D"."SRCE_EFF_START_TMSP"="A"."SRCE_EFF_START_TMSP" AND "D"."INVC_PK_ID"="A"."INVC_PK_ID")
    filter("D"."CLAIM_DETAIL_PK_ID">"A"."CLAIM_DETAIL_PK_ID")
    120 - access("INVC_GID"="INVC_GID")
    123 - access("CLAIM_GID"="CLM_GID")
    126 - access("CLAIM_DETAIL_PK_ID"="WCRS_CLAIM_DETAIL_VW5"."CLAIM_DETAIL_PK_ID")
    129 - access("WCRS_POLICY_DETAIL_VW7"."POLICY_NBR"="POLICY_IDENTIFIER")
    134 - access("ACCOUNT_NM"=CAST('1ST TEAM STAFFING SERVICES, INC.' AS VARCHAR (50 CHAR)) AND "CURRENT_SNAPSHOT_IND"='Y')
    135 - access("WCRS_POLICY_DETAIL_VW7"."CLAIM_NBR"="CLAIM_NBR" AND "WCRS_POLICY_DETAIL_VW7"."CLAIM_SYMBOL_CD"="CLAIM_SYMBOL_CD" AND
    "WCRS_POLICY_DETAIL_VW7"."KEY_OFFICE_CD"="KEY_OFFICE_CD")
    138 - filter("WCRS_POLICY_DETAIL_VW7"."CURRENT_SNAPSHOT_IND"='Y')
    156 - storage("CURRENT_CLAIM_RPT_DT"="CLAIM_REPORTING_DT")
    filter("CURRENT_CLAIM_RPT_DT"="CLAIM_REPORTING_DT")
    157 - filter("WCRS_CLAIM_DETAIL_VW5"."CURRENT_SNAPSHOT_IND"='Y')
    163 - storage("INVC_CONSIDRTN_TYPE_DESC"='Original' AND "INVC_DELETION_IND"<>'Y')
    filter("INVC_CONSIDRTN_TYPE_DESC"='Original' AND "INVC_DELETION_IND"<>'Y')
    173 - access("INVC_PK_ID"="INVC_PK_ID")
    175 - access("PROVIDER_NETWORK_PK_ID"="PPO_PROVIDER_NETWORK_PK_ID")
    176 - access("INVC_GID"="INVC_GID" AND "MONTH_END_DT_PK_ID"="MONTH_END_DT_PK_ID" AND
    "AS_OF_MONTH_END_DT_PK_ID"=TO_NUMBER(TO_CHAR(LAST_DAY(INTERNAL_FUNCTION("DT_CURRENT_INVC_RPT_DT")),'YYYYMMDD')))
    filter("AS_OF_MONTH_END_DT_PK_ID"=TO_NUMBER(TO_CHAR(LAST_DAY(INTERNAL_FUNCTION("DT_CURRENT_INVC_RPT_DT")),'YYYYMMDD')) AND
    "MONTH_END_DT_PK_ID"="MONTH_END_DT_PK_ID")

    Note
    -----
    - dynamic sampling used for this statement (level=4)
    - automatic DOP: Computed Degree of Parallelism is 4 because of degree limit

    250 rows selected.

    Elapsed: 00:00:00.05
    19:52:28 SQL>
  • 3. Re: Need Help in sql tuning in EXADATA environment
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    welcome to the forum! Please always use
     tags when posting plans (and use the preview tab to make sure you placed them correctly), otherwise they are unreadable. Also, it would be much easier to tune the query if we had rowsource statistics (to produce, do ALTER SESSION SET STATISTICS_LEVEL = ALL or place gather_plan_statistics hint inside the statement, and use format=>'iostats last' option when displaying the plan with dbms_xplan.display_cursor). Or post the output of SQL real-time monitor, it's even simpler to obtain.
    
    Best regards,
      Nikolay                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 4. Re: Need Help in sql tuning in EXADATA environment
    Mohamed Houri Pro
    Currently Being Moderated
    * 6      TABLE ACCESS STORAGE FULL      WCRS_CLAIM_DETAIL      10M      317M      848K (1)      00:00:27
    
    6 - storage("CURRENT_SNAPSHOT_IND"='Y')
    filter("CURRENT_SNAPSHOT_IND"='Y')
    The presence of STORAGE FULL in the execution plan together with the option storage ( ) in the predicate part of this execution plan is an indication that your query is able to do Smart Scan and also a predicate offload reducing the amount of data flowing between the cellserv and the database server tier. That's already a good point.

    You could use tanel poder snapper (http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper) to see what is going on behind the scene when your query is being executed

    Best Regards

    Mohamed Houri
    www.hourim.wordpress.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points