本文共 2921 字，大约阅读时间需要 9 分钟。

•  最耗内存的10个sql（SGA）

set line 130col sql_text for a100col pct for 999SELECT position,       percent,       sql_id,       round(buffer_gets / 1024 / 1024, 2) mb,       sql_text  FROM (SELECT a.*,                              rank() over(ORDER BY buffer_gets DESC) position,                              round(100 * ratio_to_report(buffer_gets)                                    over()) Percent                         FROM v$sqlarea a                        WHERE command_type = 3) WHERE position < 11;

• 使用资源最多的SQL语句：

（较高的磁盘读取（disk_reads消耗I/O）和较高的逻辑读取（buffer_gets消耗CPU）被用作衡量标准） 

select sql_text  from (select sql_text, executions, buffer_gets, disk_reads          from v$sql         where buffer_gets > 100000            or disk_reads > 100000         order by buffer_gets + 100 * disk_reads DESC) where rownum <= 5;                 

• 使用CPU最多的SQL语句：

（较高的逻辑读取（buffer_gets消耗CPU）被用作衡量标准）

select sql_text  from (select sql_text, executions, buffer_gets, disk_reads          from v$sql         where buffer_gets > 100000         order by buffer_gets desc) where rownum <= 5;#或者（直接使用v$sql里的cpu_time）select sql_text, round(cpu_time / 1000000, 2) cpu_seconds  from (select * from v$sql order by cpu_time desc) where rownum <= 5;

•  使用磁盘I/O最多的SQL语句：

（较高的磁盘读取（disk_reads消耗I/O）被用作衡量标准）         

select sql_text  from (select sql_text, executions, buffer_gets, disk_reads          from v$sql         where disk_reads > 100000         order by disk_reads desc) where rownum <= 5;

•  占用数据库时间最多的SQL语句：

select sql_text,       round(elapsed_time / 1000000, 2) elapsed_seconds,       executions  from (select * from v$sql order by elapsed_time desc) where rownum <= 5;

•  执行次数最多的SQL语句：

select sql_text, executions  from (select * from v$sql where executions > 1000 order by executions desc) where rownum <= 5;

• 解析调用最多的SQL语句：

select sql_text, parse_calls  from (select *          from v$sql         where parse_calls > 1000         order by parse_calls desc) where rownum <= 5;

•  使用共享内存最多的SQL语句：

（使用共享内存大于1048576（bytes）的SQL语句会显示）  

select sql_text, sharable_mem  from (select *          from v$sql         where sharable_mem > 1048576         order by sharable_mem desc) where rownum <= 5;

• 排序多的SQL

SELECT sql_text, sorts  FROM (SELECT sql_text, sorts FROM v$sqlarea ORDER BY sorts DESC) WHERE ROWNUM < 21;

• Parse Calls

set linesize 100

set pagesize 100

SELECT *  FROM (SELECT substr(sql_text, 1, 40) sql,               parse_calls,               executions,               hash_value,               address          FROM V$SQLAREA         WHERE parse_calls > 1000         ORDER BY parse_calls DESC) WHERE rownum <= 10;

•  version_count

set linesize 100

set pagesize 100

SELECT *  FROM (SELECT substr(sql_text, 1, 40) sql,               version_count,               executions,               hash_value,               address          FROM V$SQLAREA         WHERE version_count > 20         ORDER BY version_count DESC) WHERE rownum <= 10;

※ 如果您觉得文章写的还不错， 别忘了在文末给作者点个赞哦 ~

over

转载地址：https://blog.csdn.net/zzt_2009/article/details/80054813 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！