oracle數(shù)據(jù)庫性能健康檢查腳本

1、* os層檢查#檢查oracle進程 ps -ef|grep ora|wc -l crsstat -t -vtop glance crsctl check cluster (cssd)vmstat 3600 6*查看基本信息#database 概況信息檢查#檢查database基本信息 select * from v$version;select name ,open_mode,log_mode from v$database;select instance_number,instance_name ,status from gv$instance;show parameter cpu_csh

2、ow parameter block_sizeselect group#,thread#,members,bytes/1024/1024 from gv$log;show sgaselect count(*) from v$controlfileselect count(*) from v$tempfile;select count(*) from v$datafile;# *查看數(shù)據(jù)文件信息# 檢查表空間數(shù)據(jù)文件信息col tablespace_name for a30select tablespace_name , sum(bytes)/1024/1024 from dba_temp_fi

3、les group by tablespace_name;# 檢查表空間SELECTTABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES;# 檢查數(shù)據(jù)文件狀態(tài)select count(*),status from v$datafile group by status;# 檢查表空間使用情況selectf.tablespace_name,a.total,f.free,(a.total-f.free)/1024 "used SIZE(G)",

4、round(f.free/a.total)*100) "% Free"from(select tablespace_name, sum(bytes/(1024*1024) total from dba_data_files group by tablespace_name) a,(select tablespace_name, round(sum(bytes/(1024*1024) free from dba_free_space group by tablespace_name) fWHERE a.tablespace_name = f.tablespace_name(+

5、)order by "% Free"/# 查詢臨時segment 使用情況COL username FORMAT a10;COL segtype FORMAT a10;SELECT username, segtype, extents "Extents Allocated",blocks "Blocks Allocated"FROM v$tempseg_usage;# 查看數(shù)據(jù)文件信息,若文件較多可以根據(jù)需要字段進行排序輸出 top 10SELECT fs.phyrds "Reads", fs.phywrts &q

6、uot;Writes",fs.avgiotim "Average I/O Time", "Datafile"FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;# 查看所有數(shù)據(jù)文件i/o 情況 ,若文件太多,可以改寫為top 10 select *( order by xx desc) where rownum<=10COL ts FORMAT a10 HEADING "Tablespace"COL reads FORMAT 99999

7、0;COL writes FORMAT 999990;COL br FORMAT 999990 HEADING "BlksRead"COL bw FORMAT 999990 HEADING "BlksWrite"COL rtime FORMAT 999990;COL wtime FORMAT 999990;SELECT AS ts, fs.phyrds "Reads", fs.phywrts "Writes",fs.phyblkrd AS br, fs.phyblkwrt AS bw,fs.read

8、tim "RTime", fs.writetim "WTime"FROM v$tablespace ts, v$datafile df, v$filestat fsWHERE ts.ts# = df.ts# AND df.file# = fs.file#UNIONSELECT AS ts, ts.phyrds "Reads", ts.phywrts "Writes",ts.phyblkrd AS br, ts.phyblkwrt AS bw,ts.readtim "RTime",

9、 ts.writetim "WTime"FROM v$tablespace ts, v$tempfile tf, v$tempstat tsWHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;# 查看所有數(shù)據(jù)文件i/o 情況 (比率 ),若文件太多,可以改寫為top 10 select *( order by xx desc) whererownum<=10COL ts FORMAT a10 HEADING "Tablespace"COL reads FORMAT 999990;COL

10、writes FORMAT 999990;COL br FORMAT 999990 HEADING "BlksRead"COL bw FORMAT 999990 HEADING "BlksWrite"COL rtime FORMAT 999990;COL wtime FORMAT 999990;SELECT AS ts, fs.phyrds "Reads", fs.phywrts "Writes",fs.phyblkrd AS br, fs.phyblkwrt AS bw,fs.readtim &q

11、uot;RTime", fs.writetim "WTime"FROM v$tablespace ts, v$datafile df, v$filestat fsWHERE ts.ts# = df.ts# AND df.file# = fs.file#UNIONSELECT AS ts, ts.phyrds "Reads", ts.phywrts "Writes",ts.phyblkrd AS br, ts.phyblkwrt AS bw,ts.readtim "RTime", ts.wr

12、itetim "WTime"FROM v$tablespace ts, v$tempfile tf, v$tempstat tsWHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;# 獲取 top 10 熱 segmentcol objct_name for a30select * from(selectob.owner, ob.object_name, sum(b.tch) Touchsfrom x$bh b , dba_objects obwhere b.obj = ob.data_object_idand

13、b.ts# > 0group by ob.owner, ob.object_nameorder by sum(tch) desc)where rownum <=10# 判斷物理讀最多的objectselect * from (select owner,object_name,value from v$segment_statistics where statistic_name='physicalreads' order by value desc) where rownum<=10# 查看熱點數(shù)據(jù)文件（從單塊讀取時間判斷）SELECT t.file_name

14、,t.tablespace_name,round(s.singleblkrdtim / s.singleblkrds, 2) AS CS,s.READTIM,s.WRITETIMFROM v$filestat s, dba_data_files tWHERE s.file# = t.file_id and rownum<=10 order by cs desc*查看 redo# 檢查日志切換頻率select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round(first_time-lag(

15、first_time) over(order by first_time)*24*60,2) minutes from v$log_history where first_time > sysdate - 1 order by first_time ,minutes;# 檢查 lgwr i/o 性能 (time_waited/total_waits ：表示平均lgwr 寫入完成時間若 >1 表示寫入過慢)select total_waits,time_waited,average_wait,time_waited/total_waits as avg from v$system_e

16、vent where event = 'log file parallel write'# 檢查與 redo 相關(guān)性能指標(biāo)select name,value from v$sysstat where name like '%redo%'# 查詢 redo block size select max(lebsz) from x$kccle;# 查看 redo allocation latch col name for a30select name,gets,misses,misses/gets from v$latch_children where name=&#

17、39;redo allocation'# 查看與 redo 相關(guān)等待事件col event format a40select event,total_waits,total_timeouts,average_wait from v$system_event where upper(event) like'%REDO%'# 查看 session redo eventselect event,total_waits,total_timeouts,average_wait from v$session_event where upper(event) like'%RE

18、DO%'# 查看 user commit 次數(shù)select to_number(value,99999999999) from v$syss tat where name='user commits'# 查看系統(tǒng)運行時間select (sysdate - startup_time)*24*60*60 as seconds from v$instance# 計算出每秒用戶提交次數(shù)select user_commit 次數(shù)/系統(tǒng)運行時間from dual;select value from v$sysstat where name = 'redo blocks wr

19、itten'select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blockswritten') a ,(select value trancount from v$sysstat where name='user commits') b*sga,pga,命中率# sga,pga, 命中率# 檢查 sga show sgaselect * from v$sga;# 查看 buffer cache 命中率select 1-(

