调优，查找SQL-7727197
查找成本高的SQL语句，在10gR2以后的版本可用SET LONG 40000;
SELECT OPTIMIZER_COST,EXECUTIONS, SQL_FULLTEXT FROM V$SQLAREA WHERE OPTIMIZER_COST>(SELECT MAX(OPTIMIZER_COST)/5 FROM V$SQLAREA) ORDER BY 1;
或
SELECT * FROM ( SELECT OPTIMIZER_COST,EXECUTIONS, SQL_FULLTEXT FROM V$SQLAREA ORDER BY OPTIMIZER_COST DESC) WHERE ROWNUM(SELECT MAX(ROUND((DISK_READS/EXECUTIONS)/5)) FROM V$SQLAREA WHERE EXECUTIONS>0) AND EXECUTIONS>0 AND DISK_READS>100 ORDER BY 1;
查找扫描行数最多的SQL语句
SELECT ROUND(ROWS_PROCESSED/EXECUTIONS) ,ROWS_PROCESSED,EXECUTIONS, SQL_TEXT
FROM V$SQLAREA
WHERE ROUND(ROWS_PROCESSED/EXECUTIONS)>
(SELECT MAX(ROUND((ROWS_PROCESSED/EXECUTIONS)/5)) FROM V$SQLAREA
WHERE EXECUTIONS>0)
AND EXECUTIONS>0 AND ROWS_PROCESSED>1000 ORDER BY 1;
查找排序行数最多的SQL语句
SELECT * FROM (SELECT SQL_TEXT,SORTS FROM V$SQL ORDER BY SORTS DESC) WHERE ROWNUM
页:
[1]