以下的文章主要描述的是Oracle性能检测sql语句中的一些项目的介绍 ,以及Oracle性能检测sql语句中的一些项目实际操作。以下就是文章的详细内容介绍,望大家浏览之后会有在此方面所收获。
监控 MTS
selectbusy/(busy+idle)"sharedserversbusy"fromv$dispatcher;
此值大于0.5时,参数需加大
selectsum(wait)/sum(totalq)"dispatcherwaits" fromv$queuewheretype='dispatcher'; selectcount(*)fromv$dispatcher; selectservers_highwaterfromv$mts;
servers_highwater接近mts_max_servers时,参数需加大
碎片程度
selecttablespace_name,count(tablespace_name)from dba_free_spacegroupbytablespace_name havingcount(tablespace_name)>10; altertablespacenamecoalesce; altertablenamedeallocateunused; createorreplaceviewts_blocks_vas selecttablespace_name,block_id,bytes,blocks, 'freespace'segment_namefromdba_free_space unionall selecttablespace_name,block_id,bytes,blocks, segment_namefromdba_extents; select*fromts_blocks_v; selecttablespace_name,sum(bytes),max(bytes), count(block_id)fromdba_free_space groupbytablespace_name;
查看碎片程度高的表
SELECTsegment_nametable_name,COUNT(*)extents FROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM') GROUPBYsegment_name HAVINGCOUNT(*)=(SELECTMAX(COUNT(*)) FROMdba_segmentsGROUPBYsegment_name);
在Oracle性能检测sql语句中表、索引的存储情况检查很重要。如以下
selectsegment_name,sum(bytes),count(*) ext_quanfromdba_extentswhere tablespace_name='&tablespace_name'andsegment_type= 'TABLE'groupbytablespace_name,segment_name; selectsegment_name,count(*)fromdba_extentswhere segment_type='INDEX'andowner='&owner' groupbysegment_name;
找使用CPU多的用户session
12是cpu used by this session
selecta.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100value fromv$sessiona,v$processb,v$sesstatc wherec.statistic#=12andc.sid=a.sidanda.paddr= b.addrorderbyvaluedesc;
相关文章
标签:Oracle