20、sum(decode(name, 'physical reads', value, 0)/(sum(decode(name, 'db block gets', value, 0)+(sum(decode(name, 'consistent gets', value, 0) "Buffer Hit Ratio" from v$sysstat;# 查看 buffer cache 建議select size_for_estimate, estd_physical_read_factor,to_char(estd_physical_r

21、eads,99999999999999999999999) as"estd_physical_reads" from v$db_cache_advice where name = 'DEFAULT'COL pool FORMAT a10;SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameterWHERE name = 'db_cache_size') "Current Cache(Mb)",name "Pool", size_for_estimat

22、e "Projected Cache(Mb)",ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%"FROM v$db_cache_adviceWHERE block_size = (SELECT value FROM v$parameterWHERE name = 'db_block_size')ORDER BY 3;# 查看pgashow parameter pga# 查看cache 池show parameter cache# 查看buffer cache中 defalu

23、t pool 命中率select name,1-(physical_reads)/(consistent_gets+db_block_gets) from v$buffer_pool_statistics;# 檢查shared poolshow parameter shared# 檢查shared pool 中 library cacheselect namespace,pinhitratio from v$librarycache;# 檢查整體命中率(library cache)select sum(pinhits)/sum(pins) from v$librarycache;select

24、sum(pins) "hits",sum(reloads) "misses",sum(pins)/(sum(pins)+sum(reloads) "Hits Ratio"from v$librarycache;# 檢查 shered pool free spaceSELECT * FROM V$SGASTATWHERE NAME = 'free memory'AND POOL = 'shared pool'# 檢查 row cache（ 數(shù)據(jù)字典緩沖區(qū)）命中率# 當(dāng)執(zhí)行一個dml或ddl都會造成對數(shù)據(jù)字

25、典的遞歸修改column updates format 999,999,999SELECT parameter,sum(gets),sum(getmisses),100*sum(gets - getmisses) / sum(gets) pct_succ_gets,sum(modifications) updatesFROM V$ROWCACHEWHERE gets > 0GROUP BY parameter;SELECT (SUM(GETS - GETMISSES - FIXED) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;#

26、查看Shared pool latch(多池技術(shù))col parameter for a20col session for a20select a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value"from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv cwhere a.indx = b.indx and a.indx = c.indxand a.ksppinm = '_kghdsidx_

27、count'# 每個子shared pool由 單獨的shared pool latch 保護 查看 他們的命中率shared pool latch ,用于shared pool 空間 回收分配使用的 latchcol name format a15select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool'# 查看shared pool 建議 column c1 heading 'Pool |Size(M) column c2 heading

28、'Size|Factor'column c3 heading 'Est|LC(M) 'column c4 heading 'Est LC|Mem. Obj.'column c5 heading 'Est|Time|Saved|(sec)'column c6 heading 'Est|Parse|Saved|Factor'column c7 heading 'Est|Object Hits' format 999,999,999SELECT shared_pool_size_for_estimate

29、c1,shared_pool_size_factor c2,estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5,estd_lc_time_saved_factor c6,to_char(estd_lc_memory_object_hits,99999999999) c7 FROM V$SHARED_POOL_ADVICE;# 使用 v$shared_pool_advice 算不同 shared pool 大小情況下，響應(yīng)時間，S 單位SELECT 'Shared Pool' component,shar

30、ed_pool_size_for_estimate estd_sp_size,estd_lc_time_saved_factor parse_time_factor,CASEWHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN0ELSEcurrent_parse_time_elapsed_s + adjustment_sEND response_timeFROM (SELECT shared_pool_size_for_estimate,shared_pool_size_factor,estd_lc_time_saved_fa

31、ctor,a.estd_lc_time_saved,e.VALUE / 100 current_parse_time_elapsed_s,c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_sFROM v$shared_pool_advice a,(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,WHERE shared_pool_size_factor = 1) c)/# 查看 shared pool 中 各種類型的chunk 的大小數(shù)量SELE

32、CT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ, To_char( (SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024),'999,999.00')|'k' "AVG SIzE" FROM X$KSMSP GROUP BY KSMCHCLS;# 查看是否有庫緩沖有關(guān)的等待事件select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like

33、'library%'# row cache 命中率SELECT 'Dictionary Cache Hit Ratio ' "Ratio",ROUND(1 - (SUM(GETMISSES) / SUM(GETS) * 100,2)|'%'"Percentage"FROM V$ROWCACHE;#library cache 中詳細比率信息SELECT 'Library Lock Requests' "Ratio", ROUND(AVG(gethitratio) * 100

34、, 2)|'%' "Percentage" FROM V$LIBRARYCACHEUNIONSELECT 'Library Pin Requests' "Ratio", ROUND(AVG(pinhitratio)# 100, 2)|'%' "Percentage" FROM V$LIBRARYCACHEUNIONSELECT 'Library I/O Reloads' "Ratio", ROUND(SUM(reloads) / SUM(pins) *

35、 100, 2)|'%' "Percentage" FROM V$LIBRARYCACHEUNIONSELECT 'Library Reparses' "Ratio", ROUND(SUM(reloads) / SUM(pins) * 100, 2) |'%' "Percentage" FROM V$LIBRARYCACHE;# 查詢sga 中各個 pool 情況COL name FORMAT a32;SELECT pool, name, bytes FROM v$sgastatWHER

36、E pool IS NULLOR pool != 'shared pool' OR (pool = 'shared pool'AND (name IN('dictionary cache','enqueue','librarycache','parameters','processes','sessions','free memory')ORDER BY pool DESC NULLS FIRST, name;SELECT * FROM V$SGAIN

37、FO;# 查看使用shard_pool 保留池情況SELECT request_misses, request_failures, free_spaceFROM v$shared_pool_reserved;# Oracle 專門從共享池內(nèi)置出一塊區(qū)域來來分配內(nèi)存保持這些大塊。這個保留共享池的默認大小是共享池的 5%(_shared_pool_reserved_pct 5 控制 ) oracle 建設(shè)置為10% 。大小通過參數(shù)SHARED_POOL_RESERVED_SIZE 改。它是從共享池中分配，不是直接從SGA 中分配的，它是共享池的保留部分，專門用于存儲大塊段#shared pool

38、中內(nèi)存大于_SHARED_POOL_RESERVED_MIN_ALLOC 將放入 shared pool 保留池,保留池維護一個單獨的freelist,lru ，并且不會在lru 列表存 recreatable 類型 chunks ，普通 shared pool 的釋放與 shared pool 保留池?zé)o關(guān)。# 關(guān)于設(shè)置SHARED_POOL_RESERVED_SIZE# 1. 如果 系統(tǒng)出現(xiàn)ora-04031, 發(fā)現(xiàn) 請求內(nèi)存都是大于_SHARED_POOL_RESERVED_MIN_ALLOC(default 10GR2 4400) , 且 v$shared_pool_reserved 中

39、有大量REQUEST_MISSES( 并且可以看下LAST_MISS_SIZE ) 表示 SHARED_POOL_RESERVED_SIZE 太小了 需要大的內(nèi)存的請求失敗,那么需要加大 SHARED_POOL_RESERVED_SIZE#2.如果ora-04031 請求內(nèi)存出現(xiàn)在 4100-4400 并造成shared pool lru 合并,老化換出內(nèi)存 ，可以 調(diào)小_SHARED_POOL_RESERVED_MIN_ALLOC讓此部分內(nèi)存 進入 shared reserved pool, 相應(yīng)的加大SHARED_POOL_RESERVED_SIZE# 3.從 v$shared_pool_

40、reserved來判斷，如果 REQUEST_FAILURES>0（ 出現(xiàn)過 ora-04031）且LAST_FAILURE_SIZE（最后請求內(nèi)存大小）>_SHARED_POOL_RESERVED_MIN_ALLOC 表示 shared reserved pool 缺少連續(xù)內(nèi)存，可以力口大 SHARED_POOL_RESERVED_SIZE, 減少 _SHARED_POOL_RESERVED_MIN_ALLOC少放對象，并相對力口大 shared_pool_size# 要是反過來 REQUEST_FAILURES>0（ 出現(xiàn)過ora-04031）且LAST_FAILURE_

41、SIZE（ 最后請求內(nèi)存大 ?。?lt;_SHARED_POOL_RESERVED_MIN_ALLOC,表示在shared pool中缺少連續(xù)內(nèi)存，可以力口減少_SHARED_POOL_RESERVED_MIN_ALLOC多放入一些對象，減少 shared pool 壓力，適當(dāng)加大shared_pool_size,SHARED_POOL_RESERVED_SIZE# 從library中獲取執(zhí)行次數(shù)最多的top 10 sql查看他們的具體信息（未含解析次數(shù)）COL execs FORMAT 9999990COL parses FORMAT 99990COL fetches FORMAT 9999

42、90COL loads FORMAT 9990COL invalids FORMAT 9999990COL i/o FORMAT 99990COL hits FORMAT 9999990COL rows FORMAT 999990COL sorts FORMAT 9990COL sql FORMAT a32SELECT * FROM（SELECT executions "Execs"-Executions,parse_calls "Parses"-Parsesfetches "Fetches"-Fetches,loads"L

43、oads"-Loads and reloads,invalidations "Invalids"-Invalidations,disk_reads "I/O"-I/O,buffer_gets "Hits"-Buffer hits rows_processed "Rows"-Rows,sorts"Sorts"-Sorts,sql_text "SQL"FROM v$sqlareaORDER BY executions DESC) WHERE ROWNUM <= 1

44、0;# 查詢還保留在library cache 中，解析次數(shù)和執(zhí)行次數(shù)最多的sql( 解析 *執(zhí)行 )COL sql_text FORMAT A38;SELECT * FROM(SELECT parse_calls*executions "Product", parse_calls"Parses",executions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC)WHERE ROWNUM <= 10;# 查看shared pool 中 ,row cache 詳細信息SELEC

45、T COUNT "Entries", GETS "Requests", GETMISSES "Misses",MODIFICATIONS "DML Activity", parameter "Area"FROM v$rowcache;# DBMS_SHARED_POOL.UNKEEP (object, type);# 查看shared pool 建議SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameterWHERE name = '

46、shared_pool_size') "Current Mb", shared_pool_size_for_estimate "Projected Mb", ROUND(shared_pool_size_factor*100) "%", ESTD_LC_SIZE "Library Mb", ESTD_LC_TIME_SAVED "Parse Savings",to_char(ESTD_LC_MEMORY_OBJECT_HITS,'999999999999') "

47、Hits"FROM v$shared_pool_adviceORDER BY 1;# 查看 library cache 內(nèi)存分配情況（對哪類對象）SELECT lc_namespace "Library",LC_INUSE_MEMORY_OBJECTS "Objects",LC_INUSE_MEMORY_SIZE "Objects Mb",LC_FREEABLE_MEMORY_OBJECTS "Freeable Objects",LC_FREEABLE_MEMORY_SIZE "Freeable

48、 Mb"FROM v$library_cache_memory;# 查看pgashow parameters area_size# 查看pgaSELECT * FROM v$pgastat;查看pga建議SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameterWHERE name = 'pga_aggregate_target') "Current Mb",ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb",R

49、OUND(estd_pga_cache_hit_percentage) "%"FROM v$pga_target_adviceORDER BY 2;#Database read buffer cache hit ratio =# 1 - (physical reads / (db block gets + consistent gets)SELECT to_char(value,'9999999999999'), name FROM V$SYSSTAT WHERE name IN ('physical reads', 'db bloc

50、k gets', 'consistent gets');# 查詢數(shù)據(jù)庫命中率SELECT 'Database Buffer Cache Hit Ratio ' "Ratio",ROUND(1-(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'physical reads')/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets') + (SELECT SUM(value) FROM V$

51、SYSSTAT WHERE name = 'consistent gets') * 100)|'%' "Percentage"FROM DUAL;# alter table xx cache# 查看cache 中所有 pool, 命中情況COL pool FORMAT a10;SELECT "Pool", a.physical_reads, a.db_block_gets, a.consistent_gets,(SELECT ROUND(1-(physical_reads / (db_block_gets +

52、 consistent_gets)*100)FROM v$buffer_pool_statisticsWHERE db_block_gets+consistent_gets != 0AND name = ) "Ratio"FROM v$buffer_pool_statistics a;# ALTER TABLE(INDEX) xx STORAGE(BUFFER_POOL KEEP);# 查看數(shù)據(jù)庫cache 或 keep 了哪些 objectCOL table_name FORMAT A16COL index_name FORMAT A16SELECT tabl

