Home » RDBMS Server » Server Administration » Regarding Segment Statistics
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?
Previous Topic: TNS issues! strange!
Next Topic: Error while creating new Oracle instance in solaris
Goto Forum:
  


Current Time: Thu Dec 08 16:32:19 CST 2016

Total time taken to generate the page: 0.05338 seconds