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

看到了很多 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',

'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';

但是結果並不理想，執行上面語句後去查詢表依然還是長時間不能出來結果，

且在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 以後，再去查詢表，結果很快就出來了

 

現已證實，是此表被鎖住的原因，已經讓開發去查表設計和使用問題了