Hi, only for curiosity which is your LOG_BUFFER size? -------Original Message------- From: oracle-l@xxxxxxxxxxxxx Date: 07/26/04 13:29:47 To: oracle-l@xxxxxxxxxxxxx; oracledba@xxxxxxxxxxx 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@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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------