"free buffer waits" under eXtreme Transaction Loads

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Mon, 26 Jul 2004 22:59:29 +0530
Message-ID: <F0CB3C9983B77E4AB4ADEFA63DAB109F0EFA4F7F@twrmsg03.ad.infosys.com>

Folks

How are "free buffer waits" (below) to be addressed?

Case - Benchmark of OLTP Transactions(ATM Trans) of a Banking Application

Machine - HP Superdome
Storage XP1024
DB Server =3D 32 CPUs (Itanium)
CPU Utilization =3D 30 % approx

For log file sync wait (below) we are considering assigning 4 controllers exclusively to the 4 logfiles(raw)

Will provide any data needed

Thanks

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- -------

---

BMON            19538057 bmon                1 9.2.0.5.0   NO      sut92

            Snap Id     Snap Time      Sessions Curs/Sess Comment
            ------- ------------------ -------- ---------


-------------------

Begin Snap:     666 26-Jul-04 00:42:11       82      16.1
  End Snap:     667 26-Jul-04 00:53:04       84      35.4
   Elapsed:               10.88 (mins)

Cache Sizes (end)

---

               Buffer Cache:       768M      Std Block Size:         8K
           Shared Pool Size:       160M          Log Buffer:     1,024K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:          6,159,634.77              5,678.93
              Logical reads:            101,769.87                 93.83
              Block changes:             26,383.87                 24.32
             Physical reads:                 11.62                  0.01
            Physical writes:                392.43                  0.36
                 User calls:             33,233.83                 30.64
                     Parses:              3,261.30                  3.01
                Hard parses:                  0.00                  0.00
                      Sorts:                  0.29                  0.00
                     Logons:                  0.00                  0.00
                   Executes:             30,980.41                 28.56
               Transactions:              1,084.65

  % Blocks changed per Read:   25.93    Recursive Call %:     6.54
 Rollback per transaction %:    0.00       Rows per Sort:   126.76

Instance Efficiency Percentages (Target 100%)

---

            Buffer Nowait %:   99.98       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.99    In-memory Sort %:  100.00
            Library Hit   %:  100.02        Soft Parse %:  100.00
         Execute to Parse %:   89.47         Latch Hit %:   98.18
Parse CPU to Parse Elapsd %:   83.70     % Non-Parse CPU:   98.47

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   42.95   43.16

    % SQL with executions>1: 80.91 84.42   % Memory for SQL w/exec>1: 81.85 86.25

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s)

Ela Time
-------------------------------------------- ------------ -----------

---

log file sync                                     710,816       2,601
24.30
CPU time                                                        2,569
24.00
free buffer waits                                   3,743       2,525
23.58
latch free                                         90,584       1,056
9.86
write complete waits                                  981         749
7.00
          -------------------------------------------------------------



                                                     CPU      Elapsd

  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- ---------
---------- 9,022,249 2,182,384 4.1 13.6 272.41 527.78 931376387
Module: lisrvr_at_sut93 (TNS V1-V3)
select entity_cre_flg, del_flg, sol_id, acct_prefix, acct_num, b acid, foracid, acct_name, acct_short_name, cust_id, emp_id, gl_s ub_head_code, acct_ownership, schm_code, TO_CHAR(dr_bal_lim), ac ct_rpt_code, frez_code, frez_reason_code, TO_CHAR(acct_opn_date, 'DD-MM-YYYY HH24:MI:SS'), acct_cls_flg, TO_CHAR(acct_cls_date,'D

      7,753,648 708,188 10.9 11.7 199.02 1427.49 327885819
Module: lisrvr_at_sut93 (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

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Mon Jul 26 2004 - 12:29:05 CDT