每秒執行6000的簡單SQL優化（一）

    最近看到一個系統的負載比較高，引起了我的注意，檢視AWR報告發現，竟然是因為兩條很簡單的SQL語句導致。
    語句有多簡單呢，就是下面的兩個SQL語句。

select companyname from license

select supdepid from hrmdepartment where id =''
    突然發現以前也發現了這個問題，但是最後也是不了了之，還是因為單純從資料庫的層面調整要靈活快捷的多，從業務層面來推動還是有一定的難度和阻力。之前的分析：關於CPU使用率高的awr分析 http://blog.itpub.net/23718752/viewspace-2062157/

   表License的資料只有1行，表hrmdepartment對的資料有2000多行，id是主鍵，含有非空約束。
    為什麼這麼小數量的表，含有主鍵，怎麼還會導致嚴重的效能問題呢。
看執行計劃第一個語句是全表掃描，裡面只有1行記錄，全表和索引掃描應該差別很小。
    
就是這樣一個語句，在一個小時的時間裡竟然執行了近2000多萬次。

這樣一條SQL的影響被無限放大，就導致了資料庫的負載很高。
如此來看，每秒鐘的執行頻率極高，1秒鐘差不多是6000多次的頻率。什麼系統有如此之高的業務需求。
和開發的同學交涉，原以為分析已經到位，剩下的就是快刀斬亂麻似的解決問題了。但是發現問題比我想象的要糟糕很多。
目前的情況是，大家都認為這是一個問題，但是讓人很無奈的情況是這個系統是一個外部系統，目前還沒有原始碼，所以也就意味著這是一個黑盒的環境了。
我看了下出問題的使用者的結構資訊，真讓我大跌眼鏡，大量的儲存過程和觸發器，表竟然有1萬多個，我倒吸一口冷氣，這個問題的情況確實比我想的難，準確的說是糟糕。
 sh findobj.sh USERV6|grep TABLE|wc -l
11904
$ sh findobj.sh USERV6|grep TRIGG|wc -l
1580
$ sh findobj.sh USERV6|grep INDEX|wc -l
1542
$ sh findobj.sh USERV6|grep PROC|wc -l
2149
換句話說，從應用層面來調整SQL的可能性極小。碰到這種情況真是無語了。但是抱怨和牢騷解決不了問題。我一邊和開發的同學溝通，一邊想資料庫層面能不能做點什麼。
select companyname from license這樣一個語句，不能動SQL還有什麼優化空間了。目前來看有一個改進之處是索引，表裡有10多個欄位，輸出只有一個欄位，表裡存在一行記錄。所以也就儘可能提高訪問的效率，10%的改進被無限放大也是一個很理想的值。
    而且如果SQL語句能夠修改的情況下，我有什麼好的辦法來改進，這都是我需要考慮的問題。
我用一個新的使用者來做了一番測試。
create table license tablespace users as select * from  USERV6.LICENSE ;
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE');
可以看到執行計劃是走了全表掃描
SQL> set autot trace exp stat

嘗試1：
因為這個表只存在1行記錄，而且從表結構資訊來看資料是唯一的，於是我嘗試建立一個唯一性索引。
create unique index index_lic_companyname on license(companyname);
重新收集統計資訊
SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE');
再次檢視，發現CBO還是走了全表掃描。執行計劃沒有發生改變。


嘗試2：
嘗試新增hint,設定cardinality都是一樣的結果，沒有任何改變。
select /*+index( license index_lic_companyname)*/ companyname from license
select /*+cardinality( license,1)*/ companyname from license
嘗試3：
我想到了一個新的改進方法，就是sample,可以根據隨機演算法得到資料。但是嘗試結果依舊沒有任何變化
select companyname from license sample(1);

嘗試4：
嘗試rownum的形式，結果依舊。
SQL> select companyname from license where rownum<=1;
嘗試5：
啟用cache選項，把資料牢牢放入cache裡面，減少被換出的概率，但是在這個極端場景下，還是沒有任何的改進。
SQL> alter table license cache;
嘗試6：
如果我啟用了Unique Index的時候，即SQL語句改為下面的形式，結果不言而喻，肯定是非常理想了。
select companyname from license where companyname='xxxx'

但是改動成為上面的情況的可能性我幾乎為0，想想挺美好，總是有巨大的差距。
嘗試7：
既然在這個特殊場景中，我們需要查詢的是companyname這個欄位，有什麼辦法把索引和表結合起來呢。一種方式就是IOT,即索引組織表了。
重新建立表
  CREATE TABLE "CYDBA"."LICENSE_IOT"
   (    "COMPANYNAME" VARCHAR2(100) primary key,
        ...
        "DOCUMENT" CHAR(1)
   ) organization index
  TABLESPACE "USERS" ;
插入1行資料。
SQL> insert into LICENSE_IOT select * from license;
1 row created.
SQL> commit;
收集統計資訊。
SQL>exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'LICENSE_IOT');
可以看到還是有了不小的改進。

這個結果讓我還是充滿資訊，準備近期部署上去對比一下，希望看到鮮明的差距。第2條SQL繼續優化，還是一塊不好啃的骨頭。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2127027/，如需轉載，請註明出處，否則將追究法律責任。