Oracle Streams流复制性能优化脚本

Oracle Streams流复制性能优化脚本

---------------------------------------------------------------------------------------------------------------------
--
-- 1. Days to Retain Checkpoints CHECKPOINT_RETENTION_TIME (STAGE)
--

begin
dbms_capture_adm.alter_capture('<Capture_Name>',CHECKPOINT_RETENTION_TIME=>3);
end;
/

---------------------------------------------------------------------------------------------------------------------
--
-- 2. LogMiner (STAGE)
--

BEGIN
dbms_capture_adm.set_parameter('<Capture_Name>','_CHECKPOINT_FREQUENCY','1000');
END;
/

Make sure that the hidden capture parameter _CHECKPOINT_FREQUENCY is set to 1000 (new default value for 11g).
This will reduce the number of logminer (not database) checkpoints taken which has the side effect of reducing
the size of the SYSAUX tablespace and redo generation, as well.

---------------------------------------------------------------------------------------------------------------------
--
-- 3. Change _HASH_TABLE_SIZE (TARGET)
--

BEGIN
DBMS_APPLY_ADM.Set_parameter('<Apply_Name>','_HASH_TABLE_SIZE','10000000')
END;
/

_HASH_TABLE_SIZE: (releases prior to 10.2.0.4) In an mixed (Insert/UPdate/Delete) or heavy update workload that
results in a large number of dependency waits (WAIT_DEPs) on replicated tables with many constraints , consider
increasing the size of the dependency hash table with the hidden parameter _HASH_TABLE_SIZE. Set the value of
this parameter to a large number, for example, 1 million (1000000) or 10 million (10000000).
---------------------------------------------------------------------------------------------------------------------
--
-- 4 Change Parallelism (TARGET)
--

BEGIN
DBMS_APPLY_ADM.Set_parameter('<Apply_Name>','PARALLELISM','8')
DBMS_APPLY_ADM.Set_parameter('<Apply_Name>','_TXN_BUFFER_SIZE','32')
END;

Test with parallelism values based on available CPU. Typical settings are 4, 8, 16, or 24. 16 and
24 are normally used for high transaction loading with high end machines Values between 1 and 4
are typically used on low end machines. Midrange system typically configure 4 or 8 apply
servers.

Reduce _TXN_BUFFER_SIZE. What this will do is, reduce the number of messages that can be in
the memory which can prevent queue spillage due to the transaction staying in the memory for
more than 5 mins

---------------------------------------------------------------------------------------------------------------------
--
-- 5. Dynamic Statements (TARGET)
--

BEGIN
DBMS_APPLY_ADM.Set_parameter('<Apply_Name>','_DYNAMIC_STMTS','Y')
END;
/

If Y, then for UPDATE statements, the apply process will optimize the generation of SQL statements based on required columns.

---------------------------------------------------------------------------------------------------------------------
--
-- 6. Maintenance Issues: Keeping Segments small (STAGE)
--

You need to manually use shrink command to free unused space (but you need to stop the capture)

ALTER TABLE system.LOGMNR_RESTART_CKPT$ ENABLE ROW MOVEMENT;
ALTER TABLE system.LOGMNR_RESTART_CKPT$ SHRINK SPACE;
ALTER TABLE system.LOGMNR_RESTART_CKPT$ DISABLE ROW MOVEMENT;

You need to manually use shrink command to free unused space (but you need to stop the capture)
To improve the performance of the streams spillover table after major spillover activity, do the following:

ALTER TABLE aq$_<queue_table_name>_p ENABLE ROW MOVEMENT;
ALTER TABLE aq$_<queue_table_name>_p SHRINK SPACE;
---------------------------------------------------------------------------------------------------------------------
--
-- 7 Apply Performance (TARGET)
--

Modify the INITRANS, PCTFREE of SYS.STREAMS$_APPLY_PROGRESS table. The INITRANS value should be >= to
the apply process parallelism. Set PCTFREE to at least 10:

ALTER TABLE SYS.STREAMS$_APPLY_PROGRESS INITRANS 16 PCTFREE 10;

---------------------------------------------------------------------------------------------------------------------
--
-- 8. _SGA_SIZE (STAGE)
--

!!! This parameter should not be increased unless the logminer error ORA-1341 is encountered. !!!

Amount of memory available from the streams pool for logminer processing.
The default amount of streams_pool memory allocated to logminer is 10Mb.
Increase this value especially in environments where large LOBs are processed.
Streams pool memory allocated to logminer is unavailable for other usage.
Capture parameters can be set using the SET_PARAMETER procedure from the DBMS_CAPTURE_ADM package.
For example, to set the checkpoint frequency of the streams capture process named <CAPTURE_NAME>,
use the following syntax while logged in as the Streams Administrator:

BEGIN
dbms_capture_adm.set_parameter('<Capture_Name>','_SGA_SIZE','100');
END;
/

---------------------------------------------------------------------------------------------------------------------
--
-- 9. Propagation Latency (STAGE)
--

Maximum wait, in seconds, in the propagation window for a message to be propagated after it is enqueued.

The default value is 60. Caution: if latency is not specified for this call, then latency will over-write
any existing value with the default value.

For example, if the latency is 60 seconds, then during the propagation window, if there are no messages to be
propagated, then messages from that queue for the destination will not be propagated for at least 60 more seconds.
It will be at least 60 seconds before the queue will be checked again for messages to be propagated for the specified
destination.

BEGIN
dbms_aqadm.alter_propagation_schedule
( queue_name => 'STRMADMIN.STREAMS_OUT_Q'
, destination => '<TARGET_DB>'
, destination_queue => 'STRMADMIN.STREAMS_IN_QOA'
, latency => 3
);
END;
/
---------------------------------------------------------------------------------------------------------------------
--
-- 10. TXN_LCR_SPILL_THRESHOLD (TARGET)
--

BEGIN
dbms_apply_adm.set_parameter('<Apply_Name>','TXN_LCR_SPILL_THRESHOLD','250000');
END;
/

---------------------------------------------------------------------------------------------------------------------
--
-- 11. _JOB_QUEUE_INTERVAL (STAGE)
--

alter system set _job_queue_interval=1
---------------------------------------------------------------------------------------------------------------------
--
-- 12. Event Setting (_capture_publisher_flow_control_threshold) (STAGE)
--

alter system set events '10868 trace name context forever, level 30000';
-- restart the database

---------------------------------------------------------------------------------------------------------------------

### CHeck for Consraints aus HC herausnehmen