oracle sql

1、監(jiān)控監(jiān)控USERdistinctp.spidunix_processs.terminalto_(s.logon_timeYYYYMONDDHH24:MI)Logon_Times.usernamefromv$processpv$sessionswherep.addr=s.paddrderby21.監(jiān)控事例的等待：eventsum(decode(wait_time001))prevsum(decode(wait_time010))currc

2、ount()fromv$session_waitgroupbyeventderby42.回滾段的爭用情況：namewaitsgetswaitsgetsratiofromv$rollstatav$rollnamebwherea.usn=b.usn3.監(jiān)控表空間的IO比例：df.tablespace_namenamedf.file_name“file“f.phyrdspyrf.phyblkrdpbrf.phywrtspywf.phyblkw

3、rtpbwfromv$filestatfdba_data_filesdfwheref.file#=df.file_id4.監(jiān)空文件系統(tǒng)的IO比例：substr(a.file#12)“#“substr(a.name130)“name“a.statusa.bytesb.phyrdsb.phywrtsfromv$datafileav$filestatbwherea.file#=b.file#5.在某個用戶下找所有的索引：user_indexe

4、s.table_nameuser_indexes.index_nameuniquenesscolumn_namefromuser_ind_columnsuser_indexeswhereuser_ind_columns.index_name=user_indexes.index_nameuser_ind_columns.table_name=user_indexes.table_namederbyuser_indexes.table_t

5、ypeuser_indexes.table_nameuser_indexes.index_namecolumn_position6.監(jiān)控SGA的命中率a.valueb.value“l(fā)ogical_reads“c.value“phys_reads“round(100((a.valueb.value)c.value)(a.valueb.value))“BUFFERHITRATIO“fromv$sysstatav$sysstatbv$syss

6、tatcwherea.statistic#=38b.statistic#=39c.statistic#=40osuserusernamesql_textfromv$sessionav$sqltextbwherea.sql_address=b.addressderbyaddresspiece13.監(jiān)控字典緩沖區(qū)(SUM(PINSRELOADS))SUM(PINS)“LIBCACHE“FROMV$LIBRARYCACHE(SUM(GETSG

7、ETMISSESUSAGEFIXED))SUM(GETS)“ROWCACHE“FROMV$ROWCACHESUM(PINS)“EXECUTIONS“SUM(RELOADS)“CACHEMISSESWHILEEXECUTING“FROMV$LIBRARYCACHE后者除以前者此比率小于1%接近0%為好。SUM(GETS)“DICTIONARYGETS“SUM(GETMISSES)“DICTIONARYCACHEGETMISSES“FROM

8、V$ROWCACHE14.找ACLE字符集fromsys.props$wherename=NLS_ACTERSET15.監(jiān)控MTSbusy(busyidle)“sharedserversbusy“fromv$dispatcher此值大于0.5時，參數(shù)需加大sum(wait)sum(totalq)“dispatcherwaits“fromv$queuewheretype=dispatchercount()fromv$dispatchers

9、ervers_highwaterfromv$mtsservers_highwater接近mts_max_servers時，參數(shù)需加大16.碎片程度tablespace_namecount(tablespace_name)fromdba_free_spacegroupbytablespace_namehavingcount(tablespace_name)10altertablespacenamecoalescealtertablenam