1. Multitenant 架構介紹

Oracle 從 12c 開始推出了 Multitenant 架構，有別於以往 1個 Instance = 1 個 Database ， Multitenant 則是 1個 Instance 可以建立多個 Database ，這個 Instance 所屬的 Database 就稱為 Container Database ，簡稱 CDB ，而 CDB 之上可以再建立多個 Database ，這些 Database 稱為 Pluggable Database ， 簡稱 PDB 。

CDB 的角色只是作為 PDB 的一個載體，不存放任何 user data ，而 PDB 才是 user data 與應用系統所使用的資料庫，也就是以往使用者所用的資料庫(Non-CDB) 變成現在要使用 PDB ，雖然 PDB 都是建立在 CDB 之上，不過每個 PDB 都是獨立的資料庫且互不影響， PDB 對於使用者來說使用上與以往 Non-CDB 並無不同，差別只在於 DBA 的管理方式不同而已。

Multitenant 架構的優點是將資料庫集中化管理，傳統 Non-CDB 架構下， DBA 必須管理多個 Database Instance ，而在 Multitenant 架構下 DBA 只需管理一個 CDB Instance ，所有的 DB (PDB) 都屬於這個 CDB Instance ，不再需要切換 ORACLE_SID 去登入每個 DB 了。除此之外， PDB 可以快速的建立，對於 DBA 來說要部屬新的資料庫或是要建立測試資料庫都非常方便，而且 PDB 也可以根據需求使用 unplug-plug 或是 clone 的方式來移轉到其它 CDB ，使用上更有彈性。

Multitenant 架構下只有 CDB 有 Background Process ， CDB 本身做為 Root Container ，基於Root Container 底下建立 PDB ，每個 PDB 都有自己獨立的 Data file ，不過 Control file 與 Redo Log 只存在 CDB root ，所有 PDB 共享一份 Control file 與 Redo Log Group :

透過 DBCA 就可以建立 CDB ，只要將 Create as Container Database 選項勾選起來就可以將資料庫建立為 Multitenant 架構 :

如果選擇以 Customer 的方式建立資料庫，那麼就可以選擇 CDB 與 PDB 所要安裝的 Components :

由於不確定未來要 Plug 到此 CDB 的 PDB 具有哪些 components ，因此建議 CDB 在建立時把所有的 components 都裝上，而勾選是否 Include in PDBs 則會影響 PDB Seed ， PDB Seed 的用途是做為建立新 PDB 的一個 Template ，如果 PDB Seed 沒有安裝某些 component ，那麼未來透過 PDB Seed 所建立新的 PDB 也不會有這些 component 。

使用 show con_id 可以判斷這個 DB 是否為 Multitenant 架構，如果為 0 表示為 Non-CDB ，為 1 表示為 Container Database ， show con_name 可以顯示目前所在的 PDB 名稱，如果在 CDB 則會顯示 CDB$ROOT :

使用 show pdbs 或者是查詢 v$pdbs 可以查詢目前 CDB 中有哪些 PDB :

PDB 在正常開啟的狀態為 READ WRITE ，如果是關閉的狀態則是 MOUNTED ， PDB$SEED 做為建立 PDB 的 Template ，狀態則一直都會是 READ ONLY 。

PDB 建立之後預設的 Service 名稱就是這個 PDB 的名稱， PDB 的連線方式與傳統 DB 的連線方式無異，透過 tnsnames 就可以連線到 PDB ，如果要直接以 sqlplus / as sysdba 的方式連線至 PDB ，則需設置 ORACLE_PDB_SID 即可 :

如果登入到 CDB 要進入某個 PDB 則使用 alter session set container 來切換:

一個 CDB 可以容納多少個 PDB 由 CDB 參數 max_pdbs 決定，最多可設置到 4098 。

由於 CDB 不存放 user data ，所以不能建立一般的使用者，只能建立 Common User ， Common User 的特性就是在 CDB 建立之後，所有的 PDB 都一併會建立此使用者，建立 Common User 必須在使用者名稱前面加上 C## ， C## 為 Common User 的一個識別符號，由參數 common_user_prefix 設定，預設為 C## ，建立方式與傳統使用者建立的方式相同，只需帶上 C## 就表示為 Common User ，同理 drop C## 這個 Common User 的時候所有 PDB 也會一併刪除 :

如果不帶上 C## 表示為一般使用者則會出現 ORA-65096 錯誤 :

