服務器IBM X5，操作系統是2003 32位? ORACLE 9.2.0.1

4個CPU，13.9G內存

?

?

總體反映慢,后來又說匯總慢,原來幾十秒的現在需要1個多小時.

?

?

遠程過去查看了下，當前等待的語句都是：

?

DELETE t1 a where (a.zd1,a.zd2,a.zd3) in (select distinct zd1,zd2,zd3 from t2 where zd0=:b3 and zd1=:b2 and (nvl(data1,:b1)<>:b1 or nvl(data2,:b1)<>:b1 or nvl(data3,:b1)<>:b1))

這是一個對臨時表的刪除操作。

運行select?a.table_name,?last_analyzed?
from?user_tables?a;?看下T2的分析時間是前天的

?

使用dbms_stats重新分析下該表，仍然不起作用。

?

?

運行select sid,type,id1,id2,lmode,request,block from v$lock where sid in (select sid from v$session) order by sid;

發現連接的session不少，但是并沒有被阻塞的進程，沒有鎖住的表。

?

?

繼續查看表空間的情況，臨時表空間已用99.96%，用戶使用的表空間爭用

最多的數據文件也已經用99.96%，故先擴充該數據文件，然后給臨時表空間增加了一個數據文件。

?

進一步查看ALERT文件，發現該數據庫安裝的有問題，首先缺省塊大小才4096，另外加起來的內存也不過11M，shared_pool 才33M ,pga? 11M ,data_buffer 33m多。上面運行的應用本身就比較耗費資源，這種情況出現問題也不例外。

?

使用dbms_stats重新分析下該表，仍然不起作用。

execute?dbms_stats.gather_schema_stats(ownname=>?'ZHTJ'?,?tabname?=>'T2',cascade=>?TRUE);

?

于是調整SGA 總共為1.1G，PGA 給了300M，并為該用戶增加空閑1個小時后自動殺掉進程的控制，發現BS有4 天前的進程還在連著，重啟服務器。

?

再次運行慢的匯總模塊，這次運行了1分半完成了。查看應用的提示為：

刪除指標采集t2 對應的數據使用了1分多，此處提示信息有些誤導，以為是刪除表T2的語句慢，實際抓出的等待語句還是上面那個刪除臨時表的語句。而且過了一陣用戶多了之后，速度又慢了下來：8-9分鐘。

?

于是還是查看該語句的執行計劃：

?

綁定變量：

Set time on

Set linesize 2000

Set autotrace on explain

?

Define b1=100 –設定綁定變量 并將語句改寫了下

delete?from?t1?a?

where?(a.zd1,a.zd2,a.zd3)?in?(select?distinct?zd1,zd2,zd3?from?t2?
????????????????????????????? where?zd0=':b3'?and?zd1=':b2'?
???????????????????????????? ?and?not (nvl(data1,&b1)=&b1?and?nvl(data2,&b1)=&b1?and?nvl(data3,&b1)=&b1))

發現該語句對于臨時表的訪問使用了索引，很正常，但是對于表T2，采用的是全表掃描。查看下該表的數據為171萬，代價為5350多，都耗費在這個表的訪問上了。進一步去查看該表的主鍵和索引情況，竟然發現表T2上面沒有任何索引，也沒有主鍵，很奇怪的一件事情：該表為日采集表，數據量應為最大的表，竟然沒有主鍵和索引。聯系開發人員未果，索性先建上一個索引吧：

?

CREATE?INDEX?INX_T2?ON?T_LR_RXB(ZD0,ZD1,ZD2,ZD3) TABLESPACE INDX;

再次看語句的執行計劃，代價已經變到了2！運行程序，車間匯總5秒就出來了

再看分廠19秒結束。

?

至此我終于碰到了一個索引解決問題的優化，哈哈！? 20120625

?

?

來自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/7177735/viewspace-733790/，如需轉載，請注明出處，否則將追究法律責任。

轉載于:http://blog.itpub.net/7177735/viewspace-733790/

總結

以上是生活随笔為你收集整理的一次索引搞定的调优例子的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。