资源描述：
《oracle常遇到的问题》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、152.如何监控SGA中共享缓存区的命中率，应该小于1%？ selectsum(pins)"TotalPins",sum(reloads)"TotalReloads", sum(reloads)/sum(pins)*100libcache fromv; selectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins) "reloadpercent" fromv;153.如何显示所有数据库对象的类别和大小？ selectcount
2、(name)num_instances,type,sum(source_size)source_size, sum(parsed_size)parsed_size,sum(code_size)code_size,sum(error_size) error_size, sum(source_size)sum(parsed_size)sum(code_size)sum(error_size) size_required fromdba_object_size groupbytypeorderby2;15
3、4.监控SGA中重做日志缓存区的命中率，应该小于1% SELECTname,gets,misses,immediate_gets,immediate_misses, Decode(gets,0,0,misses/gets*100)ratio1, Decode(immediate_getsimmediate_misses,0,0, immediate_misses/(immediate_getsimmediate_misses)*100)ratio2 FROMvWHEREnameIN('redoalloc
4、ation','redocopy');155.监控内存和硬盘的排序比率，最好使它小于.10，增加sort_area_size SELECTname,valueFROMvWHEREnameIN('sorts(memory)','sorts(disk)');156.如何监控当前数据库谁在运行什幺SQL语句？ SELECTosuser,username,sql_textfromva,vb wherea.sql_address=b.addressorderbyaddress,piece;157.如何监控字典缓冲
5、区？ SELECT(SUM(PINS-RELOADS))/SUM(PINS)"LIBCACHE"FROMV; SELECT(SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)"ROWCACHE"FROM V; SELECTSUM(PINS)"EXECUTIONS",SUM(RELOADS)"CACHEMISSESWHILEEXECUTING"FROM V; 后者除以前者,此比率小于1%,接近0%为好。 SELECTSUM(GETS)"DICTIONARYGETS",
6、SUM(GETMISSES)"DICTIONARYCACHEGETMISSES" FROMV158.监控MTS selectbusy/(busyidle)"sharedserversbusy"fromv; 此值大于0.5时，参数需加大 selectsum(wait)/sum(totalq)"dispatcherwaits"fromvwhere type='dispatcher'; selectcount(*)fromv; selectservers_highwaterfromv; servers_hi
7、ghwater接近mts_max_servers时，参数需加大159.如何知道当前用户的ID号? SQL>SHOWUSER; OR SQL>selectuserfromdual;160.如何查看碎片程度高的表? SELECTsegment_nametable_name,COUNT(*)extents FROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_name HAVINGCOUNT(*)=(SELECTMAX(COUNT(*
8、))FROMdba_segmentsGROUPBYsegment_name);162.如何知道表在表空间中的存储情况? selectsegment_name,sum(bytes),count(*)ext_quanfromdba_extentswhere tablespace_name='&tablespace_name'andsegment_type='TABLE'groupbytablespace_name,segment_name;16