53、e_name AS "Table", NULL, buffer_pool, cache FROMuser_tablesWHERE buffer_pool != 'DEFAULT' OR TRIM(cache)='Y'UNIONSELECT table_name, index_name, NULL, buffer_pool FROMuser_indexesWHERE buffer_pool != 'DEFAULT'ORDER BY 1, 2 NULLS FIRST;# 取消cache 或 keep(keep pool)# ALT

54、ER TABLE XX NOCACHE;#SELECT 'ALTER INDEX '|index_name|' STORAGE(BUFFER_POOL DEFAULT);'#FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT'*# 檢查undoshow parameter undo_# 檢查undo rollback segment 使用情況select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollna

55、me b where a.usn=b.usn order by waits desc;select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b;# 計算每秒鐘產(chǎn)生的undoblk 數(shù)量select sum(undoblks)/sum(end_time-begin

56、_time)*24*60*60) from v$undostat;# Undospace=UR*UPS*blocksize + overload(10%), 計算 undo tablespace 大小show parameter block_sizeshow parameter undo_retention每秒產(chǎn)# select undo_retention* 每秒產(chǎn)生undoblk 數(shù)量 *block_size/1024/1024/1024+ (1+1undo_retention*生 undoblk 數(shù)量 *block_size/1024/1024/1024*0.1) from dual;#

