Oracle面試寶典-引數篇

Oracle 面試寶典 - 引數篇

 

請問在安裝完 Oracle 資料庫後，你會去調整哪些引數？

一  audit_trail
審計（Audit ）用於監視使用者所執行的資料庫操作，審計記錄可存在資料字典表，當資料庫的審計是開啟時，在語句執行階段產生審計記錄。由於審計表（AUD$ ）存放在SYSTEM 表空間，因此為了不影響系統的效能，保護SYSTEM 表空間，建議把AUD$ 移動到其他的表空間上，或者關閉審計。
參考命令：alter system set audit_trail='NONE' #INSTANCE# scope=spfile;

二 _optimizer_adaptive_cursor_sharing

隱含引數 _optimizer_adaptive_cursor_sharing 能控制自適應式遊標共享的部分行為，由 Oracle 自適應的處理繫結變數的窺探，但這可能會觸發效能問題。 Oracle 建議在非技術指導下，將其關閉掉。
參考命令：alter system set "_optimizer_adaptive_cursor_sharing"=FALSE #INSTANCE#;

三 _optimizer_extended_cursor_sharing

建議禁用自適應遊標共享，將隱含引數_optimizer_extended_cursor_sharing 設定為 NONE 。
參考命令：alter system set "_optimizer_extended_cursor_sharing"='NONE' #INSTANCE#;

四 _optimizer_extended_cursor_sharing_rel

建議禁用自適應遊標共享，將隱含引數_optimizer_extended_cursor_sharing_rel 設定為 NONE 。
參考命令：alter system set "_optimizer_extended_cursor_sharing_rel"='NONE' #INSTANCE#;

五 parallel_force_local

為了降低叢集間的資料互動，建議並行程式強制在本地例項分配，以便降低叢集間的資料互動。
參考命令：alter system set parallel_force_local=TRUE #INSTANCE#;

六 _gc_policy_time

DRM （Dynamic Resource Mastering ）負責將 Cache 資源 Remaster 到頻繁訪問這部分資料的節點上，從而提高 RAC 的效能。但是 DRM 在實際使用中存在諸多 Bug ，頻繁的 DRM 會引發例項長時間 Hang 住甚至是當機，建議關閉 DRM 。
參考命令：alter system set "_gc_policy_time"=0 #INSTANCE# scope=spfile;

七 _gc_undo_affinity
建議關閉叢集 Undo Affinity ，降低叢集 互動，避免觸發相關 BUG 。
參考命令：alter system set "_gc_undo_affinity"=FALSE #INSTANCE# scope=spfile;

八 _optimizer_use_feedback
基數反饋（Cardinality Feedback ）是 Oracle 11.2 中引入的關於 SQL 效能最佳化的新特性，該特性主要針對統計資訊陳舊、無直方圖或雖然有直方圖但仍基數計算不準確的情況，Cardinality 基數的計算直接影響到後續的 JOIN COST 等重要的成本計算評估，造成 CBO 選擇不當的執行計劃。但是該引數存在不穩定因素，可能會帶來執行效率的問題，建議關閉最佳化器反饋。
參考命令：alter system set "_optimizer_use_feedback"=FALSE #INSTANCE#;

九 deferred_segment_creation
延遲段建立會導致使用 Direct 方式的 Export 出來的 DMP 檔案無法正常匯入（文件 ID 1604983.1 ），建議關閉延遲段建立的特性。
參考命令：alter system set deferred_segment_creation=FALSE #INSTANCE#;

十 _undo_autotune

隱含引數 _undo_autotune 負責 undo retention （即 undo 段的保持時間）的自動調整，若由 Oracle 自動負責 undo retention ，則 Oracle 會根據事務量來佔用 undo 表空間，可能會形成 undo 表空間的爭用，建議將其關閉。
參考命令：alter system set "_undo_autotune"=FALSE #INSTANCE#;

十一 _optimizer_null_aware_antijoin

