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