switch undo tablespace

swtich undo tablespace
Step 1 : Create Tablespace   :  Create undo tablespace undotbs2   
SQL> create undo tablespace UNDOTBS2 datafile  'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF'  size 100M;

Step 2 : Edit the parameter file
SQL> alter system set undo_tablespace=UNDOTBS2 ;
SQL> alter system set undo_management=MANUAL scope=spfile;

Step 3: Check the all segment of old undo tablespace to be offline
SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
If any one the above segment is online then change it status to offline by using below command .
SQL>alter rollback segment "_SYSSMU9_1192467665$" offline;

Step 4 : Drop old undo tablespace
SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Step  5 : Change undo management to auto and restart the database
SQL> alter system set undo_management=auto scope=spfile;
SQL> shut immediate;
SQL> startup
SQL> show parameter undo_tablespace

沒有iotop指令時，如何用ps找出 wait io process

http://bencane.com/2012/08/06/troubleshooting-high-io-wait-in-linux/

for x in `seq 1 1 10`; do ps -eo state,pid,cmd | grep "^D"; echo "----"; sleep 5; done

資料庫緩衝快取區(DB Buffer Cache)

http://dbatim4026.blogspot.tw/2014/12/db-buffer-cache.html

資料庫緩衝快取區(DB Buffer Cache)

     Oracle有一個很重要的觀念,就是不到絕對必要,不會等待磁碟I/O,因為磁碟I/O是電腦系統運作中最慢的一個部份,因此,所執行的I/O是越少越好,在Oracle的資料庫架構設計裡面這種觀念是無所不在,資料庫緩衝快取區（Database Buffer Cache）即是因應此概念而規劃出來,其基礎觀念如下：

• 資料庫緩衝快取區主要的功能是用來暫時存放最近讀取自資料庫裡面的資料,也就是資料檔（Datafile）內的資料,而資料檔是以資料區塊（Block）為單位,因此資料庫緩衝快取區裡面的大小是以Block為基數。當使用者透過應用程式第一次向Oracle資料庫送出查詢需求時,Oracle會先在資料庫緩衝快取區內尋找該資料,如果有需要的資料,就直接從資料庫緩衝快取區回傳給使用者稱為快取命中（cache hit）,這麼一來,即可減少硬碟上的I/O次數,如果Oracle發現使用者要的資料並不在資料庫緩衝快取區裡面稱為快取失誤（cache miss）,Oracle會從資料庫中讀取所需要的資料blocks,先放入資料庫緩衝快取區裡面,再傳送給使用者。

          有關資料庫緩衝快取區命中快取率的計算方式如下。

   SELECT 1 - (phy.value  / (cur.value + con.value)) "CACHE HIT RATIO"

       FROM   v$sysstat cur, v$sysstat con, v$sysstat phy

       WHERE  cur.name = 'db block gets'

       AND con.name = 'consistent gets'

       AND  phy.name = 'physical reads';

• db block gets：是指DML 指令所得到的資料區塊個數。
• consistent gets：是指查詢指令得到的資料區塊個數。
• logical reads：將db block gets + consistent gets得到的資料區塊個數。
• physical reads：實際從disk讀出的資料。

Tip  - Cache Hit Ratio重要事項

1. CACHE HIT RATIO = 1 -（physical reads /（db block gets + consistent gets））
2. HIT RATIO最好要大於90%
3. 以上的查詢命中快取率是以整個資料庫緩衝快取區為主。

• 資料庫緩衝快取區裡面包含三種不同性質的快取,依序如下：

1. Dirty Buffer:此類的Buffer是指存放已修改但尚未寫入資料庫的資料。
2. Clean Buffer:此類的Buffer是指裡面的內容和資料庫內是一模一樣,也就這些Buffer是已經寫入資料庫內,隨時可以拿來覆蓋重復使用。
3. Pinned Buffer:此類的Buffer是指正在被使用的Buffer。
4. Free or Unused Buffer :此類Buffer指的是當Instance被開啟時,所有的Buffer都式呈現為使用狀態,其狀態等同Clean Buffer。

