Re: Performance problem with Shareplex and Oracle
With 2 CPUs, a Run-Queue of 1.27 isn't high. As SharePlex seems to be the only process taking CPU, it is taking 100% of 1 CPU. If it is one process only, then the CPU speed __could__ [and I'm not saying IS] the constraint. Adding CPUs wouldn't help. However, upgrading to a faster CPU would help. My comments are just a generalisation. Hemant At 07:29 AM 23-10-03 -0800, you wrote: Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 00 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance problem with Shareplex and Oracle
How big is the box that is the source for this machine? Have you tried running sp_ctrl and doing a shutdown and startup? Allan -Original Message- Sent: Thursday, October 23, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 0 0 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance problem with Shareplex and Oracle
Allan, I don't know about the source machine. I receive around 350Megs of data every day. I'm using sp_ctrl to stop and restart my Post process and monitor the queue. I'm pretty sure that the bottleneck come from Shareplex. Oracle is waiting for Shareplex, we have server's resources available (CPU is 50% idle). How can we speed up Shareplex? Luc -Original Message- Sent: October 23, 2003 12:56 PM To: Multiple recipients of list ORACLE-L How big is the box that is the source for this machine? Have you tried running sp_ctrl and doing a shutdown and startup? Allan -Original Message- Sent: Thursday, October 23, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 0 0 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). application/ms-tnef
RE: Performance problem with Shareplex and Oracle
Dan, The process taking 50% is an Oracle process and it is connected on Shareplex Oracle user. I have two different error messages: 1- System call error: sp_cop(dsm) Temporary error (h_errno = 2) gethostbyname (can't add entry for ora4) I got this error every 10 minutes, but I didn't find something about this error. 2- Operation on 'table_name' is took longer then expected. Make sure the proper index si being used If I check my Explain Plan, the Explain Plan says that he taking the proper index. How can he tell me that it took longer that expected? It's true that on some replicated tables, I have lot of indexs. But my v$session_event should not tell me the waits on indexs (db file scattered read)? Luc -Original Message- Sent: October 23, 2003 1:16 PM To: Demanche, Luc Luc, Which process is taking 50% of the CPU? Do you see any messages from SharePlex? Are there indexes on the target tables or maybe a few that may be slowing down replication (you'll see a message in the event_log on any table that posting is taking longer than expected). Has SharePlex been running for a while and this has just started happening? Just some things to start looking at. Dan - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 8:29 AM Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 00 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance problem with Shareplex and Oracle
Shareplex is fast here. We replicate a 6 CPU db to a 4 CPU machine without excessive loads or problems. We run an average of 29 messages with about 1 GB in the queues. Our data is 0 minutes old. Outside of contacting Quest support I'm sure of how much help I can be. When I have seen SP claim as much CPU as you have stated, I have had to do a shutdown, which kills sp_cop and start it again. This has fixed the high cpu consumption. SP for us is usually around 18% of a single CPU. We are processing about 240MB per day of redo. Sorry not to be of more help. Allan -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Allan, I don't know about the source machine. I receive around 350Megs of data every day. I'm using sp_ctrl to stop and restart my Post process and monitor the queue. I'm pretty sure that the bottleneck come from Shareplex. Oracle is waiting for Shareplex, we have server's resources available (CPU is 50% idle). How can we speed up Shareplex? Luc -Original Message- Sent: October 23, 2003 12:56 PM To: Multiple recipients of list ORACLE-L How big is the box that is the source for this machine? Have you tried running sp_ctrl and doing a shutdown and startup? Allan -Original Message- Sent: Thursday, October 23, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 0 0 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message
Re: Performance problem with Shareplex and Oracle
Who's using other 50%? Is SP active or is waiting? On 10/23/2003 02:19:32 PM, [EMAIL PROTECTED] wrote: Allan, I don't know about the source machine. I receive around 350Megs of data every day. I'm using sp_ctrl to stop and restart my Post process and monitor the queue. I'm pretty sure that the bottleneck come from Shareplex. Oracle is waiting for Shareplex, we have server's resources available (CPU is 50% idle). How can we speed up Shareplex? Luc -Original Message- Sent: October 23, 2003 12:56 PM To: Multiple recipients of list ORACLE-L How big is the box that is the source for this machine? Have you tried running sp_ctrl and doing a shutdown and startup? Allan -Original Message- Sent: Thursday, October 23, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 0 0 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services--
RE: Performance problem with Shareplex and Oracle
Now we don't use Shareplex, but I do know of others who do this is not the first time I hear of performance problems, but I may be able to shed some light on the problem. Since Shareplex reads the redo logs, if one statement on the source database affects more than one row (lets say 10 for arguments sake), then shareplex turns it into 10 distinct statements for the replicated database. Now imagine that your statement affected one row in one table, but due to triggers and constraints affected 1000's of other rown in other tables. DAMN, that could make for a REAL mess on the target system. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, October 23, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Shareplex is fast here. We replicate a 6 CPU db to a 4 CPU machine without excessive loads or problems. We run an average of 29 messages with about 1 GB in the queues. Our data is 0 minutes old. Outside of contacting Quest support I'm sure of how much help I can be. When I have seen SP claim as much CPU as you have stated, I have had to do a shutdown, which kills sp_cop and start it again. This has fixed the high cpu consumption. SP for us is usually around 18% of a single CPU. We are processing about 240MB per day of redo. Sorry not to be of more help. Allan -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Allan, I don't know about the source machine. I receive around 350Megs of data every day. I'm using sp_ctrl to stop and restart my Post process and monitor the queue. I'm pretty sure that the bottleneck come from Shareplex. Oracle is waiting for Shareplex, we have server's resources available (CPU is 50% idle). How can we speed up Shareplex? Luc -Original Message- Sent: October 23, 2003 12:56 PM To: Multiple recipients of list ORACLE-L How big is the box that is the source for this machine? Have you tried running sp_ctrl and doing a shutdown and startup? Allan -Original Message- Sent: Thursday, October 23, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 0 0 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nelson, Allan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Performance problem with Shareplex and Oracle
Your DNS is toasty. Gethostbyname is a UNIX system call that takes your ip address and turns it into a name. This could be a big part of your delay. Networked apps do not take kindly to being unable to both forward and reverse lookups. H_errno = 2 is not found. Allan -Original Message- Sent: Thursday, October 23, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Dan, The process taking 50% is an Oracle process and it is connected on Shareplex Oracle user. I have two different error messages: 1- System call error: sp_cop(dsm) Temporary error (h_errno = 2) gethostbyname (can't add entry for ora4) I got this error every 10 minutes, but I didn't find something about this error. 2- Operation on 'table_name' is took longer then expected. Make sure the proper index si being used If I check my Explain Plan, the Explain Plan says that he taking the proper index. How can he tell me that it took longer that expected? It's true that on some replicated tables, I have lot of indexs. But my v$session_event should not tell me the waits on indexs (db file scattered read)? Luc -Original Message- Sent: October 23, 2003 1:16 PM To: Demanche, Luc Luc, Which process is taking 50% of the CPU? Do you see any messages from SharePlex? Are there indexes on the target tables or maybe a few that may be slowing down replication (you'll see a message in the event_log on any table that posting is taking longer than expected). Has SharePlex been running for a while and this has just started happening? Just some things to start looking at. Dan - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 8:29 AM Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 0 0 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ:
RE: Performance problem with Shareplex and Oracle
Since the results of triggers firing in the source will appear in the log files, then in general you do not want the same triggers firing in the target. Similarly since data integrity is usually enforced in the source db you can typically disable it in the target. I suppose something on this order could be the problem. Allan -Original Message- Sent: Thursday, October 23, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Now we don't use Shareplex, but I do know of others who do this is not the first time I hear of performance problems, but I may be able to shed some light on the problem. Since Shareplex reads the redo logs, if one statement on the source database affects more than one row (lets say 10 for arguments sake), then shareplex turns it into 10 distinct statements for the replicated database. Now imagine that your statement affected one row in one table, but due to triggers and constraints affected 1000's of other rown in other tables. DAMN, that could make for a REAL mess on the target system. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, October 23, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Shareplex is fast here. We replicate a 6 CPU db to a 4 CPU machine without excessive loads or problems. We run an average of 29 messages with about 1 GB in the queues. Our data is 0 minutes old. Outside of contacting Quest support I'm sure of how much help I can be. When I have seen SP claim as much CPU as you have stated, I have had to do a shutdown, which kills sp_cop and start it again. This has fixed the high cpu consumption. SP for us is usually around 18% of a single CPU. We are processing about 240MB per day of redo. Sorry not to be of more help. Allan -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Allan, I don't know about the source machine. I receive around 350Megs of data every day. I'm using sp_ctrl to stop and restart my Post process and monitor the queue. I'm pretty sure that the bottleneck come from Shareplex. Oracle is waiting for Shareplex, we have server's resources available (CPU is 50% idle). How can we speed up Shareplex? Luc -Original Message- Sent: October 23, 2003 12:56 PM To: Multiple recipients of list ORACLE-L How big is the box that is the source for this machine? Have you tried running sp_ctrl and doing a shutdown and startup? Allan -Original Message- Sent: Thursday, October 23, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 0 0 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this
RE: Performance problem with Shareplex and Oracle
I probably found my problem. My replicated tables have a lot of indexs. I removed all of them, and step by step I will add indexs who will help my replication. Tx Luc -Original Message- Sent: October 23, 2003 3:04 PM To: Multiple recipients of list ORACLE-L Since the results of triggers firing in the source will appear in the log files, then in general you do not want the same triggers firing in the target. Similarly since data integrity is usually enforced in the source db you can typically disable it in the target. I suppose something on this order could be the problem. Allan -Original Message- Sent: Thursday, October 23, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Now we don't use Shareplex, but I do know of others who do this is not the first time I hear of performance problems, but I may be able to shed some light on the problem. Since Shareplex reads the redo logs, if one statement on the source database affects more than one row (lets say 10 for arguments sake), then shareplex turns it into 10 distinct statements for the replicated database. Now imagine that your statement affected one row in one table, but due to triggers and constraints affected 1000's of other rown in other tables. DAMN, that could make for a REAL mess on the target system. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, October 23, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Shareplex is fast here. We replicate a 6 CPU db to a 4 CPU machine without excessive loads or problems. We run an average of 29 messages with about 1 GB in the queues. Our data is 0 minutes old. Outside of contacting Quest support I'm sure of how much help I can be. When I have seen SP claim as much CPU as you have stated, I have had to do a shutdown, which kills sp_cop and start it again. This has fixed the high cpu consumption. SP for us is usually around 18% of a single CPU. We are processing about 240MB per day of redo. Sorry not to be of more help. Allan -Original Message- [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Allan, I don't know about the source machine. I receive around 350Megs of data every day. I'm using sp_ctrl to stop and restart my Post process and monitor the queue. I'm pretty sure that the bottleneck come from Shareplex. Oracle is waiting for Shareplex, we have server's resources available (CPU is 50% idle). How can we speed up Shareplex? Luc -Original Message- Sent: October 23, 2003 12:56 PM To: Multiple recipients of list ORACLE-L How big is the box that is the source for this machine? Have you tried running sp_ctrl and doing a shutdown and startup? Allan -Original Message- Sent: Thursday, October 23, 2003 10:30 AM To: Multiple recipients of list ORACLE-L Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 0 0 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ This email
RE: Performance problem with Shareplex and Oracle
Do you have primary keys etc and hint file? Also do you have constraints disabled. Some "Nelson, Allan" [EMAIL PROTECTED] wrote: Since the results of triggers firing in the source will appear in thelog files, then in general you do not want the same triggers firing inthe target. Similarly since data integrity is usually enforced in thesource db you can typically disable it in the target. I supposesomething on this order could be the problem.Allan-Original Message-Sent: Thursday, October 23, 2003 2:29 PMTo: Multiple recipients of list ORACLE-LNow we don't use Shareplex, but I do know of others who do this is notthe first time I hear of performance problems, but I may be able to shedsome light on the problem. Since Shareplex reads the redo logs, if onestatement on the source database affects more than one row (lets say 10for arguments sake), then shareplex turns it into 10 distinct statementsfor the replicated database.! Now imagine that your statement affectedone row in one table, but due to triggers and constraints affected1000's of other rown in other tables. DAMN, that could make for a REALmess on the target system.Dick GouletSenior Oracle DBAOracle Certified 8i DBA-Original Message-Sent: Thursday, October 23, 2003 2:44 PMTo: Multiple recipients of list ORACLE-LShareplex is fast here. We replicate a 6 CPU db to a 4 CPU machinewithout excessive loads or problems. We run an average of 29 messageswith about 1 GB in the queues. Our data is 0 minutes old.Outside of contacting Quest support I'm sure of how much help I can be.When I have seen SP claim as much CPU as you have stated, I have had todo a shutdown, which kills sp_cop and start it again. This has fixedthe high cpu consumption. SP for us is usually around 18% of a singleCPU. We are processing about 240MB per day of redo.Sorry not to be! of more help.Allan-Original Message-[EMAIL PROTECTED]Sent: Thursday, October 23, 2003 1:20 PMTo: Multiple recipients of list ORACLE-LAllan,I don't know about the source machine.I receive around 350Megs of data every day.I'm using sp_ctrl to stop and restart my Post process and monitor thequeue.I'm pretty sure that the bottleneck come from Shareplex. Oracle iswaiting for Shareplex, we have server's resources available (CPU is 50%idle). How can we speed up Shareplex?Luc-Original Message-Sent: October 23, 2003 12:56 PMTo: Multiple recipients of list ORACLE-LHow big is the box that is the source for this machine? Have you triedrunning sp_ctrl and doing a shutdown and startup?Allan-Original Message-Sent: Thursday, October 23, 2003 10:30 AMTo: Multiple recipients of list ORACLE-LHi gurus,Oracle 8.1.7.3 on Sun SolarisOne of our databases has been updated by Shareplex, and we have a hugeperformance problemShareplex is the only process running on this database.Here is the output of v$session_eventSID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITEDAVERAGE_WAIT-- --- --- -- ---17 latch free 83 69 1,01204819317 log file sync 15 0 9,617 db file sequential read 52 0 6,11538461517 file open 1 0 0017 SQL*Net message to client 74791 0 27,00036100617 SQL*Net message from client 74791 0 5235,069995053I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger andbigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU450Mhz, 2048Megs of RAM According to "top", this process is taking ! 50%of the CPU. and my "load average" is 1.26, 1.27, 1.24My average load exceed 1, can I conclude that the CPU is the bottleneck?What are my possibilities?TIALuc-Luc DemancheAstraZeneca RD MontrealOracle Database Administrator514.832.3200 x2356-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in themessage BODY, include a line containing: UNSUB ORACLE-L (or the name ofmailing list you want to be removed from). You may also send the HELPcommand for other information (like subscribing).__This email is intended solely for the person or entity to which it isaddressed and may contain confidential and/or privileged information.Copying, forwarding or distributing this message by persons or entitiesother than the addressee is prohibited. If you have received this emailin error, please contact the sender immediately and delete the materialfrom any computer. This email may have been monitored for policycompliance. [021216]-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Nelson, AllanINET: [EMAIL
RE: Performance problem with Shareplex and Oracle
One of my clients has been talking to me about similar issues. What kind of system are you replicating? How much redo logging are you generating per day? Does shareplex start to fall behind during the processing peaks? _ David Kurtz Go-Faster Consultancy Ltd. tel: +44 (0)7771 760660 fax: +44 (0)7092 348865 mailto:[EMAIL PROTECTED] web: www.go-faster.co.uk PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba -Original Message- [EMAIL PROTECTED] Sent: 23 October 2003 19:40 To: Multiple recipients of list ORACLE-L Dan, The process taking 50% is an Oracle process and it is connected on Shareplex Oracle user. I have two different error messages: 1- System call error: sp_cop(dsm) Temporary error (h_errno = 2) gethostbyname (can't add entry for ora4) I got this error every 10 minutes, but I didn't find something about this error. 2- Operation on 'table_name' is took longer then expected. Make sure the proper index si being used If I check my Explain Plan, the Explain Plan says that he taking the proper index. How can he tell me that it took longer that expected? It's true that on some replicated tables, I have lot of indexs. But my v$session_event should not tell me the waits on indexs (db file scattered read)? Luc -Original Message- Sent: October 23, 2003 1:16 PM To: Demanche, Luc Luc, Which process is taking 50% of the CPU? Do you see any messages from SharePlex? Are there indexes on the target tables or maybe a few that may be slowing down replication (you'll see a message in the event_log on any table that posting is taking longer than expected). Has SharePlex been running for a while and this has just started happening? Just some things to start looking at. Dan - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 8:29 AM Hi gurus, Oracle 8.1.7.3 on Sun Solaris One of our databases has been updated by Shareplex, and we have a huge performance problem Shareplex is the only process running on this database. Here is the output of v$session_event SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT -- ------ -- --- 17 latch free 83 69 1 ,012048193 17 log file sync15 0 9 ,6 17 db file sequential read 52 0 6 ,115384615 17 file open 1 0 00 17 SQL*Net message to client 74791 0 27 ,000361006 17 SQL*Net message from client 74791 05235 ,069995053 I really don't see any Oracle performance problems. My problem is the backlog (queue) of Shareplex is getting bigger and bigger. Where is the bottleneck? The only thing I can see is the server. The server is a Ultra-80, 2 CPU 450Mhz, 2048Megs of RAM According to top, this process is taking 50% of the CPU. and my load average is 1.26, 1.27, 1.24 My average load exceed 1, can I conclude that the CPU is the bottleneck? What are my possibilities? TIA Luc - Luc Demanche AstraZeneca RD Montreal Oracle Database Administrator 514.832.3200 x2356 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Kurtz INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message
