This is the 4th posting in my series about init.ora/SPFILE parameters in Oracle Database 126.96.36.199.
- New SPFILE Parameters in Oracle Database 188.8.131.52
- Obsolete SPFILE Parameters in Oracle Database 184.108.40.206
- Deprecated SPFILE Parameters in Oracle Database 220.127.116.11
- Default SPFILE Parameter changes between Oracle Database 18.104.22.168, 22.214.171.124 and 126.96.36.199
Roy and I did a comparison between default parameter settings in Oracle Database 188.8.131.52 vs Oracle Database 184.108.40.206 vs Oracle Database 220.127.116.11. 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 18.104.22.168||Oracle 22.214.171.124||Oracle. 126.96.36.199|
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 188.8.131.52, 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:
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.
as you name it, the parallel_min_servers behavior has changed from 11.2 to 12.1. See the doc:
In 184.108.40.206 it was "0" whereas in 220.127.116.11 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!!!
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
as migrated database from 11g to 19c. now question should I change it or leave by default value in 19c
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.