Default Changes SPFILE Parameters – Oracle 12.2

This is the 4th posting in my series about init.ora/SPFILE parameters in Oracle Database 12.2.0.1.

Roy and I did a comparison between default parameter settings in Oracle Database 11.2.0.4 vs Oracle Database 12.1.0.2 vs Oracle Database 12.2.0.1. And some changes are quite interesting – of course the memory driven parameters are left out in this list.

Color RED marks a change between releases.
The databases were all on OL (Oracle Linux) 6.8 and located on file system, not in ASM – therefore certain values may differ when the database is located in ASM and/or on a different OS port.

Parameter Oracle 11.2.0.4 Oracle 12.1.0.2 Oracle. 12.2.0.1
audit_sys_operations FALSE TRUE TRUE
compatible 11.2.0.4 12.1.0.2.0 12.2.0
control_file_record_keep_time 7 7 30
db_securefile PERMITTED PREFERRED PREFERRED
dml_locks 616 1416 2076
filesystemio_options NONE NONE setall
job_queue_processes 1000 1000 4000
object_cache_optimal_size 102400 102400 10240000
optimizer_features_enable 11.2.0.4 12.1.0.2 12.2.0.1
parallel_max_servers 48 80 80
parallel_min_servers 0 8 8
parallel_servers_target 64 32 32
parallel_adaptive_multi_user TRUE TRUE FALSE
pre_page_sga FALSE TRUE TRUE
resource_limit FALSE TRUE TRUE
sec_max_failed_login_attempts 10 3 4
sec_protocol_error_trace_action CONTINUE TRACE LOG
spatial_vector_acceleration FALSE FALSE TRUE
sql92_security FALSE FALSE TRUE

–Mike

7 thoughts on “Default Changes SPFILE Parameters – Oracle 12.2

  1. Hi Mike,

    Thank you for that interesting comparison.

    I want to say just a few words about all of these numeric values. For instance, parallel_min_servers parameter default value was not changed from 0 to 8 between 11.2 and 12.1.
    Should we really care about this "little" change?
    Well, it only has changed so in your system.
    Actually, the default value of this parameter has been changed. It means that in several my systems, having migrated to 12.1.0.2, I ended up with more than 200 parallel servers. And I even run out of processes and got ORA-20 in some of them.
    It was due to the fact that the default value formula was changed between those releases.

    Another example, as you said the default value of job_queue_processes has been changed from 1000 to 4000 between 12.1 and 12.2. That can cause a serious disaster when somewhere 4000 of jobs will be raised. I have an ongoing SR with my Oracle Support and have provided an easily reproducible test case to the GCS where I demonstrated that the ILM JOB_LIMIT parameter does not influence the actual number of jobs spawning during ILM tasks:
    SR 3-13446449241 : Predefect:ILM JOBLIMIT PARAMETER HAS NO EFFECT ON A NUMBER OF ACTIVE JOBS
    That day I got 1000 active ILM jobs. It means that I may get 4000 ILM jobs under the same conditions in 12.2 – not only will it lead to high load, but also some of my instances do not have the processes parameter set to accommodate such number of jobs.

    The same may be applicable to other numeric parameters as well.

  2. Some of the above listed parameters are derived from other parameters so can also differ from database to database running on same version:
    dml_locks ->transactions->sessions->processes
    parallel_max_servers (computed based on cpu_count, threads, asmm/amm, pga)

  3. Of course – that’s what I’m saying above.
    But the 3 databases reside all on the same server – therefore things such as CPU_COUNT is supposed to be the same in all three – meaning at the end the defaults are still higher.

    Cheers
    Mike

  4. Mikhail,

    as you name it, the parallel_min_servers behavior has changed from 11.2 to 12.1. See the doc:
    http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams188.htm#REFRN10160
    vs.
    http://docs.oracle.com/database/121/REFRN/GUID-1D7EC131-7B5B-40E5-A0F8-ABC7B4C5B0E8.htm#REFRN10160

    In 11.2.0.4 it was "0" whereas in 12.1.0.2 it is calculated:
    CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2

    I don’t say that you have to take care on every change. The purpose for such a blog post is simply to give people an insight view about things which are not documented directly or under a specific section in the upgrade guide. But I don’t say that you have to take care on everything 😉 Take it just as a FYI only 😉

    I think I blogged about this change a while back already.

    And we have a wonderful slide about JOB_QUEUE_PROCESSES (and a blog post) recommending to set it to the number of actual CPUs in your system. And I know that such tiny little changes can have a dramatic effect (especially the "parallel" parameters) on systems with a high CPU count (SPARC boxes are famous for it with all their virtual cores).

    Cheers and thanks for your comment!!!
    MIke

  5. Pingback: New SPFILE parameters in Oracle Database 12.2.0.1 | Upgrade your Database - NOW!

  6. Pingback: Obsolete SPFILE Parameters in Oracle Database 12.2.0.1 | Upgrade your Database - NOW!

  7. Pingback: Deprecated Parameters in Oracle Database 12.2.0.1 | Upgrade your Database - NOW!

Leave a Reply

Your email address will not be published. Required fields are marked *