2 Replies Latest reply: Dec 29, 2012 10:22 PM by Rohit Jadhav-Oracle RSS

    Slow inserts due to physical reads every time for fresh account i am passin

    Rohit Jadhav-Oracle
      I have a table which has 40million data in it. Of-course partitioned!.

      begin
      pk_cm_entity_context.set_entity_in_context(1);
      end;
      /

      SELECT COUNT(1) FROM XFACE_ADDL_DETAILS_TXNLOG;
      alter table XFACE_ADDL_DETAILS_TXNLOG rename to XFACE_ADDLDTS_TXNLOG_PTPART;
      SELECT COUNT(1) FROM XFACE_ADDLDTS_TXNLOG_PTPART;

      -- Create table
      create table XFACE_ADDL_DETAILS_TXNLOG
      (
      REF_TXN_NO CHAR(40),
      REF_USR_NO CHAR(40),
      REF_KEY_NO VARCHAR2(50),
      REF_TXN_NO_ORG CHAR(40),
      REF_USR_NO_ORG CHAR(40),
      RECON_CODE VARCHAR2(25),
      COD_TASK_DERIVED VARCHAR2(5),
      COD_CHNL_ID VARCHAR2(6),
      COD_SERVICE_ID VARCHAR2(10),
      COD_USER_ID VARCHAR2(30),
      COD_AUTH_ID VARCHAR2(30),
      COD_ACCT_NO CHAR(22),
      TYP_ACCT_NO VARCHAR2(4),
      COD_SUB_ACCT_NO CHAR(16),
      COD_DEP_NO NUMBER(5),
      AMOUNT NUMBER(15,2),
      COD_CCY VARCHAR2(3),
      DAT_POST DATE,
      DAT_VALUE DATE,
      TXT_TXN_NARRATIVE VARCHAR2(60),
      DATE_CHEQUE_ISSUE DATE,
      TXN_BUSINESS_TYPE VARCHAR2(10),
      CARD_NO CHAR(20),
      INVENTORY_CODE CHAR(10),
      INVENTORY_NO CHAR(20),
      CARD_PASSBOOK_NO CHAR(30),
      COD_CASH_ANALYSIS CHAR(20),
      BANK_INFORMATION_NO CHAR(8),
      BATCH_NO CHAR(10),
      SUMMARY VARCHAR2(60),
      MAIN_IC_TYPE CHAR(1),
      MAIN_IC_NO CHAR(48),
      MAIN_IC_NAME CHAR(64),
      MAIN_IC_CHECK_RETURN_CODE CHAR(1),
      DEPUTY_IC_TYPE CHAR(1),
      DEPUTY_IC_NO CHAR(48),
      DEPUTY_NAME CHAR(64),
      DEPUTY_IC_CHECK_RETURN_CODE CHAR(1),
      ACCOUNT_PROPERTY CHAR(4),
      CHEQUE_NO CHAR(20),
      COD_EXT_TASK CHAR(10),
      COD_MODULE CHAR(4),
      ACC_PURPOSE_CODE VARCHAR2(15),
      NATIONALITY CHAR(3),
      CUSTOMER_NAME CHAR(192),
      COD_INCOME_EXPENSE CHAR(6),
      COD_EXT_BRANCH CHAR(6),
      COD_ACCT_TITLE CHAR(192),
      FLG_CA_TT CHAR(1),
      DAT_EXT_LOCAL DATE,
      ACCT_OWNER_VALID_RESULT CHAR(1),
      FLG_DR_CR CHAR(1),
      FLG_ONLINE_UPLOAD CHAR(1),
      FLG_STMT_DISPLAY CHAR(1),
      COD_TXN_TYPE NUMBER(1),
      DAT_TS_TXN TIMESTAMP(6),
      LC_BG_GUARANTEE_NO VARCHAR2(20),
      COD_OTHER_ACCT_NO CHAR(22),
      COD_MOD_OTHER_ACCT_NO CHAR(4),
      COD_CC_BRN_SUB_ACCT NUMBER(5),
      COD_CC_BRN_OTHR_ACCT NUMBER(5),
      COD_ENTITY_VPD NUMBER(5) default NVL(sys_context('CLIENTCONTEXT','entity_code'),11),
      COD_EXT_TASK_REV VARCHAR2(10)
      )
      partition by hash (REF_TXN_NO)
      PARTITIONS 128
      store in (FCHDATA1,FCHDATA2,FCHDATA3,FCHDATA4, FCHDATA5, FCHDATA6, FCHDATA7, FCHDATA8);


      insert /*+APPEND NOLOGGING */ into XFACE_ADDL_DETAILS_TXNLOG
      select /*+PARALLEL */ * from XFACE_ADDLDTS_TXNLOG_PTPART;


      -- Add comments to the table
      comment on table XFACE_ADDL_DETAILS_TXNLOG
      is ' Additional Data log table ';
      -- Add comments to the columns
      comment on column XFACE_ADDL_DETAILS_TXNLOG.REF_TXN_NO
      is 'Transaction Reference Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.REF_USR_NO
      is 'User Reference Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.REF_KEY_NO
      is 'Unique key to identify a leg of the transaction';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.REF_TXN_NO_ORG
      is 'Original Transaction Reference Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.REF_USR_NO_ORG
      is 'Original Transaction User Reference Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.RECON_CODE
      is 'Reconciliation of transactions in future';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_TASK_DERIVED
      is 'Transaction mnemonic for the request';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_CHNL_ID
      is 'Channel ID';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_SERVICE_ID
      is 'Service ID';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_USER_ID
      is 'User ID';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_AUTH_ID
      is 'Authorizer ID';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_ACCT_NO
      is 'It can be Card number or MCA or GL or CASH GL';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.TYP_ACCT_NO
      is 'Type of input (Valid values CARD, MCA, GL, CASH, LN)';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_SUB_ACCT_NO
      is 'MC Sub Account Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_DEP_NO
      is 'Deposit Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.AMOUNT
      is 'Transaction Amount';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_CCY
      is 'Currency Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.DAT_POST
      is 'Posting Date of the transaction';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.DAT_VALUE
      is 'Value Date of the transaction';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.TXT_TXN_NARRATIVE
      is 'Text Transaction Narrative';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.DATE_CHEQUE_ISSUE
      is 'Date of Issue of Cheque';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.TXN_BUSINESS_TYPE
      is 'Transaction Business Type';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.CARD_NO
      is 'Card Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.INVENTORY_CODE
      is 'Inventory Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.INVENTORY_NO
      is 'Inventory Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.CARD_PASSBOOK_NO
      is 'Card Passbook Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_CASH_ANALYSIS
      is 'Cash Analysis Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.BANK_INFORMATION_NO
      is 'Bank Information Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.BATCH_NO
      is 'Batch Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.SUMMARY
      is 'Summary';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.MAIN_IC_TYPE
      is 'IC Type';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.MAIN_IC_NO
      is 'IC Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.MAIN_IC_NAME
      is 'IC Name';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.MAIN_IC_CHECK_RETURN_CODE
      is 'IC Check Return Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.DEPUTY_IC_TYPE
      is 'Deputy IC Type';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.DEPUTY_IC_NO
      is 'Deputy IC Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.DEPUTY_NAME
      is 'Deputy Name';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.DEPUTY_IC_CHECK_RETURN_CODE
      is 'Deputy IC Check Return Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.ACCOUNT_PROPERTY
      is 'Account Property';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.CHEQUE_NO
      is 'Cheque Number';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_EXT_TASK
      is 'External Task Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_MODULE
      is 'Module Code - CH, TD, RD , LN, CASH, GL';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.ACC_PURPOSE_CODE
      is 'Account Purpose Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.NATIONALITY
      is 'Nationality';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.CUSTOMER_NAME
      is 'Customer Name';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_INCOME_EXPENSE
      is 'Income Expense Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_EXT_BRANCH
      is 'External Branch Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_ACCT_TITLE
      is 'Account Title Code';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.FLG_CA_TT
      is 'Cash or Funds Transfer flag';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.DAT_EXT_LOCAL
      is 'Local Date';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.ACCT_OWNER_VALID_RESULT
      is 'Account Owner Valid Result';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.FLG_DR_CR
      is 'Flag Debit Credit - D, C.';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.FLG_ONLINE_UPLOAD
      is 'Flag Online Upload - O, U.';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.FLG_STMT_DISPLAY
      is 'Statement Display Flag - Y/N, Y(Normal Reversal), N(Correction Reversal)';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_TXN_TYPE
      is 'To denote the kind of transaction:
      1 ?Cash Credit Transaction
      2 ?Cash Debit Transaction
      3 ?Funds Transfer Credit Transaction
      4 ?Funds Transfer Debit Transaction
      ';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.DAT_TS_TXN
      is 'Date and Timestamp of the record being inserted';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.LC_BG_GUARANTEE_NO
      is 'LC/BG Guarantee Number for which the request for the Liquidation has been initiated.';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_OTHER_ACCT_NO
      is 'Other Account No';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_MOD_OTHER_ACCT_NO
      is 'Module Code of Other Account No - CH, TD, RD , LN, CASH, GL';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_CC_BRN_SUB_ACCT
      is 'Branch Code for Sub Account';
      comment on column XFACE_ADDL_DETAILS_TXNLOG.COD_CC_BRN_OTHR_ACCT
      is 'Branch Code for Other Account';


      -- Create/Recreate indexes
      drop index IN_XFACE_ADDL_DETAILS_TXNLOG_1;
      drop index IN_XFACE_ADDL_DETAILS_TXNLOG_2;
      drop index IN_XFACE_ADDL_DETAILS_TXNLOG_3;
      drop index IN_XFACE_ADDL_DETAILS_TXNLOG_4;
      drop index IN_XFACE_ADDL_DETAILS_TXNLOG_5;
      drop index IN_XFACE_ADDL_DETAILS_TXNLOG_6;
      drop index IN_XFACE_ADDL_DETAILS_TXNLOG_7;
      drop index IN_XFACE_ADDL_DETAILS_TXNLOG_8;

      create index IN_XFACE_ADDL_DETAILS_TXNLOG_1 on XFACE_ADDL_DETAILS_TXNLOG (REF_TXN_NO, REF_KEY_NO, COD_SUB_ACCT_NO, COD_ENTITY_VPD)
      GLOBAL PARTITION BY HASH (REF_TXN_NO, REF_KEY_NO, COD_SUB_ACCT_NO) PARTITIONS 128 STORE IN (FCHINDX1, FCHINDX2, FCHINDX3, FCHINDX4) PARALLEL (DEGREE 32) NOLOGGING;

      create index IN_XFACE_ADDL_DETAILS_TXNLOG_2 on XFACE_ADDL_DETAILS_TXNLOG (REF_USR_NO, REF_KEY_NO, COD_SUB_ACCT_NO, COD_ENTITY_VPD)
      GLOBAL PARTITION BY HASH(REF_USR_NO, REF_KEY_NO, COD_SUB_ACCT_NO) PARTITIONS 128 STORE IN (FCHINDX1, FCHINDX2, FCHINDX3, FCHINDX4) PARALLEL (DEGREE 32) NOLOGGING;

      create index IN_XFACE_ADDL_DETAILS_TXNLOG_3 on XFACE_ADDL_DETAILS_TXNLOG (COD_SUB_ACCT_NO, FLG_STMT_DISPLAY,DAT_POST COD_ENTITY_VPD)
      GLOBAL PARTITION BY HASH(COD_SUB_ACCT_NO, FLG_STMT_DISPLAY) PARTITIONS 128 STORE IN (FCHINDX1, FCHINDX2, FCHINDX3, FCHINDX4) PARALLEL (DEGREE 32) NOLOGGING;

      create index IN_XFACE_ADDL_DETAILS_TXNLOG_4 on
      XFACE_ADDL_DETAILS_TXNLOG (COD_ACCT_NO, REF_TXN_NO, COD_TXN_TYPE, COD_USER_ID, COD_EXT_BRANCH, COD_ENTITY_VPD)
      GLOBAL PARTITION BY HASH(COD_ACCT_NO, REF_TXN_NO, COD_TXN_TYPE, COD_USER_ID, COD_EXT_BRANCH)
      PARTITIONS 128 STORE IN (FCHINDX1, FCHINDX2, FCHINDX3, FCHINDX4) PARALLEL (DEGREE 32) NOLOGGING;

      create index IN_XFACE_ADDL_DETAILS_TXNLOG_5 on XFACE_ADDL_DETAILS_TXNLOG (COD_USER_ID, DAT_POST, COD_ENTITY_VPD)
      GLOBAL PARTITION BY HASH(COD_USER_ID) PARTITIONS 128 STORE IN (FCHINDX1, FCHINDX2, FCHINDX3, FCHINDX4) PARALLEL (DEGREE 32) NOLOGGING;

      create index IN_XFACE_ADDL_DETAILS_TXNLOG_6 on XFACE_ADDL_DETAILS_TXNLOG (REF_TXN_NO_ORG, COD_ENTITY_VPD)
      GLOBAL PARTITION BY HASH(REF_TXN_NO_ORG) PARTITIONS 128 STORE IN (FCHINDX1, FCHINDX2, FCHINDX3, FCHINDX4) PARALLEL (DEGREE 32) NOLOGGING;

      create index IN_XFACE_ADDL_DETAILS_TXNLOG_7 on XFACE_ADDL_DETAILS_TXNLOG (DAT_EXT_LOCAL, DAT_POST,TXN_BUSINESS_TYPE, FLG_ONLINE_UPLOAD, COD_CHNL_ID, REF_TXN_NO, COD_ENTITY_VPD)
      GLOBAL PARTITION BY HASH(DAT_EXT_LOCAL) PARTITIONS 128 STORE IN (FCHINDX1, FCHINDX2, FCHINDX3, FCHINDX4) PARALLEL (DEGREE 32) NOLOGGING;

      /* Previous Key order: (COD_EXT_BRANCH,DAT_POST,REF_TXN_NO_ORG,COD_SERVICE_ID,COD_ENTITY_VPD) */
      create index IN_XFACE_ADDL_DETAILS_TXNLOG_8 on XFACE_ADDL_DETAILS_TXNLOG (DAT_POST, COD_EXT_BRANCH, REF_TXN_NO_ORG, COD_SERVICE_ID, COD_ENTITY_VPD)
      GLOBAL PARTITION BY HASH(DAT_POST) PARTITIONS 128 STORE IN (FCHINDX1, FCHINDX2, FCHINDX3, FCHINDX4) PARALLEL (DEGREE 32) NOLOGGING;

      ALTER TABLE XFACE_ADDL_DETAILS_TXNLOG NOPARALLEL PCTFREE 50 INITRANS 128 LOGGING;

      ALTER index IN_XFACE_ADDL_DETAILS_TXNLOG_1 NOPARALLEL INITRANS 128;
      ALTER index IN_XFACE_ADDL_DETAILS_TXNLOG_2 NOPARALLEL INITRANS 128;
      ALTER index IN_XFACE_ADDL_DETAILS_TXNLOG_3 NOPARALLEL INITRANS 128;
      ALTER index IN_XFACE_ADDL_DETAILS_TXNLOG_4 NOPARALLEL INITRANS 128;
      ALTER index IN_XFACE_ADDL_DETAILS_TXNLOG_5 NOPARALLEL INITRANS 128;
      ALTER index IN_XFACE_ADDL_DETAILS_TXNLOG_6 NOPARALLEL INITRANS 128;
      ALTER index IN_XFACE_ADDL_DETAILS_TXNLOG_7 NOPARALLEL INITRANS 128;
      ALTER index IN_XFACE_ADDL_DETAILS_TXNLOG_8 NOPARALLEL INITRANS 128;


      BEGIN
      DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA => UPPER('FCR44HOST'),
      OBJECT_NAME => UPPER('XFACE_ADDL_DETAILS_TXNLOG '),
      POLICY_NAME => 'FC_ENTITY_POLICY',
      FUNCTION_SCHEMA => UPPER('FCR44HOST'),
      POLICY_FUNCTION => 'pk_cm_vpd_policy.get_entity_predicate',
      STATEMENT_TYPES => 'select,insert,update,delete',
      UPDATE_CHECK => TRUE,
      ENABLE => TRUE,
      STATIC_POLICY => FALSE,
      POLICY_TYPE => DBMS_RLS.SHARED_STATIC,
      LONG_PREDICATE => FALSE,
      SEC_RELEVANT_COLS => NULL,
      SEC_RELEVANT_COLS_OPT => NULL);

      END;
      /

      begin
      dbms_stats.gather_table_stats(ownname => 'FCR44HOST',tabname => 'XFACE_ADDL_DETAILS_TXNLOG', cascade=>true,method_opt=>'for all columns size 1',degree => 32, GRANULARITY => 'PARTITION');
      end;
      /

      Query which takes time.

      INSERT INTO xface_addl_dtls_tlog_temp
      (ref_txn_no,
      ref_usr_no,
      ref_key_no,
      ref_txn_no_org,
      ref_usr_no_org,
      recon_code,
      cod_task_derived,
      cod_chnl_id,
      cod_service_id,
      cod_user_id,
      cod_auth_id,
      cod_acct_no,
      typ_acct_no,
      cod_sub_acct_no,
      cod_dep_no,
      amount,
      cod_ccy,
      dat_post,
      dat_value,
      txt_txn_narrative,
      date_cheque_issue,
      txn_business_type,
      card_no,
      inventory_code,
      inventory_no,
      card_passbook_no,
      cod_cash_analysis,
      bank_information_no,
      batch_no,
      summary,
      main_ic_type,
      main_ic_no,
      main_ic_name,
      main_ic_check_return_code,
      deputy_ic_type,
      deputy_ic_no,
      deputy_name,
      deputy_ic_check_return_code,
      account_property,
      cheque_no,
      cod_ext_task,
      cod_module,
      acc_purpose_code,
      nationality,
      customer_name,
      cod_income_expense,
      cod_ext_branch,
      cod_acct_title,
      flg_ca_tt,
      dat_ext_local,
      acct_owner_valid_result,
      flg_dr_cr,
      flg_online_upload,
      flg_stmt_display,
      cod_txn_type,
      dat_ts_txn,
      lc_bg_guarantee_no,
      cod_other_acct_no,
      cod_mod_other_acct_no,
      cod_cc_brn_sub_acct,
      cod_cc_brn_othr_acct,
      cod_ext_task_rev,
      sessionid)
      SELECT ref_txn_no,
      ref_usr_no,
      ref_key_no,
      ref_txn_no_org,
      ref_usr_no_org,
      recon_code,
      cod_task_derived,
      cod_chnl_id,
      cod_service_id,
      cod_user_id,
      cod_auth_id,
      cod_acct_no,
      typ_acct_no,
      cod_sub_acct_no,
      cod_dep_no,
      amount,
      cod_ccy,
      dat_post,
      dat_value,
      txt_txn_narrative,
      date_cheque_issue,
      txn_business_type,
      card_no,
      inventory_code,
      inventory_no,
      card_passbook_no,
      cod_cash_analysis,
      bank_information_no,
      batch_no,
      summary,
      main_ic_type,
      main_ic_no,
      main_ic_name,
      main_ic_check_return_code,
      deputy_ic_type,
      deputy_ic_no,
      deputy_name,
      deputy_ic_check_return_code,
      account_property,
      cheque_no,
      cod_ext_task,
      cod_module,
      acc_purpose_code,
      nationality,
      customer_name,
      cod_income_expense,
      cod_ext_branch,
      cod_acct_title,
      flg_ca_tt,
      dat_ext_local,
      acct_owner_valid_result,
      flg_dr_cr,
      flg_online_upload,
      flg_stmt_display,
      cod_txn_type,
      dat_ts_txn,
      lc_bg_guarantee_no,
      cod_other_acct_no,
      cod_mod_other_acct_no,
      cod_cc_brn_sub_acct,
      cod_cc_brn_othr_acct,
      cod_ext_task_rev,
      var_l_sessionid
      FROM xface_addl_details_txnlog
      WHERE cod_sub_acct_no = var_pi_cod_acct_no
      AND dat_post between var_pi_start_dat AND var_pi_end_dat;

      Index referred is in_xface_addl_details_txnlog_3.

      First time when i execute the query it takes huge time. but subsequent queries are faster. This is only if i pass same account and criteria again.
      Observed that first time it goes for physical reads which takes time. and subsequent runs physical reads are less.....

      Request suggestions.....this is account statement inquiry user may have 10000txns in a day as well