• 資料庫緩衝快取區是利用兩種List來控管內部那三種不同的性質的Buffer:

1. Dirty list：包含存放dirty buffer,主要是將準備要寫入資料庫的資料,在適當的時機寫入。也就是被修改但尚未入Datafile的Bolock。
2. LRU list：包含Free Buffer、Dirty Buffer、Pinned Buffers。

• Oracle對於資料庫緩衝快取區的運作方式是利用背景處理程式的DBWRn寫入資料檔（Data File）內,而DBWRn將Dirty Buffer從資料庫緩衝快取區取出寫到資料檔（Data File）內主要是透過兩種方式多個時機點

方式一： LRU機制 

時機點：

1. Dirty buffer達到閥值時 沒有free buffer時（server process在LRU list裏找不到足夠多的free buffer）。
2. 3每3秒,DBWn會去檢查dirty list,如果dirty list未到觸發值,就去讀LRU list,將dirty buffer移到dirty list；如果dirty list已滿或已達觸發值,則寫入數Datafile中。

方式二：當Checkpoint發生時 

 時機點：

1. log switch時,要求做檢查點,也就是把DBWn將dirty buffer從LRU list中移到dirty list,然後把dirty list中的dirty block 回寫到Datafile中。
2. Tablespace offline或Hot backup時。
3. drop一個物件時
4. 關閉DB時

資料緩衝快取區的運作狀況

資料緩衝快取區（Data Buffer Cache）的運作狀況詳細內容如圖


Data Buffer Cache的運作狀況

1. 當使用者連線透過Server Process要讀取一筆資料時,第一個動作是會去資料緩衝快取區（Data Buffer Cache）裡使用雜湊函式（Hash Function）的運算來找到這筆資料的資料區塊的位置有沒有存在資料緩衝快取區（Data Buffer Cache）裡面。在此步驟會有兩種狀況:
   狀況一  如果有存在資料緩衝快取區（Data Buffer Cache）裡面就往步驟2。
   狀況二  如果沒有存在資料緩衝快取區（Data Buffer Cache）裡面就步驟5。
    
2. 假如有存在資料緩衝快取區（Data Buffer Cache）的話,就從LRU List移到MRU端。
3. 當Server Process在LRU List裡面搜尋的時候,會先從LRU端開始找,找的過程中當他遇到一個Free Buffer的就將它註記起來,一直找到需要的個數,如果我再找的過程中當我遇到Dirty Buffer,Server Process就負責將這些Dirty Buffer移到Dirty List去。
4. 在此步驟會有兩種狀況:
   狀況一   如果Server Process將Dirty Buffer由LRU List移到Dirty List的過程中造成Dirty List滿了 ,也就是超過Dirty List的最大值,此時Server Process就會觸發DBWn ( DB Writer ),將Dirty List裡面的Dirty Buffer寫到資料檔（Data File）裡面去,當寫進去後呢？這些被寫到資料檔（Data File）裡去的Dirty Buffer就會都變成Free Buffer,而這些Free Buffer會全部加到LRU List最尾端去,此時Server Process會再重新去找,就可以更快找到Free Buffer ( 還記得嗎？掃LRU List是從LRU端開始掃的 )；所以不一定要把LRU List掃完才會發現不夠,當在掃的過程中發現Dirty List滿了,就會先做寫入的動作,然後把空間release出來。
狀況二   還有另一種狀況是,當Server Process從LRU List裡面找Free Buffer的時候,看到Dirty Buffer就把Dirty Buffer丟到Dirty List裡面去,當Dirty List尚未到達最大值時,只要Server Process讀了多少個資料區塊（Block）之後,發現Dirty Buffer太多了而Free Buffer又太少了,此時又會觸發另一種狀況,就是告訴Server Process不要再找了,因為裡面的Dirty Buffer太多了,而Free Buffer又太少了,所以不用浪費這個時間再找了,就直接將Dirty List裡的東西直接寫到資料檔（Data File）裡面去,寫完之後就會將空間釋放出來了,這樣Server Process就可以很快的找到所需要的Free Buffer,找到空間之後就會將資料從資料檔（Data File）裡面讀出,放到資料緩衝快取區（Data Buffer Cache）裡面去,之後Oracle就會將那些資料區塊（Block）放到LRU List裡面的MRU端,之後就是可以新增、修改或刪除。
 
