Individual Segment Statistics

Dear All,
i have executed the following script to know about invidual segment causing problem

select * from
(
select
DECODE
(GROUPING (a.object_name), 1, 'All Objects', a.object_name)
AS "Object",
sum (case when a.statistic_name = 'ITL waits'
then a.value else null end) "ITL Waits",
sum(case when a.statistic_name = 'buffer busy waits'
then a.value else null end) "Buffer Busy Waits",
sum(case when a.statistic_name = 'row lock waits'
then a.value else null end) "Row Lock Waits",
sum(case when a.statistic_name = 'physical reads'
then a.value else null end) "Physical Reads",
sum(case when a.statistic_name = 'logical reads'
then a.value else null end) "Logical Reads"
from v$segment_statistics a
where a.owner like upper('&owner')
group by rollup(a.object_name)) b
where (b."ITL Waits">0 or b."Buffer Busy Waits">0)

Object ITLWaits BufferWaits Physical Reads Logical Reads
----------------- ------ ----------------- -------------- -------
HR_EMP_ATTENDANCE 0 1910 12678637 19801744
HR_EMP_CHECKIN 0 5 12788 96368
All Objects 0 1915 12947951 22760320

HR_EMP_ATTENDANCE= 512MB in size
it looks like that HR_EMP_ATTENDANCE is causing 99% of the total
buffer busy waits and also most of the physical reads belong to
HR_EMP_ATTENTDANCE
Now what i should do now?
because i am having 57% buffer cache hit percentage
can u help me in this regard?

you need to learn the different between buffer busy waits and a buffer cache hit ratio first as they arent related as simply as you would like them to be.

search google for buffer busy waits and you can see what it means, as what you have posted there means nothing

I suggest you query v$segstat instead of v$segment_statistics to reduce overhead.

Is buffer busy waits causing you any problems at all? Normally buffer busy waits means hot blocks, you need to query v$session_wait and get p1 values, if it's 130 the most probably it is hotspots.

If you get 130 and in your case I assume it is a table if that is the case I would check the sql statements accesing the table, see if there are Full Table Scan running at same time by several sessions causing buffer busy waits.