6 Replies Latest reply: Dec 30, 2012 9:50 PM by rp0428 RSS

    SQL Query very slow.

    Rohit Jadhav
      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

      Bymistake earlier i raised this in "Oracle -> Text"

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

      They suggested to use bind variable. But as i know, we are already using bind variables to bind account number and start and end date.
        • 1. Re: SQL Query very slow.
          rp0428
          Welcome to the forum!

          Whenever you post provide your 4 digit Oracle version (SELECT * FROM V$VERSION).
          >
          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.
          >
          1. If your question is about the INSERT query into xface_addl_dtls_tlog_temp why didn't you post any information about the DDL for that table? Is it the same structure as the table you did post DDL for?

          2. Why doesn't your INSERT query use APPEND, NOLOGGING and PARALLEL like the first query you posted? If those help for the first query why didn't you try them for the query you are now having problems with?

          3. What does this mean: 'Index referred is in_xface_addl_details_txnlog_3.'? You haven't posted any plan that refers to any index. Do you have an execution plan? Why didn't you post it?

          4. Why are you defining 37 columns as CHAR datatypes? Are you aware that CHAR data REQUIRES the use of the designated number of BYTES/CHARACTERS?

          5. Are you aware that #4 means those 37 columns columns, even if all of them are NULL, mean that your MINIMUM record length is 1012? Care to guess how many of those records Oracle can fit into an 8k block? And that is if you ignore the other 26 VARCHAR2, NUMBER and DATE columns.

          Two of your columns take 192 bytes MINIMUM even if they are null
          >
          CUSTOMER_NAME CHAR(192),
          COD_ACCT_TITLE CHAR(192)
          >
          Why are you wasting all of that space? If you are using a multi-byte character set and your data is multi-byte those 37 columns are using even more space because some characters will use more than one byte.

          If the name and title average 30 characters/bytes then those two columns alone use 300+ unused bytes. With 40 million records those unused bytes, just for those two columns take 12 GB of space.

          WIth a block size of 8k that would totally waste 1.5 million blocks that Oracle has to read just to ignore the empty space that isn't being used.

          I highly suspect that your use of CHAR is a large part of this performance problem and probably other performance problems in your system. Not only for this table but for any other table that uses similar CHAR datatypes and wastes space.

          Please reconsider your use of CHAR datatypes like this. I can't imagine what justification you have for using them.
          • 2. Re: SQL Query very slow.
            Rohit Jadhav
            My Replies below.


            Whenever you post provide your 4 digit Oracle version (SELECT * FROM V$VERSION).

            Ans :
            -----
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            PL/SQL Release 11.2.0.3.0 - Production
            "CORE     11.2.0.3.0     Production"
            TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production


            1. If your question is about the INSERT query into xface_addl_dtls_tlog_temp why didn't you post any information about the DDL for that table? Is it the same structure as the table you did post DDL for?
            Ans :
            -----
            -- Create table
            create global temporary table XFACE_ADDL_DTLS_TLOG_TEMP
            (
            REF_TXN_NO CHAR(40) not null,
            REF_USR_NO CHAR(40) not null,
            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 VARCHAR2(150),
            MAIN_IC_NAME VARCHAR2(192),
            MAIN_IC_CHECK_RETURN_CODE CHAR(1),
            DEPUTY_IC_TYPE CHAR(1),
            DEPUTY_IC_NO VARCHAR2(150),
            DEPUTY_NAME VARCHAR2(192),
            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 VARCHAR2(360),
            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_EXT_TASK_REV VARCHAR2(10),
            SESSIONID NUMBER default USERENV('SESSIONID') not null
            )
            on commit delete rows;
            -- Create/Recreate indexes
            create index IN_XFACE_ADDL_DTLS_TLOG_TEMP on XFACE_ADDL_DTLS_TLOG_TEMP (COD_SUB_ACCT_NO, REF_TXN_NO, COD_SERVICE_ID, REF_KEY_NO, SESSIONID);

            2. Why doesn't your INSERT query use APPEND, NOLOGGING and PARALLEL like the first query you posted? If those help for the first query why didn't you try them for the query you are now having problems with?
            Ans :
            -----
            I will try to use append but i cannot use parallel since i have hardware limitations.


            3. What does this mean: 'Index referred is in_xface_addl_details_txnlog_3.'? You haven't posted any plan that refers to any index. Do you have an execution plan? Why didn't you post it?
            Ans :
            -----
            Plan hash value: 4081844790

            ----------------------------------------------------------------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
            ----------------------------------------------------------------------------------------------------------------------------------------
            | 0 | INSERT STATEMENT | | | | 5 (100)| | | |
            | 1 | LOAD TABLE CONVENTIONAL | | | | | | | |
            | 2 | FILTER | | | | | | | |
            | 3 | PARTITION HASH ALL | | 1 | 494 | 5 (0)| 00:00:01 | 1 | 128 |
            | 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| XFACE_ADDL_DETAILS_TXNLOG | 1 | 494 | 5 (0)| 00:00:01 | ROWID | ROWID |
            | 5 | INDEX RANGE SCAN | IN_XFACE_ADDL_DETAILS_TXNLOG_3 | 1 | | 3 (0)| 00:00:01 | 1 | 128 |
            ----------------------------------------------------------------------------------------------------------------------------------------

            4. Why are you defining 37 columns as CHAR datatypes? Are you aware that CHAR data REQUIRES the use of the designated number of BYTES/CHARACTERS?
            Ans :
            -----
            I understand and appreciate your points, but since it is huge application and is built over a period of time. I am afraid if i will be allowed to do change on datatypes. there are lot of queries over this table.

            5. Are you aware that #4 means those 37 columns columns, even if all of them are NULL, mean that your MINIMUM record length is 1012? Care to guess how many of those records Oracle can fit into an 8k block? And that is if you ignore the other 26 VARCHAR2, NUMBER and DATE columns.

            Two of your columns take 192 bytes MINIMUM even if they are null
            CUSTOMER_NAME CHAR(192),
            COD_ACCT_TITLE CHAR(192)

            Why are you wasting all of that space? If you are using a multi-byte character set and your data is multi-byte those 37 columns are using even more space because some characters will use more than one byte.

            If the name and title average 30 characters/bytes then those two columns alone use 300+ unused bytes. With 40 million records those unused bytes, just for those two columns take 12 GB of space.

            WIth a block size of 8k that would totally waste 1.5 million blocks that Oracle has to read just to ignore the empty space that isn't being used.

            I highly suspect that your use of CHAR is a large part of this performance problem and probably other performance problems in your system. Not only for this table but for any other table that uses similar CHAR datatypes and wastes space.

            Please reconsider your use of CHAR datatypes like this. I can't imagine what justification you have for using them.
            Ans :
            -----
            I understand your points, but since it is huge application is built over a period of time. I am afraid if i will be allowed to do change on datatypes.

            I have to manage in current situation. Not expecting query to respond in millisecs but not even 40secs which is happening currently.

            Edited by: Rohit Jadhav on Dec 30, 2012 6:44 PM
            • 3. Re: SQL Query very slow.
              sb92075
              Rohit Jadhav wrote:
              -----
              I understand your points, but since it is huge application is built over a period of time. I am afraid if i will be allowed to do change on datatypes.

              I have to manage in current situation. Not expecting query to respond in millisecs but not even 40secs which is happening currently.
              so we avoid making recommendations that you deem unworkable, tell us now what you are willing to do to improve performance.
              • 4. Re: SQL Query very slow.
                Rohit Jadhav
                My idea behind having insert in temp table was to get limited set of records which will be use further in some other query instead of directly query xface_addl_details_txnlog.

                but it looks like select query during insert in temp table itself is taking time. Apologies if i have used any offending language during my replies. Well you can suggest me the solutions to tune with existing query as well as

                DDL level changes if any.
                • 5. Re: SQL Query very slow.
                  sb92075
                  Rohit Jadhav wrote:

                  DDL level changes if any.
                  change the table from CHAR to VARCHAR2
                  • 6. Re: SQL Query very slow.
                    rp0428
                    >
                    I understand your points, but since it is huge application is built over a period of time. I am afraid if i will be allowed to do change on datatypes.

                    I have to manage in current situation. Not expecting query to respond in millisecs but not even 40secs which is happening currently.
                    >
                    Then why is your temp table using different datatype definitions than the actual table?
                    COD_ACCT_TITLE CHAR(192),         -- defined in source table
                    COD_ACCT_TITLE VARCHAR2(360),  -- defined in temp table
                    Your temp table uses VARCHAR2 and a length longer than the source. That doesn't make much sense.

                    You should pass my reply on to your DBAs and manager. Depending on how fully those CHAR columns are populated you are most likely wasting the bulk of the space those tables are consuming. More importantly you likely are consuming 5 to 10 times as many blocks as necessary by using CHAR columns that only sparsely populate the entire column length.

                    That is a SERIOUS impact to performance throughout your entire system for ALL queries that use those tables. It also wastes space in the buffer cache.

                    And with this predicate
                    >
                    WHERE cod_sub_acct_no = var_pi_cod_acct_no
                    AND dat_post between var_pi_start_dat AND var_pi_end_dat;
                    >
                    That index might not be much help since 'dat_post' is the third column in the index. Oracle is doing an index range scan on the 'cod_sub_acct_no' column (the leading index column) but has to skip the 'FLG_STMT_DISPLAY' (second index column).

                    Try a new index on 'cod_sub_acct_no, dat_post'.