#acl merlyn:read,write All:read

== archive_lag_target ==
Dell EMC recommends setting the archive_lag_target parameter to 900 to force a log switch every fifteen minutes.

Dell EMC recommends a 512-byte block size for redo log files on ScaleIO. 

Dell EMC recommends a minimum of five redo log groups per thread for any database.
Enterprise grade storage arrays largely negate the benefits of multiple redo log group
members.


== Determining a starting value for optimizer_index_cost_adj ==
http://www.dba-oracle.com/oracle_tips_cost_adj.htm

We can see that the optimal setting for optimizer_index_cost_adj is partially a function of the I/O waits for sequential reads vs. scattered reads:
{{{
select
   a.average_wait                                  c1,
   b.average_wait                                  c2,
   a.total_waits /(a.total_waits + b.total_waits)*100  c3,
   b.total_waits /(a.total_waits + b.total_waits)*100  c4,
   (b.average_wait / a.average_wait)*100           c5
from
   v$system_event  a,
   v$system_event  b
where
   a.event = 'db file scattered read'
and
   b.event = 'db file sequential read'; 
}}}

 In Oracle 10g and beyond, you can use this script using the dba_hist_system_event table:
{{{
col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
 
select
   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
   sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
   (
      sum(a.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c3,
   (
      sum(b.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c4,
  (
      sum(b.time_waited_micro) /
      sum(b.total_waits)) /
      (sum(a.time_waited_micro)/sum(a.total_waits)
   ) * 100 c5
from
   dba_hist_system_event a,
   dba_hist_system_event b
where
   a.snap_id = b.snap_id
and
   a.event_name = 'db file scattered read'
and
   b.event_name = 'db file sequential read';
}}}

== optimizer_index_caching ==

Here is a script that will display the total number of data bocks in the data buffer and a starting value for optimizer_index_caching:
{{{
select
count(case when o.object_type= 'INDEX' then 1 end) index_blocks,
count(case when o.object_type= 'INDEX PARTITION' then 1 end)
idx_part_blk,
count(case when o.object_type= 'TABLE' then 1 end) table_blocks,
count(case when o.object_type= 'TABLE PARTITION' then 1 end)
tbl_part_blcks,
count(case when o.object_type != 'TABLE' and o.object_type != 'INDEX'
and
o.object_type != 'TABLE PARTITION' and o.object_type
!= 'INDEX PARTITION' then 1 end) others_blocks
from
   dba_objects o,
   v$bh bh
where
   o.data_object_id = bh.objd;
}}}
=== References ===
http://www.dba-oracle.com/art_so_optimizer_index_caching.htm

https://richardfoote.wordpress.com/2009/09/01/optimizer_index_caching-parameter/

http://manishnashikkar.blogspot.com/2014/07/about-optimizerindexcaching.html