Hidden parameter for 10g2R Streams
My database alert log.
================================================
Tue Dec 16 08:51:14 2008
C001: large txn detected (14306 LCRs), xid: 0x000a.015.00058191
Tue Dec 16 08:56:15 2008
Propagation Schedule for (STRMADMIN.STREAMS_QUEUE, MRPC64.SAE.COM.HK) encountered following error:
ORA-25307: Enqueue rate too high, flow control enabled
================================================
this error is caused by large transaction, it should be seted the hidden parameter to prevent this error,
but the most importment to avoid the large transaction.
Long-running transactions are open transactions with no activity( ie, no new change records , rollback
or commit ) for an extended period (20 minutes). Large transactions are open transactions with a large number
of change records.The alert log will report the fact that a long-running or large transaction hasbeen seen
every 20 minutes. Not all such transactions will be reported - only 1 per 20 minute period. When the commit
or rollback is received, this fact will be reported in the alert log as well.
1)TXN_LCR_SPILL_THRESHOLD
the default values is 10,000. If the reader server of an apply process has the specfied number of
messages in memory for a particular transaction, when it detects the next message for this
transaction, it spill the messages from memory to hard disk. So, the apply process applies messages
from hard disk and the last messages from memory. Message spilling occures at the transaction
level. It can query the DBA_APPLY_SPILL_TXN for the information about transactions spilled by an
apply process.
2)_TXN_BUFFER_SIZE
large transaction means having many row changes within a single transaction [100000's] and
parallelism > 1, consider reducing the transaction buffer size even lower.
3)_DYNAMIC_STMTS
For 10gR2 with a workload consisting of many UPDATE transactions that modify less than half of the columns
for any given row, consider using the hidden apply parameter _DYNAMIC_STMTS. This parameter setting may
reduce redo generation as well as improve apply performance.
4)_HASH_TABLE_SIZE
(all releases) 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.
5)_RESTRICT_ALL_REF_CONS
exec dbms_apply_adm.set_parameter('STRMADMIN_APPLY', 'TXN_LCR_SPILL_THRESHOLD', 100000);
exec dbms_apply_adm.set_parameter('STRMADMIN_APPLY', '_TXN_BUFFER_SIZE', 2);
exec dbms_apply_adm.set_parameter('STRMADMIN_APPLY', '_HASH_TABLE_SIZE', 1000000);
exec dbms_apply_adm.set_parameter('STRMADMIN_APPLY', '_RESTRICT_ALL_REF_CONS','N');
exec dbms_apply_adm.set_parameter('STRMADMIN_APPLY','_SGA_SIZE','100');
exec dbms_apply_adm.set_parameter('STRMADMIN_APPLY','PARALLELISM','4');
exec dbms_apply_adm.set_parameter('STRMADMIN_APPLY','DISABLE_ON_ERROR','N');
releated dictionary
======================
select * from SYS.STREAMS$_APPLY_PROGRESS;
select * From sys.STREAMS$_APPLY_SPILL_MESSAGES;
select * From SYS.STREAMS$_APPLY_SPILL_TXN;
select * from dba_apply_spill_txn;
select * from dba_apply_parameters;
======================
select * from sys.streams$_process_params;
阅读(3008) | 评论(0) | 转发(0) |
