分析數據庫性能的SQL

　　用於查看哪些實例的哪些操作使用了大量的臨時段
　　
　　SELECT to_number(decode(SID NULL SID)) sid
　　operation_type OPERATIONtrunc(EXPECTED_SIZE/) ESIZE
　　trunc(ACTUAL_MEM_USED/) MEM trunc(MAX_MEM_USED/) MAX MEM
　　NUMBER_PASSES PASS trunc(TEMPSEG_SIZE/) TSIZE
　　FROM V$SQL_WORKAREA_ACTIVE
　　ORDER BY ;
　　
　　查詢有熱塊查詢的SQL語句
　　
　　select hash_value
　　from v$sqltext a
　　(select distinct aownerasegment_nameasegment_type from
　　dba_extents a
　　(select dbarfildbablk
　　from (select dbarfildbablk
　　from x$bh order by tch desc) where rownum < ) b
　　where aRELATIVE_FNO = bdbarfil
　　and aBLOCK_ID <= bdbablk and ablock_id + ablocks > bdbablk) b
　　where asql_text like %||bsegment_name||% and bsegment_type = TABLE
　　order by　ahash_valueaaddressapiece;
　　
　　全表掃描
　　
　　select opnametargetbnum_rowsbtablespace_namecount(target) from v$session_longops aall_all_tables b
　　where aTARGET=bowner||||btable_name
　　having count(target)> group by　 opnametargetbnum_rowsbtablespace_name
　　
　　查看磁盤排序和緩存排序次數
　　
　　select　to_char(snsnap_timeyyyymmdd hh) time_
　　avg(newmenvalue oldmenvalue) sorts_memeory
　　avg(newdskvalue olddskvalue) disk_sort
　　from　　stats$sysstat oldmen
　　stats$sysstat newmen
　　stats$sysstat newdsk
　　stats$sysstat olddsk
　　stats$snapshot sn
　　where　 newdsksnap_id=snsnap_id
　　and　　 olddsksnap_id=snsnap_id
　　and　　 newmensnap_id=snsnap_id
　　and　　 newdsksnap_id=snsnap_id
　　and　　 oldmenname=sorts (memory)
　　and　　 newmenname=sorts (memory)
　　and　　 olddskname=sorts (disk)
　　and　　 newdskname=sorts (disk)
　　group by　to_char(snsnap_timeyyyymmdd hh)
　　
　　執行最慢的前個SQL？？？
　　
　　select * from (
　　select
　　to_char(snap_timedd Mon HH:mi:ss) mydate
　　executions　　　　　　　　　　　　　　 exec
　　loads　　　　　　　　　　　　　　　　　loads
　　parse_calls　　　　　　　　　　　　　　parse
　　disk_reads　　　　　　　　　　　　　　 reads
　　buffer_gets　　　　　　　　　　　　　　gets
　　rows_processed　　　　　　　　　　　　 rows_proc
　　sorts　　　　　　　　　　　　　　　　　sorts
　　sql_text
　　hash_value
　　from
　　perfstatstats$sql_summary sql
　　perfstatstats$snapshot　　 sn
　　where
　　sqlsnap_id >
　　(select min(snap_id) min_snap
　　from stats$snapshot where snap_time > sysdate$days_back)
　　and
　　sqlsnap_id = snsnap_id
　　order by $sortskey desc)　tt where rownum<;
　　
　　SQL緩存池的命中率查詢（pinhitratiogethitratio應該大於%以上）
　　
　　select namespacegethitratiopinhitratioreloadsinvalidations
　　from v$librarycache
　　where namespace in (SQL AREATABLE/PROCEDUREBODYTRIGGER)
　　
　　數據庫的常規參數我就不說了除了V$parameter中的常規參數外ORACLE還有大量的隱含參數下面的語句就可以查詢到數據庫的所有隱含參數以及其值與參數的描述
　　
　　SELECT NAME
　　VALUE
　　decode(isdefault TRUEYN) as Default
　　decode(ISEMTRUEYN) as SesMod
　　decode(ISYMIMMEDIATE I
　　DEFERRED D
　　FALSE N) as SysMod
　　decode(IMODMODIFIEDU
　　SYS_MODIFIEDSN) as Modified
　　decode(IADJTRUEYN) as Adjusted
　　description
　　FROM ( GV$SYSTEM_PARAMETER
　　SELECT xinst_id as instance
　　xindx+
　　ksppinm as NAME
　　ksppity
　　ksppstvl as VALUE
　　ksppstdf as isdefault
　　decode(bitand(ksppiflg/)TRUEFALSE) as ISEM
　　decode(bitand(ksppiflg/)
　　IMMEDIATEDEFERREDFALSE) as ISYM
　　decode(bitand(ksppstvf)MODIFIEDFALSE) as IMOD
　　decode(bitand(ksppstvf)TRUEFALSE) as IADJ
　　ksppdesc as DESCRIPTION
　　FROM x$ksppi x
　　x$ksppsv y
　　WHERE xindx = yindx
　　AND substr(ksppinm) = _
　　AND xinst_id = USERENV(Instance)
　　)
　　ORDER BY NAME
　　
　　想知道現在哪個用戶正在利用臨時段嗎？這個語句將告訴你哪個用戶正在利用臨時段
　　
　　SELECT btablespace bsegfile# bsegblk# bblocks asid aserial#
　　ausername aosuser astatuscsql_text
　　FROM v$session av$sort_usage b v$sql c
　　WHERE asaddr = bsession_addr
　　AND asql_address = caddress(+)
　　ORDER BY btablespace bsegfile# bsegblk# bblocks;
　　
　　查看磁盤碎片
　　
　　select tablespace_namesqrt(max(blocks)/sum(blocks))*
　　(/sqrt(sqrt(count(blocks)))) FSFI
　　from dba_free_space
　　group by tablespace_name order by
　　
　　查看表空間的名稱及大小
　　
　　select ttablespace_name round(sum(bytes/(*))) ts_size
　　from dba_tablespaces t dba_data_files d
　　where ttablespace_name = dtablespace_name
　　group by ttablespace_name;
　　
　　查看表空間物理文件的名稱及大小
　　
　　select tablespace_name file_id file_name
　　round(bytes/(*)) total_space
　　from dba_data_files
　　order by tablespace_name;
　　
　　查看回滾段名稱及大小
　　
　　select segment_name tablespace_name rstatus
　　(initial_extent/) InitialExtent(next_extent/) NextExtent
　　max_extents vcurext CurExtent
　　From dba_rollback_segs r v$rollstat v
　　Where rsegment_id = vusn(+)
　　order by segment_name
　　
　　耗資源的進程（top session)
　　
　　select sschemaname schema_name　　decode(sign( command)
　　to_char(command) Action Code # || to_char(command) ) action　　status
　　session_status　 sosuser os_user_name　 ssid　　　　 pspid 　　　　 sserial# serial_num
　　nvl(susername [Oracle process]) user_name　 sterminal terminal
　　sprogram program　 stvalue criteria_value　from v$sesstat st　 v$session s　 v$process p
　　where stsid = ssid and　 ststatistic# = to_number() and　 (ALL = ALL
　　or sstatus = ALL) and paddr = spaddr order by stvalue desc　pspid asc susername asc sosuser asc
　　
　　查看鎖（lock）情況
　　
　　select /*+ RULE */ lsosuser os_user_name　 lsusername user_name
　　decode(lstype RW Row wait enqueue lock TM DML enqueue lock TX
　　Transaction enqueue lock UL User supplied lock) lock_type
　　oobject_name object　 decode(lslmode null Row Share
　　Row Exclusive Share Share Row Exclusive Exclusive null)
　　lock_mode　　oowner　 lssid　 lsserial# serial_num　 lsid　 lsid
　　from sysdba_objects o (　 select sosuser　 susername　 ltype
　　llmode　 ssid　 sserial#　 lid　 lid　 from v$session s
　　v$lock l　 where ssid = lsid ) ls　where oobject_id = lsid and　　oowner
　　<> SYS　 order by oowner oobject_name
　　
　　查看低效率的SQL語句
　　
　　SELECT EXECUTIONS DISK_READS BUFFER_GETS
　　ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS) Hit_radio
　　ROUND(DISK_READS/EXECUTIONS) Reads_per_run
　　SQL_TEXT
　　FROM　 V$SQLAREA
　　WHERE　EXECUTIONS>
　　AND　　 BUFFER_GETS >
　　AND (BUFFER_GETSDISK_READS)/BUFFER_GETS <
　　ORDER BY DESC
From:http://tw.wingwit.com/Article/program/Oracle/201311/17408.html