引數 _optimizer_null_aware_antijoin 是在 Oracle 11g 引入的新引數，它用於解決在反連線（Anti-Join ）時，關聯列上存在空值（NULL ）或關聯列無非空約束的問題。但是該引數不穩定，存在較多的 Bug ，為避免觸發相關 Bug ，建議關閉。
參考命令：alter system set "_optimizer_null_aware_antijoin"=FALSE #INSTANCE#;

十二 _PX_use_large_pool
並行執行的從屬程式在工作時需要交換資料和資訊，預設從 Shared Pool 中分配記憶體空間。當 _PX_use_large_pool=TRUE 時並行程式將從 Large Pool 中分配記憶體，減少對共享池（Shared Pool ）的爭用。
參考命令：alter system set "_PX_use_large_pool"=TRUE scope=spfile #INSTANCE#;

十三 _partition_large_extents

建議關閉分割槽使用大的初始化區（Extent ）。
參考命令：alter system set "_partition_large_extents"=FALSE #INSTANCE#;

十四 _use_adaptive_log_file_sync

Oracle 預設啟用 _use_adaptive_log_file_sync 引數，使得 LGWR 程式寫日誌的方式能自動在 post/wait 和 polling 兩種方式之間進行取捨，可能會導致比較嚴重的寫日誌等待（log file sync 的平均單次等待時間較高）, 建議關閉此功能。
參考命令：alter system set "_use_adaptive_log_file_sync"=FALSE #INSTANCE#;

十五 _memory_imm_mode_without_autosga

alter system set "_memory_imm_mode_without_autosga"=false sid='*' scope=spfile;
說明：11.2.0.3 開始，即使是手工管理記憶體方式下，如果某個POOL 記憶體吃緊，Oracle 仍然可能會自動調整記憶體，用這個引數來關閉這種行為。

十六 event

alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1' sid='*' scope=spfile;
說明：這個引數主要設定2 個事件：
1 ） 10949 事件用於關閉11g 的自動serial direct path read 特性，避免出現過多的直接路徑讀，消耗過多的IO 資源。
2 ） 28401 事件用於關閉11g 資料庫中使用者持續輸入錯誤密碼時的延遲使用者驗證特性，避免使用者持續輸入錯誤密碼時產生大量的row cache lock 或library cache lock 等待，嚴重時使資料庫完全不能登入。

十七 _b_tree_bitmap_plans
alter system set “_b_tree_bitmap_plans”=false sid=’*’ scope=spfile;
說明：對於OLTP 系統，Oracle 可能會將兩個索引上的ACCESS PATH 得到的rowid 進行bitmap 操作再回表，這種操作有時邏輯讀很高，對於此類SQL 使用複合索引才能從根本上解決問題。

十八 enable_ddl_logging

alter system set enable_ddl_logging=true sid='*' scope=spfile;
說明：在11g 裡面，開啟這個引數可以將ddl 語句記錄在alert 日誌中。以便於某些故障的排查。建議在OLTP 類系統中使用。

十九 parallel_max_servers

alter system set parallel_max_servers=cpu_count 邏輯CPU 數 sid='*' scope=spfile;
說明：這個引數預設值與CPU 相關，OLTP 系統中將這個引數設定小一些，可以避免過多的並行對系統造成衝擊。

二十 _optimizer_ads_use_result_cache

alter system set "_optimizer_ads_use_result_cache" = FALSE scope=spfile sid='*';
說明：12c 中關閉result_cache ，容易觸發latch free 等bug 。

二十一 _datafile_write_errors_crash_instance
alter system set "_datafile_write_errors_crash_instance"=FALSE scope=spfile sid='*';
說明：在 PDB 由於某些原因丟失資料檔案後，允許 CDB 繼續執行。注意: 只對 PDB 的非系統資料檔案有效。

二十二 _optimizer_adaptive_plans

