Samstag, 31. März 2018

spfile parameters derived from cpu_count

In Oracle databases some spfile parameters are derived based on others, if they are not set explicit.
One of the main drivers I discovered is CPU_COUNT.
So I did some tests based in the past for version 11.1.0.7 and 12.1.0.2.
To simulate a machine with sufficient CPUs, I had to use a trick described in a previous post. Using _disable_cpu_check threw an ORA-7445:[ksucre] in 12.2 with cpu_count >126.

the script I used this time is basically (of course with 180 instead of 122 for 18.0).
#!/usr/bin/ksh  -x

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

  echo "*.audit_file_dest='/_some_dir_/BX1/adump'
*.audit_trail='db'
*.compatible='12.2.0.1.0'
*.control_files='/_some_dir_/BX1/controlfile/o1_mf_djd017z2_.ctl', '/_some_dir_/fast_recovery_area/BX1/BX1/controlfile/o1_mf_djd01834_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/_some_dir_/oradata'
*.db_name='BX1'
*.db_recovery_file_dest='/_some_dir_/fast_recovery_area/BX1'
*.db_recovery_file_dest_size=7G
*.db_unique_name='BX1'
*.diagnostic_dest='/appl/oracle'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=7g
*.undo_tablespace='UNDOTBS1'
*.max_string_size='EXTENDED'
#*._disable_cpu_check='TRUE'
*.cpu_count=$i" > /_some_dir_/cpus/initBX1_$i.ora

  echo "shutdown abort;
startup pfile='/_some_dir_/cpus/initBX1_$i.ora' ;

insert into init_parameters
select '12.2'     ,
       $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/122doit$i.sql

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

sleep 5

  (( i = i + 1));
done


11.1.0.712.1.0.212.2.0.118.0.0.0
_cursor_db_buffers_pinnedxxxx
_db_block_buffers xxxx
_db_block_lru_latches xxxx
__db_cache_sizexxxx
_db_file_exec_read_count xx
db_file_multiblock_read_countxx
_db_handlesxxx
db_writer_processesxxxx
dml_locksxxx
_enqueue_hashxxx
_enqueue_hash_chain_latchesxxxx
_enqueue_locksxxxx
_enqueue_resourcesxxx
_flashback_generation_buffer_sizexxxx
_gc_fast_index_split_waitx
_gcs_min_cr_slavesxx
_kghdsidx_countxx

_hang_resolution_scopexx
_ksmsq_hintmaxprocx
__large_pool_sizexxx
_lms_rollbacksxx
log_bufferxxxx
_log_parallelism_maxxxxx
_log_simultaneous_copiesxxxx
_max_log_write_parallelismxx
_max_outstanding_log_writesxxx
_max_pending_scn_bcastsxxx
_messagesxxx
_num_longop_child_latchesxxxx
_num_rlslavesxx
parallel_max_serversxxxx
_parallel_min_message_poolxxxx
parallel_min_serversxxx
parallel_servers_targetxxx
pga_aggregate_limitxxx
_pmon_max_slavesxx
_pmon_slaves_arr_sizexx
processesxxx
_px_proactive_slave_alloc_thresholdxxx
_session_allocation_latchesxxx
sessionsxxx
sga_max_sizexx
shared_pool_reserved_sizexxx
__shared_pool_sizexxxx
_shared_server_num_queuesxxxx
_small_table_thresholdxxxx
_spin_count xxxx
_sqlmon_max_plan
xxx
_super_shared_conversion_threshold
xx
transactions xxx
_use_single_log_writer xxx

To get a good picture of the changes, feel free to select parameters, versions or whatever you want below.
Raw data is available at GistHub.
The js app is available here.
(It's based on an idea PerfSheet.js by Luca Canali.

As some of the values are not numbers, I applied this mapping:
TRUE1
FALSE0
OFF0
ADAPTIVE -1
INSTANCE -2


Kommentar veröffentlichen