签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c 性能优化 之 Real-Time SQL Monitoring

2021-01-24 20:42 371 0 原创 Oracle 19c
作者: Dave

1 Real-Time SQL Monitoring 概述

Oracle 数据库的诊断工具有很多, 主要分如下三类。

https://www.cndba.cn/dave/article/4366
https://www.cndba.cn/dave/article/4366

系统级别,包括如下工具:

Top Activity、AWR Report、ASH、ADDM、EM、ExaWatcher、OS Tools、Hang Analyze、Trace Events、System State Dump;

SQL 级别,包括如下工具:

https://www.cndba.cn/dave/article/4366

SQL Monitor Report、ASH、DBMS_XPLAN、EM、Explain Plan、SQLT、10053 Trace、Trace Events、SQL Health Check;

会话级别, 包括如下工具:

ASH、EM、10046 Trace、Trace Events。https://www.cndba.cn/dave/article/4366

本篇讲的SQL Monitoring 是Oracle 11g 引入的工具,可以通过EM、SQL Developer、SQL命令来生成报告。SQL Monitor是SQL 级别性能问题诊断的最佳工具,可以显示一条SQL语句的某次执行的详细信息(包括尚未执行完的SQL),对于长时间运行的SQL, 可以精准的分析确定时间都消耗在哪些地方。

SQL Monitoring 会监视如下的SQL 语句:

1.所有并行执行的SQL语句;
2.超过5秒的串行执行的SQL 语句;
3.带有/+ MONITOR / hint的SQL 语句。

可以从V$SQL_MONITOR视图中可以查到被监控的SQL。 官网关于Real-Time SQL Monitoring的说明如下:https://www.cndba.cn/dave/article/4366

https://www.oracle.com/database/technologies/real-time-sql-monitoring.html

2 生成SQL Monitoring 报告

在上节提到有3种方法可以生成,我们这里只看使用SQL 命令的方式。 https://www.cndba.cn/dave/article/4366

https://www.cndba.cn/dave/article/4366

命令行通过dbms_sql_monitor.report_sql_monitor 函数来生成报告,该函数参数较多,详细可以直接参考官网:

https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_SQL_MONITOR.html

SQL 命令如下:

set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sqltune.report_sql_monitor( sql_id=>'5muutnmhu5myt',type=>'active') from dual;
spool off

在生成报告之前,要先确保SQL 已经被监控(前面提到的3种情况),监控的SQL 可以通过如下SQL 查询,如果SQL 执行时间过短,就需要使用前面提供到hint /+ MONITOR / 来强制监控SQL,然后再生成SQL Monitoring 报告:https://www.cndba.cn/dave/article/4366

select status,username,module,program,sql_id,sql_text from V$SQL_MONITOR order by sql_exec_start desc;

显示所有存储在V$SQL_MONITOR中的sql。

SQL> SET LONG 1000000
SQL> SET LONGCHUNKSIZE 1000000
SQL> SET LINESIZE 1000
SQL> SET PAGESIZE 0
SQL> SET TRIM ON
SQL> SET TRIMSPOOL ON
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>’TEXT’,report_level => ‘ALL’) AS report FROM dual;

我们注意这里的报告的显示type 类型,官网显示有如下几种类型:Text、HTML、ACTIVE、XML。 这几种类型中,首选Active, 因为其最方便我们阅读和分析。

‘ACTIVE’只在11g R2以后才支持,该类型使用HTML和Flash的方式显示动态的报告,与在EM中看到的类似,但在显示时需要从Oracle网站读取相关联的Javascript和Flash。 https://www.cndba.cn/dave/article/4366

不幸的是Adobe 公司在21年已经停用了Flash, 所以如果使用active 类型,报告可能无法正常显示,据说在19c 中已经有了新的类型JET, 不过刚在19c中测试还不支持。

3 SQL Monitoring 分析方法和注意事项

SQL Monitoring 报告阅读时从顶开始向下阅读,重点关注以下几点内容:

1.时间都花在哪个步骤?
2.哪一个行源(row sources)?
3.执行和实际的行数是否一致?
4.并行服务器执行数
5.Nested Loop 迭代次数
6.智能分区操作
7.并行执行进程是否存在数据倾斜https://www.cndba.cn/dave/article/4366

通过以上分析可以精确的定位出SQL 的时间都花在哪里,从而确定SQL 性能不好的根本原因。

除此之外还需要注意以下问题:

1.除非是索引唯一扫描,否则估值为1的步骤一定要引起重视。
2.查看实际行数时需要考虑执行次数。
3.对于Nested Loop 表关联的执行计划,估计的行数时基于单词执行的,而实际的行数时针对所有执行数的总和。
4.并行进程的响应时间进度条长度和实际时间的大小不成比例,要注意看后面的数字。
5.SQL Monitor 报告保存在一个有限的内存空间,被Monitor 的语句可能会因为过期而被刷出内存。
6.被监控的SQL 执行计划行数上限默认是300,如果SQL 执行计划的行数超过300行,则不会被Monitor。 此时可以通过修改_sqlmon_max_planlines 参数来修改该值。

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1832
    原创
  • 2
    翻译
  • 417
    转载
  • 119
    评论
  • 访问:3164828次
  • 积分:2888
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群