Re: "free buffer waits" under eXtreme Transaction Loads
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Jul 2004 15:50:08 -0400
Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Could I ask you please if is possible in your tests to incrase to 10M, te=
st=20
and share if this improves in some way your performance. =0D
Thank you in advance=0D
=0D
-------Original Message-------=0D
=0D
From: oracle-l@xxxxxxxxxxxxx=0D
Date: 07/26/04 15:35:43=0D
To: oracle-l@xxxxxxxxxxxxx=0D
Subject: Re: "free buffer waits" under eXtreme Transaction Loads=0D
=0D
=46rom the 1st email:=0D
=0D
Cache Sizes (end)=0D
~~~~~~~~~~~~~~~~~=0D
Buffer Cache: 768M Std Block Size: 8K=0D
Shared Pool Size: 160M Log Buffer: 1,024K=0D
=0D
--------------------=0D
Hi, only for curiosity=0D
which is your LOG_BUFFER size?=0D
=0D
-------Original Message-------=0D
=0D
From: oracle-l@xxxxxxxxxxxxx=0D
Date: 07/26/04 13:29:47=0D
To: oracle-l@xxxxxxxxxxxxx; oracledba@xxxxxxxxxxx=0D
Subject: "free buffer waits" under eXtreme Transaction Loads=0D
=0D
Folks=0D
=0D
How are "free buffer waits" (below) to be addressed?=0D
=0D
Case - Benchmark of OLTP Transactions(ATM Trans) of a Banking=0D
Application=0D
=0D
Machine - HP Superdome=0D
Storage XP1024=0D
DB Server =3D3D 32 CPUs (Itanium)=0D
CPU Utilization =3D3D 30 % approx=0D
=0D
For log file sync wait (below) we are considering assigning 4=0D
controllers exclusively to the 4 logfiles(raw)=0D
=0D
Will provide any data needed=0D
=0D
Thanks=0D
=0D
=0D
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@=0D
STATSPACK report for=0D
=0D
DB Name DB Id Instance Inst Num Release Cluster Host=0D
------------ ----------- ------------ -------- ----------- -------=0D
------------=0D
BMON 19538057 bmon 1 9.2.0.5.0 NO sut92=0D
=0D
Snap Id Snap Time Sessions Curs/Sess Comment=0D
------- ------------------ -------- ---------=0D
-------------------=0D
Begin Snap: 666 26-Jul-04 00:42:11 82 16.1=0D
End Snap: 667 26-Jul-04 00:53:04 84 35.4=0D
Elapsed: 10.88 (mins)=0D
=0D
Cache Sizes (end)=0D
~~~~~~~~~~~~~~~~~=0D
Buffer Cache: 768M Std Block Size: 8K=0D
Shared Pool Size: 160M Log Buffer: 1,024K=0D
=0D
Load Profile=0D
~~~~~~~~~~~~ Per Second Per Transaction=0D
--------------- ---------------=0D
Redo size: 6,159,634.77 5,678.93=0D
Logical reads: 101,769.87 93.83=0D
Block changes: 26,383.87 24.32=0D
Physical reads: 11.62 0.01=0D
Physical writes: 392.43 0.36=0D
User calls: 33,233.83 30.64=0D
Parses: 3,261.30 3.01=0D
Hard parses: 0.00 0.00=0D
Sorts: 0.29 0.00=0D
Logons: 0.00 0.00=0D
Executes: 30,980.41 28.56=0D
Transactions: 1,084.65=0D
=0D
% Blocks changed per Read: 25.93 Recursive Call %: 6.54=0D
Rollback per transaction %: 0.00 Rows per Sort: 126.76=0D
=0D
Instance Efficiency Percentages (Target 100%)=0D
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=0D
Buffer Nowait %: 99.98 Redo NoWait %: 100.00=0D
Buffer Hit %: 99.99 In-memory Sort %: 100.00=0D
Library Hit %: 100.02 Soft Parse %: 100.00=0D
Execute to Parse %: 89.47 Latch Hit %: 98.18=0D
Parse CPU to Parse Elapsd %: 83.70 % Non-Parse CPU: 98.47=0D
=0D
Shared Pool Statistics Begin End=0D
------ ------=0D
Memory Usage %: 42.95 43.16=0D
% SQL with executions>1: 80.91 84.42=0D
% Memory for SQL w/exec>1: 81.85 86.25=0D
=0D
Top 5 Timed Events=0D
~~~~~~~~~~~~~~~~~~ %=0D
Total=0D
Event Waits Time (s)=0D
Ela Time=0D
-------------------------------------------- ------------ -----------=0D
--------=0D
log file sync 710,816 2,601=0D
24.30=0D
CPU time 2,569=0D
24.00=0D
free buffer waits 3,743 2,525=0D
23.58=0D
latch free 90,584 1,056=0D
9.86=0D
write complete waits 981 749=0D
7.00=0D
-------------------------------------------------------------=0D
=0D
=0D
=0D
CPU Elapsd=0D
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)=0D
Hash Value=0D
--------------- ------------ -------------- ------ -------- ---------=0D
---------- 9,022,249 2,182,384 4.1 13.6 272.41=0D
527.78 931376387=0D
Module: lisrvr@sut93 (TNS V1-V3)=0D
select entity_cre_flg, del_flg, sol_id, acct_prefix, acct_num, b=0D
acid, foracid, acct_name, acct_short_name, cust_id, emp_id, gl_s=0D
ub_head_code, acct_ownership, schm_code, TO_CHAR(dr_bal_lim), ac=0D
ct_rpt_code, frez_code, frez_reason_code, TO_CHAR(acct_opn_date,=0D
'DD-MM-YYYY HH24:MI:SS'), acct_cls_flg, TO_CHAR(acct_cls_date,'D=0D
=0D
7,753,648 708,188 10.9 11.7 199.02 1427.49=0D
327885819=0D
Module: lisrvr@sut93 (TNS V1-V3)=0D
insert into TBA_DAILY_TRAN_DETAIL_TBL (tran_date,tran_id,part_tr=0D
an_srl_num,del_flg,tran_type,tran_sub_type,part_tran_type,gl_sub=0D
_head_code,acid,value_date,tran_amt,tran_particular,entry_user_i=0D
d,pstd_user_id,vfd_user_id,entry_date,pstd_date,vfd_date,rpt_cod=0D
e,ref_num,instrmnt_type,instrmnt_date,instrmnt_num,instrmnt_alph=0D
=0D
----------------------------------------------------------------=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D
----------------------------------------------------------------=0D
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at //www.freelists.org/archives/oracle-l/=0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=0D
-----------------------------------------------------------------=0D
----------------------------------------------------------------=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D
----------------------------------------------------------------=0D
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at //www.freelists.org/archives/oracle-l/=0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=0D
-----------------------------------------------------------------=0D
=0D
----------------------------------------------------------------=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D
----------------------------------------------------------------=0D
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at //www.freelists.org/archives/oracle-l/=0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=0D
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: Image/jpeg
-- File: 397220_backg_tile_new.jpg
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
