RE: Big RAC Benchmark Performance .......issues ........ ?

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Fri, 4 Aug 2006 17:23:35 +0530
Message-ID: <BBD944BCAC3AB4499DFBAFB1D8AF3020019698D8@BLRKECMSG11.ad.infosys.com>

Replies in CAPITALS embedded Below:-

Thanks indeed

---

From: Riyaj Shamsudeen [mailto:rshamsud_at_jcpenney.com] Sent: Thursday, August 03, 2006 10:28 PM To: VIVEK_SHARMA
Subject: Re: Big RAC Benchmark Performance .......issues ........ ?

Vivek

    Statspack reveals that you almost use no CPU in the DB server. But, from your email, I derive that you observed higher CPU usage. Can you clarify ?    

CPU USAGE is actually indeed LOW. Sorry about the confusion caused.

    Even with almost no load conditions, there is quite a bit of cluster related waits. Refer to the following SQLs. First SQL elapsed time is 48 seconds and cpu time is 14 seconds. While the executions are 14000 with an average elapsed time 3.2 milliseconds and 0.9 millisecond of that spent in cpu. So, remaining 36 seconds in total are spent waiting for cluster related event.

    Considering first SQL, 14 buffer gets, almost zero disk reads. So, IO is not your problem here, at least, not yet.

    Question is why do we spend 36 seconds for cluster events for this SQL alone ?    

    Let's look at the GC related event. Most of the waits are for current mode. I guess, I have to assume that these insert statements are causing them without actually tracing them. You might want to look at these objects and see whether you have problems with freelists/freelist groups for these tables, indexes ?.

FREELISTS = 80, FREELIST GROUPS = 4 Do you use sequence in this SQL ?

YES. If yes, how many values do you cache ?

CACHED VALUE 35000 How many indexes do you have in these tables ? Any triggers ?

4 INDEXES EXIST ON TBA_DAILY_TRAN_DETAIL_TBL     I also think that you might want to increase the load and see whether these events are really bottlenecks. Typically, bottlenecks gets magnified under heavier load.

 SQLs:

  Cluster      CWT % of     Elapsd        CPU                      Old

Wait Time (s) Elapsd Time Time (s) Time (s) Executions Hash Value
------------- ----------- ----------- ----------- --------------

---

        38.16 79.3 48.13 14.03 14,637 1624201947
Module: lisrvr-uniserver_at_app1 (TNS V1-V3) insert into TBA_DAILY_TRAN_DETAIL_TBL (tran_date,tran_id,part_tr an_srl_num,del_flg,tran_type,tran_sub_type,part_tran_type,gl_sub
_head_code,acid,value_date,tran_amt,tran_particular,entry_user_i
d,pstd_user_id,vfd_user_id,entry_date,pstd_date,vfd_date,rpt_cod e,ref_num,instrmnt_type,instrmnt_date,instrmnt_num,instrmnt_alph

• CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** -- http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 04 2006 - 06:53:35 CDT