This is the 4th posting in my series about init.ora/SPFILE parameters in Oracle Database 12.2.0.1.
- New SPFILE Parameters in Oracle Database 12.2.0.1
- Obsolete SPFILE Parameters in Oracle Database 12.2.0.1
- Deprecated SPFILE Parameters in Oracle Database 12.2.0.1
- Default SPFILE Parameter changes between Oracle Database 11.2.0.4, 12.1.0.2 and 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
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.
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)
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
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
so should we change parameter in 12c same as 11g or keep as it is with default value come up in 12c
like sec_protocol_error_further_action= continue in 11g
12c
sec_protocol_error_further_action=(DROP,3)
as migrated database from 11g to 19c. now question should I change it or leave by default value in 19c
Regards,
Nayaan
This is a decision I can’t take for you.
Usually you should be fine with the defaults but there may be cases, especially when the application demands it, where you’d like to stick with the old value.
Cheers,
Mike