在Windows上調整SGA大小遭遇ora-27100、ora-27102錯誤的處理方法
今天早上去一公司合作伙伴那裡，協助處理他們某客戶的數據庫性能問題，那個庫是Oracle 10.2.0.1的，前台業務系統是政府某機構查詢系統，碰到的問題是首頁展示非常慢，與之相關的SQL語句查詢結果需要跑59s多，而其他頁面相關模塊的查詢都只需要幾秒就可以出結果了。
碰到數據庫性能問題通常從兩個方面著手調整： 1. 內存參數調整 2. SQL語句優化
因此，首先就查看了該庫的SGA參數，發現只分配了1.2G，而數據庫服務器的物理內存為8G，顯然這個值太小了。拉了一份AWR報告，顯示shared pool只分配到了200多M，簡直少的令人發指。這個數據庫是運行在Windows 2003 Enterprise x64上面的，因此應該不存在SGA不能超過1.7G的限制，於是對SGA參數進行調整，目標是調整到OS物理內存的50%，即SGA=4G。
由於開始並未設置過sga_max_size的值，所以當調整實例sga_target為某個固定的值再重啟後，如果sga_target的值大於sga_max_size的值，那麼sga_max_size的值就會隨著sga_target自動增加為相同的值，反之，則不會變。此時這2個值都是1200M。盡管sga_target是動態參數，但此時是不允許調大的，當我們需要設置sga_target=4G，就超過了sga_max_size的值，數據庫會報錯，所以，要調大SGA，還必須先修改sga_max_size，而該參數是靜態參數，也就意味著需要停庫，中午向客戶申請了20分鐘的停機時間，然後著手對該參數進行調整。
依次執行以下命令： SQL> alter system set sga_max_size=4G scope=spfile; SQL> shutdown immediate
當再次啟動數據庫的時候，碰到了問題，報了ora-27102: out of memory
SQL> startup ORA-27102: out of memory OSD-00022: Message 22 not found; product=RDBMS; facility=SOSD O/S-Error: (OS 8) Not enough storage is available to process this command. SQL>
之後無論是關閉或者啟動數據庫，哪怕只是啟動到mount，都會報ora-27100錯誤：
SQL> shutdown immediate; ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist SQL> startup; ORA-27100: shared memory realm already exists SQL>
看來是設置sga_max_size=4G，造成了oracle占用OS內存過大，導致數據庫無法啟動，這裡比較納悶，為何設置SGA為物理內存的50%也會報錯呢？Windows又不像Linux/Unix那樣，還有個maxshmall的限制。
由於是在spfile中修改的sga_max_size的值，現在數據庫卻無法啟動了，由於還未進入到oracle實例，spfile也無法再次修改回來，相當於spfile被人為地損壞了，更糟糕的是，之前修改參數值的時候，忘記先生成一個pfile作為備份了，這可麻煩了。還好測試庫上有一個同樣10g實例，於是生成一個pfile，然後修改其中的路徑及實例名為生產庫的值後進行替換，拷貝到生產庫的%ORACLE_HOME/database下面，再用這個pfile來啟動數據庫
SQL> startup pfile=E:\oracle\app\product\10.2.0\db_1\database\initnt.ora; ORA-27100: shared memory realm already exists
錯誤依然存在，難道數據庫就這樣無法啟動了嘛？當然不會，這可是生產庫，停了以後業務就都掛了，眼看20分鐘的停機時間就要到了。
其實，在windows上運行的oracle實例有一點特殊，如果啟動數據庫實例時，由於sga_max_size設置過大而造成實例啟動失敗，盡管把實例啟動，但此時仍然會有一個錯誤的實例存在，因而會導致shutdown immediate及shutdown abort都關閉不了，也無法startup，始終會報ora-27100。這是因為在缺省安裝時，oracle實例的服務（oracleSERVICESID）會在windows啟動時自動啟動，且每次啟動服務時，都會自動用默認的spfile啟動實例（如果存在的話），因此就導致了一直出現ora-27100的內存錯誤。
知道了這個機制，那麼再處理之前的內存錯誤就很容易了，先把錯誤的那個spfile刪除掉，然後停止oracle實例相應的服務，再重新把服務起來，再用pifle啟動數據庫即可。
SQL> startup pfile=E:\oracle\app\product\10.2.0\db_1\database\initnt.ora;
這次數據庫不在報ora-27100了，但是仍然會報ora-27102，這是怎麼了，來來回回出現相同的問題，後來通過一次次的嘗試，終於發現了一個事實，就是在pfile中設置成2G、3G時，再用之前的方法啟動數據庫，數據庫都可以正常啟動，唯獨設置成4G時，就會出現ora-27102。只能接受這個現實了。於是就把sga_max_size設置為3G，sga_target也調整為3G，好歹也是比之前1G要多了2倍了。重新啟動數據庫之後，再用pfile重新創建了一個正確的spfile，調整SGA的任務算是完成了
SQL> alter system set sga_target=3G scope=both; SQL> create spfile from pfile; SQL> shutdown immediate; SQL> startup --用spfile再次啟動數據庫（推薦）
SGA增大之後，由於是采用10g的自動內存管理，shared pool的值也得到了相應的增加，對於跑SQL語句而言是有極大好處的。
調整完內存參數後，現在就要對相應的SQL語句來調整，由於SQL語句我並沒有拿到，只能憑回憶說一下大致的情況，這個首頁調用的SQL語句是個視圖，視圖中還有一個由存儲過程生成的視圖，用了半連接的in進行多表連接，查看了執行計劃發現，2個視圖中的子查詢的多表連接都采用了union的方式，詢問了一下，此處並無排序的需求，因此建議改成了union all，可以避免排序操作。另外視圖中連接的這些表（共3個），無一例外地都是走了Full Table Scan，即全表掃描，沒有一個用到索引，顯然這不太合理，通過在一個查詢字段”currentstate“上建立索引後，再次查詢發現，該條語句單獨跑的時候，cost立即從原來的800多降低到了200多，以此類推，我建議了他們在相應的查詢列上建立索引，來優化這條SQL語句。優化思路提出來了，具體的優化過程由他們自己完成。
總結：
再次強調一下，數據庫性能問題，先從兩方面著手，一是調整數據庫參數（查看內存參數設置是否合理等），二是對SQL語句進行調整（優化），分析執行計劃，查看索引是否被高效地利用起來，另外需要結合AWR報告分析數據庫是否負載過高（DB Time過高），存在性能瓶頸（TOP 5 event）,命中率過低（Buffer Hit%、Library Hit%過低）等不利因素。
