Oracle - v$lock查询慢原因分析
数据库内部对象X$统计信息过旧,导致v$lock查询慢
前段时间用python写了个zabbix监控脚本,里面有一个检查锁的sql语句,sql语句是这样子的
select count(*) retvalue from v$lock where type in('TM', 'TX') and ctime > 600;
但是zabbix界面显示这条语句超时,zabbix超时时间默认是3s,我将其改为15s,竟然还是超时,看样子要仔细研究这个sql语句了。
这一看不得了,这条语句执行用了18s,统计v$lock的行数竟然要7min之久,这明显无法接受。
SQL> select count(*) retvalue from v$lock where type in('TM', 'TX') and ctime > 600; RETVALUE
----------
0 Elapsed: 00:00:18.82
查看其执行计划
SQL> select * from table(dbms_xplan.display_cursor()); ---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 53 | |
|* 2 | HASH JOIN | | 1 | 53 | 0 (0)|
| | MERGE JOIN CARTESIAN | | | | ()|
|* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 19 | 0 (0)|
| 5 | BUFFER SORT | | 1 | 22 | 0 (0)|
|* 6 | FIXED TABLE FULL | X$KSQRS | 1 | 22 | 0 (0)|
| 7 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)|
| 8 | UNION-ALL | | | | |
|* 9 | FILTER | | | | |
| 10 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0)|
| 11 | UNION-ALL | | | | |
|* 12 | FIXED TABLE FULL| X$KDNSSF | 1 | 77 | 0 (0)|
|* 13 | FIXED TABLE FULL| X$KSQEQ | 1 | 77 | 0 (0)|
|* 14 | FIXED TABLE FULL | X$KTADM | 1 | 77 | 0 (0)|
|* 15 | FIXED TABLE FULL | X$KTATRFIL | 1 | 77 | 0 (0)|
|* 16 | FIXED TABLE FULL | X$KTATRFSL | 1 | 77 | 0 (0)|
|* 17 | FIXED TABLE FULL | X$KTATL | 1 | 77 | 0 (0)|
|* 18 | FIXED TABLE FULL | X$KTSTUSC | 1 | 77 | 0 (0)|
|* 19 | FIXED TABLE FULL | X$KTSTUSS | 1 | 77 | 0 (0)|
|* 20 | FIXED TABLE FULL | X$KTSTUSG | 1 | 77 | 0 (0)|
|* 21 | FIXED TABLE FULL | X$KTCXB | 1 | 77 | 0 (0)|
--------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("SADDR"="S"."ADDR" AND
TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||
RAWTOHEX("R"."ADDR"))
4 - filter("S"."INST_ID"=USERENV('INSTANCE'))
6 - filter(("R"."KSQRSIDT"='TM' OR "R"."KSQRSIDT"='TX'))
9 - filter(USERENV('INSTANCE') IS NOT NULL)
统计v$lock的行数
SQL> select count(*) from v$lock; COUNT(*)
----------
600 Elapsed: 00:07:46.84
这条语句的执行计划与上面的一样,这里我就不贴出来了
v$lock只有600行,怎么会执行时间这么久,通过v$session能看到这条语句的等待事件为"direct path read temp"
该等待事件表示服务器进程直接读取临时表空间的数据,通常由临时表太大造成。从上面的执行计划中可以看出临时表很大的原因可能是"MERGE JOIN CARTESIAN"。
"MERGE JOIN CARTESIAN"表示笛卡尔联接,如果两表的行数都不小的话,这的确会造成临时表过大。查看X$KSUSE,X$KSQRS的行数
SQL> select count(*) from X$KSUSE; COUNT(*)
----------
4544 SQL> select count(*) from X$KSQRS; COUNT(*)
----------
20224
这几千和几万来个笛卡尔积就是几千万的临时数据了,而我的pga只有4g,pga不够所以就用到了临时表空间进行表关联,也就造成了等待事件,所以说这条语句慢的主因就是这个笛卡儿积。
这条语句之前执行都好好的,为什么现在慢了呢,最可能的情况是统计信息过旧,因为自动统计信息收集job不会收集固定对象也就是X$表的统计信息。
收集下固定对象的统计信息
SQL> begin
dbms_stats.gather_fixed_objects_stats;
end;
/ PL/SQL procedure successfully completed.
再执行以下语句,可以看到执行时间0.1s都不到,而且执行计划也恢复正常,赶紧在我这边的生产库把类似问题进行处理,嘿嘿。
SQL> select count(*) from v$lock; COUNT(*)
----------
600 Elapsed: 00:00:00.08 SQL> select * from table(dbms_xplan.display_cursor()); ---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)| |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
| 2 | HASH JOIN | | 3034 | 106K| 29 (100)| 00:00:01 |
| 3 | HASH JOIN | | 15 | 360 | 23 (100)| 00:00:01 |
| 4 | VIEW | GV$_LOCK | 15 | 180 | 22 (100)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | FILTER | | | | | |
| 7 | VIEW | GV$_LOCK1 | 7 | 84 | 15 (100)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | FIXED TABLE FULL| X$KDNSSF | 1 | 16 | 1 (100)| 00:00:01 |
| 10 | FIXED TABLE FULL| X$KSQEQ | 6 | 102 | 14 (100)| 00:00:01 |
| 11 | FIXED TABLE FULL | X$KTADM | 1 | 20 | 5 (100)| 00:00:01 |
| 12 | FIXED TABLE FULL | X$KTATRFIL | 1 | 14 | 0 (0)| |
| 13 | FIXED TABLE FULL | X$KTATRFSL | 1 | 14 | 0 (0)| |
| 14 | FIXED TABLE FULL | X$KTATL | 1 | 20 | 0 (0)| |
| 15 | FIXED TABLE FULL | X$KTSTUSC | 1 | 14 | 0 (0)| |
| 16 | FIXED TABLE FULL | X$KTSTUSS | 1 | 16 | 0 (0)| |
| 17 | FIXED TABLE FULL | X$KTSTUSG | 1 | 14 | 0 (0)| |
| 18 | FIXED TABLE FULL | X$KTCXB | 1 | 18 | 1 (100)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSUSE | 4544 | 54528 | 1 (100)| 00:00:01 |
| 20 | FIXED TABLE FULL | X$KSQRS | 20224 | 237K| 5 (100)| 00:00:01 |
---------------------------------------------------------------------------------------
总结:
1.一些动态性能视图v$查询很慢的话,可能是由于动态性能视图所查询的内部对象表x$统计信息过旧,cbo选择了错误的执行计划造成。
2.自动统计信息收集job不会收集内部对象表的统计信息,所以需要dba定时手工收集,或者是自己创建个job定期执行。
最新文章
- iOS引入JavaScriptCore引擎框架(一)
- MySQL数据库备份--mysqldump用法
- Quartz.Net与MVC结合定时任务
- 使用Wireshark 查看查找未被过滤端口
- [poi2007]mem
- [zt]矩阵求导公式
- Struts2(十七)验证框架二
- jQuery 选择器语法
- hdu 1075 What Are You Talking About
- Nodejs 处理gb2312内容乱码问题
- bzoj1878
- malloc &; free
- PHP加密字符串函数(Discuz内置的)
- Chrome DevTools 开发者工具 技巧 调试
- ioutil包二
- python封装configparser模块获取conf.ini值
- CMDB资产管理系统开发【day25】:表结构设计2
- 【POI每日题解 #6】KRA-The Disks
- 【BZOJ】4008: [HNOI2015]亚瑟王
- mirror op 如果在windows receiver上是黑屏
热门文章
- 必威电竞2019或将赞助SKT,携手Faker再创辉煌
- 第04组 Beta冲刺(3/4)
- java之四种权限修饰符
- ESP8266源码分析--打印的基本用法
- 对numpy.meshgrid()理解
- 通俗易懂,什么是.NET/.NET Framework/.NET Core/.Net Standard?
- C#中 EF 性能优化
- 验证码无法正常显示,提示Could not initialize class sun.awt.X11GraphicsEnvironment
- Zimbra
- Command CompileSwiftSources failed with a nonzero exit code