RE: "free buffer waits" under eXtreme Transaction Loads.......CORRECTION

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Tue, 3 Aug 2004 21:37:53 +0530
Message-ID: <F0CB3C9983B77E4AB4ADEFA63DAB109F0C3B3C59@twrmsg03.ad.infosys.com>

Alexandre

VXFS is 3.01.001=20
Sorry for the mistake below.

Apologies
Vivek

-----Original Message-----
From: VIVEK_SHARMA=20
Sent: Tuesday, August 03, 2004 9:13 PM
To: 'agorbatchev_at_amadeus.net'
Cc: 'oracle-l_at_freelists.org'
Subject: RE: "free buffer waits" under eXtreme Transaction Loads

Alexandre=20

Thanks so much for the advice=20
VXFS is 3.5 with HP-UX 11.23

Regards
Vivek

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alexandre Gorbatchev Sent: Tuesday, August 03, 2004 11:20 AM
To: oracle-l_at_freelists.org
Subject: RE: "free buffer waits" under eXtreme Transaction Loads

Vivek,
Our environment is exactly like yours:
HP-UX 11i 64 bits, Oracle 9.2.0.4, FS - VxFS 3.2. What Veritas version do=20
you have?

Just had this problem recently in one of our databases. The rest is RAC on=20
raw devices but this one is an FS based. After some research I found out that we don't have ODM installed and our

Veritas (3.2) file system doesn't support it at all. Migration to 3.5=20 isn't an option at the moment. The problem was that natural Unix behavior=20
is to lock the file when the process touches/writes to it. ODM would=20 provide Oracle with means to have concurrent writes to the same file by=20 more than one process without this "logical" contention. I wasn't very=20 fancy about moving the whole 1 TB database to raw devices so I identified=20
few hot datafiles (most written to) and moved them to raw devices - the=20 result is that free buffer waits dropped 4 times (it was 80-85 percent).

And the batch execution time reduced about 20 times. I still have free=20 buffer waits to about 20% but that's because some of hot files are still

on a FS. And, honestly, I don't care since we are on track with our=20 "tuning goal". ;-)

If you case is the same as mine, I would suggest to look at a statspack=20 report and move datafiles with most writes to raw devices or use ODM. Your=20
UNDO is most probably one of the hot potatoes.

hth,
Alex

From: "VIVEK_SHARMA" <VIVEK_SHARMA_at_infosys.com>@freelists.org on=20 02-08-2004 13:30 ZE5B
Please respond to oracle-l_at_freelists.org Sent by: oracle-l-bounce_at_freelists.org

To:
<oracle-l_at_freelists.org>
<oracledba_at_lazydba.com>

cc:

Subject:
RE: "free buffer waits" under eXtreme Transaction Loads

Some OBSERVATIONS:-
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D= 3D=3D3D=3D3D

1. Reduced "free buffer waits" by:-
2. REDUCING log_checkpoint_interval to a fraction of the Total ONLINE REDO LOG FILE Size (NOTE - log_checkpoint_timeout=3D3D0)
3. Putting undo Tablespace onto Raw
4. "free buffer waits" & "log file sync" are unrelated

NOTE - Only Online Redo logfiles & Undo on RAW. Rest of Database on VXFS (Mounted FS)
Transactions - OLTP in Nature of Banking Application on Oracle 9.2.0.5/HP-UX 11i

HTH P.S. Thanks again one & all for the Support

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Gaja Krishna Vaidyanatha
Sent: Tuesday, July 27, 2004 12:49 AM
To: oracle-l_at_freelists.org
Subject: Re: "free buffer waits" under eXtreme Transaction Loads

Vivek,

Could you please share with us a 10046 trace output on the said SQL statements, so that one can ascertain what the "real problem" is? Just by looking at the STATSPACK report and the high-level waits, it is very difficult to determine anything, at the level of granularity one needs.

Some questions you need to ask yourself:

1. What is your tuning goal?
2. What is an acceptable response time for each of your SQL statements?
3. What is the quantifiable benefit of your tuning exercise? -- Business Value for the Bank

If the SQL statements run within the "required response times" during heavy/peak load, then no matter what the "waits" are, should one really care? One can try eliminating every wait in the database, but may never accomplish that goal. Also, if one consciously tries "eliminating waits" even though the application is running within its response time goals - One is probably suffering from CTD... Compulsive Tuning Disorder...:)

