Oracle 8i upgrade to 9i , 9i upgrade to 11g/12c 遇上效能問題(CPU busy)

Case1 :  8i upgrade to 9i

目前證實是資料庫升板可能遇上相容性問題 , 9i 新的subquery 增強功能在某些狀況下 會變得緩慢 (特別是在8i upgrade to 9i 的資料庫)

有兩種方法如下:

1.

SQL>

alter session set "_UNNEST_SUBQUERY" = false;

alter session set "_ALWAYS_SEMI_JOIN" = off;

alter session set "_ALWAYS_ANTI_JOIN" = off;

 then execute your SQL command...

2.設定資料庫參數

*._UNNEST_NOTEXISTS_SQ='OFF' (需重啟)

Case 2 :  9i to 10g/11g

一樣跟subquery 機制有關聯 , 修改前後占用CPU 資源差異甚鉅

SQL>

alter session set "_optimizer_cost_based_transformation" =off;  --遇到ORA 600 , ORA 7445問題需調整

alter session set "_gby_hash_aggregation_enabled" = FALSE;

then execute your SQL command...

詳細說明可以參考網誌內的另一篇文章
http://jaychu649.blogspot.tw/2011/12/10ggroup-by-sort.html

BTW,

如果還無效 , 記得分析相關表格與索引再試試.

Ref:

http://www.netsavysupport.com/oracle-9i-to-11g-upgrade-performance-issues 

update 2017/3/14
1.12c建議的參數如下:

• "optimizer_adaptive_features"=FALSE  (關閉12c  cost optimizer 的new features)
• "_optimizer_ads_use_result_cache" = FALSE (關閉 12c 自動使用 result cache, 減少latch free的等待事件)
• DEFERRED_SEGMENT_CREATION = FALSE (讓table create 時就建立 segment)
• SEC_CASE_SENSITIVE_LOGON = TRUE --(設定帳號的密碼要不要區分大小寫… 預設為TRUE，此參數於12c 已deprecated, 還是可以設為FALSE只是alert會報此參數已deprecated)
• 關閉密碼過期alter profile default limit password_life_time unlimited;
• 關閉lgwr adative feature : alter system set _use_adaptive_log_file_sync=false; --在11.2.0.3/ 11.2.0.4 預設會自動invoke 多個writer process, 但開啟後偶然會造成大量的redo log file寫入等待事件Multiple Log Writers in 12c Causing Enabling and Disabling of Adaptive Scalable Log Writer Workers Which Cause High 'log file sync' Wait Event (Doc ID 2174075.1)
• parallel_force_local=true --關閉RAC中的跨節點查詢
• alter system set "_kks_obsolete_dump_threshold" =0; --避免invalid cursor產生過大的trc files  on 12.1.0.2
• alter system set events="trace[krb.*] disk disable, memory disable" scope=both; --避免rman產生過大的trc files on 12.2.0.1 (Bug 22700845 - RMAN krb event Writes to Disk when Syntax Includes Not to Do It (Doc ID 22700845.8))
• job_queue_processes=equivalent of 2 * CPU cores

2.通過測試證明,在12.1..2  的Oracle   RAC環境中,如果要使用IN MEMORY特性,

需要設定

parallel_degree_policy=AUTO和

parallel_force_local=false之後,才能夠真正意義上的啟動IM特性,不然只是執行計畫中的啟用,可能是假像
ref

http://www.xifenfei.com/2015/05/%E5%9C%A8race%E4%B8%AD%E4%BD%BF%E7%94%A8in-memory%E7%89%B9%E6%80%A7%E8%AF%B7%E6%B3%A8%E6%84%8Fparallel_degree_policy%E5%92%8Cparallel_force_local%E5%8F%82%E6%95%B0.html
Bug 21283023 - ALTER DATABASE in PDBs fails when PARALLEL_FORCE_LOCAL=TRUE
ref:

Parameter Recommendations for Oracle Database 12c - Part Ihttps://blogs.oracle.com/upgrade/parameter-recommendations-for-oracle-database-12c-part-i

Parameter Recommendations for Oracle Database 12c - Part II
https://blogs.oracle.com/upgrade/parameter-recommendations-for-oracle-database-12c-part-ii