Freitag, 15. Januar 2016

Instance parameters derived from cpu_count - 12.1.0.2

About 6 years ago I wanted to know which instance parameters are derived from cpu_count. So it tested a 11.1.0.7 DB - in that version there 21 parameters changed based on the value of cpu_count.
Some DB versions passed by so I decided it's time for another check. This time it's 12.1.0.2 without any PSUs/patches. The machine is the same class as previous, so it took some time.

the script is nearly the same today:
!/usr/bin/ksh  -x

integer i=1
while ((i <= 128));
do
  print " $i ";

  echo "*.audit_file_dest='/appl/oracle/admin/BERX3_GLOBAL/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/appl/oracle/oradata/BERX3_GLOBAL/controlfile/o1_mf_c9h3bxlw_.ctl','/appl/oracle/fast_recovery_area/BERX3_GLOBAL/controlfile/o1_mf_c9h3bxpg_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/appl/oracle/oradata'
*.db_name='BERX3_GL'
*.db_recovery_file_dest='/appl/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4815m
*.db_unique_name='BERX3_GLOBAL'
*.diagnostic_dest='/appl/oracle'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=13g
*.undo_tablespace='UNDOTBS1'
*.cpu_count=$i" > /appl/home/oracle/instance_parameters/cpus/initBERX2_$i.ora

  echo "startup pfile='/appl/home/oracle/instance_parameters/cpus/initBERX2_$i.ora' ;

insert into init_parameters
select $i         ,
       a.ksppinm  ,
       b.ksppstvl ,
       c.ksppstvl
from x\$ksppi a, x\$ksppcv b, x\$ksppsv c
where a.indx = b.indx and a.indx = c.indx;

commit;

select distinct(\"CPUs\") from init_parameters;

shutdown;

exit;"> cpus/doit$i.sql

sqlplus "/ as sysdba" @cpus/doit$i.sql >cpus/log$i.log

sleep 5

  (( i = i + 1));
done

And all the values interesting is available again.
There are 40 parameters now changing value based on cpu_count - so a lot more of things which will change, when you "only" add/remove CPUs or alter instance caging.

Here are the graphs for 12.1.0.2 - and 11.1.0.7 if they exist already. Some look quite different.


__db_cache_size


11.1.0.7:



__java_pool_size




__large_pool_size




__shared_pool_size


11.1.0.7:



_cursor_db_buffers_pinned


11.1.0.7:


_db_block_buffers



11.1.0.7:



_db_block_lru_latches



11.1.0.7:



_db_handles




_enqueue_hash




_enqueue_hash_chain_latches


11.1.0.7:



_enqueue_locks


11.1.0.7:



_enqueue_resources




_flashback_generation_buffer_size


11.1.0.7:



_kghdsidx_count


11.1.0.7:



_log_parallelism_max


11.1.0.7:



_log_simultaneous_copies


11.1.0.7:



_max_outstanding_log_writes




_max_pending_scn_bcasts




_messages




_num_longop_child_latches



11.1.0.7:



_parallel_min_message_pool


11.1.0.7:



_px_proactive_slave_alloc_threshold




_session_allocation_latches




_shared_server_num_queues


11.1.0.7:



_small_table_threshold


11.1.0.7:



_spin_count


11.1.0.7:



_sqlmon_max_plan


11.1.0.7:



_use_single_log_writer

is TRUE for cpu_count 1..7 and ADAPTIVE for 8..128



db_writer_processes


11.1.0.7:



dml_locks




log_buffer


11.1.0.7:



parallel_max_servers


11.1.0.7:



parallel_min_servers




parallel_servers_target



pga_aggregate_limit



processes



sessions



shared_pool_reserved_size



transactions