Hi Tom,
I am new to Oracle 10g new features of using dba_hist views.
Here it is my headache:
The following query shows me the significant Buffer Busy Waits:
column buffer_busy_wait format 999,999,999
select sn.snap_id,
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait
from
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_stat new,
dba_hist_snapshot sn
where new.snap_id = sn.snap_id
and new.snap_id = sn.snap_id
and old.snap_id = sn.snap_id-1
having avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100
group by to_char(sn.end_interval_time,'dd Mon HH24:mi:ss'), sn.snap_id;
SNAP_ID MYDATE BUFFER_BUSY_WAIT
------- --------------------- ----------------
15882 27 Jul 11:00:44 876
15891 27 Jul 19:00:21 136
15899 28 Jul 03:00:58 1,193
Now, conforming to most of the documentation I've seen th reasonable action you take is to find the offending tables having to few freelist and to increase the freelist.
How can we find the "offending" tables based on what we have above ?
The only way I found is to use dba_hist_active_sess_history in the above join so that will allow me to join also dba_data_files and dba_objects so I can find the objects. For simplicity of the case I took only first snap_id = 15882 from above.
select
obj.object_type,
obj.object_name,
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait
from
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_stat new,
dba_hist_snapshot sn,
dba_hist_active_sess_history ash,
dba_data_files df,
dba_objects obj
where new.snap_id = sn.snap_id
and new.snap_id = sn.snap_id
and old.snap_id = sn.snap_id-1
and df.file_id = ash.current_file#
and obj.object_id = ash.current_obj#
and ash.snap_id = sn.snap_id
and sn.snap_id = 15882
having avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100
group by to_char(sn.end_interval_time,'dd Mon HH24:mi:ss'), obj.object_type, obj.object_name;
OBJECT_TYPE OBJECT_NAME Yr. Mo Dy Hr. BUFFER_BUSY_WAIT
------------------- -------------------------------- ---------------- ----------------
TABLE STUDENT_LOAN 27 Jul 11:00:44 876
TABLE OSFI_FEED_CORE_DATA 27 Jul 11:00:44 876
TABLE PARTITION STG_FIN_ACC 27 Jul 11:00:44 876
TABLE PARTITION DDS_PARTY_SRCD 27 Jul 11:00:44 876
TABLE PARTITION STG_BCAR_DETAIL 27 Jul 11:00:44 876
So apparently these will be the offending tables for 'buffer busy wait' event. So for these tables/segments I should increase freelist.
But, but ... if I add to the above join DBA_HIST_SEG_STAT I see BUFFER_BUSY_WAITS_TOTAL = 0 and BUFFER_BUSY_WAITS_DELTA = 0 like there were no buffer waits !!! why ? what's going on here ?
select BUFFER_BUSY_WAITS_TOTAL,
BUFFER_BUSY_WAITS_DELTA,
obj.object_type,
obj.object_name,
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,
avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait
from
dba_hist_buffer_pool_stat old,
dba_hist_buffer_pool_stat new,
dba_hist_snapshot sn,
dba_hist_active_sess_history ash,
dba_data_files df,
dba_objects obj,
DBA_HIST_SEG_STAT dss
where new.snap_id = sn.snap_id
and new.snap_id = sn.snap_id
and old.snap_id = sn.snap_id-1
and df.file_id = ash.current_file#
and obj.object_id = ash.current_obj#
and dss.snap_id = sn.snap_id
and dss.OBJ# = obj.object_id
and ash.snap_id = sn.snap_id
and sn.snap_id = 15882
having avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100
group by
to_char(sn.end_interval_time,'dd Mon HH24:mi:ss'), obj.object_type, obj.object_name, BUFFER_BUSY_WAITS_TOTAL, BUFFER_BUSY_WAITS_DELTA;
BUFFER_BUSY_WAITS_TOTAL BUFFER_BUSY_WAITS_DELTA OBJECT_TYPE OBJECT_NAME
----------------------- ----------------------- ------------------- ---------------------------
0 0 TABLE STUDENT_LOAN
0 0 TABLE OSFI_FEED_CORE_DATA
0 0
TABLE PARTITION STG_FIN_ACC .....
Also I have another question in here: all tablespaces containing these segments/tables have SPACE MANAGEMENT = AUTO so that we should not worry about adjusting freelist. Then why 'busy buffer waits' is happenning, why not Oracle takes care of that because of 'AUTO' setting ? If space management = 'AUTO' takes care for me of eliminating 'busy buffer waits' then why do I have this top event and does it make sense to add freelist as long as I have 'AUTO' ?
Please put some light for me in here ... Thank you.