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@xxxxxxxxxxx> 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
> http://www.freelists.org/archives/oracle-l/
> FAQ is at
> http://www.freelists.org/help/fom-serve/cache/1.html
>
-----------------------------------------------------------------
> 


=====
Gaja Krishna Vaidyanatha
E-mail :gajav@xxxxxxxxx

Co-author:Oracle Insights:Tales of the Oak Table
- http://www.apress.com/book/bookDisplay.html?bID=314

Co-author:Oracle Performance Tuning 101
- 
http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766


        
                
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 
----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: