近期需要给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'
2、如果某个sql被异常终止(手动ctrl+c),会不会记录到V$SQL_MONITOR?
依旧会记录到V$SQL_MONITOR,并且会记录status='DONE (ERROR)';
3、正在执行的sql,超过了5秒,会记录V$SQL_MONITOR,并且状态status='EXECUTING';
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不同;