Re: "free buffer waits" under eXtreme Transaction Loads
- From: Peter Gram <peter.gram@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, VIVEK_SHARMA@xxxxxxxxxxx
- Date: Mon, 26 Jul 2004 21:03:52 +0200
Hello
Even if the "free buffer waits" looks interesting I would try to fix the
"log file sync" first since this is the wait that uses
the most time in the statspack report. When you reduced the I/O problem
you have on the redo log files will
influence the "free buffer waits" as well.
/peter
VIVEK_SHARMA wrote:
>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@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@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@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
>-----------------------------------------------------------------
>
>
>
--
Best regards / Venlig hilsen
/*Peter Gram*/
Miracle A/S <www.miracleas.dk>
Kratvej 2
DK - 2760 Måløv
Cell: (+45) 2527 7107
Phone: (+45) 4466 8855
Fax: (+45) 4466 8856
Home: (+45) 3874 5696
Email: peter.gram@xxxxxxxxxxxx
25 Aug 2004 OracleEkspert-konference 2004 på Trekroner
<http://www.oracleekspert.dk/konference.htm>
17 Sep 2004 DECUS-fest i Pederstrup
18-20 Oct 2004 RAC-kursus med Jonathan Lewis
28-30 Oct 2004 The Last Database Forum at Lalandia
<http://miracleas.dk/events/DBF2004/invitation.html>
22-24 Nov 2004 RMAN-kursus med Tuomas Pystynen
17-19 Jan 2005 Miracle Master Class with Tim Gorman
7-9 Mar 2005 Database Master Class with Chris Date
2-4 May 2005 SQL Server Master Class with Kimberly Tripp
----------------------------------------------------------------
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:
