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博客 ” ，链接：https://blog.itpub.net/25323853/viewspace-695861/ ，如需转载，请注明出处，否则将追究法律责任。
