以下的文章主要描述的是Oracle性能检测sql语句中的一些项目的介绍 ,以及Oracle性能检测sql语句中的一些项目实际操作。以下就是文章的详细内容介绍，望大家浏览之后会有在此方面所收获。
监控 MTS
- select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时，参数需加大
- select sum(wait)/sum(totalq) "dispatcher waits"
from v$queue where type='dispatcher';- select count(*) from v$dispatcher;
- select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
碎片程度
- 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- 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);
在Oracle性能检测sql语句中表、索引的存储情况检查很重要。如以下
- 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;
找使用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;
上述的相关内容就是对Oracle性能检测sql语句中的项目的描述，希望会给你带来一些帮助在此方面。
【编辑推荐】
