RE: Script for identifying objects having freelists contention

From: Wong, Bing <bing.wong_at_IngramMicro.com>
Date: Wed, 17 Apr 2002 14:58:28 -0800
Message-ID: <F001.00447536.20020417145828@fatcity.com>

I hope it works for you.      

set Trimspool on
set Line 132
set Pages 57
set Newpage0
set FeedBack off
set Verify off
set Term off
TTitle off
Btime off
column Pct Format 990.99 Heading "% Of |Free List Waits" column Instance New_Value _Instance NoPrint column Today New_Value _Date No Print select Global_name Instance, To_char(SysDate, 'FXDay DD, YYYY HH:MI') Today from Global_Name;  

TTitle On
TTitle Left 'Date Run: ' _Date Skip 1-

  Center 'Free list Contention' Skip 1 -
  Center 'If Percentage is Greater then 1%' Skip 1 -
  Center 'Consider increasing the number of free lists' Skip 1 -
  Center 'Instance Name: ' _Instance

select ((A.Count / (B.Value + C.Value)) * 100) Pct from V$WaitStat A, V$SysStat B, V$SysStat C where A.Class = 'free list'
  and B.Statistic# = (select Statistic# from V$StatName

                                          where Name = 'db block gets')
and   C.Statistic# = (select Statistic# from V$StatName
                                          where Name = 'consistent gets')

/

Column Total_Waits  Format 999,999,999,990 Heading "Buffer Busy Waits"
Column DB_Get       Format 999,999,999,990 Heading "DB Block Gets"
Column Con_Get      Format 999,999,999,990 Heading "Consistent Gets"
Column Busy_Rate    Format 990.999         Heading "Busy Rate"
TTitle Left 'Date Run: ' _Date Skip 1-
       Center 'Buffer Busy Waits Rate' Skip 1 -
       Center 'If >5% review V$waitStat' Skip 1 -
       Center 'Instance Name: ' _Instance Skip 2
select Total_Waits, B.Value DB_Get, C.Value Con_Get,
       ((A.Total_Waits / (B.Value + C.Value)) * 100) Busy

from V$System_Event A, V$SysStat B, V$SysStat C where A.Event = 'buffer busy waits'
and B.Statistic# = (select Statistic# from V$StatName where Name = 'db block gets')
and C.Statistic# = (select Statistic# from V$StatName where Name = 'consistent gets')
/
 

-----Original Message-----
Sent: Wednesday, April 17, 2002 2:29 PM
To: Multiple recipients of list ORACLE-L

Does anyone have the scripts (or URL) for identifying objects having freelists contention?  

Thanks.
Anand Prakash

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Wong, Bing
  INET: bing.wong_at_IngramMicro.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Apr 17 2002 - 17:58:28 CDT