oracle學習小結10

  1  開啟統計日誌資訊

      set autot on stat

2  ORACLE的記憶體配置大致小結
    SGA的查看:
     select * from v$sga;

    (注意oracle在unix下是多進程的,在windows下是單進程的)
    查看系統實際分配的各類pool
    select * from v$sgastat;

     sga大小估計:
   查看UGA大小

     show parameter area_size
    windows上存在32bit的限制，如AIX、HP UNIX 等有明確的64BIT OS and ORACLE的版本，32bit oracle可以裝在64bit os 上，64  bit oracle不能裝在32 bit  OS上

不管oracle是32 bit ORACLE還是 64  bit 的,假定應用存在沒有很好的使用bind  var 的情況，也不能設定 shared_pool_size 過大，通常應該控制在200M--300M,如果是 ORACLE ERP 一類的使用了很多預存程序函數、包 ，或者很大的系統，可以考慮增大shared_pool_size ,但是如果超過500M可能是危險的，達到1G可能會造成CPU的嚴重負擔，系統甚至癱瘓。所以shared_pool_size 如果超過300M還命中率不高，那麼應該從應用上找原因而不是一味的增加記憶體，shared_pool_size 過大主要增加了管理負擔和latch 的開銷。

log_buffer :  128K ----  1M 之間通常問題不大，不應該太大

large_pool_size :如果不設定MTS，通常在 RMAN 、OPQ 會使用到，但是在10M ---  50M 應該差不多了。假如設定 MTS,則由於 UGA 放到large_pool_size 的緣故，這個時候依據 session最大數量和 sort_ares_size 等參數設定，必須增大large_pool_size 的設定，可以考慮為  session *  (sort_area_size + 2M)。這裡要提醒一點，不是必須使用MTS，我們都不主張使用MTS，尤其同時線上使用者數小於500的情況下。

java_pool_size : 若不使用java，給30M通常就夠了

data  buffer ,在做了前面的設定後，凡可以提供給oracle的記憶體，都應該給data buffer = (db_block_size * db_block_buffers)
在9i 中可以是 db_cache_size

還有2個重要參數我們需要注意

sort_area_size and  hash_area_size
這兩個參數在非MTS下都是屬於PGA ，不屬於SGA,是為每個session單獨分配的，在我們的伺服器上除了OS + SGA,一定要考慮這兩部分

(****)  : OS 使用記憶體+  SGA + session*(sort_area_size + hash_area_size + 2M) < 總物理RAM  為好

這樣歸結過來，假定oracle是 32 bit ,伺服器RAM大於2G ，注意你的PGA的情況，,則建議

shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G

再具體化，注意滿足上面(****) 的原則的基礎上可以參考如下設定
如果512M RAM
建議 shared_pool_size = 50M, data buffer = 200M

如果1G RAM  
shared_pool_size = 100M , data  buffer = 500M

如果2G
shared_pool_size = 150M ,data  buffer = 1.2G

實體記憶體再大已經跟參數沒有關係了

假定64  bit  ORACLE

記憶體4G
shared_pool_size =  200M , data  buffer = 2.5G

記憶體8G
shared_pool_size = 300M , data  buffer = 5G

記憶體 12G
shared_pool_size = 300M-----800M , data  buffer = 8G

  其中shared_pool_size主要是放已經執行過的SQL語句,是SQL語句的緩衝池

3 記憶體參數的調整
    資料緩衝區命中率
SQL> select value from v$sysstat where name ='physical reads';
VALUE
----------
14764
SQL> select value from v$sysstat where name ='physical reads direct';
VALUE
----------
50
SQL> select value from v$sysstat where name ='physical reads direct (lob)';
VALUE
----------
0
SQL> select value from v$sysstat where name ='consistent gets';
VALUE
----------
167763
SQL> select value from v$sysstat where name = 'db block gets';
VALUE
----------
14305
這裡命中率的計算應該是
令 x = physical reads direct + physical reads direct (lob)
命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100
通常如果發現命中率低於90%,則應該調整應用可可以考慮是否增大資料緩衝區
共用池的命中率
SQL> select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;
hit radio
----------
99.809291
假如共用池的命中率低於95%,就要考慮調整應用（通常是沒使用bind var ）或者增加記憶體
關於排序部分
SQL> select name,value from v$sysstat where name like '%sort%';
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 67935
sorts (disk) 1
sorts (rows) 7070
SQL>
假如我們發現sorts (disk)/ (sorts (memory)+ sorts (disk))的比例過高，則通常意味著
sort_area_size 部分記憶體較小，可考慮調整相應的參數。
關於log_buffer
SQL> select name,value from v$sysstat
2 where name in('redo entries','redo buffer allocation retries');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 2325719
redo buffer allocation retries 10
假如 redo buffer allocation retries/ redo entries 的比例超過1%我們就可以考慮增大log_buffer
通常來說，記憶體的調整的焦點就集中在這幾個方面，更多更詳細的內容，建議從statspack
入手來一步一步調整。最後關於記憶體的調整，再強調這一點，一定要結合作業系統來衡量，
任何理論都必須要實踐來檢驗。在作業系統中觀察 page in/out 狀況，發現問題嚴重，應
該考慮調小SGA。

 

 

4 在調優時，往往要這樣做，就是要查出使用者的哪條ＳＱＬ語句慢了，因為一使用者發出了很多個事務，每個事務又包含了很多語句，查詢的步驟如下
　　１）select addr,ses_addr from v$transcation;

        查看其addr    ses_addr

       比如　　　　　　592bae00
      通過ses_addr到session表中去查看由哪個使用者發起的transcation,v$session.saddr=v$transcation.ses_addr

  2)   select saddr,sid,username,status,paddr from v$session;

      查出
                 saddr     sid    paddr

                   592bae00      12     692906e8

 

   3)   因為　v$process.addr=v$session.paddr,所以
         select addr,spid from v$process

    查出　　　　addr    spid

                 592906E8     3797

  　4 )　　然後用ps -ef |grep 3797 就可以知道起其用戶端串連上來的進程號，

　　　　　在windows端，用netstat -anp |more
       就可知道具體由用戶端哪個程式連上來
　　５）　select sid,pre_sql_addr,username,status from v$session;

          查出最近發起的sql

               sid   prev_sql    username

                12   5ab0f3ac    hr

   6)  select sql_text,address from v$sql where address='5ab0f3ac';

     就查出該ＳＱＬ了