Home » Server Options » Streams & AQ » Slow Perfomance In Oracle Streams (Linux, Oracle 10g)
Slow Perfomance In Oracle Streams [message #401652] Tue, 05 May 2009 23:45 Go to next message
amitchhabra1981
Messages: 12
Registered: December 2008
Location: NOIDA
Junior Member
Hi,

When we delete records from source schema it takes long time to reflect on target schema and any other activity of streaming on other table gets delayed due to this.Example: to delete 50,000 records it takes 15 minutes on target schema.we have both the databases on the same server. How can we reduce this delay?

Thanks
Amit Chhabra Confused
Re: Slow Perfomance In Oracle Streams [message #401664 is a reply to message #401652] Wed, 06 May 2009 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Increase streams pool size, decrease streams time processes wake-up but Streams is slow anyway.

Regards
Michel
Re: Slow Perfomance In Oracle Streams [message #401667 is a reply to message #401664] Wed, 06 May 2009 01:28 Go to previous messageGo to next message
amitchhabra1981
Messages: 12
Registered: December 2008
Location: NOIDA
Junior Member
Hi Michel,

our database have SGA of 1.2Gb out of this 300MB is alocated for streams pool size and also parameter _job_queue_interval is set to 1. still we are facing delay in response on target database

Regards,
Amit Chhabra
Re: Slow Perfomance In Oracle Streams [message #401677 is a reply to message #401652] Wed, 06 May 2009 02:15 Go to previous messageGo to next message
cmdeepak
Messages: 1
Registered: September 2008
Location: Mumbai India
Junior Member

hi,

Check job_queue_processes.

Regards,
Dc
Re: Slow Perfomance In Oracle Streams [message #401684 is a reply to message #401677] Wed, 06 May 2009 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also check aq_tm_processes and decrease propagation schedule.

Regards
Michel
Re: Slow Perfomance In Oracle Streams [message #401708 is a reply to message #401684] Wed, 06 May 2009 04:01 Go to previous messageGo to next message
amitchhabra1981
Messages: 12
Registered: December 2008
Location: NOIDA
Junior Member
Hi Michel,

aq_tm_processes is set to 2 in current setup and please let me know how to reduce propogation schedule?

Regards,
Amit
Re: Slow Perfomance In Oracle Streams [message #401724 is a reply to message #401708] Wed, 06 May 2009 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE

Regards
Michel
Re: Slow Perfomance In Oracle Streams [message #403541 is a reply to message #401652] Sat, 16 May 2009 13:30 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
might be due to delay of LOGMINER in scanning redos ?
Re: Slow Perfomance In Oracle Streams [message #403956 is a reply to message #403541] Tue, 19 May 2009 17:19 Go to previous message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Your stream pool size is very small.
As suggested by Mr Michel,Please increase the size of streams_pool_size or increase the
size of sga_target to higher value and enable ASMM(set stream_pool_size=0 in that case) as your first step.
Regards,
Varun Punj
Previous Topic: Capture Latency
Next Topic: Streams REQUIRED_CHECKPOINT_SCN
Goto Forum:
  


Current Time: Fri Mar 29 00:25:57 CDT 2024