调优，查找SQL-7727197

yangcctv

　　查找成本高的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