메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

Parameter Recommendations for Oracle Database 12c - Part II

 

By Mike Dietrich-Oracle on Mar 08, 2016

 


 

Best Practice Hint

Time for a new round on Parameter Recommendations for Oracle Database 12.1.0.2. The focus of this blog post settles on very well known parameters with interesting behavior. This can be a behavior change or simply something we'd like to point out. And even if you still work on Oracle Database 11g some of the below recommendations may apply to your environment as well.

Preface

Again, 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 Real Application Testing, especially the SQL Performance Analyzer but also Database Replay to verify the effect of any of those parameters. 
.

Known Parameters - Interesting Behavior
 

  • parallel_min_servers
    • What it does?
    • Default:
      • CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2
    • Behavior Change:
      • Setting it to a value below the default will let the database ignore it.
      • In Oracle Database 11g the default was 0
      • Compare 11.2.0.4 vs 12.1.0.2 on the same box:
        • 11g:
          SQL> show parameter parallel_min_servers
          NAME                  TYPE     VALUE
          --------------------- -------- ------
          parallel_min_servers  integer  0
        • 12c: 
          SQL> show parameter parallel_min_servers
          NAME                  TYPE     VALUE
          --------------------- -------- ------
          parallel_min_servers  integer  8
    • Explanation:

 

  • recyclebin
    • What it does?
      • See the Oracle Documentation - controls whether the Flashback Drop capability is turned on or off. If the parameter is set to OFF, then dropped tables do not go into the recycle bin. If this parameter is set to ON, then dropped tables go into the recycle bin and can be recovered.
    • Default:
      • ON
    • Recommendation:
      • If the recyclebin is ON (the default) in your environment then empty it at least once per week. Create a default job in all your environments emptying the recycle bin every Sunday morning at 3am for instance:
        SQL> purge DBA_RECYCLEBIN;
    • Explantion:
      • The recycle bin is on in every database by default since Oracle 10g. The danger is that it may not be emptied but especially on developer databases many objects may be created and dropped again. As a result the dropped objects and its dependents still stay in the database until the space needs to be reclaimed. That means, they exist in the data dictionary as well, for instance in TAB$. Their name is different now starting with "BIN$..." instead of "EMP" - but they will blow up your dictionary. And emptying it not often enough may introduce a performance dip to your system as the cleanup of many objects can be quite resource intense
      • Check your current recycle bins:
        SQL > SHOW RECYCLEBIN;
        ORIGINAL NAME RECYCLEBIN NAME              OBJECT TYPE DROP TIME
        ------------- ---------------------------- ----------- -------------------
        TEST_RBIN     BIN$2e51YTaSK8TL/mPy+FuA==$0 TABLE       2010-05-27:15:23:45
        TEST_RBIN     BIN$5dF60S3GSEOSSYREaqCg==$0 TABLE       2010-05-27:15:23:43
        TEST_RBIN     BIN$JHCDN9YwQRXjXGOJcCIg==$0 TABLE       2010-05-27:15:23:42
    • More Information:

       

.
.

 

  • deferred_segment_creation
    • What it does?
      • See the Oracle Documentation - set to the default (TRUE), then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table
    • Default:
      • TRUE
    • Recommendation:
      • Set it to FALSE unless you plan to create a larger number of tables/indexes knowing that you won't populate many of them.
    • Explantion/Risk:
      • If my understanding is correct this parameter got introduced with Oracle Database 11.2 in order to save space when applications such as EBS, Siebel or SAP create tons of tables and indexes which never may get used as you don't work with the matching module of the software
      • The risk can be that certain query check DBA_SEGMENTS and/or DBA_EXTENTS - and if there's no segment allocated you won't find an indication about the existence of the object in there - but it actually exists. Furthermore we have seen issues with Data Pump workers getting contention, and some other things. 
    • More Information:
      • The documentation has become now pretty conservative as well since Oracle 11.2.0.4 and I'll second that:
        Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.
        ..

 --Mike

 

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

위로