资源描述：
《DB常用性能查询语句》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、常用的一些性能查询sql语句--查看表锁select*fromsys.v_$sqlareawheredisk_reads>100--监控事例的等待selectevent, sum(decode(wait_Time,0,0,1))"Prev", sum(decode(wait_Time,0,1,0))"Curr", count(*)"Tot" fromv$session_Wait groupbyevent orderby4--回滚段的争用情况selectname,waits,gets,waits/gets"Rat
2、io" fromv$rollstata,v$rollnameb wherea.usn=b.usn--查看前台正在发出的SQL语句selectuser_name,sql_text fromv$open_cursor wheresidin(selectsid from(selectsid,serial#,username,program fromv$session wherestatus='ACTIVE'))-
3、-数据表占用空间大小情况selectsegment_name,tablespace_name,bytes,blocks fromuser_segments wheresegment_type='TABLE' ORDERBYbytesDESC,blocksDESC--查看表空间碎片大小selecttablespace_name, round(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))), 2)FSFI from
4、dba_free_space groupbytablespace_name orderby1--查看碎片程度高的表SELECTsegment_nametable_name,COUNT(*)extents FROMdba_segments WHEREownerNOTIN('SYS','SYSTEM') GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*)) FROMdba_segments GROUPBYsegmen
5、t_name); --查看表空间占用磁盘情况selectb.file_id文件id, b.tablespace_name表空间名, b.bytes/1024/1024总大小, (b.bytes-sum(nvl(a.bytes,0)))/1024/1024已使用大小, sum(nvl(a.bytes,0))/1024/1024剩余空间, sum(nvl(a.bytes,0))/(b.bytes)*100剩余百分比 fromdba_free_spacea,dba_data_filesb whe
6、rea.file_id=b.file_id groupbyb.tablespace_name,b.file_id,b.bytes orderbyb.file_id;--查看session使用回滚段SELECTr.name回滚段名, s.sid, s.serial#, s.username用户名, t.status, t.cr_get, t.phy_io, t.used_ublk, t.noundo, substr(s.program,1,78)操作程序
7、 FROMsys.v_$sessions,sys.v_$transactiont,sys.v_$rollnamer WHEREt.addr=s.taddr andt.xidusn=r.usn ORDERBYt.cr_get,t.phy_io--查看SGA区剩余可用内存selectname, sgasize/1024/1024 "Allocated(M)", bytes/1024/1024 "自由空间(M)", round(bytes/sgas
8、ize*100,2) "自由空间百分比(%)" from(selectsum(bytes)sgasizefromsys.v_$sgastat)s, sys.v_$
