oracle效能優化（二）-調整查詢

--V$SQLAREA檢視中選出最佔用資源的查詢
SELECT B.USERNAME USERNAME,A.DISK_READS READS,
       A.EXECUTIONS EXEC,A.DISK_READS/DECODE(A.EXECUTIONS,0,1,A.EXECUTIONS) RDS_EXEC_RATIO,
       A.SQL_TEXT STATEMENT
FROM V$SQLAREA A,DBA_USERS B
WHERE A.PARSING_USER_ID=B.USER_ID
AND A.DISK_READS>100000
ORDER BY A.DISK_READS DESC
/
--v$sql檢視中選出最佔用資源的查詢
SELECT *
FROM (SELECT S.SQL_TEXT,
             RANK() OVER(ORDER BY BUFFER_GETS DESC) AS RANK_BUFGETS,
             TO_CHAR(100*RATIO_TO_REPORT(BUFFER_GETS) OVER(),'999,99') PCT_BUFGETS
      FROM V$SQL S)
WHERE RANK_BUFGETS<11
/
--選出最佔資源的會話
v$sessmetric
從v$sessmetric檢視中選出當前最佔用資源的會話

select *from v$sessmetric
/
--檢視可用的awr快照
dba_hist_snapshot

select *From dba_hist_snapshot
/
--dba_hist_sqlstat檢視中選出最佔用資源的查詢
select  *from dba_hist_sqlstat
/
--dba_hist_sql_plan檢視中選出查詢執行計劃
/
---------------=========================
對select 和 where中的列使用索引

...................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25323853/viewspace-695861/，如需轉載，請註明出處，否則將追究法律責任。