This discussion is archived
6 Replies Latest reply: Dec 30, 2012 7:50 PM by rp0428 RSS

SQL Query very slow.

RohitJadhav Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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.
    RohitJadhav Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.
    RohitJadhav Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Rohit Jadhav wrote:

    DDL level changes if any.
    change the table from CHAR to VARCHAR2
  • 6. Re: SQL Query very slow.
    rp0428 Guru
    Currently Being Moderated
    >
    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'.

Legend

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