alter system set "_optimizer_adaptive_plans"=FALSE scope=spfile sid='*';
說明：關閉自適應執行計劃。

二十三 _optimizer_aggr_groupby_elim
alter system set "_optimizer_aggr_groupby_elim"=FALSE scope=spfile sid='*';
19567916.8 ，Wrong results when GROUP BY uses nested queries in 12.1.0.2

 

二十四 _optimizer_reduce_groupby_key
alter system set "_optimizer_reduce_groupby_key"=FALSE scope=spfile sid='*';
說明：Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause

二十五 _optimizer_cost_based_transformation

alter system set "_optimizer_reduce_groupby_key"=off scope=spfile sid='*';
說明：關閉COST 查詢轉換。

二十六 job_queue_processes

alter system set job_queue_processes=cpu_core （CPU 核數） scope=spfile sid='*';
說明：預設1000 ，建議調整為CPU 核數。

二十七 optimizer_dynamic_sampling( 預設 2)

alter system set optimizer_dynamic_sampling=4 scope=both sid='*';

說明：動態取樣，有些場景下，可根據時間情況調大采樣級別，比如調到4 。

二十八 optimizer_index_cost_adj( 預設 100)

alter system set optimizer_index_cost_adj=40 scope=both sid='*';

說明：最佳化器計算透過索引掃描訪問表資料的cost 開銷

某些場景下，值越大最佳化器越傾向於使用全表掃描。相反，值越小，最佳化器越傾向於使於索引掃描。可根據實際情況進行調整，比如調小到40 。

二十九 _optimizer_mjc_enabled

alter system set "_optimizer_mjc_enabled" = false scope=both sid='*';

說明: 某些場景下，需要MERGE JOIN CARTESIAN ；

注意：

引數和說明，參考墨天輪

查詢引數值：

select a.ksppinm name , b.ksppstvl value , a.ksppdesc description

  from x$ksppi a , x$ksppcv b

  where a.indx = b.indx

   and a.ksppinm in ( '_optimizer_adaptive_cursor_sharing' ,

                     '_optimizer_extended_cursor_sharing' ,

                     '_optimizer_extended_cursor_sharing_rel' ,

                     '_gc_policy_time' ,

                     '_gc_undo_affinity' ,

                     '_optimizer_use_feedback' ,

                     '_undo_autotune' ,

                     '_optimizer_null_aware_antijoin' ,

                     '_partition_large_extents' ,

                     '_use_adaptive_log_file_sync' ,

                     '_memory_imm_mode_without_autosga' ,

                     '_b_tree_bitmap_plans' ,

                     '_optimizer_mjc_enabled' ,

                     '_optimizer_ads_use_result_cache' ,

                     '_datafile_write_errors_crash_instance:' ,

                     '_optimizer_adaptive_plans' ,

                     '_optimizer_aggr_groupby_elim' ,

                     '_optimizer_reduce_groupby_key' ,

                      '_optimizer_cost_based_transformation' ,

                     'audit_trail' ,

                     'deferred_segment_creation' ,

                     'event' ,

                     'enable_ddl_logging' ,

                     'job_queue_processes' ,

                     'optimizer_dynamic_sampling' ,

                     'optimizer_index_cost_adj' )

  order by 1 ;

如何檢視當前系統設定過哪些 event?

SQL> alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1' sid='*' scope=spfile;

SQL> shutdown immediate

SQL> startup

---1 直接檢視引數

---2 oradebug

SQL> oradebug setmypid

Statement processed.

SQL> oradebug eventdump system

28401 trace name context forever,level 1

10949 trace name context forever,level 1

---3 告警日誌

[oracle@cjcos ~]$ cd /u01/app/oracle19/diag/rdbms/cjcdb01/cjcdb01/trace/

[oracle@cjcos trace]$ vim alert_cjcdb01.log

歡迎關注我的微信公眾號 "IT 小 Chen" ，共同學習，共同成長！！！