Oracle TOP Sql

zz775520666

SELECT   *　　FROM (SELECT   b.username username,
　　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 b.username = 'ZYK'
　　ORDER BY rds_exec_ratio DESC)
　　WHERE ROWNUM < 11
　　ORDER BY rds_exec_ratio DESC
　　1.       识别’低效执行’的SQL语句
　　用下列SQL工具找出低效SQL:
　　SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
　　ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
　　ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
　　SQL_TEXT
　　FROM   V$SQLAREA
　　WHERE  EXECUTIONS>0
　　AND     BUFFER_GETS > 0
　　AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
　　ORDER BY 4 DESC;
　　2.查找单条语句的内存占用率
　　alter system set pga_aggregate_target=10m
　　alter system set workarea_size_policy=auto;
　　select distinct * from a where rownum 10000
　　ORDER BY buffer_gets DESC)
　　WHERE rownum  1000
　　ORDER BY disk_reads DESC)
　　WHERE rownum  100
　　ORDER BY executions DESC)
　　WHERE rownum  1000
　　ORDER BY parse_calls DESC)
　　WHERE rownum  1048576
　　ORDER BY sharable_mem DESC)
　　WHERE rownum  20
　　ORDER BY version_count DESC)
　　WHERE rownum