메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

 

Parameter Recommendations for Oracle Database 12c - Part I

By Mike Dietrich-Oracle on Mar 04, 2016

Best Practice Hint

A few weeks ago we've published some parameter recommendations including several underscores but based on an internal discussion (still ongoing) we decided to remove this entry and split up the tasks. The optimizer team will take over parts of it and I'll post an update as soon as something is published.

.

Preface

Please be advised - the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.

We strongly recommend SQL Performance Analyzer to verify the effect of any of those parameters. 
.

How to read this blog post?

Never ever blindly set any underscore or hidden parameters because "somebody said" or "somebody wrote on a blog" (including this blog!) or "because our country has the best tuning experts worldwide" ... Only trust Oracle Support if it's written into a MOS Note or an official Oracle White Paper or if you work with a particular support or consulting engineer for quite a long time who understands your environment.
.

Important Parameter Settings
 

  • _kks_obsolete_dump_threshold
    • What it does?
      • Introduced in Oracle 12.1.0.2 as an enhancement  to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and its child cursors after the parent cursor has been obsoleted N times. 
    • Problem:
      • Trace files can grow like giant mushrooms due to cursor invalidations
    • Solution:
    • Patches:
      • Fix included in DBBP 12.1.0.2.160216
      • Fix on-top of 12.1.0.2.13DBEngSysandDBIM
      • Since Feb 13, 2016 there's a one-off available but on Linux only - and only on top of a fresh 12.1.0.2 
    • Remarks:
      • The underlying cursor sharing problem needs to be investigated - always
        If you have cursor sharing issues you may set this parameter higher therefore not every invalidation causes a dump, then investigate and solve the issue, and finally switch the parameter to 0 once the issue is taken care of. 
        Please be aware that switching the parameter to 0 will lead to a lack of diagnostics information in case of cursor invalidations.

         
  • _use_single_log_writer
  • memory_target
    • What it does?
    • Problem:
      • Unexpected failing database upgrades with settings of  memory_target < 1GB where equal settings ofsga_target and pga_aggregate_target didn't cause issues 
      • It prevents the important use of HugePages
    • Solution:
      • Avoid memory_target by any chance
      • Better use sga_target and pga_aggregate_target instead

         
  • pga_aggregate_limit

 

Essential MOS Notes for Oracle Database 12.1.0.2
 

 

 

출처 : https://blogs.oracle.com/UPGRADE/entry/parameter_recommendations_for_oracle_database

  •  
번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 12
27 V$EVENT_NAME 뷰의 Name 컬럼에 정의된 event name에서 오는 오해 명품관 2017.03.08 50
26 Can I apply a BP on top of a PSU? Or vice versa? 명품관 2016.06.01 113
25 How to change the database name in 12c 명품관 2016.05.31 46
24 How to Recover Data (Without a Backup!) 명품관 2016.05.11 86
23 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 77
22 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 99
21 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 85
20 Quick tip on Function Based Indexes 명품관 2016.04.19 35
19 Oracle Linux 6.7 with Oracle 11.2.0.4 RAC 명품관 2016.04.15 99
18 Parameter Recommendations for Oracle Database 12c - Part II 명품관 2016.03.18 71
» Parameter Recommendations for Oracle Database 12c - Part I 명품관 2016.03.07 240
16 Hybrid Columnar Compression Common Questions 명품관 2016.03.04 34
15 What is an In-Memory Compression Unit (IMCU)? 명품관 2016.02.24 48
14 Why You Can Get ORA-00942 Errors with Flashback Query 명품관 2016.02.01 58
13 How do I capture a 10053 trace for a SQL statement called in a PL/SQL package? 명품관 2016.01.06 39
12 (유투브) KISS series on Analytics: Dealing with null - Connor McDonald 명품관 2016.01.05 28
11 Upgrade a Pluggable Database in Oracle 12c 명품관 2015.12.30 11
10 Oracle 12c SQL – Using JSON 명품관 2015.12.29 148
9 Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) Installation on Oracle Linux 6 and 7 명품관 2015.12.23 60
8 Top 5 SQL Monitor Features file 명품관 2015.12.01 17
위로