Oracle 运维篇+查看TOP SQL

最新推荐文章于 2021-06-28 19:29:00 发布

zzt_2009

最新推荐文章于 2021-06-28 19:29:00 发布

阅读量318

点赞数 1

本文作者：zzt_2009

本文链接：https://blog.csdn.net/zzt_2009/article/details/80054813

版权

数据库+Oracle 专栏收录该内容

93 篇文章 17 订阅

订阅专栏

最耗内存的10个sql（SGA）

set line 130
col sql_text for a100
col pct for 999
SELECT 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;

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