雖然 Common User 會建立在 CDB 與所有 PDB 上，但是對於 Common User 的權限授予使可以分開授權的，在 grant 語法中加上 container 就可以分開授權，例如 Common User 在所有 PDB 都具有這個權限則使用 container=all ；若是只有在這個 PDB 才有這個權限，則使用 container=current :

最後提一下 DBA 在管理上多了 CDB_ 開頭的 view 可以查詢，例如以往所查詢的 dba_users 、 dba_tables 、 dba_objects …等都有相對應的 cdb_ view 可以查詢，例如 cdb_users 、 cdb_tables 、 cdb_objects …等，差別在於 cdb_ 開頭的 view 多了一個 con_id 的欄位，也就是 DBA 只要在 CDB 層級就可以查詢底下所有 PDB 的物件，不須使用 alter session set container 進到每個 PDB 進行查詢 :

若是在 PDB 層級使用 cdb_ 開頭的 view ，則只會顯示這個 PDB 本身的物件，不會顯示其它 PDB :

由於 Multitenant 架構未來可能會成為 Oracle 資料庫的主要架構，身為 Oracle DBA 必須熟悉如何管理與使用 Container Database 。

19c 重要的隱藏參數

• _cursor_obsolete_threshold

此參數主要的用途是為了避免 child cursor 過多而造成 High Version Count 的問題，當 child cursor 的數量超過此參數的設定時，便會重新產生 Parent Cursor 。此參數於 11.2.0.3 的預設值為 100 、 11.2.0.4 預設值為 1024 ，而在 12.2 版本之後的預設值為 8192 ，表示一個 Parent Cursor 最多可產生 8192 個 Child Cursor ，顯然這個預設值是太高了，建議設定為 100 或 1024 。

• _cursor_reload_failure_threshold

當多個 session 同時需要 parse 同一句 SQL 時，由於無法同時進行 parsing ，其中某幾個 session 可能會進行 retry (reload) 並重新嘗試 parsing ，如果同時執行同一句 SQL 的 session 太多時，就有可能造成 reload 頻率過高，進而產生 library cache contention 的問題而影響效能。此參數就是為了避免 cursor reload 的頻率過高，當 reload 的次數超過此參數的設定時，便會視為此 cursor 無效，也就不會卡住一個 library cache 的空間。 19.18 之後的參數預設值為 20 ，如果系統有 parsing 所造成的效能問題，則可以考慮再調降此參數值。

• _adg_parselock_timeout

這個參數是為了避免 ADG 產生 ORA-4021 而 crash 的情況 :

error 4021 detected in background process ORA-04021: timeout occurred while waiting to lock object … LGWR (ospid: 189317): terminating the instance due to error 4021

此參數預設是 0 ，建議設定為 550 避免 ORA-4021 的問題。

• _ksipc_service_mask

此參數預設為 1 ，表示 IPC (Inter-Process Communication) 預設使用 MGA ， MGA (Managed Global Area) 是 12.1 版本開始引進的一個新的共享區域，主要是用來存放 process 與 process 之間相同且可以共享的空間位址， MGA 由系統按需求自動創建與刪除，目前 IPC 為這塊區域的主要使用者。

• _use_large_pages_for_mga

此參數用來設定 MGA 是否使用 large page ，預設為 FALSE ，不建議 MGA 使用 large page 。

• pga_aggregate_target

建議設定值為 5MB * 最大 session 數量，例如線上最大 session 數量為 1000 ，則 pga_aggregate_target 設定為 5MB * 1000 = 5GB 。

• _use_adaptive_log_file_sync

此參數設定是否啟用 adaptive log file sync 功能，預設為 TRUE ，建議維持預設值啟用。

• _lm_comm_channel

用來設定 GES (Global Enqueue Service) 所使用的協定，預設值為 msgq ，更改此參數會影響 GC 的效能，建議維持預設值。

• _spin_count

當一個 process 嘗試獲取 latch 而沒有成功時，便會再重新嘗試獲取一次，當嘗試的次數超過 _spin_count 仍然無法獲得 latch 時，此 process 就會進入休眠的隊列 (Latch Wait List) ，直到上一個 latch 被釋放後，此 process 才會從 Latch Wait List 中再度被喚醒進行操作。在 multi-processor 的環境下，此參數的預設值為 2000 ，由於會影響等待 latch 的行為，不建議隨意更改。

• gcs_server_processes

用來設定 GCS server processes (LMSn and LMnn) 的數量，建議值為 2 + (CPUs / 32) 。

