Mark, you forgot to include v$segment_statistics.subobject_name = dba_segments.partition_name in join conditions. You should account for partitioned/subpartitioned tables too. btw - that query is a "little" slow when you have a DB with large number of segments (more than 350 000 in my case), Here is something that I found to be more performance-friendly: with segstat as ( select object_type, object_name, owner, subobject_name, max(case statistic_name when 'physical reads' then value end) pr, max(case statistic_name when 'physical reads direct' then value end) prd from v$segment_statistics vss where statistic_name in ('physical reads', 'physical reads direct') group by object_type, object_name, owner, subobject_name ) select * from ( select st.owner, st.object_name, st.subobject_name, (st.pr-st.prd) pr_prd_diff, ds.bytes/power(1024,2) mb from dba_segments ds, segstat st where ds.owner=st.owner and ds.partition_name=st.subobject_name and ds.segment_name=st.object_name and ds.segment_type=st.object_type and ds.buffer_pool='DEFAULT' order by st.pr-st.prd desc ) where rownum <= 20; On 3/14/07, oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> wrote:
Dave, You're absolutely right! Good point. I come from a very OLTP-centric view of the world.... Here's a modified version of the query which will account for physical reads direct: column owner format a20 column statistic_name format a15 select * from( select vss1.owner, vss1.object_name, vss1.statistic_name, vss1.value-vss2.value value, ds.bytes/1048576 "SEGSIZE (MB)", ds.buffer_pool from v$segment_statistics vss1, v$segment_statistics vss2, dba_segments ds where vss1.statistic_name ='physical reads' and vss2.statistic_name = 'physical reads direct' and ds.segment_type = vss1.object_type and ds.segment_name = vss1.object_name and ds.owner=vss1.owner and ds.segment_type = vss2.object_type and ds.segment_name = vss2.object_name and ds.owner=vss2.owner and ds.buffer_pool = 'DEFAULT' order by (vss1.value-vss2.value) desc ) where rownum <=20 / Note that, while I was tinkering, I made a few other changes too: - It no longer needs the threshold value, 5,000,000, or whatever. It will automatically return the top 20 segments w/ the most non-direct physical I/O. - The order of the output is reversed, worst is on top. - Segment size is displayed in MB. - Added some reasonable column formatting, for a wide terminal (at least 132 columns) -Mark -- Mark J. Bobak Senior Oracle Architect ProQuest/CSA
-- Alexander Fatkulin -- //www.freelists.org/webpage/oracle-l