5. 如果今天所需要的資料在資料緩衝快取區（Data Buffer Cache）裡沒有找到的話呢？Oracle會將資料從資料檔（Data File）讀取到資料緩衝快取區（Data Buffer Cache）裡來,當讀取的過程中要先檢查一件事,就是確認到底有沒有空間容納這些資料,所以說當Server Process要從資料檔（Data File）裡面讀取資料到資料緩衝快取區（Data Buffer Cache）裡前,Oracle會先確認有沒有足夠的空間,如何確認呢？Oracle會去掃LRU List,掃LRU List主要是看看有沒有足夠的Free Buffer,在掃的過程中會有幾種狀況如步驟3與步驟4。

除了以上的資料緩衝快取區（Data Buffer Cache）的運作狀況之外,當遇到以下的狀況也會觸發將資料從資料緩衝快取區（Data Buffer Cache）回寫資料檔（Data File）：

• 當Dirty List超過最大值,時背景處理程序DBWn（DB Writer）會將Dirty List裡的資料寫回到資料檔（Data File）裡去,然後再將這些空間釋放出來。
• 當Server Process在從LRU List裡面尋找空的Free Buffer時,從當Oracle找了一段時間還找不到足夠的Free Buffer,代表裡面Dirty Buffer太多了,所以也會觸發DBWn（DB Writer）將裡面的Dirty Buffer回寫資料檔（Data File）。
• 超過三秒,也就是說每三秒鐘DBWn會自動起來檢查Dirty List

狀況一   在檢查的過程中如果發現Dirty List的最大值尚未到達時,會去讀LRU List,將讀到的Dirty Block移到LRU List。
狀況二   如果在檢查的過程中發現的Dirty List的最大值達到了,就會回寫回寫資料檔（Data File）。
• 當LGWR觸發LOG SWITCH時就會要求作checkpoint,當執行checkpoint時也就是將資料緩衝快取區（Data Buffer Cache）裡的Dirty Buffer回寫到回寫資料檔（Data File）裡面去,也就是說我們的DBWn（DB Writer）會複製Dirty Buffer從LRU List到Dirty List裡面去,然後再將Dirty List裡面的資料回寫資料檔（Data File）。
• 當針對表格空間（Tablespace）執行以下指令也會觸發Checkpoint

   alter tablespace offline temporary；
   alter tablespace offline normal；
   alter tablespace begin backup；

• 當你移除（Drop）一個物件時,Oracle會針對這個物件產生checkpoint事件,做完checkpoint之後才會把物件移除（Drop）,也就是說當某一個物件被執行移除（Drop）的指令時,會先產生Checkpoint利用DBWn將LRU List上的Dirty Buffer移到Dirty List上再寫入資料檔（Data File）裡,如此checkpoint就算執行結束,之後才會真正的移除（Drop）物件。
• 當關閉資料庫是以shutdown normal、Immediate或Transactional方式也會產生checkpoint。

資料庫緩衝快取區重要觀念

      資料庫緩衝快取區是在SGA內佔有相當重要的份量,下面將針對資料庫緩衝快取區的觀念做重點提示。

資料庫緩衝快取區的大小設定

資料庫緩衝快取區大小設定有兩種方式,依序如下

1. 直接在參數檔內設定DB_CACHE_SIZE即可,預設值48M。
2. 第二種方式是利用DB_BLOCK_BUFFERS和DB_BLOCK_SIZE這兩個參數DB_BLOCK_BUFFERS則指定了Database Buffer Cache中的Block數量,DB_BLOCK_SIZE則指定了每個Block的大小。因此,資料庫緩衝快取區的大小就等於DB_BLOCK_BUFFERS * DB_BLOCK_SIZE。,Block的大小預設值是8K。

Tip