一句 SQL 不使用 Index 之案例分析

問題描述:

一句 SQL 語法的執行計畫不使用 Index 造成效能不佳 :

SELECT T.* FROM T01_URL T WHERE T.URL LIKE :B1 AND TYPE = 'U'

此 table 具有 Index IX1_T01_URL 但是 execution plan 卻沒有使用 :

問題分析:

收集 10053 trace 分析為何沒有使用 Index :

SQL> exec dbms_sqldiag.dump_trace(p_sql_id=>'847df3vkcbsvh',p_child_number=>0, p_component=>'Compiler');

依據 10053 的內容來計算 cost ，公式如下 :

Cost = IO Cost + CPU Cost IO Cost = SRs + (MRs * (MREADTIM / SREADTIM)) CPU Cost = CYCLES / (CPUSPEED * 1000 * SREADTIM) SREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE / IOTFRSPEED) MREADTIM = IOSEEKTIM + (DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE) / IOTFRSPEED Full Table Scan SRs = 0 MRs = BLOCKS / MBRC Index Range Scan MRs = 0 SRs = LEVELS + (LBs * IX_SEL) + (CLUF * IX_SEL_WF)

其中 :

CPU Cycles : 指執行一條指令所花費的時間, 單位為 納秒  1/1,000,000,000 秒

SRs = Single-block reads.

MRs = Multi-block reads.

sreadm = single block read time.

mreadtim = multi block read time.

LBs = Index Leaf Blocks.

IX_SEL = Index Selectivity.

IX_SEL_WF = Index Selectivity with Filters.

由 10053 所收集到的系統統計資訊可得到的數據如下 :

使用 Full Table Scan 的 Cost 約為 48657 :

套入公式計算如下 :

IO Cost = 48319

SREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE / IOTFRSPEED) = 10 + (8192/4096) = 12

CPU Cost = CYCLES / (CPUSPEED * 1000 * SREADTIM) = 5351367865.712324 / (1319*1000*12) = 338.095013

Total Cost = IO Cost + CPU Cost = 48319 + 338.095013 = 48657.095

使用 Index Range Scan 的 Cost 約為 770733 :

其中 Index 所收集的統計資訊如下 :

套入公式計算如下 :

IO Cost = SRs + (MRs * (MREADTIM / SREADTIM)) = SRs = LEVELS + (LBs * IX_SEL) + (CLUF * IX_SEL_WF) = 3 + (47453 * 0.646377) + (8096445 * 0.091352) = 770301.971

CPU Cost = CYCLES / (CPUSPEED * 1000 * SREADTIM) = 6835653728 / (1319*1000*12) = 431.870971

Total Cost = IO Cost + CPU Cost = 770301.971 + 431.870971 = 770733.842

雖然手工計算出來的最終 cost 有一點誤差，但不影響我們分析。以結論來說，使用 Table Scan 的 cost 48657 小於使用 Index Range Scan 的 cost 770733 ，所以優化器最終使用的是 Table Scan。

進一步分析，針對 CPU Cost 來說， Table Scan 為 338 、 Index Range Scan 為 431 ，兩者其實差不多，但是 Index Range Scan 的 IO Cost 770733 卻遠遠大於 Table Scan 的 IO Cost 48319 ，這最終導致優化器認為使用 Index Range Scan 的成本太高而不選擇它。

由公式推斷，影響 Index Range Scan 的 IO Cost 最重要的因素為 IX_SEL 與 IX_SEL_WF ，也就是使用此 Index 的選擇性，這兩個因素的數值過大導致最終計算出來的 IO Cost 過大，所以從這邊可以知道，此 Index IX1_T01_URL(URL,TYPE) 的選擇性不佳，在這兩個欄位建立 Index 對於優化器來說是不適合的，必須尋求是否有更適合的欄位或是調整欄位的順序來建立 Index 。

結論:

Oracle CBO 會選擇 Cost 最低的路徑來做為最後執行的 Execution Plan ，由上述 10053 分析結果得知 IX1_T01_URL(URL,TYPE) 的選擇性不佳，但是由 SQL 語句來看，就只有 URL 、 TYPE 兩個 where 條件，理論上建立這個 Index 是合適的，那麼這邊就與優化器的判斷產生了矛盾。

以這個例子來說，使用 Index 應該是較好的選擇，但優化器因為數據公式化的關係無法做此判斷，所以本次我們應該使用 Hint ，或是建立 SQL Profile 來輔助優化器來使用 Index 。