Jokes aside, response time and business value for the tuning exercise are key elements. Everything else is secondary. I am optimistic that you have already considered that.

Cheers,

Gaja

• VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com> wrote:
  > Folks
  >=3D20
  > How are "free buffer waits" (below) to be addressed?
  >=3D20
  > Case - Benchmark of OLTP Transactions(ATM Trans) of
  > a Banking
  > Application
  >=3D20
  > Machine - HP Superdome
  > Storage XP1024
  > DB Server =3D3D3D 32 CPUs (Itanium)
  > CPU Utilization =3D3D3D 30 % approx
  >=3D20
  > For log file sync wait (below) we are considering
  > assigning 4
  > controllers exclusively to the 4 logfiles(raw)
  >=3D20
  > Will provide any data needed
  >=3D20
  > Thanks
  >=3D20 >=3D20 > @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
  > STATSPACK report for
  >=3D20
  > DB Name DB Id Instance Inst Num
  > Release Cluster Host
  > ------------ ----------- ------------ --------
  > ----------- -------
  > ------------
  > BMON 19538057 bmon 1
  > 9.2.0.5.0 NO sut92
  >=3D20
  > Snap Id Snap Time Sessions
  > Curs/Sess Comment
  > ------- ------------------ --------
  > ---------
  > -------------------
  > Begin Snap: 666 26-Jul-04 00:42:11 82 =3D20
  > 16.1
  > End Snap: 667 26-Jul-04 00:53:04 84 =3D20
  > 35.4
  > Elapsed: 10.88 (mins)
  >=3D20
  > Cache Sizes (end)
  > ~~~~~~~~~~~~~~~~~
  > Buffer Cache: 768M Std
  > Block Size: 8K
  > Shared Pool Size: 160M Log
  > Buffer: 1,024K
  >=3D20
  > Load Profile
  > ~~~~~~~~~~~~ Per Second =3D20
  > Per Transaction
  > --------------- =3D20
  > ---------------
  > Redo size: 6,159,634.77 =3D20
  > 5,678.93
  > Logical reads: 101,769.87 =3D20
  > 93.83
  > Block changes: 26,383.87 =3D20
  > 24.32
  > Physical reads: 11.62 =3D20
  > 0.01
  > Physical writes: 392.43 =3D20
  > 0.36
  > User calls: 33,233.83 =3D20
  > 30.64
  > Parses: 3,261.30 =3D20
  > 3.01
  > Hard parses: 0.00 =3D20
  > 0.00
  > Sorts: 0.29 =3D20
  > 0.00
  > Logons: 0.00 =3D20
  > 0.00
  > Executes: 30,980.41 =3D20
  > 28.56
  > Transactions: 1,084.65
  >=3D20
  > % Blocks changed per Read: 25.93 Recursive
  > Call %: 6.54
  > Rollback per transaction %: 0.00 Rows per
  > Sort: 126.76
  >=3D20
  > 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
  >=3D20
  > 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
  >=3D20
  > Top 5 Timed Events
  > ~~~~~~~~~~~~~~~~~~ =3D20
  > %
  > Total
  > Event =3D20
  > Waits Time (s)
  > Ela Time
  > --------------------------------------------
  > ------------ -----------
  > --------
  > log file sync =3D20
  > 710,816 2,601
  > 24.30
  > CPU time =3D20
  > 2,569
  > 24.00
  > free buffer waits =3D20
  > 3,743 2,525
  > 23.58
  > latch free =3D20
  > 90,584 1,056
  > 9.86
  > write complete waits =3D20
  > 981 749
  > 7.00
  > =3D20
  >

  ---

  >=3D20 >=3D20 >=3D20
  > =3D20
  > CPU Elapsd
  > Buffer Gets Executions Gets per Exec %Total
  > Time (s) Time (s)
  > Hash Value
  > --------------- ------------ -------------- ------
  > -------- ---------
  > ---------- 9,022,249 2,182,384 =3D20
  > 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
  >=3D20
  > 7,753,648 708,188 10.9 11.7 =3D20
  > 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
-----------------------------------------------------------------

Received on Tue Aug 03 2004 - 11:05:18 CDT