1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help in fine tuning the View

Discussion in 'SQL PL/SQL' started by cikamani, Oct 26, 2009.

  1. cikamani

    cikamani Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    We have a performance issue for a view. The view 'vw_imt_ticket_metrics' which is built with the combination of - ticket_metric(materialized view) and REFWKCTR_AP_MAP (local table)

    The requirement is : when rm.REF_WK_CTR=tm.REF_WK_CTR_ID for BMP Tickets , then it should pick rm.ACCESS_PROVIDER_NAME column
    else tm.ACCESS_PROVIDER_NAME, rest all columns for the view vw_imt_ticket_metrics will be picked from ticket_metric itself.

    ticket_metric is again a materialized view created on ITL DB thru a DB Link and REFWKCTR_AP_MAP is local table in our DB.

    ticket_number, data_source column are indexed in ticket_metric and REF_WK_CTR is indexed in REFWKCTR_AP_MAP table.

    Options already tried :
    1)Creating vw_imt_ticket_metrics as materialized view on ticket_metric is fetching data fast but it is leading to slow refresh of main view (ticket_metric ) itself.
    2)Using union all also working fine but reteriving duplicate data
    3) Forcing index is also of not much help

    Attached are view defination and explain plan. Can anyone please help in fine tuning it or suggesting some other way to create the view with this requirement.

    Query to get data from this view is taking 12 -15 sec. Need to Fine tune for 1-2 seconds.



    Code (SQL):


    CREATE OR REPLACE VIEW vw_imt_ticket_metrics AS
    SELECT
       ticket_number,
    -- t1.MR_REVISION,
     ASSET_ID,
       SERVICE_ID_TYPE,
     CUSTOMER_CHRONIC,
     CLIENT_NAME,
       ICORE_MCN,
       MCN,
       TROUBLE_REPORTED_DATE,
     TICKET_SOURCE,
       SERVICE_CLASSIFICATION,
       TICKET_TYPE,
     tier1_wk_ctr_id,
        decode(data_source,'AOTS',ticket_state) AS ticket_state,
     tier2_wk_ctr_id,
       TIER2_WKLST_CATEGORY_CODE,
       ASSIGNED_TO_WK_CTR,
       MR_WORK_LOGIN_ID,
       TECH_PRIORITY,
       trouble_code,
     HEADQUARTERS_STUDY_CODE,
     WORK_CENTER_STUDY_CODE,
       CLEARANCE_SUMMARY,
       ANALYSIS_CODE,
       CLEARANCE_CODE,
       mr_clear_date,
       RESOLUTION_SERVICE_LINE,
     MARKET_SEGMENTATION_INDICATOR,
       FEATURE_CODE,
       UNRESOLVED_INDICATOR,
       SYS_DETMN_RECURRING_INDICATOR,
       next_check_date,
       tier2_next_check,
       NFO_MRID,
       LINK_TYPE,
       IATM_SVC_INDICATOR,
       mttr_at_refresh,
       mttr_delay_factor,
       reported_by_phone,
    -- em_flag_due_at_refresh,
    -- em_flag_due_delay_factor, (mttr_at_refresh-(sysdate-last_metric_refresh))
       new_wait_pickup_at_refresh,
    -- new_wait_pickup_delay_factor,
       supplier_esc_due_at_refresh,
    -- supplier_esc_due_delay_factor, (sysdate-lsst_metric_refresh)
       cust_prog_due_at_refresh,
    -- cust_prog_due_delay_factor,(sysdate-last_metric_refresh)
       last_metric_refresh,
    -- ticket_need_refresh_flag, last_updated<last_metric_refresh
       ext_esc_level_code,
       int_esc_level_code,
       tm.Access_provider_name Access_provider_name,
       open_nadm_indicator,
       e_flag_due_at_refresh,
       r_flag_due_at_refresh,
       m_flag_due_at_refresh,
       ref_start_date,
       last_ref_acty_date,
       next_check_due,
       TIER2_NEXT_CHECK_DUE,
       eb_msg_flag,
       eb_msg_flag_date,
       mr_test_flag,
       mr_test_flag_date,
       wms_msg_flag,
       wms_msg_flag_date,
     electronic_bonding_flag,
       act_at_refresh,
       tst_at_refresh,
       tier1_abbrv_code,
       tier2_abbrv_code,
       type_of_service,
       common_cust_identifier,
       ts2_at_refresh,
       data_source,
       client_ticket__,
        BRIDGE_SYS_TKT_ID,
        last_updated,
       AVAIL_STATUS,
      TRBL_REPO_USER_STATUS,
    TICKET_CHRONIC_COUNT,
    ASSET_TYPE,
    HANDLING_INDICATOR,
    TELECOM_SVC_PRIORITY_VAL,
    CITY_DATABASE,
    ESCALATION,
    COMPONENTS,
    ACTIVITY_RESPONSE_TIME,
    EXT_ESC_INTERVAL,
    EXT_PROG_INTERVAL,
    CUST_FACING_MSG_FLAG,
    NEXT_PROG_DUE,
    LAST_EXT_PROG,
    TIER1_CATEGORY_DESC,
    TIER1_SORT_ORDER,
    TIER1_RNT_TIER1_INDICATOR,
    TIER2_CATEGORY_DESC,
    TIER2_SORT_ORDER,
    TIER2_RNT_TIER1_INDICATOR,
    LAST_ESC_ACTIVITY,
    CKL_COMPANY_ID,
    RESTORE_D_M_TIME__MIN,
    RESTORE_N_A_TIME__MIN,
    REPAIR_N_A_TIME__MIN,
    ADJTIMETOREPAIR,
    SEVERITY,
    TEST_FLAG,
    LINK_FLAG,
    CUSTOMER_MESSAGE_FLAG,
    END_USER_NAME,
    LOCATION_STATE_PROVINCE,
    LOCATION_ADDRESS_1,
    LOCATION_ADDRESS_2,
    LOCATION_ZIPPOSTAL,
    COMPONENT,
    PARENT_TICKET_NUMBER,
    LOCATION_ID,
    RESTRICTION_TYPE,
    REF_MTCE_REQ_ID,
    CKL_COMPANY_NAME,
    CPE_TYPE,
    CPE_VENDOR,
    CKL_FLR,
    CKL_RM,
    CKL_CITY_STATE,
    CKL_COUNTRY,
    A_CLLI,
    Z_CLLI,
    RESTORAL_INDICATOR,
    ACCESS_CIRCUIT_ID,
    CIRCUIT_SEGMENT_NUMBER,
    LOCAL_SERVICE_INDICATOR,
    COUNTRY_CODE,
    ATI,
    LSO_CLLI,
    CKL_NO,
    TYPE_CODE,
    FUNCTIONAL_AREA,
    WMS_TICKET_NUM,
    REF_WK_CTR_ID,
    ETTR_DATE,
    IGEMS_IND,
    CUST_ATTITUDE_IND,
    SECURE_ACCESS,
    TIER1_WKLST_CATEGORY_CODE,
    TIER1_AVAIL_PICKUP_DATE,
    TIER2_AVAIL_PICKUP_DATE,
    TELE_AUTH,
    REPORTED_BY,
    ACCOUNT_CONTACT,
    BACKUP,
    CHILD_TICKET_NUMBER,
    GC_TICKET_NUMBER,
    GP_TICKET_NUMBER,
    LMOS_FL3,
    WFAC_REGION,
    CURR_COMMIT_DT,
     LOG_FCT,
     CAC,
     LOG_FCT_CODE,
     LOG_ENTRY_DATE,
     IRATE,
     CUST_BLLNG,
     CUR_GRP,
     LATA,
     ACNA,
     GROUP_NAME,
     GROUP_TYPE,
     LOC_A,
     LOC_Z,
     RESOLUTION_ITEM,
     GROUP_TICKET,
     ASSIGNED_TO
    FROM ticket_metric tm WHERE ticket_number IS NOT NULL AND
    ( (data_source = 'WFAC_CC') OR
    (data_source = 'AOTS' AND TIER1_WK_CTR_ID NOT IN ('US-TEST-ORT')) OR (data_source='BMP'))
    UNION
    SELECT /*+ index (rm IND_REFWKCTR_AP_MAP_1) */
        ticket_number,
    -- t1.MR_REVISION,
     ASSET_ID,
       SERVICE_ID_TYPE,
     CUSTOMER_CHRONIC,
     CLIENT_NAME,
       ICORE_MCN,
       MCN,
       TROUBLE_REPORTED_DATE,
     TICKET_SOURCE,
       SERVICE_CLASSIFICATION,
       TICKET_TYPE,
     tier1_wk_ctr_id,
        decode(data_source,'AOTS',ticket_state) AS ticket_state,
     tier2_wk_ctr_id,
       TIER2_WKLST_CATEGORY_CODE,
       ASSIGNED_TO_WK_CTR,
       MR_WORK_LOGIN_ID,
       TECH_PRIORITY,
       trouble_code,
     HEADQUARTERS_STUDY_CODE,
     WORK_CENTER_STUDY_CODE,
       CLEARANCE_SUMMARY,
       ANALYSIS_CODE,
       CLEARANCE_CODE,
       mr_clear_date,
       RESOLUTION_SERVICE_LINE,
     MARKET_SEGMENTATION_INDICATOR,
       FEATURE_CODE,
       UNRESOLVED_INDICATOR,
       SYS_DETMN_RECURRING_INDICATOR,
       next_check_date,
       tier2_next_check,
       NFO_MRID,
       LINK_TYPE,
       IATM_SVC_INDICATOR,
       mttr_at_refresh,
       mttr_delay_factor,
       reported_by_phone,
    -- em_flag_due_at_refresh,
    -- em_flag_due_delay_factor, (mttr_at_refresh-(sysdate-last_metric_refresh))
       new_wait_pickup_at_refresh,
    -- new_wait_pickup_delay_factor,
       supplier_esc_due_at_refresh,
    -- supplier_esc_due_delay_factor, (sysdate-lsst_metric_refresh)
       cust_prog_due_at_refresh,
    -- cust_prog_due_delay_factor,(sysdate-last_metric_refresh)
       last_metric_refresh,
    -- ticket_need_refresh_flag, last_updated<last_metric_refresh
       ext_esc_level_code,
       int_esc_level_code,
       rm.ACCESS_PROVIDER_NAME Access_provider_name,
       open_nadm_indicator,
       e_flag_due_at_refresh,
       r_flag_due_at_refresh,
       m_flag_due_at_refresh,
       ref_start_date,
       last_ref_acty_date,
       next_check_due,
       TIER2_NEXT_CHECK_DUE,
       eb_msg_flag,
       eb_msg_flag_date,
       mr_test_flag,
       mr_test_flag_date,
       wms_msg_flag,
       wms_msg_flag_date,
     electronic_bonding_flag,
       act_at_refresh,
       tst_at_refresh,
       tier1_abbrv_code,
       tier2_abbrv_code,
       type_of_service,
       common_cust_identifier,
       ts2_at_refresh,
       data_source,
       client_ticket__,
        BRIDGE_SYS_TKT_ID,
        last_updated,
       AVAIL_STATUS,
      TRBL_REPO_USER_STATUS,
    TICKET_CHRONIC_COUNT,
    ASSET_TYPE,
    HANDLING_INDICATOR,
    TELECOM_SVC_PRIORITY_VAL,
    CITY_DATABASE,
    ESCALATION,
    COMPONENTS,
    ACTIVITY_RESPONSE_TIME,
    EXT_ESC_INTERVAL,
    EXT_PROG_INTERVAL,
    CUST_FACING_MSG_FLAG,
    NEXT_PROG_DUE,
    LAST_EXT_PROG,
    TIER1_CATEGORY_DESC,
    TIER1_SORT_ORDER,
    TIER1_RNT_TIER1_INDICATOR,
    TIER2_CATEGORY_DESC,
    TIER2_SORT_ORDER,
    TIER2_RNT_TIER1_INDICATOR,
    LAST_ESC_ACTIVITY,
    CKL_COMPANY_ID,
    RESTORE_D_M_TIME__MIN,
    RESTORE_N_A_TIME__MIN,
    REPAIR_N_A_TIME__MIN,
    ADJTIMETOREPAIR,
    SEVERITY,
    TEST_FLAG,
    LINK_FLAG,
    CUSTOMER_MESSAGE_FLAG,
    END_USER_NAME,
    LOCATION_STATE_PROVINCE,
    LOCATION_ADDRESS_1,
    LOCATION_ADDRESS_2,
    LOCATION_ZIPPOSTAL,
    COMPONENT,
    PARENT_TICKET_NUMBER,
    LOCATION_ID,
    RESTRICTION_TYPE,
    REF_MTCE_REQ_ID,
    CKL_COMPANY_NAME,
    CPE_TYPE,
    CPE_VENDOR,
    CKL_FLR,
    CKL_RM,
    CKL_CITY_STATE,
    CKL_COUNTRY,
    A_CLLI,
    Z_CLLI,
    RESTORAL_INDICATOR,
    ACCESS_CIRCUIT_ID,
    CIRCUIT_SEGMENT_NUMBER,
    LOCAL_SERVICE_INDICATOR,
    COUNTRY_CODE,
    ATI,
    LSO_CLLI,
    CKL_NO,
    TYPE_CODE,
    FUNCTIONAL_AREA,
    WMS_TICKET_NUM,
    REF_WK_CTR_ID,
    ETTR_DATE,
    IGEMS_IND,
    CUST_ATTITUDE_IND,
    SECURE_ACCESS,
    TIER1_WKLST_CATEGORY_CODE,
    TIER1_AVAIL_PICKUP_DATE,
    TIER2_AVAIL_PICKUP_DATE,
    TELE_AUTH,
    REPORTED_BY,
    ACCOUNT_CONTACT,
    BACKUP,
    CHILD_TICKET_NUMBER,
    GC_TICKET_NUMBER,
    GP_TICKET_NUMBER,
    LMOS_FL3,
    WFAC_REGION,
    CURR_COMMIT_DT,
     LOG_FCT,
     CAC,
     LOG_FCT_CODE,
     LOG_ENTRY_DATE,
     IRATE,
     CUST_BLLNG,
     CUR_GRP,
     LATA,
     ACNA,
     GROUP_NAME,
     GROUP_TYPE,
     LOC_A,
     LOC_Z,
     RESOLUTION_ITEM,
     GROUP_TICKET,
     ASSIGNED_TO
    FROM ticket_metric tm,REFWKCTR_AP_MAP rm
    WHERE ticket_number IS NOT NULL AND
    (data_source = 'BMP' AND (rm.REF_WK_CTR=tm.REF_WK_CTR_ID AND tm.REF_WK_CTR_ID IS NOT NULL))  
     ;


     

    The Execution Plan for query is attached. It is taking 12 -15 sec. Need to Fine tune for 1-2 seconds.
     

    Attached Files:

  2. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    just optimize your query in PL/SQL Developer in Explain Plain, there you will easily find out the reson why your view is getting data in 15 to 20 seconds.....
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The plan has already been posted; have you not examined it? The major contributor to the long query time is the SORT UNIQUE operation due to the UNION operators. Not having test data I cannot run the query and test options to see if the UNION can be rewritten in a more efficient manner.

    I had hoped you had actually supplied usable suggestions rather than ask that the plan be posted again.