RE: High elapsed time when running on a Unique Index ??
what is the cpu utilization on your box? Waleed -Original Message- To: Multiple recipients of list ORACLE-L Sent: 4/17/02 12:43 AM Qs Why is elapsed Time so HIGH in the following SELECT ? NOTE - 1) TBA_GENERAL_ACCT_MAST_TBL is a SYNONYM for GENERAL_ACCT_MAST_TABLE Table 2) The Respective Tablespace Contains ONLY ONE (i.e. This) Object (GENERAL_ACCT_MAST_TABLE) 3) There is a UNIQUE Index on the field of the Where Clause (i.e. acid) for the Object (GENERAL_ACCT_MAST_TABLE) 4) This Table has a Total of 10 indexes of which 5 are present in 1 Tablespace 5 are present in a 2nd Tablespace . 5) This Table is a Highly Accessed Table day-to-day Working of the Application Select ,TO_CHAR(notional_rate) ,notional_rate_code ,TO_CHAR(fx_clr_bal_amt) , TO_CHAR(fx_bal_on_purge_date) ,fd_ref_num ,TO_CHAR(fx_cum_cr_amt) , TO_CHAR(fx_cum_dr_amt) ,crncy_code ,source_of_fund ,anw_non_cust_alwd_flg , acct_crncy_code ,TO_CHAR(lien_amt) ,acct_classification_flg , system_only_acct_flg ,single_tran_flg ,TO_CHAR(utilised_amt) , inter_sol_access_flg ,purge_allowed_flg ,purge_text ,TO_CHAR(min_value_date, 'DD-MM- HH24:MI:SS') ,acct_mgr_user_id ,schm_type , TO_CHAR(last_frez_date,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_unfrez_date, 'DD-MM- HH24:MI:SS') ,TO_CHAR(bal_on_frez_date) ,swift_allowed_flg , TO_CHAR(dacc_lim_pcnt) ,TO_CHAR(dacc_lim_abs) ,chrg_level_code , acct_cls_chrg_pend_verf ,partitioned_flg ,partitioned_type , pbf_download_flg ,TO_CHAR(pbf_delink_date,'DD-MM- HH24:MI:SS') , wtax_flg ,wtax_amount_scope_flg ,int_adj_for_deduction_flg ,operative_acid , phone_num ,native_lang_name ,nat_lang_title_code ,lang_code ,NVL(ts_cnt,0) , rowid into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14, :b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24,:b25,:b26,:b27,:b28,:b 29, :b30,:b31,:b32,:b33,:b34,:b35,:b36,:b37,:b38,:b39,:b40,:b41,:b42,:b43,:b 44, :b45,:b46,:b47,:b48,:b49,:b50,:b51,:b52,:b53,:b54,:b55,:b56,:b57,:b58,:b 59, :b60,:b61,:b62,:b63,:b64,:b65,:b66,:b67,:b68,:b69,:b70,:b71,:b72,:b73,:b 74, :b75,:b76,:b77,:b78,:b79,:b80,:b81,:b82,:b83,:b84,:b85,:b86,:b87,:b88,:b 89, :b90,:b91,:b92,:b93,:b94,:b95,:b96,:b97,:b98,:b99,:b100,:b101,:b102,:b10 3, :b104,:b105,:b106,:b107,:b108,:b109,:b110,:b111,:b112,:b113,:b114,:b115, :b116 from TBA_GENERAL_ACCT_MAST_TBL where acid=:b117 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 6928 3.99 999.46 0 0 0 0 Fetch 6928 4.47 5.57 2171 27717 0 0 --- -- -- -- -- -- -- total13857 8.461005.03 2171 27717 0 0 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: High elapsed time when running on a Unique Index ??
Because it has executed nearly 7000 times at 0.145 seconds per execution. I would wonder why a query was executed 7000 that didn't return a result set. Jared VIVEK_SHARMA [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/16/02 09:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:High elapsed time when running on a Unique Index ?? Qs Why is elapsed Time so HIGH in the following SELECT ? NOTE - 1) TBA_GENERAL_ACCT_MAST_TBL is a SYNONYM for GENERAL_ACCT_MAST_TABLE Table 2) The Respective Tablespace Contains ONLY ONE (i.e. This) Object (GENERAL_ACCT_MAST_TABLE) 3) There is a UNIQUE Index on the field of the Where Clause (i.e. acid) for the Object (GENERAL_ACCT_MAST_TABLE) 4) This Table has a Total of 10 indexes of which 5 are present in 1 Tablespace 5 are present in a 2nd Tablespace . 5) This Table is a Highly Accessed Table day-to-day Working of the Application Select ,TO_CHAR(notional_rate) ,notional_rate_code ,TO_CHAR(fx_clr_bal_amt) , TO_CHAR(fx_bal_on_purge_date) ,fd_ref_num ,TO_CHAR(fx_cum_cr_amt) , TO_CHAR(fx_cum_dr_amt) ,crncy_code ,source_of_fund ,anw_non_cust_alwd_flg , acct_crncy_code ,TO_CHAR(lien_amt) ,acct_classification_flg , system_only_acct_flg ,single_tran_flg ,TO_CHAR(utilised_amt) , inter_sol_access_flg ,purge_allowed_flg ,purge_text ,TO_CHAR(min_value_date, 'DD-MM- HH24:MI:SS') ,acct_mgr_user_id ,schm_type , TO_CHAR(last_frez_date,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_unfrez_date, 'DD-MM- HH24:MI:SS') ,TO_CHAR(bal_on_frez_date) ,swift_allowed_flg , TO_CHAR(dacc_lim_pcnt) ,TO_CHAR(dacc_lim_abs) ,chrg_level_code , acct_cls_chrg_pend_verf ,partitioned_flg ,partitioned_type , pbf_download_flg ,TO_CHAR(pbf_delink_date,'DD-MM- HH24:MI:SS') , wtax_flg ,wtax_amount_scope_flg ,int_adj_for_deduction_flg ,operative_acid , phone_num ,native_lang_name ,nat_lang_title_code ,lang_code ,NVL(ts_cnt,0) , rowid into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14, :b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24,:b25,:b26,:b27,:b28,:b29, :b30,:b31,:b32,:b33,:b34,:b35,:b36,:b37,:b38,:b39,:b40,:b41,:b42,:b43,:b44, :b45,:b46,:b47,:b48,:b49,:b50,:b51,:b52,:b53,:b54,:b55,:b56,:b57,:b58,:b59, :b60,:b61,:b62,:b63,:b64,:b65,:b66,:b67,:b68,:b69,:b70,:b71,:b72,:b73,:b74, :b75,:b76,:b77,:b78,:b79,:b80,:b81,:b82,:b83,:b84,:b85,:b86,:b87,:b88,:b89, :b90,:b91,:b92,:b93,:b94,:b95,:b96,:b97,:b98,:b99,:b100,:b101,:b102,:b103, :b104,:b105,:b106,:b107,:b108,:b109,:b110,:b111,:b112,:b113,:b114,:b115, :b116 from TBA_GENERAL_ACCT_MAST_TBL where acid=:b117 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 6928 3.99 999.46 0 0 0 0 Fetch 6928 4.47 5.57 2171 27717 0 0 --- -- -- -- -- -- -- total13857 8.461005.03 2171 27717 0 0 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
High elapsed time when running on a Unique Index ??
Qs Why is elapsed Time so HIGH in the following SELECT ? NOTE - 1) TBA_GENERAL_ACCT_MAST_TBL is a SYNONYM for GENERAL_ACCT_MAST_TABLE Table 2) The Respective Tablespace Contains ONLY ONE (i.e. This) Object (GENERAL_ACCT_MAST_TABLE) 3) There is a UNIQUE Index on the field of the Where Clause (i.e. acid) for the Object (GENERAL_ACCT_MAST_TABLE) 4) This Table has a Total of 10 indexes of which 5 are present in 1 Tablespace 5 are present in a 2nd Tablespace . 5) This Table is a Highly Accessed Table day-to-day Working of the Application Select ,TO_CHAR(notional_rate) ,notional_rate_code ,TO_CHAR(fx_clr_bal_amt) , TO_CHAR(fx_bal_on_purge_date) ,fd_ref_num ,TO_CHAR(fx_cum_cr_amt) , TO_CHAR(fx_cum_dr_amt) ,crncy_code ,source_of_fund ,anw_non_cust_alwd_flg , acct_crncy_code ,TO_CHAR(lien_amt) ,acct_classification_flg , system_only_acct_flg ,single_tran_flg ,TO_CHAR(utilised_amt) , inter_sol_access_flg ,purge_allowed_flg ,purge_text ,TO_CHAR(min_value_date, 'DD-MM- HH24:MI:SS') ,acct_mgr_user_id ,schm_type , TO_CHAR(last_frez_date,'DD-MM- HH24:MI:SS') ,TO_CHAR(last_unfrez_date, 'DD-MM- HH24:MI:SS') ,TO_CHAR(bal_on_frez_date) ,swift_allowed_flg , TO_CHAR(dacc_lim_pcnt) ,TO_CHAR(dacc_lim_abs) ,chrg_level_code , acct_cls_chrg_pend_verf ,partitioned_flg ,partitioned_type , pbf_download_flg ,TO_CHAR(pbf_delink_date,'DD-MM- HH24:MI:SS') , wtax_flg ,wtax_amount_scope_flg ,int_adj_for_deduction_flg ,operative_acid , phone_num ,native_lang_name ,nat_lang_title_code ,lang_code ,NVL(ts_cnt,0) , rowid into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14, :b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24,:b25,:b26,:b27,:b28,:b29, :b30,:b31,:b32,:b33,:b34,:b35,:b36,:b37,:b38,:b39,:b40,:b41,:b42,:b43,:b44, :b45,:b46,:b47,:b48,:b49,:b50,:b51,:b52,:b53,:b54,:b55,:b56,:b57,:b58,:b59, :b60,:b61,:b62,:b63,:b64,:b65,:b66,:b67,:b68,:b69,:b70,:b71,:b72,:b73,:b74, :b75,:b76,:b77,:b78,:b79,:b80,:b81,:b82,:b83,:b84,:b85,:b86,:b87,:b88,:b89, :b90,:b91,:b92,:b93,:b94,:b95,:b96,:b97,:b98,:b99,:b100,:b101,:b102,:b103, :b104,:b105,:b106,:b107,:b108,:b109,:b110,:b111,:b112,:b113,:b114,:b115, :b116 from TBA_GENERAL_ACCT_MAST_TBL where acid=:b117 call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 6928 3.99 999.46 0 0 0 0 Fetch 6928 4.47 5.57 2171 27717 0 0 --- -- -- -- -- -- -- total13857 8.461005.03 2171 27717 0 0 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
