1 Reply Latest reply: Oct 9, 2012 6:13 AM by odie_63 RSS

    Tuning xml

    944750
      Hi,
      I have 2 tables as below (in similar format) having CLOB type column and these are range partitioned on the value_date column.

      CREATE TABLE I_TAB_A
      (
      STG_RECORD_ID NUMBER NOT NULL,
      STG_LOAD_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      STG_STATUS_FLAG VARCHAR2(1 CHAR) DEFAULT 'V' NOT NULL,
      VALUE_DATE DATE,
      MESSAGE_XML CLOB NOT NULL
      )

      And, i am creating a view to select from these 2 tables. but following select is taking long time. Can someone suggest me how i can tune this select ?

      oracle DB and client version are as below:
      ============================================

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      With the Partitioning option

      SQL> select * from v$version;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE 11.2.0.2.0 Production
      TNS for Linux: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
      SQL>


      ============================================

      --CREATE OR REPLACE VIEW V_A_MOV
      --AS
      SELECT glc.stg_record_id                stg_record_id
      , glc.value_date stg_cob_date
      , glc.msg_type msg_type
      , mh.business_date business_date
      , mh.event_datetime event_datetime
      , SUBSTR(mh.time_context_ref,1,255) time_context_ref
      , mh.e_transfer_id e_transfer_id
      , mh.e_transfer_id_1 e_transfer_id_1
      , gi.setmt_status setmt_status
      , glc.msg_type||gi.setmt_id setmt_id
      , DECODE (glc.msg_type, 'GEQ', 'Glacier Equities'
      , 'Glacier Credit/Rates') setmt_system_name
      , rgi1.business_line
      , rgi1.ops_team
      , gi.ext_gateway external_gateway
      , nt.value_date value_date
      , nt.value_date_1 value_date_1
      , nt.payer_pty_ref payer_pty_ref
      , nt.rec_pty_ref rec_pty_ref
      , nt.setmt_ccy setmt_ccy
      , nt.net_setmt_amount net_setmt_amount
      , nt.netted_payment netted_payment
      , nt.setmt_instruction_type setmt_instruction_type
      , nt.swift_mt swift_mt
      , si.correspondent_id correspondent_id
      , si.correspondent_id_type correspondent_id_type
      , si.correspondent_acct_name correspondent_acct_name
      , si.correspondent_acct_no correspondent_acct_no
      , si.correspondent_sub_acct_no correspondent_sub_acct_no
      , si.benf_bank_id benf_bank_id
      , si.benf_bank_id_type benf_bank_id_type
      , si.benf_bank_name benf_bank_name
      , si.benf_bank_acct_no benf_bank_acct_no
      , si.benf_bank_sub_acct_no benf_bank_sub_acct_no
      , si.benf_bank_ref_text benf_bank_ref_text
      , si.benficiary_name benficiary_name
      , si.benficiary_acct_no benficiary_acct_no
      , si.benficiary_sub_acct_no benficiary_sub_acct_no
      , SUBSTR(TRIM(REPLACE(REPLACE(XMLQUERY('settlementInstruction/beneficiary/routingExplicitDetails/routingAddress/streetAddress/streetLine' PASSING sa.COLUMN_VALUE RETURNING CONTENT)
      ,'<streetLine>' , ''), '</streetLine>' , '')),1,2000) benficiary_street_line
      , si.benficiary_city benficiary_city
      , si.benficiary_state benficiary_state
      , si.benficiary_country benficiary_country
      , si.benficiary_postalCode benficiary_postalCode
      , si.benficiary_id benficiary_id
      , si.benficiary_id_type benficiary_id_type
      , si.benficiary_name_1 benficiary_name_1
      , si.benficiary_acct_no_1 benficiary_acct_no_1
      , si.benficiary_sub_acct_no_1 benficiary_sub_acct_no_1
      , SUBSTR(TRIM(REPLACE(REPLACE(XMLQUERY('settlementInstruction/beneficiary/routingIdsAndExplicitDetails/routingAddress/streetAddress/streetLine' PASSING sa.COLUMN_VALUE RETURNING CONTENT)
      ,'<streetLine>' , ''), '</streetLine>' , '')),1,2000) benficiary_street_line_1
      , si.benficiary_city_1 benficiary_city_1
      , si.benficiary_state_1 benficiary_state_1
      , si.benficiary_country_1 benficiary_country_1
      , si.benficiary_postalCode_1 benficiary_postalCode_1
      , pty.book_ent_persp book_ent_persp
      , pty.party_id_1 party_id_1
      , pty.party_id_2 party_id_2
      , UPPER(pty.party_internal_1) party_internal_1
      , UPPER(pty.party_internal_2) party_internal_2
      , UPPER(pty.party_role_1) party_role_1
      , UPPER(pty.party_role_2) party_role_2
      , pty.party_1_name_1 party_1_name_1
      , pty.party_1_name_2 party_1_name_2
      , pty.party_2_name_1 party_2_name_1
      , pty.party_2_name_2 party_2_name_2
      , pty.party_3_name_1 party_3_name_1
      , pty.party_3_name_2 party_3_name_2
      , pty.party_4_name_1 party_4_name_1
      , pty.party_4_name_2 party_4_name_2
      , pty.party_5_name_1 party_5_name_1
      , pty.party_5_name_2 party_5_name_2
      , pty.party_1_id_1 party_1_id_1
      , pty.party_1_id_2 party_1_id_2
      , pty.party_2_id_1 party_2_id_1
      , pty.party_2_id_2 party_2_id_2
      , pty.party_3_id_1 party_3_id_1
      , pty.party_3_id_2 party_3_id_2
      , pty.party_4_id_1 party_4_id_1
      , pty.party_4_id_2 party_4_id_2
      , pty.party_5_id_1 party_5_id_1
      , pty.party_5_id_2 party_5_id_2
      , UPPER(pty.party_1_id_type_1) party_1_id_type_1
      , UPPER(pty.party_1_id_type_2) party_1_id_type_2
      , UPPER(pty.party_2_id_type_1) party_2_id_type_1
      , UPPER(pty.party_2_id_type_2) party_2_id_type_2
      , UPPER(pty.party_3_id_type_1) party_3_id_type_1
      , UPPER(pty.party_3_id_type_2) party_3_id_type_2
      , UPPER(pty.party_4_id_type_1) party_4_id_type_1
      , UPPER(pty.party_4_id_type_2) party_4_id_type_2
      , UPPER(pty.party_5_id_type_1) party_5_id_type_1
      , UPPER(pty.party_5_id_type_2) party_5_id_type_2
      FROM (select glc1.*, 'GEQ' msg_type from i_tab_a glc1 where value_date = :l_cob_date
      UNION ALLL
      select glc2.*, 'GCR' msg_type from i_tab_b glc2 where value_date = :l_cob_date) glc
      , XMLTABLE( 'emml/messageHeader' PASSING XMLTYPE(glc.MESSAGE_XML)
      COLUMNS business_date VARCHAR2(255) PATH 'businessDate/text()'
      , event_datetime VARCHAR2(255) PATH 'eventDateTime/text()'
      , time_context_ref VARCHAR2(255) PATH 'eventDateTime/@timeContextReference'
      , e_transfer_id VARCHAR2(255) PATH 'sourceSystemMessageIdentifier'
      , e_transfer_id_1 VARCHAR2(255) PATH 'sourceSystemMessageInstigator'
      ) mh
      , XMLTABLE( 'emml/newTransfer/settlementInformation/settlementInstruction' PASSING XMLTYPE(glc.MESSAGE_XML)) sa
      , XMLTABLE( 'emml/transferEventHeader' PASSING XMLTYPE(glc.MESSAGE_XML)
      COLUMNS setmt_id VARCHAR2(255) PATH 'transferGroupIdentifier/transferGroupId'
      , setmt_system_name VARCHAR2(255) PATH 'transferGroupIdentifier/systemReference'
      , setmt_status VARCHAR2(255) PATH 'additionalComments'
      , ext_gateway VARCHAR2(255) PATH 'externalGateway'
      , group_class_scheme VARCHAR2(255) PATH 'transferGroupIdentifier/transferGroupClassificationScheme'
      ) gi
      , ( SELECT glc.stg_record_id stg_record_id
      , MAX(CASE
      WHEN rgi.group_type = 'Operations Processing Group' THEN rgi.ops_team
      END) ops_team
      , MAX(CASE
      WHEN rgi.group_type = 'Reporting Unit' THEN rgi.ops_team
      END) business_line
      FROM (select glc1.*, 'GEQ' msg_type from i_glc_e_transfer_eq glc1 where value_date = '05-OCT-2012'
      UNION ALL
      select glc2.*, 'GCR' msg_type from i_glc_e_transfer_cr glc2 where value_date = '05-OCT-2012') glc
      , XMLTABLE('emml/transferEventHeader/reportingGroupIdentifier' PASSING XMLTYPE(glc.MESSAGE_XML)
      COLUMNS ops_team VARCHAR2(255) PATH 'reportingGroupId'
      , group_type VARCHAR2(255) PATH 'reportingGroupClassificationScheme'
      ) rgi
      GROUP BY glc.stg_record_id ) rgi1
      , XMLTABLE( 'emml/newTransfer' PASSING XMLPARSE(glc.MESSAGE_XML)
      COLUMNS value_date DATE PATH 'adjustedSettlementDate'
      , value_date_1 VARCHAR2(255) PATH 'tradeAttributes/tradeDate/text()'
      , payer_pty_ref VARCHAR2(255) PATH 'cashMovement/payerPartyReference'
      , rec_pty_ref VARCHAR2(255) PATH 'cashMovement/receiverPartyReference'
      , setmt_ccy VARCHAR2(255) PATH 'cashMovement/settlementAmount/currency'
      , net_setmt_amount VARCHAR2(255) PATH 'cashMovement/settlementAmount/amount'
      , setmt_instruction_type VARCHAR2(255) PATH 'settlementDetails/settlementInstructionType'
      , swift_mt VARCHAR2(255) PATH 'settlementDetails/settlementProcessingInstruction'
      , netted_payment VARCHAR2(255) PATH 'settlementDetails/nettedCashflow'
      ) nt
      , XMLTABLE( 'emml/newTransfer/settlementInformation/settlementInstruction' PASSING XMLTYPE(glc.MESSAGE_XML)
      COLUMNS correspondent_id VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingIds/routingId'
      , correspondent_id_type VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingIds/routingIdScheme'
      , correspondent_acct_name VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingName'
      , correspondent_acct_no VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingAccountNumber'
      , correspondent_sub_acct_no VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingSubAccountNumber'
      , benf_bank_id VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingIds/routingId'
      , benf_bank_id_type VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingIds/routingIdScheme'
      , benf_bank_name VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingName'
      , benf_bank_acct_no VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingAccountNumber'
      , benf_bank_sub_acct_no VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingSubAccountNumber'
      , benf_bank_ref_text VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingReferenceText'
      , benficiary_name VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingName'
      , benficiary_acct_no VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAccountNumber'
      , benficiary_sub_acct_no VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingSubAccountNumber'
      , benficiary_city VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAddress/city'
      , benficiary_state VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAddress/state'
      , benficiary_country VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAddress/country'
      , benficiary_postalCode VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAddress/postalCode'
      , benficiary_id VARCHAR2(255) PATH 'beneficiary//routingIdsAndExplicitDetails/routingIds/routingId'
      , benficiary_id_type VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingIds/routingIdScheme'
      , benficiary_name_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingName'
      , benficiary_acct_no_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAccountNumber'
      , benficiary_sub_acct_no_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingSubAccountNumber'
      , benficiary_city_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAddress/city'
      , benficiary_state_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAddress/state'
      , benficiary_country_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAddress/country'
      , benficiary_postalCode_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAddress/postalCode'
      ) si
      , XMLTABLE('emml' PASSING XMLTYPE(glc.MESSAGE_XML)
      COLUMNS book_ent_persp VARCHAR2(255) PATH 'newTransfer/bookingEntityPerspective'
      , party_internal_1 VARCHAR2(255) PATH 'newTransfer/party[1]/isPartyInternal'
      , party_internal_2 VARCHAR2(255) PATH 'newTransfer/party[2]/isPartyInternal'
      , party_id_1 VARCHAR2(255) PATH 'newTransfer/party[1]/@id'
      , party_id_2 VARCHAR2(255) PATH 'newTransfer/party[2]/@id'
      , party_role_1 VARCHAR2(255) PATH 'newTransfer/party[1]/partyRole'
      , party_role_2 VARCHAR2(255) PATH 'newTransfer/party[2]/partyRole'
      , party_1_name_1 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[1]/partyName'
      , party_1_name_2 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[2]/partyName'
      , party_2_name_1 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[1]/partyName'
      , party_2_name_2 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[2]/partyName'
      , party_3_name_1 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[1]/partyName'
      , party_3_name_2 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[2]/partyName'
      , party_4_name_1 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[1]/partyName'
      , party_4_name_2 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[2]/partyName'
      , party_5_name_1 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[1]/partyName'
      , party_5_name_2 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[2]/partyName'
      , party_1_id_1 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[1]/partyId'
      , party_1_id_2 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[2]/partyId'
      , party_2_id_1 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[1]/partyId'
      , party_2_id_2 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[2]/partyId'
      , party_3_id_1 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[1]/partyId'
      , party_3_id_2 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[2]/partyId'
      , party_4_id_1 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[1]/partyId'
      , party_4_id_2 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[2]/partyId'
      , party_5_id_1 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[1]/partyId'
      , party_5_id_2 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[2]/partyId'
      , party_1_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[1]/partyIdClassificationScheme'
      , party_1_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[2]/partyIdClassificationScheme'
      , party_2_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[1]/partyIdClassificationScheme'
      , party_2_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[2]/partyIdClassificationScheme'
      , party_3_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[1]/partyIdClassificationScheme'
      , party_3_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[2]/partyIdClassificationScheme'
      , party_4_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[1]/partyIdClassificationScheme'
      , party_4_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[2]/partyIdClassificationScheme'
      , party_5_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[1]/partyIdClassificationScheme'
      , party_5_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[2]/partyIdClassificationScheme'
      ) pty
      WHERE rgi1.stg_record_id = glc.stg_record_id
      ;
        • 1. Re: Tuning xml
          odie_63
          Hi,
          Can someone suggest me how i can tune this select ?
          With such an extensive query, I think there's absolutely no chance of optimizing anything without migrating the column to Binary XMLType.