Oracle-L: (no subject)

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
Date: Tue, 16 Apr 2002 20:38:22 -0800
Message-ID: <F001.00445FFD.20020416203822@fatcity.com>

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-YYYY HH24:MI:SS') ,acct_mgr_user_id ,schm_type , TO_CHAR(last_frez_date,'DD-MM-YYYY HH24:MI:SS') ,TO_CHAR(last_unfrez_date, 'DD-MM-YYYY 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-YYYY 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 cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      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
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    13857      8.46    1005.03       2171      27717          0           0

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infy.com

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: ListGuru_at_fatcity.com (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).

Received on Tue Apr 16 2002 - 23:38:22 CDT