RE: Script for identifying objects having freelists contention

[Wong, Bing]

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----- From: Anand Prakash [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 2:29 PM To: Multiple recipients of list ORACLE-L Subject: Script for identifying objects having freelists contention Does anyone have the scripts (or URL) for identifying objects having freelists contention?   Thanks. Anand Prakash