ITPub博客

首页 > 数据库 > Oracle > Oracle慢sql监控

Oracle慢sql监控

原创 Oracle 作者:贺子_DBA时代 时间:2024-01-02 22:36:18 0 删除 编辑
近期需要给Oracle数据库添加慢sql监控,需要拿到sql文本,以及schema信息和真实耗时等等,于是探索了下Oracle 11g的特性sql monitor;
1、如果同一个条慢sql执行两次,就会记录两条数据,如下图所示:
select  sql_id,username,sql_text,to_char(sql_exec_start,'yyyy-mm-dd hh24:mi:ss'),elapsed_time/1000000   from V$SQL_MONITOR
where  username='LIUWENHE'and  sql_id='aqpnmfb67398u'
0
2、如果某个sql被异常终止(手动ctrl+c),会不会记录到V$SQL_MONITOR?
依旧会记录到V$SQL_MONITOR,并且会记录status='DONE (ERROR)';
0
3、正在执行的sql,超过了5秒,会记录V$SQL_MONITOR,并且状态status='EXECUTING';
0
3、处于锁等待的sql不会记录到V$SQL_MONITOR;
4、V$SQL_MONITOR视图中的统计信息将被实时刷新,频率为每秒1次;
5、oracle慢sql执行时间统计,统计最近65秒的数据
select  sql_id,status,username,sql_text,to_char(sql_exec_start,'yyyy-mm-dd hh24:mi:ss'),sql_exec_start,TRUNC(elapsed_time/1000000,0) spend_time   from gV$SQL_MONITOR  where  username not in ('SYS','SYSTEM')  and last_refresh_time>sysdate-65/(3600*24);
针对rac场景使用gv$SQL_MONITOR
select  sql_id,inst_id,status,username,inst_id,sql_text,to_char(sql_exec_start,'yyyy-mm-dd hh24:mi:ss') sql_exec_start,TRUNC(elapsed_time/1000000,0) spend_time   from gv$SQL_MONITOR  where  username not in ('SYS','SYSTEM')  and last_refresh_time>sysdate-65/(3600*24)
6、V$SQL_MONITOR视图有三个时间字段:(慢sql监控,我选择的是last_refresh_time,每60秒探测一次数据)
1)first_refresh_time :sql第一刷进V$SQL_MONITOR的时间;
2)last_refresh_time : sql最近一次刷新的时间,例如某个sql执行了100秒,但是V$SQL_MONITOR一秒刷新一次,所以这个时间会一直刷新,直到sql执行结束!
3)sql_exec_start   :sql开始执行的时间,注意默认执行时间超过5秒才会记录到V$SQL_MONITOR;
7、v$sql_monitor 数据存储在哪里?
数据存在于sga中,重启就没有了;
8.Oracle Database 对复合数据库操作进行监视,当满足以下任一条件时:plsql块,包等等
使用 DBMS_SQL_MONITOR.BEGIN_OPERATION 开始数据库操作,并且该操作已经消耗了至少5秒的CPU或I/O时间。
在 DBMS_SQL_MONITOR.BEGIN_OPERATION 中将 FORCE_TRACKING 设置为 Y 来强制跟踪该操作。
对于复合数据库操作,每一行包含一个操作,该操作的统计信息是通过在同一会话中作为操作的一部分运行的SQL语句和PL/SQL子程序的累积得出的。
9、开启sql monitor
默认的,statistics_level和control_management_pack_access为TYPICAL和DIAGNOSTIC+TUNING,即自动开启sql monitor。
SQL> show parameter STATISTICS_LEVE
NAME               TYPE            VALUE
------------------------------------ ----------- ------------------------------
statistics_level   string          TYPICAL
SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS
NAME                                               TYPE               VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access   string     DIAGNOSTIC+TUNING
10、默认v$sql_monitor中数据保证保存1分钟,然后超过5秒的sql会记录到v$sql_monitor,可以通过隐含参数来调整:
1)修改成超过2秒就记录,类似于MySQL的慢sql阈值:(不需要重启就可以生效)
alter system set "_sqlmon_threshold"=2;
2) 修改强制保存的时间,如下强制保存300秒,也就是5分钟: (不需要重启就可以生效)
alter system set "_sqlmon_recycle_time"=300
11、关闭sql monitor
如果statistics_level和control_management_pack_access为TYPICAL和DIAGNOSTIC+TUNING,那么可以修改隐含参数_sqlmon_threshold为0即可关闭sql monitor,防止某sql从v$sql_monitor age out,除了增大_sqlmon_recycle_time,还可以临时关闭sql monitor
关闭:
alter system set "_sqlmon_threshold"=0;
开启:
alter system set "_sqlmon_threshold"=5
_sqlmon_threshold设为0后,以下sql全都不再被自动监控
1)sql单次执行的IO/CPU时间超过5s
2)加hint并行执行的sql,如加 / +parallel(t,4)/
3)加/ +monitor/这种强制监控的语句
12、v$sql_monitor的主键:
SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID三个字段构成的组合主键;其中sql_exec_id 就是一个标识id,主要用来区分同一个sql_id的sql在同一个session中多次执行,这个时候SQL_EXEC_ID不同;

小结:Oracle有类似于MySQL慢sql的记录, v$sql_monitor就可以实现。

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/29654823/viewspace-3002427/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
10年数据库管理员从业经历,熟悉Oracle、mysql、tidb、oceanbase、redis、 mongodb 、python等技术栈,持有11g ocm 、mysql 5.7 ocp 、tidb 5.0 ptcp、tidb 6.0 ptca、oceanbase obcp等

注册时间:2014-05-12

  • 博文量
    330
  • 访问量
    2156683