Hi All,
One of my OLTP database is running on 11.1.0.7 (11.1.0.7.0 - 64bit Production) with RHEL 5.4.
On frequent basis, i am observing 'BUFFER BUSY WAITS' and last time i tried to capture some dictionary information to dig the waits.
1. Session Watis :
Oracle Sec Hash
Sid,Serial User OS User Svr-Pgm Wait Event State-Seq Wt Module Cmnd Value P1 P2 P3
---------- -------- -------- ---------- --------------- --------- ---- ----------------------- ---- ----------- ----------- ----------- ----
633,40830 OLTP_USE fateadm 21646-orac buffer busy wai Wtng-9999 1 ORDERS ISRT 3932487748 384 1863905 1
647, 1761 OLTP_USE fateadm 22715-orac buffer busy wai Wtng-3837 0 ORDERS ISRT 3932487748 384 1863905 1
872, 5001 OLTP_USE fateadm 21836-orac buffer busy wai Wtng-9999 1 ORDERS ISRT 3932487748 384 1863905 1
702, 1353 OLTP_USE fateadm 21984-orac buffer busy wai Wtng-9999 1 ORDERS ISRT 3932487748 384 1863905 1
337,10307 OLTP_USE fateadm 21173-orac buffer busy wai Wtng-9999 1 ORDERS ISRT 3932487748 384 1863905 1
751,43016 OLTP_USE fateadm 21619-orac buffer busy wai Wtng-9999 1 ORDERS ISRT 3932487748 384 1863905 1
820,17959 OLTP_USE fateadm 21648-orac buffer busy wai Wtng-9999 0 ORDERS ISRT 3932487748 384 1863905 1
287,63359 OLTP_USE fateadm 27053-orac buffer busy wai Wtng-9999 0 ORDERS ISRT 3932487748 384 1863905 1
629, 1653 OLTP_USE fateadm 22468-orac buffer busy wai Wtng-9999 1 ORDERS ISRT 3932487748 384 1863905 1
788,14160 OLTP_USE fateadm 22421-orac buffer busy wai Wtng-9999 0 ORDERS ISRT 3932487748 384 1863905 1
615, 4580 OLTP_USE fateadm 21185-orac buffer busy wai Wtng-9999 0 ORDERS ISRT 3932487748 384 1863905 1
525,46068 OLTP_USE fateadm 27043-orac buffer busy wai Wtng-9034 1 ORDERS ISRT 3932487748 384 1863905 1
919,23243 OLTP_USE fateadm 21428-orac buffer busy wai Wtng-6340 1 ORDERS ISRT 3932487748 384 1863906 1
610,34557 OLTP_USE fateadm 21679-orac buffer busy wai Wtng-6422 1 ORDERS ISRT 3932487748 384 1863906 1
803, 1583 OLTP_USE fateadm 21580-orac buffer busy wai Wtng-6656 1 ORDERS ISRT 3932487748 384 1863906 1
781, 1523 OLTP_USE fateadm 21781-orac buffer busy wai Wtng-9999 0 ORDERS ISRT 3932487748 384 1863906 1
369,11005 OLTP_USE fateadm 21718-orac buffer busy wai Wtng-9999 0 ORDERS ISRT 3932487748 384 1863906 1
823,35800 OLTP_USE fateadm 21148-orac buffer busy wai Wtng-9999 1 ORDERS ISRT 3932487748 384 1863906 1
817, 1537 OLTP_USE fateadm 22505-orac buffer busy wai Wtng-9999 1 ORDERS ISRT 3932487748 384 1863906 1
579,54959 OLTP_USE fateadm 22517-orac buffer busy wai Wtng-9999 0 ORDERS ISRT 3932487748 384 1863906 1
591,33597 OLTP_USE fateadm 27027-orac buffer busy wai Wtng-9999 1 ORDERS ISRT 3932487748 384 1863906 1
481, 3031 OLTP_USE fateadm 21191-orac buffer busy wai Wtng-3502 1 ORDERS ISRT 3932487748 384 1863906 1
473,24985 OLTP_USE fateadm 22629-orac buffer busy wai Wtng-9999 0 ORDERS ISRT 3932487748 384 1863906 1
868, 3984 OLTP_USE fateadm 27191-orac buffer busy wai Wtng-9999 0 ORDERS ISRT 3932487748 384 1863906 1
select owner,segment_name,segment_type from dba_extents where file_id = 384 and 1863905 between block_id and block_id + blocks -1;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
ORDER ORDER_DETAILS TABLE
select TABLE_NAME,PARTITIONED,ini_trans ,degree,compression,FREELISTS from dba_TABLES WHERE TABLE_NAME='ORDER_DETAILS';
TABLE_NAME PAR INI_TRANS DEGREE COMPRESS FREELISTS
------------------------------ --- ---------- ------------------------------ -------- ----------
ORDER_DETAILS NO 1 1 ENABLED 1
Tablespace is not ASSM managed !
select
object_name,
statistic_name,
value
from
V$SEGMENT_STATISTICS
where
object_name = 'ORDER_DETAILS';
OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ---------------------------------------------------------------- ----------
ORDER_DETAILS logical reads 487741104
ORDER_DETAILS buffer busy waits 4715174
ORDER_DETAILS db block changes 200858896
ORDER_DETAILS physical reads 143642724
ORDER_DETAILS physical writes 20581330
ORDER_DETAILS physical reads direct 55239903
ORDER_DETAILS physical writes direct 19500551
ORDER_DETAILS space allocated 1.6603E+11
ORDER_DETAILS segment scans 9727
ORDER_DETAILS table is ~ 153 GB non-partitioned table.
It seems its not a READ BY OTHER SESSIONS wait but BUFFER BUSY due to write-wirte contention inside same block. I have never observed Cache Buffer Chain/ ITL-Wait/ High wait time on dbfile sequential/scattered reads.
Table contains one PK (composite index on 3 columns) which seems to be highly fragmented.This non partitioned global Index has 3182037735 rows and blevel is 4.
BHAVIK_DBA.FATE1NA>select index_name,status,num_rows,blevel,pct_free,ini_trans,clustering_factor from dba_indexes where index_name='IDX_ORDERS';
INDEX_NAME STATUS NUM_ROWS BLEVEL PCT_FREE INI_TRANS CLUSTERING_FACTOR
------------------------------ -------- ---------- ---------- ---------- ---------- -----------------
IDX_ORDERS VALID 3182037735 4 2 2 2529462377
1 row selected.
One of the index column value is being populated by sequence. (Monotonically increasing value)
SEGMENT_NAME MB
--------------------------------------------------------------------------------- ----------
IDX_ORDERS 170590.438
Index size is greater than table size !
Tuning goal here is to reduce buffer busy waits and thus commit latencies.
I think, i need to increase FREELISTS and PCT_FREE to address this issue, but not much confident whether it is going to solve the issue or not?
Can i ask for any help here ?