• DB_CACHE_SIZE和DB_BLOCK_BUFFERS是不能同時設置的,否則當Instance啟動時會有錯誤訊息。
• DB_CACHE_SIZE是可以動態修改的參數,修改後Instance不需重啟。
• 按照一般經驗在線上交易系統（On-line Transaction Processing；OLTP）DB_CACHE_SIZE的大小可設定為DB_CACHE_SIZE=SGA_MAX_SIZE/2到SGA_MAX_SIZE*2/3之間,但還是需要以實際的狀況做調整。

清除資料庫緩衝快取區

      之前有提過Dirty Buffer是指存放已修改但尚未寫入資料庫的資料，但如果資料庫緩衝快取區內存在大量的Dirty Buffer，那麼就可能導致整個效能低落，此時可以使用人工的方式將Dirty Buffer整批的回寫到資料檔內,語法如下。
   alter system flush buffer_cache;

資料庫緩衝快取區分區管理

可以將資料庫緩衝快取區切分為三種不同性質的分區,每一個分區都有不同功用,如圖


資料庫緩衝快取區分區管理

• RECYCLE POOL：當資料只要放到recycle pool時,只要空間不夠用時馬上就會被釋放出來。要被放在Recycle pool的資料區塊應該不會被反覆使用,也就是說,這些資料區塊只是在交易（transaction）還存在的時候才會被用到,當交易結束時,就會被被釋放出來。RECYCLE  POOL的大小最好是default pool的1/2大。
• KEEP POOL：當資料只要放到keep pool裡時,代表這個資料是需要常常被重複使用的,所以資料如果是被放在keep pool裡,代表資料會盡可能的被放在裡面。KEEP POOL的大小應該是Default pool的10%
• DEFAULT POOL：當沒有指定時就會放在default pool,也就是說,放在Default Pool的資料是利用LRU機制。

分割資料緩衝快取區是在初始參數檔裡面設定，設定方式如下

   DB_BLOCK_BUFFERS = 20000

   DB_BLOCK_LRU_LATCHES = 6

   BUFFER_POOL_KEEP=(BUFFERS:14000,LRU_LATCHES:1)

   BUFFER_POOL_RECYCLE=(BUFFERS:2000,LRU_LATCHES:3)



Tip 

• BUFFER_POOL_KEEP：分配的Buffer是14000個；LRU LATCH是1個。
• BUFFER_POOL_RECYCLE：分配的Buffe是2000個；LRU LATCH是3個。
• BUFFER_POOL_DEFAULT：剩下的就是default。buffers是4000個；LRU  LATCH是2個。

除了指定Buffer個數之外還需指定LRU_LATCH,LRU_LATCH的用途是,一個LRU_LATCH就要控制一個LRU LIST如果定義有3個LRU_LATCH代表同時可以有3個Server Process同時使用,因此由此可知上面的例子是

• BUFFER_POOL_KEEP： 是一個LATCH管14000個BUFFERS。
• BUFFER_POOL_RECYCLE：是一個LATCH管600多個BUFFERS。
• BUFFER_POOL_DEFAULT：是一個LATCH管2000多個BUFFERS。

一個LATCH最少要管50個BLOCKS。

設定好之後如何讓某一個Segment使用某一個buffer pool呢？方式有兩種

在建置segment時在storage參數裡面加上 buffer_pool_keep或是buffer_pool_recycle就可以了。語法範例如下

   CREATE INDEX INDEX_NAME STORAGE (BUFFER_POOL KEEP …);或是

   CREATE TABLE table_name (column_name)

     tablespace tablespace_name storage ( initial 200k   next   200k   BUFFER_POOL KEEP);

 

將已存在的SEGMENT利用Alter語法指定。語法範例如下

   ALTER TABLE table_name STORAGE (BUFFER_POOL RECYCLE);

   ALTER INDEX index_name REBUILD STORAGE (BUFFER_POOL KEEP);

資料庫緩衝快取區重要參數與View

DB_CACHE_ADVICE

        Oracle提供了一些自動偵測效能的工具,用於提高系統效能。DB_CACHE_ADVICE就是其中一種,當設定DB_CACHE_ADVICE的作用就是在系統運作過程中,透過監控相關的統計資料,提供給DBA對於資料庫緩衝快取區相關訊息,以作最佳的調整。DB_CACHE_ADVICE的設定有三種方式如下所示：

   DB_CACHE_ADVICE=OFF

   DB_CACHE_ADVICE=ON

   DB_CACHE_ADVICE=READY

