oracle表查询速度极慢的处理过程记录一下

Oracle 单个表查询速度极慢处理过程

 

现象：前两天看到我们的oracle数据库，一条查询语句执行的特别慢，导致应用程序连接超时，客户根本查不出来东西，非常着急。后来在plus中执行select count(1) from fee,也特别慢，这张表一共才50w的数据。

配置：        Oracle 11G RAC 、linux redhat操作系统

处理过程：

我最开始认为是不是查询语句的问题，或者索引的问题。于是把性能差的语句截出来看了看。

SELECT * FROM (select * FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10;

前十条基本都是查询有分页的情况，数据库在计算分页的总条数。于是根据SQL建了几个字段的索引，日期和BU_CD等字段。结果优化的不是很理想。

查了一下 上一篇网友提供的步骤，觉得思路特别对，于是就按照执行了一遍。然后查看了alert日志，看了看临时表空间和系统表空间的状况，结果两个节点都很正常没有问题；最后怀疑是表锁的问题。

SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;

也没有锁表的问题。奇怪，最后查了一下所有连接库的session运行情况。

select distinct a.sid,b.SERIAL#,b.PROCESS,b.STATUS from v$session_wait a,v$session b
where a.SID=b.SID

最后查出来有一堆的session，基本分不清什么连接，于是粗暴的把session 全部kill掉：

alter system kill session '2,1';
alter system kill session '69,1';
alter system kill session '70,3';
alter system kill session '71,7';
alter system kill session '76,443';
alter system kill session '126,3';
alter system kill session '131,1';
alter system kill session '134,9';
alter system kill session '192,1';
alter system kill session '3,1';
alter system kill session '4,3';
alter system kill session '5,1';
alter system kill session '125,1';
alter system kill session '6,1';
alter system kill session '67,1';
alter system kill session '127,3';
alter system kill session '190,1';
alter system kill session '66,1';
alter system kill session '128,1';
alter system kill session '129,1';
alter system kill session '144,3023';
alter system kill session '187,1';
alter system kill session '194,2149';
alter system kill session '1,1';
alter system kill session '189,1';
alter system kill session '196,3';
alter system kill session '7,3';
alter system kill session '132,11';
alter system kill session '193,1';
alter system kill session '197,6729';
alter system kill session '9,3';
alter system kill session '10,3';
alter system kill session '65,1';
alter system kill session '64,1';
alter system kill session '75,1567';
alter system kill session '188,1';
alter system kill session '191,1';

执行上面语句后去查询表依然还是长时间不能出来结果，

且在v$session里查这些session都标记为killed了，然后就想到了到操作系统级别将这些session进程占用的资源彻底的给释放掉，如下：

在linux下执行：

ps -aux|grep ora;

oracle   23485  0.0  0.2 3440784 22132 ?       Ss   18:50   0:00 ora_pmon_cdzfrac1
oracle   23487  0.1  0.2 3440424 17600 ?       Ss   18:50   0:00 ora_psp0_cdzfrac1
oracle   23489  1.6  0.2 3438356 17188 ?       Ss   18:50   0:00 ora_vktm_cdzfrac1
oracle   23493  0.0  0.2 3440040 19592 ?       Ss   18:50   0:00 ora_gen0_cdzfrac1
oracle   23495  0.1  0.3 3444520 25228 ?       Ss   18:50   0:00 ora_diag_cdzfrac1
oracle   23497  0.1  0.3 3441960 28800 ?       Ss   18:50   0:00 ora_dbrm_cdzfrac1
oracle   23499  0.0  0.2 3438356 18056 ?       Ss   18:50   0:00 ora_ping_cdzfrac1
oracle   23501  0.0  0.2 3438356 17236 ?       Ss   18:50   0:00 ora_acms_cdzfrac1
oracle   23503  0.2  0.3 3446568 31328 ?       Ss   18:50   0:00 ora_dia0_cdzfrac1
oracle   23506  0.3  0.5 3452964 48656 ?       Ss   18:50   0:00 ora_lmon_cdzfrac1
oracle   23508  1.9  0.4 3454660 39888 ?       Ss   18:50   0:00 ora_lmd0_cdzfrac1
oracle   23510  1.0  1.2 3453268 100168 ?      Ss   18:50   0:00 ora_lms0_cdzfrac1
oracle   23514  1.0  1.2 3453268 100380 ?      Ss   18:50   0:00 ora_lms1_cdzfrac1
oracle   23518  0.0  0.2 3438356 18120 ?       Ss   18:50   0:00 ora_rms0_cdzfrac1
oracle   23520  0.0  0.2 3438952 19564 ?       Ss   18:50   0:00 ora_lmhb_cdzfrac1
oracle   23522  1.8  2.6 3438356 216396 ?      Ss   18:50   0:00 ora_mman_cdzfrac1
oracle   23524  0.0  0.3 3449252 28544 ?       Ss   18:50   0:00 ora_dbw0_cdzfrac1
oracle   23526  0.1  0.4 3464124 40288 ?       Ss   18:50   0:00 ora_lgwr_cdzfrac1
oracle   23528  0.0  0.3 3442620 26648 ?       Ss   18:50   0:00 ora_ckpt_cdzfrac1
oracle   23530  0.3  0.5 3441976 43860 ?       Ss   18:50   0:00 ora_smon_cdzfrac1
oracle   23532  0.0  0.2 3438356 17476 ?       Ss   18:50   0:00 ora_reco_cdzfrac1
oracle   23534  0.0  0.2 3441020 20968 ?       Ss   18:50   0:00 ora_rbal_cdzfrac1
oracle   23536  0.0  0.2 3440236 23272 ?       Ss   18:50   0:00 ora_asmb_cdzfrac1
oracle   23538  2.1  1.0 3446932 86268 ?       Ss   18:50   0:00 ora_mmon_cdzfrac1
oracle   23542  0.0  0.2 3439588 23952 ?       Ss   18:50   0:00 ora_mmnl_cdzfrac1
oracle   23544  0.0  0.2 3444612 17456 ?       Ss   18:50   0:00 ora_d000_cdzfrac1
oracle   23546  0.0  0.3 3446068 25048 ?       Ss   18:50   0:00 ora_mark_cdzfrac1
oracle   23548  0.0  0.1 3439552 16184 ?       Ss   18:50   0:00 ora_s000_cdzfrac1
oracle   23550  0.1  0.2 3440236 21196 ?       Ss   18:50   0:00 ora_ocf0_cdzfrac1
oracle   23554  2.2  0.4 3439464 35864 ?       Ss   18:50   0:00 ora_lck0_cdzfrac1
oracle   23556  0.0  0.2 3444372 22148 ?       Ss   18:50   0:00 ora_rsmn_cdzfrac1
oracle   23575  0.1  0.2 3439836 19432 ?       Ss   18:50   0:00 ora_o000_cdzfrac1
oracle   23595  0.0  0.2 3438356 17464 ?       Ss   18:50   0:00 ora_gtx0_cdzfrac1
oracle   23597  0.1  0.3 3438888 24540 ?       Ss   18:50   0:00 ora_rcbg_cdzfrac1
oracle   23601  0.0  0.2 3438356 18248 ?       Ss   18:50   0:00 ora_qmnc_cdzfrac1
oracle   23603  0.5  0.4 3445076 33124 ?       Ss   18:50   0:00 ora_q000_cdzfrac1
oracle   23605  1.3  0.5 3445200 45316 ?       Ss   18:50   0:00 ora_q001_cdzfrac1
oracle   23648  0.6  0.3 3439888 29068 ?       Ss   18:50   0:00 ora_pz99_cdzfrac1
oracle   23662  4.2  0.5 3447084 46432 ?       Ss   18:50   0:00 ora_cjq0_cdzfrac1
root     23663  0.0  0.0 110240  1176 pts/1    R+   18:51   0:00 ps -aux

基本也分不清这都是什么，因为我们是RAC两台服务器，害怕把所有oracle的进程关闭后，数据库也瘫了。

于是查了查，找spid对应的进程ID。

select distinct a.sid,b.SERIAL#,b.PROCESS,b.STATUS from v$session_wait a,v$session b
where a.SID=b.SID

找出来之后，全部kill掉。

kill -9 23662;

再去查询表，结果很快就出来了.顿时觉得 这个世界又美好了一点。

后来看了一下，他们写的程序，有一个后台线程，多次在执行扫数据的操作，一条报文的数据在2000条左右，一次更新2000条数据做的都是update操作，锁表的情况很严重，导致session超时。好了，问题找着了，已经让他们去重新写这个程序了。

记录一下此次检查的工作，接下来好好想想，大批量的插入更新的整体架构设计，能不能用上并发等手段去解决他们的频繁扫描插入更新表数据的情况。