Re: "free buffer waits" under eXtreme Transaction Loads

From: Terry Sutton <terrysutton_at_usa.net>
Date: Mon, 26 Jul 2004 12:37:51 -0700
Message-ID: <024a01c47348$0afe1600$51f5a8c0@TerrySutton>

>From the 1st email:

Cache Sizes (end)

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

Hi, only for curiosity
which is your LOG_BUFFER size?

-------Original Message-------

From: oracle-l_at_freelists.org
Date: 07/26/04 13:29:47
To: oracle-l_at_freelists.org; oracledba_at_lazydba.com Subject: "free buffer waits" under eXtreme Transaction Loads

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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------

----------------------------------------------------------------
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 - 14:34:27 CDT