OFF代表不開啟DB_CACHE_ADVIDE

• DB_CACHE_ADVICE=ON代表開啟DB_CACHE_ADVICE,但這要注意一開啟為ON時Oracle會從共用區（Shared Pool）中取得一些記憶體空間,所以有可能會影響效能。
• DB_CACHE_ADVICE=READY 代表在下次開啟Instance時會預先分配足夠的記憶體,通成建議使用此方式。
• 此參數是可以以動態方式也就是”ALTER SYSTEM SET…”修改,並且當DB_CACHE_ADVICE開啟後會將統計資料儲存到V$DB_CACHE_ADVICE內。

        DB_CACHE_ADVICE的參數設定可以提供給資料庫緩衝快取區相關訊息，以作最佳的調整。當開啟參數DB_CACHE_ADVICE後，經過一段時間，Oracle就會自動收集足夠的相關統計資料，並預測出DB_CACHE_SIZE在不同大小情況的性能資料，而這些資料就是透過V$DB_CACHE_ADVICE來顯示出來，因此可以根據這些資料對DB_CACHE_SZIE做相關的調整，以達到最佳狀況。欄位解說如下所列:

• ID:不同資料庫緩衝快取區的編號,一般來說DB_CACHE_SIZE的編號是3
• NAME:資料庫緩衝快取區的名稱（Default、Keep、Recycle）
• BLOCK_SIZE:資料區塊的大小（單位是K）
• ADVICE_STATUS:開啟狀態：ON代表開啟,OFF代表關閉
• SIZE_FOR_ESTIMATE:預測效能的Cache大小（M為單位）
• SIZE_FACTOR:預測的Cache大小比例,也就是與目前大小的比例
• BUFFERS_FOR_ESTIMATE:預測性能資料的資料區塊個數
• ESTD_PHYSICAL_READ_FACTOR:在資料庫緩衝快取區裡實體讀取因數,也就是說當Buffer Cache大小為SIZE_FOR_ESTIMATE此欄位時,db_cache_advice預測的實體讀數與當前實體讀數的比率值。如果當前實體讀數為0,這個值為空。
• ESTD_PHYSICAL_READS:當Buffer Cache大小為SIZE_FOR_ESTIMATE時,db_cache_advice預測得實際讀數。
• ESTD_PHYSICAL_READ_TIME:當前實體讀取的時間。
• ESTD_PCT_OF_DB_TIME_FOR_READS:當前實體讀取的時間佔所有時間的比例。

V$DB_CACHE_ADVICE主要觀察欄位是ADVICE_STATUS、SIZE_FOR_ESTIMATE、ESTD_PHYSICAL_READ_FACTOR、ESTD_PHYSICAL_READS，查詢語法如下：
   SELECT ADVICE_STATUS,
          SIZE_FOR_ESTIMATE,
          ESTD_PHYSICAL_READ_FACTOR,
          ESTD_PHYSICAL_READS
     FROM V$DB_CACHE_ADVICE  WHERE NAME = 'DEFAULT';
查詢結果如圖

查詢V$DB_CACHE_ADVICE

由上圖可以知道ESTD_PHYSICAL_READ_FACTOR為1時,最佳的DB_BUFFER_SIZE是1600,因為在這之後的調整對降低I/O的效能有限,以上面的數據用座標圖來呈現就可以明顯的看出趨緩的狀況。

使用OEM上可以看到相同的圖表步驟如下

1. 進入OEM之後點選「Server」頁籤,並點選「Database Configuration」區塊內的「Memory Advisors」連結。
2. 點選「SGA」頁籤並點選「Buffer Cache」的「Advice」按鈕,如圖所示。

使用OEM確認DB BUFFER CATCH建議-1

     3.  此畫面及提供資料庫快取緩衝區相關建議事項,如圖2-30所示

用OEM確認DB BUFFER CATCH建議-2

