| Regarding Segment Statistics [message #202853] |
Mon, 13 November 2006 01:17 |
M.Shakeel Azeem
Messages: 226
Registered: September 2006
|
Senior Member |
|
|
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
