Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: "free buffer waits" under eXtreme Transaction Loads
Shall get requested info & post. Below data from the SAME Statspack Report
Buffer Pool Statistics for DB: BMON Instance: bmon Snaps: 666 -667
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Write Buffer Number of Cache Buffer Physical Physical Buffer Complete Busy P Buffers Hit % Gets Reads Writes Waits WaitsWaits
init.ora Parameters for DB: BMON Instance: bmon Snaps: 666 -667
End value Parameter Name Begin value (ifdifferent)
----------------------------- --------------------------------- ------------ _library_cache_advice FALSE _log_simultaneous_copies 256 _system_trig_enabled FALSE aq_tm_processes 0 compatible 9.2.0.5 control_files /BMON/findata1/TBAHP/CF/bmon_cont db_2k_cache_size 268435456 db_block_size 8192 db_cache_advice off db_cache_size 536870912 db_file_multiblock_read_count 8 db_files 1024 db_name bmon db_writer_processes 4 dml_locks 2500 enqueue_resources 800000 global_names TRUE hash_join_enabled FALSE hpux_sched_noage 178 job_queue_processes 4 log_buffer 1048576 log_checkpoint_interval 31457290 log_checkpoint_timeout 0 log_parallelism 4 max_dump_file_size 10485760 open_cursors 5000 optimizer_index_cost_adj 15 parallel_max_servers 0 pga_aggregate_target 314572800 processes 2500 session_cached_cursors 50 sessions 4000 shared_pool_size 167772160 sort_area_size 1048576 transaction_auditing FALSE undo_management AUTO undo_tablespace undotbs -------------------------------------------------------------
-----Original Message-----
From: VIVEK_SHARMA=20
Sent: Monday, July 26, 2004 10:59 PM
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 ElapsdBuffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
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
-- 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 - 22:24:21 CDT
![]() |
![]() |