Re: Script for identifying objects having freelists contention
SELECT s.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.state FROM dba_segments s , V$session_wait w WHERE s.header_file=w.p1 AND s.header_block=w.p2; Bunyamin K. Karadeniz Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S. Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 / 1217Mobile : +90 535 3357729 The degree of normality in a database is inversely proportional to that of its DBA. - Original Message - From: Aponte, Tony To: Multiple recipients of list ORACLE-L Sent: Thursday, April 18, 2002 8:13 AM Subject: RE: Script for identifying objects having freelists contention Steve Adams' site is a good start. http://www.ixora.com.au/ Tony Aponte -Original Message-From: Anand Prakash [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 17, 2002 5:29 PMTo: Multiple recipients of list ORACLE-LSubject: Script for identifying objects having freelists contention Does anyone have the scripts (or URL) for identifying objects having freelists contention? Thanks. Anand Prakash
Re: Script for identifying objects having freelists contention
That won't do it Anjo. "Bunyamin K. Karadeniz" wrote: SELECT s.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.state FROM dba_segments s , V$session_wait w WHERE s.header_file=w.p1 AND s.header_block=w.p2; Bunyamin K. Karadeniz Oracle DBA / Developer Civilian IT Department Havelsan A.S. Eskisehir yolu 7.km Ankara Turkey Phone: +90 312 2873565 / 1217 Mobile : +90 535 3357729The degree of normality in a database is inversely proportional to that of its DBA. - Original Message - From: Aponte, Tony To: Multiple recipients of list ORACLE-L Sent: Thursday, April 18, 2002 8:13 AM Subject: RE: Script for identifying objects having freelists contention Steve Adams' site is a good start. http://www.ixora.com.au/Tony Aponte -Original Message- From: Anand Prakash [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 5: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
RE: Script for identifying objects having freelists contention
I hope it works for you. set Trimspool onset Line 132set Pages 57set Newpage0set FeedBack offset Verify offset Term offTTitle offBtime offcolumn Pct Format 990.99 Heading "% Of |Free List Waits"column Instance New_Value _Instance NoPrintcolumn Today New_Value _Date No Printselect Global_name Instance, To_char(SysDate, 'FXDay DD, HH:MI') Todayfrom Global_Name; TTitle OnTTitle 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: ' _Instanceselect ((A.Count / (B.Value + C.Value)) * 100) Pctfrom V$WaitStat A, V$SysStat B, V$SysStat Cwhere 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 2select Total_Waits, B.Value DB_Get, C.Value Con_Get, ((A.Total_Waits / (B.Value + C.Value)) * 100) Busyfrom V$System_Event A, V$SysStat B, V$SysStat Cwhere 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 PMTo: Multiple recipients of list ORACLE-LSubject: Script for identifying objects having freelists contention Does anyone have the scripts (or URL) for identifying objects having freelists contention? Thanks. Anand Prakash
RE: Script for identifying objects having freelists contention
Steve Adams' site is a good start. http://www.ixora.com.au/ Tony Aponte -Original Message-From: Anand Prakash [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 17, 2002 5:29 PMTo: Multiple recipients of list ORACLE-LSubject: Script for identifying objects having freelists contention Does anyone have the scripts (or URL) for identifying objects having freelists contention? Thanks. Anand Prakash
