Oracle查看逻辑读、物理读资源占用排行的SQL语句

206 阅读 0 评论 136 点赞

我是靠谱客的博主细心电灯胆，最近开发中收集的这篇文章主要介绍Oracle查看逻辑读、物理读资源占用排行的SQL语句，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

查看逻辑读前10的SQL：

复制代码代码如下:

set linesize 300;

set pagesize 300;

set long 50000;

SELECT *

FROM (

    SELECT sql_fulltext AS sql, SQL_ID,buffer_gets / executions AS "Gets/Exec",buffer_gets, executions

    FROM V$SQLAREA

    WHERE buffer_gets > 10000

    ORDER BY "Gets/Exec" DESC

)

WHERE rownum <= 10;

查看物理读前10的SQL：

复制代码代码如下:

set linesize 300;

set pagesize 300;

set long 50000;

SELECT *

FROM (

    SELECT sql_fulltext AS sql, SQL_ID,disk_reads / executions AS "Reads/Exec",disk_reads, executions 

    FROM V$SQLAREA

    WHERE disk_reads > 1000

    ORDER BY "Reads/Exec" DESC

)

WHERE rownum <= 10;