57、 查詢 undo 具體信息COL undob FORMAT 99990;COL trans FORMAT 99990;COL snapshot2old FORMAT 9999999990;SELECT undoblks "UndoB", txncount "Trans",maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency",ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSp

58、aceWait"FROM v$undostat;# 查詢 rollback 段詳細信息（收縮次數(shù),擴展次數(shù),平均活動事務(wù)等）COL RBS FORMAT a4;SELECT "RBS", s.extends "Extends", s.shrinks"Shrinks",s.wraps "Wraps", s.aveshrink "AveShrink",s.aveactive "AveActive"FROM v$rollname n JOIN v$rolls

59、tat s USING(usn)WHERE != 'SYSTEM'# 查詢當(dāng)前rollback segment 使用情況COL RBS FORMAT a4;SELECT "RBS", s.status, s.waits, s.gets, to_char(s.writes,'9999999999999'),s.xacts "Active Trans"FROM v$rollname n JOIN v$rollstat s USING(usn)WHERE != 'SYSTEM&#

60、39;# 查詢使用rollback segment 時等待比率SELECT ROUND(SUM(waits/gets)*100,2)|'%' "Contention" FROMv$rollstat;# 查詢使用rollback segment 時等待比率及其平局活動事務(wù)數(shù)COL contention FORMAT 9999999990;SELECT AVG(xacts) "Trans per RBS",ROUND(SUM(waits/gets)*100,2)|'%' "Contention"*FROM

61、v$rollstat;查看其它性能指標(biāo)信息（等待事件,latch 等 ,排序,解析,index 使用率 ）# 查看數(shù)據(jù)庫中行chainSELECT 'Chained Rows ' "Ratio, ROUND(SELECT SUM(value) FROM V$SYSSTATWHERE name = 'table fetch continued row')/ (SELECT SUM(value) FROM V$SYSSTATWHERE name IN ('table scan rows gotten', 'table fetch b

62、yrowid')# 100, 3)|'%' "Percentage"FROM DUAL;# 在內(nèi)存中排序比率(最優(yōu)排序)SELECT 'Sorts in Memory ' "Ratio", ROUND(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)')/ (SELECT SUM(value) FROM V$SYSSTATWHERE name IN ('sorts (memory)', 'sorts (disk)') * 100, 5)|'%' "Percentage"FROM DUAL;# 查詢解析比率SELECT 'Soft Parses '