Oracle 单个表查询速度极慢处理过程
来源:互联网 发布:二战苏联远东部队知乎 编辑:程序博客网 时间:2024/06/05 16:13
Oracle 单个表查询速度极慢处理过程
现象: 今天,接到开发人员报有一个表查询了半天也没出结果,表里大概有几十条数据
配置: Oracle 10G RAC 、Aix Unix操作系统
处理过程:
我的第一反应数据库问题不大,因为只有这张表查询慢,而数据库里其它表数据的查询速度正常,但是按照流程还是先查看了alert日志,看了看临时表空间和系统表空间状况,结果两个节点都很正常没有问题;
然后我就怀疑可能是表锁的问题
select sid,seq#,event from v$session_wait
SID SEQ# EVENT
729 30819 cursor: pin S wait on X
730 55091 cursor: pin S wait on X
731 64627 cursor: pin S wait on X
733 13616 cursor: pin S wait on X
734 3754 cursor: pin S wait on X
735 49828 cursor: pin S wait on X
736 39022 cursor: pin S wait on X
737 44358 cursor: pin S wait on X
738 17548 cursor: pin S wait on X
739 54959 cursor: pin S wait on X
740 26186 cursor: pin S wait on X
741 4140 cursor: pin S wait on X
743 5668 cursor: pin S wait on X
747 54012 cursor: pin S wait on X
730 55091 cursor: pin S wait on X
731 64627 cursor: pin S wait on X
733 13616 cursor: pin S wait on X
734 3754 cursor: pin S wait on X
735 49828 cursor: pin S wait on X
736 39022 cursor: pin S wait on X
737 44358 cursor: pin S wait on X
738 17548 cursor: pin S wait on X
739 54959 cursor: pin S wait on X
740 26186 cursor: pin S wait on X
741 4140 cursor: pin S wait on X
743 5668 cursor: pin S wait on X
747 54012 cursor: pin S wait on X
看到了很多 cursor: pin S wait on X 事件
然后根据这些SID找到SESSION
select * from v$session where sid in(
'908','923','949','984','1034','1012','1015',
'1049','969','936','950','902','954','905',
'908','923','949','984','1034','1012','1015',
'1049','969','936','950','902','954','905',
'924','929','1018','967','993','920','945','1048')
把这些session杀掉
alter system kill session '902,44335';
alter system kill session '905,45229';
alter system kill session '908,5869';
alter system kill session '920,17125';
alter system kill session '923,43172';
alter system kill session '924,614';
alter system kill session '929,669';
alter system kill session '936,1092';
alter system kill session '945,48687';
alter system kill session '949,61466';
alter system kill session '950,50965';
alter system kill session '954,24688';
alter system kill session '967,61065';
alter system kill session '969,63579';
alter system kill session '984,57651';
alter system kill session '993,65365';
alter system kill session '1012,47090';
alter system kill session '1015,59755';
alter system kill session '1018,52751';
alter system kill session '1034,36331';
alter system kill session '1048,13283';
alter system kill session '1049,58233';
alter system kill session '905,45229';
alter system kill session '908,5869';
alter system kill session '920,17125';
alter system kill session '923,43172';
alter system kill session '924,614';
alter system kill session '929,669';
alter system kill session '936,1092';
alter system kill session '945,48687';
alter system kill session '949,61466';
alter system kill session '950,50965';
alter system kill session '954,24688';
alter system kill session '967,61065';
alter system kill session '969,63579';
alter system kill session '984,57651';
alter system kill session '993,65365';
alter system kill session '1012,47090';
alter system kill session '1015,59755';
alter system kill session '1018,52751';
alter system kill session '1034,36331';
alter system kill session '1048,13283';
alter system kill session '1049,58233';
但是结果并不理想,执行上面语句后去查询表依然还是长时间不能出来结果,
且在v$session里查这些session都标记为killed了,然后就想到了到操作系统
级别将这些session进程占用的资源彻底的给释放掉,如下
# kill -9 700924
# kill -9 835748
# kill -9 798908
# kill -9 696720
# kill -9 635236
# kill -9 872802
# kill -9 856554
# kill -9 679966
# kill -9 786648
# kill -9 676248
# kill -9 774198
# kill -9 782442
# kill -9 319908
# kill -9 778586
# kill -9 794812
# kill -9 331788
# kill -9 676090
# kill -9 123228
# kill -9 893198
# kill -9 585828
# kill -9 340378
# kill -9 565494
#
# kill -9 835748
# kill -9 798908
# kill -9 696720
# kill -9 635236
# kill -9 872802
# kill -9 856554
# kill -9 679966
# kill -9 786648
# kill -9 676248
# kill -9 774198
# kill -9 782442
# kill -9 319908
# kill -9 778586
# kill -9 794812
# kill -9 331788
# kill -9 676090
# kill -9 123228
# kill -9 893198
# kill -9 585828
# kill -9 340378
# kill -9 565494
#
经kill -9 以后,再去查询表,结果很快就出来了
现已证实,是此表被锁住的原因,已经让开发去查表设计和使用问题了
转载地址:http://blog.itpub.net/16400082/viewspace-708841/
0 0
- Oracle 单个表查询速度极慢处理过程
- oracle表查询速度极慢的处理过程记录一下
- 解决模糊查询速度慢问题(ORACLE)
- Oracle查询速度慢的原因总结
- Oracle查询速度慢的原因总结
- oracle sql 语句嵌套子查询 查询速度慢
- oracle 11g 表查询慢优化过程
- 查询速度慢
- 一次delete速度异常慢的处理过程
- 一次delete速度异常慢的处理过程
- Oracle中解决DATE类型数据查询速度慢问题
- oracle v$ 视图查询慢处理
- oracle 11g速度慢、plsql登录查询慢、服务器本地sqlplus正常
- 查询速度慢的原因
- 查询速度慢的原因
- mongodb查询速度慢是什么原因?
- 参数化查询速度慢
- Java图片处理速度慢
- Rikka with Chess
- mysql workbench 问题整理
- JS中的call、apply、bind方法
- iOS开发百度导航SDK的使用入门
- 文件的读和写(基础用法)
- Oracle 单个表查询速度极慢处理过程
- 编程学习第六天,参数传递
- centos6安装tomcat7
- Python基础——列表、元组、字典
- memset函数与二维数组初始化的问题
- 2015 Xcode7 真机调试(开发者)以及P12配置
- android:transcriptMode="normal" listview 设置这个属性 讲会滚动到最底一项
- 24 point game(ACM)
- QML的 property 支持的类型