监控数据库性能的sql常见问题(二)

　   16. 碎片程度

　　select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name

　　having count(tablespace_name)>10

　　alter tablespace name coalesce; alter table name deallocate unused; create or replace view ts_blocks_v as select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space uni

　　alter tablespace name coalesce;

　　alter table name deallocate unused;

　　create or replace view ts_blocks_v as

　　select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space

　　union all

　　select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;

　　select * from ts_blocks_v;

　　select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space

　　group by tablespace_name;

　　查看碎片程度高的表

　　SELECT segment_name table_name , COUNT(*) extents

　　FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name

　　HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

　　17. 表、索引的存储情况检查

　　select segment_name,sum(bytes),count(*) ext_quan from dba_extents where

　　tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;

　　select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'

　　group by segment_name;

　　18、找使用CPU多的用户session

　　12是cpu used by this session

　　select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value

　　from v$session a,v$process b,v$sesstat c

　　where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

　　查看Lock

　　SELECT sn.username, m.sid, m.type,

　　DECODE(m.lmode, 0, 'None',

　　1, 'Null',

　　2, 'Row Share',

　　3, 'Row Excl.',

　　4, 'Share',

　　5, 'S/Row Excl.',

　　6, 'Exclusive',

　　lmode, ltrim(to_char(lmode,'990'))) lmode,

　　DECODE(m.request,0, 'None',

　　1, 'Null',

　　2, 'Row Share',

　　3, 'Row Excl.',

　　4, 'Share',

　　5, 'S/Row Excl.',

　　6, 'Exclusive',

　　request, ltrim(to_char(m.request,