sql语句使用帮助学习

《sql语句使用帮助学习》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、一些有用的SQL，都是Oraclemanage常用的。列在这里做参考，因为太难记了。时时更新。1、监控当前数据库谁在运行什么SQL语句SELECTosuser,username,sql_textfromv$sessiona,v$sqltextbwherea.sql_address=b.addressorderbyaddress,piece;2、查看碎片程度高的表SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('

2、SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);3。表空间使用状态selecta.file_id"FileNo",a.tablespace_name"Tablespace_name",round(a.bytes/1024/1024,4)"TotalMB",round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4)"U

3、sedMB",round(sum(nvl(b.bytes,0))/1024/1024,4)"FreeMB",round(sum(nvl(b.bytes,0))/a.bytes*100,4)"%Free"fromdba_data_filesa,dba_free_spacebwherea.file_id=b.file_id(+)groupbya.tablespace_name,a.file_id,a.bytesorderbya.tablespace_name4。查看USERSELECTOSUSER,SERIAL#FR

4、OMV$SESSION,V$SQLWHEREV$SESSION.SQL_ADDRESS=V$SQL.ADDRESSANDV$SESSION.STATUS='ACTIVE';5。监控SGA的命中率selecta.value+b.value"logical_reads",c.value"phys_reads",round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO"fromv$sysstata,v$sysstatb,v$sysst

5、atcwherea.statistic#=38andb.statistic#=39andc.statistic#=40;6。监控SGA中字典缓冲区的命中率selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100"missratio",(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"fromv$rowcachewheregets+getmisses<>0groupbyparam

6、eter,gets,getmisses;7。监控SGA中共享缓存区的命中率，应该小于1%selectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins)"reloadpercent"fromv$librarycache;8。监控内存和硬盘的排序比率监控内存和硬盘的排序比率，最好使它小于.10，增加sort_area_sizeSELECTname,valueFROMv$sysstatWHEREnameIN('sorts(memory)','s

7、orts(disk)');9。哪笔数据正在被人update，而且是被谁正在updateselecta.os_user_name,a.oracle_username,a.object_id,c.object_name,c.object_typefromv$locked_objecta,dba_objectscwherea.object_id=c.object_id资料引用:http://www.knowsky.com/385333.html10、查看sql语句占用的空间selectaddress,hash_valu

8、e,disk_reads/executionsdisk_reads,elapsed_time/1000000/executionsas"ELAPSD_TIME(s)",buffer_gets/executionsbgets_per,executions,first_load_timeasfirst_time,sql_textfromv$sqlwhereexecutions