DB_nK_CACHE_SIZE

        Oracle可以同時支援不同大小的資料區塊 (2K,4K,8KB,16KB及32KB五種）,除了DB_BLOCK_SIZE之外還可以設定其他的大小,並且可以為不同塊尺寸的資料塊指定不同大小的資料庫緩衝快取區分。而DB_BLOCK_SIZE的參數設定,稱為標準資料區塊『Standard Block』,區間在2k-32k之間。9i以後,除了SYSTEM表格空間（System Tablespace）和TEMPORARY表格空間（Temporary Tablespace）必須使用標準標準資料區塊外,所有其他表格空間都可以最多指定四種不同的標準資料區塊大小。
當設定好DB_nK_CACHE_SIZE之後在建置表格空間（TABLESPACE）可透過BLOCKSIZE此參數來指定資料區塊大小,如以下範例：
在參數檔內設定DB_nK_CACHE_SIZE範例如下
   db_16k_cache_size=16M
此參數也可動態設定語法範例如下
   ALTER SYSTEM SET db_16k_cache_size = 16 SCOPE=MEMORY;
   ALTER SYSTEM SET db_32k_cache_size = 32 SCOPE=MEMORY;
   ALTER SYSTEM SET db_4k_cache_size = 4 SCOPE=MEMORY;
   ALTER SYSTEM SET db_8k_cache_size = 8 SCOPE=MEMORY;
此時以show parameter方式確認,如圖

DB_nK_CACHE_SIZE設定

手動建置表格空間的語法就必須加上BLOCKSIZE此參數來指定資料區塊的大小,語法如下
   CREATE TABLESPACE  TEST  LOGGING
      DATAFILE '/u01/app/oracle/oradata/test/test01.dbf' SIZE 5M
      BLOCKSIZE 16384
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO;
如果設定了DB_nK_CACHE_SIZE在OEM上建置表格空間時,在「Storage」分頁籤「Block information」區塊內的「Block Size」部份就有下拉選項可供選擇,如圖所示。

OEM之區塊大小設定

        當設定好DB_nK_CACHE_SZIE與建置相關大小的表格空間後,當資料庫緩衝快取區讀取此表格空間時就會使用此相對應的DB_nK_CACHE_SZIE大小的資料區塊,也就是說當建置一個16K的表格空間,就可以透過設定DB_16K_CACHE_SIZE為來指定資料庫緩衝快取區存取這個表空間資料區塊。

Tip

• 不同大小的資料區塊是不能相互混合儲存,也就是說一個16K的表格空間就必須透過DB_16K_CACHE_SIZE為來指定資料庫緩衝快取區,不可用4K的去儲存。
• 如果當DB_BLOCK_SIZE 設定為 8K,就不可再設定DB_8K_CACHE_SIZE 參數。

V$BUFFER_POOL

     之前有提過可將資料庫緩衝快取區切分為三個不同類型的分區,而V$BUFFER_POOL,可將每個分區的詳細情況列出
查詢V$BUFFER_POOL,方式如下：
      SELECT * FROM v$buffer_pool  WHERE id <> 0;查詢結果如圖

V$BUFFER_POOL查詢結果

V$BUFFER_POOL_STATISTICS

     可查詢V$BUFFER_POOL_STATISTICS來看每個分割資料庫緩衝快取區命中快取率,方式如下：
    SELECT name,1 - (physical_reads / (db_block_gets + consistent_gets))
           "HIT_RATIO" FROM sys.v$buffer_pool_statistics
        WHERE db_block_gets + consistent_gets > 0;結果如圖所示。

查詢分割資料庫緩衝快取區命中快取率

Tip

• SYS.V$BUFFER_POOL_STATISTICS,要先跑 catclust.sql 這支script。
• 注意,原則上GET HIT RATIO要在90%以上。但Recycle如果是在90%以上,就代表這常用的東西是被放在Recycle裡,那Recycle裡的東西又是常被reused,所以如果Recycle是在90%以上,就代表有問題,因為這代表90%以上的東西用完就丟,所以應該將常用的東西放在KEEP裡面。