Re: Performance Issue w/ Blob Data
Hi! Maybe it's a delayed commit cleanout issue, due massive deletes, so during your first select most of your buffers involved in delete have to be cleaned out (thus becoming dirty and generating extra redo). Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, October 31, 2003 4:49 PM > Good Morning, > > I have a database (8.1.7.3 on Sun Solaris 8) that has > a mixture of tables and indexes in the same > tablespace. Poor initial setup, but that is starting > to be addressed. One of the tables has a BLOB data > type and the LOBSEGMENT is stored in the same > tablespace as the tables and indexes. The size of the > tablespace is about 45G. The table with the BLOB had > about 3 million rows in it before we started to purge > old data out. After we were done purging I wanted to > see how many rows were left. I did what I though was a > harmless SELECT COUNT(*) on the table, and I had to > kill it after 3 hours without anything getting > returned. Before the purge, it would return in 10-15 > minutes. In addition, our client base slowed to a > crawl. But not because my query was running away. > Memory, cpu and i/o on the server were very low. It > was almost like only a few sessions at a time were > getting to the server. My query maxed out at about 3% > of the cpu. > > Using performance monitor didn't show any massive > usage from the database side either. It was almost > like the query was just chugging away under the radar, > but preventing others from doing barely any work. As > soon as I killed this query, the system went back to > normal. > > We have been experiencing intermittent slowness for > awhile during normal processing, but have never been > able to find the silver bullet reason that was > dragging everyone down. I am wondering if I have > stumbled onto something here. It could be that > whatever slowed my query is having the same affect > anytime a client is doing anything with the table with > the BLOB data type. Could anyone tell me why this > SELECT could have taken so long and had such an effect > on the clients? > > Thanks in advance for any assistance > > Larry > > > > = > > > __ > Do you Yahoo!? > Exclusive Video Premiere - Britney Spears > http://launch.yahoo.com/promos/britneyspears/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Larry Hahn > 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: Tanel Poder 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 Issue w/ Blob Data
Robin, Thanks for the reply. I figured it would. Thats what we are working at now. It's unfortunate it was set up this way to begin with, but I am starting to see the light at the end of the tunnel. Larry --- Robin Li <[EMAIL PROTECTED]> wrote: > I had the performance issue with CLOB in one of my > databases. After I did a > re-org, and separated the tables,indexes and CLOB > into different > tablespaces, the performance got tremendous > improvement. > > Robin > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Friday, October 31, 2003 9:59 AM > > > > After deleting lot of old data, an analyze of the > table is in order though > .. > > > > Raj > > > -- > -- > > Rajendra dot Jamadagni at nospamespn dot com > > All Views expressed in this email are strictly > personal. > > QOTD: Any clod can have facts, having an opinion > is an art ! > > > > > > -Original Message- > > Sent: Friday, October 31, 2003 9:50 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Good Morning, > > > > I have a database (8.1.7.3 on Sun Solaris 8) that > has > > a mixture of tables and indexes in the same > > tablespace. Poor initial setup, but that is > starting > > to be addressed. One of the tables has a BLOB data > > type and the LOBSEGMENT is stored in the same > > tablespace as the tables and indexes. The size of > the > > tablespace is about 45G. The table with the BLOB > had > > about 3 million rows in it before we started to > purge > > old data out. After we were done purging I wanted > to > > see how many rows were left. I did what I though > was a > > harmless SELECT COUNT(*) on the table, and I had > to > > kill it after 3 hours without anything getting > > returned. Before the purge, it would return in > 10-15 > > minutes. In addition, our client base slowed to a > > crawl. But not because my query was running away. > > Memory, cpu and i/o on the server were very low. > It > > was almost like only a few sessions at a time were > > getting to the server. My query maxed out at about > 3% > > of the cpu. > > > > Using performance monitor didn't show any massive > > usage from the database side either. It was almost > > like the query was just chugging away under the > radar, > > but preventing others from doing barely any work. > As > > soon as I killed this query, the system went back > to > > normal. > > > > We have been experiencing intermittent slowness > for > > awhile during normal processing, but have never > been > > able to find the silver bullet reason that was > > dragging everyone down. I am wondering if I have > > stumbled onto something here. It could be that > > whatever slowed my query is having the same affect > > anytime a client is doing anything with the table > with > > the BLOB data type. Could anyone tell me why this > > SELECT could have taken so long and had such an > effect > > on the clients? > > > > Thanks in advance for any assistance > > > > Larry > > > > > > > > = > > > > > > __ > > Do you Yahoo!? > > Exclusive Video Premiere - Britney Spears > > http://launch.yahoo.com/promos/britneyspears/ > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Larry Hahn > > 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 e-mail message is confidential, intended only > for the named > recipient(s) above and may contain information that > is privileged, attorney > work product or exempt from disclosure under > applica
RE: Performance Issue w/ Blob Data
Raj, I agree. I could see where that could affect the overall performance. The analyze wouldnt have an effect on a SELECT COUNT(*) though would it??? That is the piece that really has me stumped at the moment. --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > After deleting lot of old data, an analyze of the > table is in order though .. > > Raj > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly > personal. > QOTD: Any clod can have facts, having an opinion is > an art ! > > > -Original Message- > Sent: Friday, October 31, 2003 9:50 AM > To: Multiple recipients of list ORACLE-L > > > Good Morning, > > I have a database (8.1.7.3 on Sun Solaris 8) that > has > a mixture of tables and indexes in the same > tablespace. Poor initial setup, but that is starting > to be addressed. One of the tables has a BLOB data > type and the LOBSEGMENT is stored in the same > tablespace as the tables and indexes. The size of > the > tablespace is about 45G. The table with the BLOB had > about 3 million rows in it before we started to > purge > old data out. After we were done purging I wanted to > see how many rows were left. I did what I though was > a > harmless SELECT COUNT(*) on the table, and I had to > kill it after 3 hours without anything getting > returned. Before the purge, it would return in 10-15 > minutes. In addition, our client base slowed to a > crawl. But not because my query was running away. > Memory, cpu and i/o on the server were very low. It > was almost like only a few sessions at a time were > getting to the server. My query maxed out at about > 3% > of the cpu. > > Using performance monitor didn't show any massive > usage from the database side either. It was almost > like the query was just chugging away under the > radar, > but preventing others from doing barely any work. As > soon as I killed this query, the system went back to > normal. > > We have been experiencing intermittent slowness for > awhile during normal processing, but have never been > able to find the silver bullet reason that was > dragging everyone down. I am wondering if I have > stumbled onto something here. It could be that > whatever slowed my query is having the same affect > anytime a client is doing anything with the table > with > the BLOB data type. Could anyone tell me why this > SELECT could have taken so long and had such an > effect > on the clients? > > Thanks in advance for any assistance > > Larry > > > > = > > > __ > Do you Yahoo!? > Exclusive Video Premiere - Britney Spears > http://launch.yahoo.com/promos/britneyspears/ > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Larry Hahn > 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 e-mail message is confidential, intended only > for the named recipient(s) above and may contain > information that is privileged, attorney work > product or exempt from disclosure under applicable > law. If you have received this message in error, or > are not the named recipient(s), please immediately > notify corporate MIS at (860) 766-2000 and delete > this e-mail message from your computer, Thank you. > **5 > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Jamadagni, Rajendra > 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). = __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Hahn INET: [EM
Re: Performance Issue w/ Blob Data
I had the performance issue with CLOB in one of my databases. After I did a re-org, and separated the tables,indexes and CLOB into different tablespaces, the performance got tremendous improvement. Robin - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, October 31, 2003 9:59 AM > After deleting lot of old data, an analyze of the table is in order though .. > > Raj > -- -- > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > > > -Original Message- > Sent: Friday, October 31, 2003 9:50 AM > To: Multiple recipients of list ORACLE-L > > > Good Morning, > > I have a database (8.1.7.3 on Sun Solaris 8) that has > a mixture of tables and indexes in the same > tablespace. Poor initial setup, but that is starting > to be addressed. One of the tables has a BLOB data > type and the LOBSEGMENT is stored in the same > tablespace as the tables and indexes. The size of the > tablespace is about 45G. The table with the BLOB had > about 3 million rows in it before we started to purge > old data out. After we were done purging I wanted to > see how many rows were left. I did what I though was a > harmless SELECT COUNT(*) on the table, and I had to > kill it after 3 hours without anything getting > returned. Before the purge, it would return in 10-15 > minutes. In addition, our client base slowed to a > crawl. But not because my query was running away. > Memory, cpu and i/o on the server were very low. It > was almost like only a few sessions at a time were > getting to the server. My query maxed out at about 3% > of the cpu. > > Using performance monitor didn't show any massive > usage from the database side either. It was almost > like the query was just chugging away under the radar, > but preventing others from doing barely any work. As > soon as I killed this query, the system went back to > normal. > > We have been experiencing intermittent slowness for > awhile during normal processing, but have never been > able to find the silver bullet reason that was > dragging everyone down. I am wondering if I have > stumbled onto something here. It could be that > whatever slowed my query is having the same affect > anytime a client is doing anything with the table with > the BLOB data type. Could anyone tell me why this > SELECT could have taken so long and had such an effect > on the clients? > > Thanks in advance for any assistance > > Larry > > > > = > > > __ > Do you Yahoo!? > Exclusive Video Premiere - Britney Spears > http://launch.yahoo.com/promos/britneyspears/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Larry Hahn > 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 e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. > **5 > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jamadagni, Rajendra > 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 f
RE: Performance Issue w/ Blob Data
After deleting lot of old data, an analyze of the table is in order though .. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Friday, October 31, 2003 9:50 AM To: Multiple recipients of list ORACLE-L Good Morning, I have a database (8.1.7.3 on Sun Solaris 8) that has a mixture of tables and indexes in the same tablespace. Poor initial setup, but that is starting to be addressed. One of the tables has a BLOB data type and the LOBSEGMENT is stored in the same tablespace as the tables and indexes. The size of the tablespace is about 45G. The table with the BLOB had about 3 million rows in it before we started to purge old data out. After we were done purging I wanted to see how many rows were left. I did what I though was a harmless SELECT COUNT(*) on the table, and I had to kill it after 3 hours without anything getting returned. Before the purge, it would return in 10-15 minutes. In addition, our client base slowed to a crawl. But not because my query was running away. Memory, cpu and i/o on the server were very low. It was almost like only a few sessions at a time were getting to the server. My query maxed out at about 3% of the cpu. Using performance monitor didn't show any massive usage from the database side either. It was almost like the query was just chugging away under the radar, but preventing others from doing barely any work. As soon as I killed this query, the system went back to normal. We have been experiencing intermittent slowness for awhile during normal processing, but have never been able to find the silver bullet reason that was dragging everyone down. I am wondering if I have stumbled onto something here. It could be that whatever slowed my query is having the same affect anytime a client is doing anything with the table with the BLOB data type. Could anyone tell me why this SELECT could have taken so long and had such an effect on the clients? Thanks in advance for any assistance Larry = __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Hahn 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 e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
Performance Issue w/ Blob Data
Good Morning, I have a database (8.1.7.3 on Sun Solaris 8) that has a mixture of tables and indexes in the same tablespace. Poor initial setup, but that is starting to be addressed. One of the tables has a BLOB data type and the LOBSEGMENT is stored in the same tablespace as the tables and indexes. The size of the tablespace is about 45G. The table with the BLOB had about 3 million rows in it before we started to purge old data out. After we were done purging I wanted to see how many rows were left. I did what I though was a harmless SELECT COUNT(*) on the table, and I had to kill it after 3 hours without anything getting returned. Before the purge, it would return in 10-15 minutes. In addition, our client base slowed to a crawl. But not because my query was running away. Memory, cpu and i/o on the server were very low. It was almost like only a few sessions at a time were getting to the server. My query maxed out at about 3% of the cpu. Using performance monitor didnt show any massive usage from the database side either. It was almost like the query was just chugging away under the radar, but preventing others from doing barely any work. As soon as I killed this query, the system went back to normal. We have been experiencing intermittent slowness for awhile during normal processing, but have never been able to find the silver bullet reason that was dragging everyone down. I am wondering if I have stumbled onto something here. It could be that whatever slowed my query is having the same affect anytime a client is doing anything with the table with the BLOB data type. Could anyone tell me why this SELECT could have taken so long and had such an effect on the clients? Thanks in advance for any assistance Larry = __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Hahn 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: Re: performance issue on select count(*)
"Exactly" my point! ;-) You cannot use it for anymore than that, and neither should you ignore it completely. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, October 29, 2003 6:39 AM > yes bchr is only useful at extremes, but its based on interpretation. if you have a very high BCHR, you probably have alot of very bad sql. > > if you have a very low one AND are in a type of application where you should(namely OLTP) you may want to consider increasing your buffer cache. > > mladen is right. there is no 'exact' very high and very low. you have to interpret it. > > that is about it. Anyone who uses it for anymore than that is wrong. > > > > From: Mladen Gogala <[EMAIL PROTECTED]> > > Date: 2003/10/28 Tue PM 12:09:34 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: Re: performance issue on select count(*) > > > > So, what exactly is indicated by a high or low hit rate? What, exactly, is "high" > > and what do you consider "low"? > > What "HR" are you talking about? > > This would be the infamous BCHR: > > > > select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) > > + sum(decode(name,'db block gets', value,0)) > > - sum(decode(name,'physical reads', value,0))) > > / ( sum(decode(name, 'consistent gets',value,0)) > > + sum(decode(name,'db block gets', value,0)) ) * 100 > > from v$sysstat > > > > What exactly should the number returned by this query tell me? > > > > > > On 10/28/2003 10:59:25 AM, Binley Lim wrote: > > > > > > The symptom suggests caching is a big factor here - most likely > > > block-buffers. > > > > > > Contrary to ?current? popular beliefs, BCHR is still a very relevant > > > performance indicator - either being very high, or being too low - both of > > > which gives a good indication of something that needs to be looked at. > > > > > > > > > > I would be interested to know if there is a way to speed up the initial > > > > execution or how to diagnose what the delay was. It does not seems right > > > > that there is such a big difference in elapsed time between the initial > > > and > > > > subsequent execution. > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Binley Lim > > > 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-- 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: [
Re: performance issue on select count(*)
An unusually high BCHR could be an indicator that your database is running Connor McDonald's choose_a_hit_ratio procedure. http://www.oracledba.co.uk/tips/choose.htm Jared Mladen Gogala <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/28/2003 09:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: performance issue on select count(*) So, what exactly is indicated by a high or low hit rate? What, exactly, is "high" and what do you consider "low"? What "HR" are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: > > The symptom suggests caching is a big factor here - most likely > block-buffers. > > Contrary to ?current? popular beliefs, BCHR is still a very relevant > performance indicator - either being very high, or being too low - both of > which gives a good indication of something that needs to be looked at. > > > > I would be interested to know if there is a way to speed up the initial > > execution or how to diagnose what the delay was. It does not seems right > > that there is such a big difference in elapsed time between the initial > and > > subsequent execution. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Binley Lim > INET: [EMAIL PROTECTED] >
Re: Re: performance issue on select count(*)
yes bchr is only useful at extremes, but its based on interpretation. if you have a very high BCHR, you probably have alot of very bad sql. if you have a very low one AND are in a type of application where you should(namely OLTP) you may want to consider increasing your buffer cache. mladen is right. there is no 'exact' very high and very low. you have to interpret it. that is about it. Anyone who uses it for anymore than that is wrong. > > From: Mladen Gogala <[EMAIL PROTECTED]> > Date: 2003/10/28 Tue PM 12:09:34 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: performance issue on select count(*) > > So, what exactly is indicated by a high or low hit rate? What, exactly, is "high" > and what do you consider "low"? > What "HR" are you talking about? > This would be the infamous BCHR: > > select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) > + sum(decode(name,'db block gets', value,0)) > - sum(decode(name,'physical reads', value,0))) > / ( sum(decode(name, 'consistent gets',value,0)) > + sum(decode(name,'db block gets', value,0)) ) * 100 > from v$sysstat > > What exactly should the number returned by this query tell me? > > > On 10/28/2003 10:59:25 AM, Binley Lim wrote: > > > > The symptom suggests caching is a big factor here - most likely > > block-buffers. > > > > Contrary to ?current? popular beliefs, BCHR is still a very relevant > > performance indicator - either being very high, or being too low - both of > > which gives a good indication of something that needs to be looked at. > > > > > > > I would be interested to know if there is a way to speed up the initial > > > execution or how to diagnose what the delay was. It does not seems right > > > that there is such a big difference in elapsed time between the initial > > and > > > subsequent execution. > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Binley Lim > > 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-- 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: <[EMAIL PROTECTED] 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 issue on select count(*)
So, what exactly is indicated by a high or low hit rate? What, exactly, is "high" and what do you consider "low"? What "HR" are you talking about? This would be the infamous BCHR: select 'bc_hit_ratio' ratio,( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) - sum(decode(name,'physical reads', value,0))) / ( sum(decode(name, 'consistent gets',value,0)) + sum(decode(name,'db block gets', value,0)) ) * 100 from v$sysstat What exactly should the number returned by this query tell me? On 10/28/2003 10:59:25 AM, Binley Lim wrote: > > The symptom suggests caching is a big factor here - most likely > block-buffers. > > Contrary to ?current? popular beliefs, BCHR is still a very relevant > performance indicator - either being very high, or being too low - both of > which gives a good indication of something that needs to be looked at. > > > > I would be interested to know if there is a way to speed up the initial > > execution or how to diagnose what the delay was. It does not seems right > > that there is such a big difference in elapsed time between the initial > and > > subsequent execution. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Binley Lim > 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-- 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 issue on select count(*)
The symptom suggests caching is a big factor here - most likely block-buffers. Contrary to ?current? popular beliefs, BCHR is still a very relevant performance indicator - either being very high, or being too low - both of which gives a good indication of something that needs to be looked at. > I would be interested to know if there is a way to speed up the initial > execution or how to diagnose what the delay was. It does not seems right > that there is such a big difference in elapsed time between the initial and > subsequent execution. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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 issue on select count(*)
Tim, Thanks for your reply. The select count(*) is doing an index range scan on the column tid. No table access in the execution plan. The query you provided returned the following result: NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY LAST_ANAL - --- --- - 2326064.1 161201 1 3 28-OCT-03 I have tried local partition index on tid but the execution time was still around 10secs for the initial execution and <1sec subsequently. The table is partitioned on a date field. I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution. I monitored the wait events during both executions. They were all pretty low. It does not appear to be I/O bound either. tnsping from my PC to the database took about 30msec. Any other suggestions what I could check? Thanks. linda select * from v$session_event where sid=98; Initial run: SID EVENTTOTAL TOTAL TIME AVERAGE MAX WAITS TIMEOUTS WAITED WAIT WAIT --- - -- 98 latch free 115 681 .008695652 1 98 control file sequential read 300 0 0 98 refresh controlfile command 100 0 0 98 buffer busy waits 100 0 0 98 log file sync 101 1 1 98 db file sequential read 1968 0 827 .42022357710 98 file open 502 .4 1 98 SQL*Net message to client 305 00 0 0 98 SQL*Net message from client 3040 31819 104.667763 29911 Subsequent run: - SID EVENT TOTAL TOTAL TIME AVERAGE MAX WAITS TIMEOUTS WAITED WAIT WAIT --- - -- 99 latch free 162 93 3 .018518519 2 99 control file sequential read 3 0 0 0 0 99 refresh controlfile command1 0 0 0 0 99 buffer busy waits 1 00 0 0 99 log file sync 1 00 0 0 99 file open 3 01 .3 1 99 SQL*Net message to client 54 00 0 0 99 SQL*Net message from client53 02893 54.5849057 2698 From: Tim Gorman <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: performance issue on select count(*) Date: Mon, 27 Oct 2003 10:34:59 -0800 Linda, I am guessing that since your table is partitioned on an unspecified date column, that the index on TID is either LOCAL or non-partitioned (i.e. GLOBAL). If it is LOCAL (you would have had to specify the keyword, as it is not the default), then you will be performing indexed RANGE scans on each of the partitions in the index. Naturally, the more partitions there are, the longer this may take, but probably not a great deal longer than if the index was a GLOBAL non-partitioned index. But regardless of the number of RANGE scans and the type of index it is, the main question is whether TID is a good index to use in the first place. This is a matter of data, purely the nature of the data. You can diagnose this better using results from the following query: SELECT NUM_ROWS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, LAST_ANALYZED FROMDBA_INDEXES WHERE INDEX_NAME = ''; Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate the cost of an index RANGE scan (assuming that column-level statistics or "histograms" have not been gathered). If the values of these two columns are high, then
Re: performance issue on select count(*)
Linda, I am guessing that since your table is partitioned on an unspecified date column, that the index on TID is either LOCAL or non-partitioned (i.e. GLOBAL). If it is LOCAL (you would have had to specify the keyword, as it is not the default), then you will be performing indexed RANGE scans on each of the partitions in the index. Naturally, the more partitions there are, the longer this may take, but probably not a great deal longer than if the index was a GLOBAL non-partitioned index. But regardless of the number of RANGE scans and the type of index it is, the main question is whether TID is a good index to use in the first place. This is a matter of data, purely the nature of the data. You can diagnose this better using results from the following query: SELECT NUM_ROWS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, LAST_ANALYZED FROMDBA_INDEXES WHERE INDEX_NAME = ''; Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate the cost of an index RANGE scan (assuming that column-level statistics or "histograms" have not been gathered). If the values of these two columns are high, then the CBO will be hesitant to use the index, and with good reason. Thus, with the use of the index rejected as an option, you'll of course have a FULL table scan on your hands. There is probably more to it, but this should be a start. Feel free to post the results of the query above to the list, if you wish... Hope this helps... -Tim on 10/27/03 6:24 AM, Linda Wang at [EMAIL PROTECTED] wrote: > Hi, > I have an online application that does a 'select count(*)' on a few tables. > The 'select counts' always runs slow (about 10secs) for the first time and > then fast again (< 1sec) after subsequent accesses. The query runs slow > again when the data is flushed out of the buffer cache. > 10046 trace shows that the query takes a long time whenever there are disk > accesses to fetch the data (about 1000 8K) into db cache. It should not take > that long to fetch 1000 8K blocks into the cache and I/O does not appear to > be the problem. > > Anyone has any idea what the problem may be or how I can speed up my query? > > DB: 8.1.7.4 > query: select count(*) from tickets where tid='value1'; > where tickets has about 2 million records partition on a date field. > and tid is indexed. > > thanks. > > linda > > _ > Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet > Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman 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 issue on select count(*)
Stephane, the execution plan for the statement is an index range scan on tid. It did not access the table. index is not partitioned. I will testpartitioning the index and with the parallel fast full scan. Anyone else has any other suggestions? Thanks. linda From: "Stephane Faroult" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: performance issue on select count(*) Date: Mon, 27 Oct 2003 05:49:24 -0800 Linda, I guess that the key word is 'partition'. This type of query should not require to access the table if (hopefully) tid is indexed. If the index on tid is also partitioned, all index partitions have to be searched. My feeling is that in such a case what should run faster is some parallel fast full scan. Does your execution plan show this type of process or something wildly different ? SF >- --- Original Message --- - >From: "Linda Wang" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 27 Oct 2003 05:24:32 > >Hi, >I have an online application that does a 'select >count(*)' on a few tables. >The 'select counts' always runs slow (about 10secs) >for the first time and >then fast again (< 1sec) after subsequent accesses. >The query runs slow >again when the data is flushed out of the buffer >cache. >10046 trace shows that the query takes a long time >whenever there are disk >accesses to fetch the data (about 1000 8K) into db >cache. It should not take >that long to fetch 1000 8K blocks into the cache >and I/O does not appear to >be the problem. > >Anyone has any idea what the problem may be or how >I can speed up my query? > >DB: 8.1.7.4 >query: select count(*) from tickets where >tid='value1'; >where tickets has about 2 million records partition >on a date field. >and tid is indexed. > >thanks. > >linda > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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). _ See when your friends are online with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang 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 issue on select count(*)
Linda, I guess that the key word is 'partition'. This type of query should not require to access the table if (hopefully) tid is indexed. If the index on tid is also partitioned, all index partitions have to be searched. My feeling is that in such a case what should run faster is some parallel fast full scan. Does your execution plan show this type of process or something wildly different ? SF >- --- Original Message --- - >From: "Linda Wang" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 27 Oct 2003 05:24:32 > >Hi, >I have an online application that does a 'select >count(*)' on a few tables. >The 'select counts' always runs slow (about 10secs) >for the first time and >then fast again (< 1sec) after subsequent accesses. >The query runs slow >again when the data is flushed out of the buffer >cache. >10046 trace shows that the query takes a long time >whenever there are disk >accesses to fetch the data (about 1000 8K) into db >cache. It should not take >that long to fetch 1000 8K blocks into the cache >and I/O does not appear to >be the problem. > >Anyone has any idea what the problem may be or how >I can speed up my query? > >DB: 8.1.7.4 >query: select count(*) from tickets where >tid='value1'; >where tickets has about 2 million records partition >on a date field. >and tid is indexed. > >thanks. > >linda > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
performance issue on select count(*)
Hi, I have an online application that does a 'select count(*)' on a few tables. The 'select counts' always runs slow (about 10secs) for the first time and then fast again (< 1sec) after subsequent accesses. The query runs slow again when the data is flushed out of the buffer cache. 10046 trace shows that the query takes a long time whenever there are disk accesses to fetch the data (about 1000 8K) into db cache. It should not take that long to fetch 1000 8K blocks into the cache and I/O does not appear to be the problem. Anyone has any idea what the problem may be or how I can speed up my query? DB: 8.1.7.4 query: select count(*) from tickets where tid='value1'; where tickets has about 2 million records partition on a date field. and tid is indexed. thanks. linda _ Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang 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: IN or Exists --- performance issue
I nearly always have issues with blanket statements on performance. That includes the statement that EXISTS should be used in place of IN. If the IN performs better in the particular case, use it. If it doesn't perfom adiquately then, by all means, convert it to an EXISTS. And do the reverse as well. I write SQL to answer the given question. If the question is stated " is in " then I code it using an IN. If the question is stated "process the that have " then I code it using an EXISTS. If the query doesn't perform well, then I convert it to the other subquery type. Especially in later versions of O8i and later Oracle will frequently auto-convert the subquery for you. I've seen it convert both ways. Kevin -Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED]Sent: Tuesday, June 03, 2003 7:00 AMTo: Multiple recipients of list ORACLE-LSubject: IN or Exists --- performance issue Hi Listers I have a unique performance problem. As a general rule by oracle while writing SQL scripts EXISTS should be used in place of IN. I'm having 2 sql for comparison using IN and EXISTS operators. With IN operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) With Exists Operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND EXISTS (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE a."ID" = dmv3.asset_id AND dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) The Statement having exists is taking more time than the one with IN operator. IN operator statement time = 3sec and the Exists operator statement time = 12 sec. After analysis I have come to know that the EXISTS statement is causing more logical block reads that IN statement, approx 4 times and hence the delay. I have a index on all the predicates mentioned in the where clause. and the explain plan shows a index range search. Can anyone please help me to reduce these high Logical reads which result when I use the EXISTS operator. Thanks to all Best Regards Munish Bajaj
Re: RE: IN or Exists --- performance issue
http://asktom.oracle.com/pls/ask/f?p=4950:8:247354401321242398::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:953229842074, that will give you a detailed explanation > > From: "Stephane Faroult" <[EMAIL PROTECTED]> > Date: 2003/06/03 Tue AM 09:01:28 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: IN or Exists --- performance issue > > > > > >Hi Listers > > > >I have a unique performance problem. As a general > >rule by oracle while > >writing SQL scripts EXISTS should be used in place > >of IN. > > > > As a general rule there is no general rule. Why do you want to use EXISTS if it goes > faster with IN ? > > EXISTS is mostly used with a correlated subquery - in other words, a query which you > cannot execute without knowing some values from the current row. It fires for each > row you return. In contrast, a IN is usually used with an uncorrelated subquery - > you execute it once, get a number of values, and then compare each row to the > resulting set. > If you have no other criterion, and if of course the uncorrelated subquery doesn't > return zillions of rows, the uncorrelated subquery is usually faster. If you have > other efficient criteria and the existence test is executed as a kind of > after-thought, final screening of a relatively modest set of rows, go for the EXISTS > and the correlated subquery. In case of doubt, test both. > > Do not try to make results fit the theory, especially when the theory is wrong. And > if I were you I would get rid of hints. I tend to see hints as surgeons see > amputation. If I can avoid them ... > > Regards, > > Stephane Faroult > Oriole > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephane Faroult > 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: <[EMAIL PROTECTED] 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: IN or Exists --- performance issue
> >Hi Listers > >I have a unique performance problem. As a general >rule by oracle while >writing SQL scripts EXISTS should be used in place >of IN. > As a general rule there is no general rule. Why do you want to use EXISTS if it goes faster with IN ? EXISTS is mostly used with a correlated subquery - in other words, a query which you cannot execute without knowing some values from the current row. It fires for each row you return. In contrast, a IN is usually used with an uncorrelated subquery - you execute it once, get a number of values, and then compare each row to the resulting set. If you have no other criterion, and if of course the uncorrelated subquery doesn't return zillions of rows, the uncorrelated subquery is usually faster. If you have other efficient criteria and the existence test is executed as a kind of after-thought, final screening of a relatively modest set of rows, go for the EXISTS and the correlated subquery. In case of doubt, test both. Do not try to make results fit the theory, especially when the theory is wrong. And if I were you I would get rid of hints. I tend to see hints as surgeons see amputation. If I can avoid them ... Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: IN or Exists --- performance issue
--_=_NextPart_001_01C329BC.79FDEA20
Content-Type: text/plain; charset="iso-8859-1"
Munish
I've got a funny feeling that this thing about using EXISTS rather than IN
is a bit of a myth. I do a lot of this sort of thing and I find that almost
invariably, an IN with a simple subquery is faster than an EXISTS with a
correlated subquery.
Regards
David Lord
-Original Message-
Sent: 03 June 2003 12:00
To: Multiple recipients of list ORACLE-L
Hi Listers
I have a unique performance problem. As a general rule by oracle while
writing SQL scripts EXISTS should be used in place of IN.
I'm having 2 sql for comparison using IN and EXISTS operators.
With IN operator
SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */
COUNT(1)
FROM mam_assets a
WHERE 1 = 1
AND a.is_current_version = 1
AND a."ID" IN (SELECT dmv3.asset_id
FROM mam_asset_attr_domain_values dmv3
WHERE dmv3.domain_value_id = 71
AND dmv3.asset_attribute_xid = 3
AND dmv3.domain_xid = 7)
With Exists Operator
SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */
COUNT(1)
FROM mam_assets a
WHERE 1 = 1
AND a.is_current_version = 1
AND EXISTS (SELECT dmv3.asset_id
FROM mam_asset_attr_domain_values dmv3
WHERE a."ID" = dmv3.asset_id
AND dmv3.domain_value_id = 71
AND dmv3.asset_attribute_xid = 3
AND dmv3.domain_xid = 7)
The Statement having exists is taking more time than the one with IN
operator. IN operator statement time = 3sec and the Exists operator
statement time = 12 sec. After analysis I have come to know that the EXISTS
statement is causing more logical block reads that IN statement, approx 4
times and hence the delay.
I have a index on all the predicates mentioned in the where clause. and the
explain plan shows a index range search.
Can anyone please help me to reduce these high Logical reads which result
when I use the EXISTS operator.
Thanks to all
Best Regards
Munish Bajaj
**
This message (including any attachments) is confidential and may be
legally privileged. If you are not the intended recipient, you should
not disclose, copy or use any part of it - please delete all copies
immediately and notify the Hays Group Email Helpdesk at
[EMAIL PROTECTED]
Any information, statements or opinions contained in this message
(including any attachments) are given by the author. They are not
given on behalf of Hays unless subsequently confirmed by an individual
other than the author who is duly authorised to represent Hays.
A member of the Hays plc group of companies.
Hays plc is registered in England and Wales number 2150950.
Registered Office Hays House Millmead Guildford Surrey GU2 4HJ.
**
--_=_NextPart_001_01C329BC.79FDEA20
Content-Type: text/html; charset="iso-8859-1"
Blank
BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 25px; COLOR: #00;
FONT-FAMILY: Arial, Helvetica
}
P.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #cc;
FONT-FAMILY: Helvetica, "Times New Roman"
}
LI.msoNormal {
MARGIN-TOP: 0px; FONT-SIZE: 10pt; MARGIN-LEFT: 0px; COLOR: #cc;
FONT-FAMILY: Helvetica, "Times New Roman"
}
cid:[EMAIL PROTECTED]>
Munish
I've got a funny
feeling that this thing about using EXISTS rather than IN is a bit of a
myth. I do a lot of this sort of thing and I find that almost invariably,
an IN with a simple subquery is faster than an EXISTS with a correlated
subquery.
Regards
David
Lord
-Original Message-From: Munish Bajaj
[mailto:[EMAIL PROTECTED]Sent: 03 June 2003 12:00To:
Multiple recipients of list ORACLE-LSubject: IN or Exists ---
performance issue
Hi Listers
I have a unique performance problem. As a
general rule by oracle while writing SQL scripts EXISTS should be used in
place of IN.
I'm having 2 sql for comparison using IN and
EXISTS operators.
With IN operator
SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */
COUNT(1)
FROM mam_assets a
WHERE 1 = 1
AND a.is_current_version = 1
AND a."ID" IN (SELECT dmv3.asset_id
FROM mam_asset_attr_domain_values dmv3
WHERE dmv3.domain_value_id = 71
AND dmv3.asset_attribute_xid = 3
AND dmv3.domain_xid = 7)
With Exists Operator
SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */
COUNT(1)
FROM mam_assets a
WHERE 1 = 1
AND a.is_current_version = 1
AND EXISTS (SELECT dmv3.asset_id
FROM mam_asset_attr_domain_values dmv3
WHERE a."ID" = dmv3.asset_id
AND dmv3.domain_value_id = 71
AND dmv3.asset_attribute_xid = 3
AND dmv3.domain_xid = 7)
The Statement having exists is taking more
time than the one with IN operator. IN operator statement time = 3sec and the
Exists operator statement time = 12 sec. After analysis I have come to know
that the EXISTS statement is causing more logic
RE: IN or Exists --- performance issue
Munish, You are right, as a *general rule of thumb* EXISTS is *usually* faster than IN.. There can however be problems when an EXISTS is used to manipulate or select data from a very large table, where the row exists in a far smaller table, as it will read every row in the large table, and then scan the smaller table for corresponding rows.. Is this the case with you? You have a few options: 1) Use IN ;) 2) Try an ALWAYS_SJ(MERGE) hint. 3) Set the ALWAYS_SEMI_JOIN init.ora parameter to "MERGE" (though this has had Ora-600 problems reported against it - so test it thoroughly first!). HTH Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -Original Message- Sent: 03 June 2003 12:00 To: Multiple recipients of list ORACLE-L Hi Listers I have a unique performance problem. As a general rule by oracle while writing SQL scripts EXISTS should be used in place of IN. I'm having 2 sql for comparison using IN and EXISTS operators. With IN operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a ALWAYS_SEMI_JOIN WHERE 1 = 1 AND a.is_current_version = 1 AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) With Exists Operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND EXISTS (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE a."ID" = dmv3.asset_id AND dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) The Statement having exists is taking more time than the one with IN operator. IN operator statement time = 3sec and the Exists operator statement time = 12 sec. After analysis I have come to know that the EXISTS statement is causing more logical block reads that IN statement, approx 4 times and hence the delay. I have a index on all the predicates mentioned in the where clause. and the explain plan shows a index range search. Can anyone please help me to reduce these high Logical reads which result when I use the EXISTS operator. Thanks to all Best Regards Munish Bajaj --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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: IN or Exists --- performance issue
there is an in & exists thread on asktom. Generally speaking exists is better if the sub-query will have a larger and most costly result set than the outer query. Its the other way around for 'in'. I may have them backward, though I think that is correct. > > From: Munish Bajaj <[EMAIL PROTECTED]> > Date: 2003/06/03 Tue AM 06:59:52 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: IN or Exists --- performance issue > > Hi Listers > > I have a unique performance problem. As a general rule by oracle while > writing SQL scripts EXISTS should be used in place of IN. > > I'm having 2 sql for comparison using IN and EXISTS operators. > > With IN operator > > SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ > > COUNT(1) > > FROM mam_assets a > > WHERE 1 = 1 > > AND a.is_current_version = 1 > > AND a."ID" IN (SELECT dmv3.asset_id > > FROM mam_asset_attr_domain_values dmv3 > > WHERE dmv3.domain_value_id = 71 > > AND dmv3.asset_attribute_xid = 3 > > AND dmv3.domain_xid = 7) > > With Exists Operator > > SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ > > COUNT(1) > > FROM mam_assets a > > WHERE 1 = 1 > > AND a.is_current_version = 1 > > AND EXISTS (SELECT dmv3.asset_id > > FROM mam_asset_attr_domain_values dmv3 > > WHERE a."ID" = dmv3.asset_id > > AND dmv3.domain_value_id = 71 > > AND dmv3.asset_attribute_xid = 3 > > AND dmv3.domain_xid = 7) > > The Statement having exists is taking more time than the one with IN > operator. IN operator statement time = 3sec and the Exists operator > statement time = 12 sec. After analysis I have come to know that the EXISTS > statement is causing more logical block reads that IN statement, approx 4 > times and hence the delay. > > I have a index on all the predicates mentioned in the where clause. and the > explain plan shows a index range search. > > Can anyone please help me to reduce these high Logical reads which result > when I use the EXISTS operator. > > Thanks to all > > Best Regards > > Munish Bajaj > > > > > Hi Listers I have a unique performance problem. As a general rule by oracle while writing SQL scripts EXISTS should be used in place of IN. I'm having 2 sql for comparison using IN and EXISTS operators. With IN operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) With Exists Operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND EXISTS (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE a."ID" = dmv3.asset_id AND dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) The Statement having exists is taking more time than the one with IN operator. IN operator statement time = 3sec and the Exists operator statement time = 12 sec. After analysis I have come to know that the EXISTS statement is causing more logical block reads that IN statement, approx 4 times and hence the delay. I have a index on all the predicates mentioned in the where clause. and the explain plan shows a index range search. Can anyone please help me to reduce these high Logical reads which result when I use the EXISTS operator. Thanks to all Best Regards Munish Bajaj Content-Type: image/gif; The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
IN or Exists --- performance issue
Hi Listers I have a unique performance problem. As a general rule by oracle while writing SQL scripts EXISTS should be used in place of IN. I'm having 2 sql for comparison using IN and EXISTS operators. With IN operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) With Exists Operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND EXISTS (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE a."ID" = dmv3.asset_id AND dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) The Statement having exists is taking more time than the one with IN operator. IN operator statement time = 3sec and the Exists operator statement time = 12 sec. After analysis I have come to know that the EXISTS statement is causing more logical block reads that IN statement, approx 4 times and hence the delay. I have a index on all the predicates mentioned in the where clause. and the explain plan shows a index range search. Can anyone please help me to reduce these high Logical reads which result when I use the EXISTS operator. Thanks to all Best Regards Munish Bajaj <>
RE: Performance issue
> Hi Lisetrs, > > I have enough free memory from shared_pool_size and > I run stats every week but the BD still shows up the > low hit ratio and some times the later full down to > 15%! > Here is some informations: Are any of the users complaining that their business functions are performing poorly? If not, you should follow these steps 1. Stop looking at hit ratios 2. Go to your local and buy a very good beer (I'd recommend Coopers (Australian) or Leffe (Belgian)) 3. Tell yourself "I must learn the benefits of 10046 event tracing" 4. Enjoy the beer. (Oh, and if the users are complaining, I'd recommend the same :-) ) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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).
Performance issue
Hi Lisetrs, I have enough free memory from shared_pool_size and I run stats every week but the BD still shows up the low hit ratio and some times the later full down to 15%! Here is some informations: Obj mem: 79503437 bytes Shared sql: 23852410 bytes Cursors: 244875 bytes Free memory: 204512816 bytes (195.04MB) Shared pool utilization (total): 124320866 bytes (118.56MB) Shared pool allocation (actual): 3 bytes (286.1MB) Percentage Utilized: 41% Hit Ratio : 74.2249062 I appreciate to give me some guidelines. Thanks, Ben __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben 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: Re_ Performance Issue on 817 !!!
* Leftover DLL's that can cause problems
* The directory structure is not Win2k
* I don't know what else, I trust my SA's judgement. :)
Jared
"Jackson Dumas" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/09/2003 12:03 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Re_ Performance Issue on 817 !!!
Jared
The O/S was upgraded to Win2000. It was not reinstalled, I mean disk
was not formatted, this was done a fly from WinNT TO Win2k. Please
tell me more about the problem with upgrading this as I don't know
much about O/S issues. It's only one application out of 5 that is
having this problem.
>-Original Message-
>Sent: Wednesday, January 08, 2003 1:55 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Jackson,
>
>Was the OS actually upgraded to win2k, or was it reinstalled?
>
>because Win2k UPG != Win2k installed
>
>The concensus among SA's seems to be that upgrading from
>NT to Win2k is not a good thing. Our SA's refuse to do it.
>
>Jared
>
>
>
>
>
>
>"Jackson Dumas" <[EMAIL PROTECTED]>
>Sent by: [EMAIL PROTECTED]
> 01/07/2003 11:58 PM
> Please respond to ORACLE-L
>
>
>To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]
>m>
>cc:
>Subject:Performance Issue on 817 !!!
>
>
>Hi guys
>
>Compliments of the new season.
>
>We have a database running on Oracle 817 and there are around 4 to 5
>applications running there. Initially the platform was Windows NT,
>service pack 6. Everything was working fine, now recently, the O/S
was
>upgraded to Windows 2000. Then our problems started, one of those 4
to
>5 applications is having a severe performance problems, others are
>working fine. Now the problem is that one does not know if the O/S
>upgrade have an effect on this, or maybe that particular application
>is having the problems. Help advice as now things are sour. What
could
>have been the main problem, on the Database, Application or Operating
>system level ? I thought it could not be the database because other
>applications are working fine on the very same database, I could be
>wrong Please help .1
>
>___
> http://www.webmail.co.za the South-African free email service
>
> NetWiseGurus.Com Portal - Your Own Internet Business Today!
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Jackson Dumas
> 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: Boivin, Patrice J
> 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).
>
___
http://www.webmail.co.za the South-African free email service
NetWiseGurus.Com Portal - Your Own Internet Business Today!
--
RE: Re_ Performance Issue on 817 !!!
If re-installing Win2K is not an option, then you have to start with the assumption that since the other apps are running, there is a problem with the one app that is not running. In my opinion it would have been better to rebuild the server from scratch as a Windows2000 server, instead of upgrading the OS. It would have given your sa an opportunity to update the firmware, etc. If it is the application... look for differences between it and the other ones. What is it using that the other ones are not using? Does Win2K show any entries in Event Viewer? What about Oracle's various log files, anything in there? Any stats inside Oracle that might provide you with clues? (I suspect you already started checking the logs, etc. though). Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Thursday, January 09, 2003 4:04 AM To: Multiple recipients of list ORACLE-L Jared The O/S was upgraded to Win2000. It was not reinstalled, I mean disk was not formatted, this was done a fly from WinNT TO Win2k. Please tell me more about the problem with upgrading this as I don't know much about O/S issues. It's only one application out of 5 that is having this problem. >-Original Message- >Sent: Wednesday, January 08, 2003 1:55 PM >To: Multiple recipients of list ORACLE-L > > >Jackson, > >Was the OS actually upgraded to win2k, or was it reinstalled? > >because Win2k UPG != Win2k installed > >The concensus among SA's seems to be that upgrading from >NT to Win2k is not a good thing. Our SA's refuse to do it. > >Jared > > > > > > >"Jackson Dumas" <[EMAIL PROTECTED]> >Sent by: [EMAIL PROTECTED] > 01/07/2003 11:58 PM > Please respond to ORACLE-L > > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED] >m> >cc: >Subject:Performance Issue on 817 !!! > > >Hi guys > >Compliments of the new season. > >We have a database running on Oracle 817 and there are around 4 to 5 >applications running there. Initially the platform was Windows NT, >service pack 6. Everything was working fine, now recently, the O/S was >upgraded to Windows 2000. Then our problems started, one of those 4 to >5 applications is having a severe performance problems, others are >working fine. Now the problem is that one does not know if the O/S >upgrade have an effect on this, or maybe that particular application >is having the problems. Help advice as now things are sour. What could >have been the main problem, on the Database, Application or Operating >system level ? I thought it could not be the database because other >applications are working fine on the very same database, I could be >wrong Please help .1 > >___ > http://www.webmail.co.za the South-African free email service > > NetWiseGurus.Com Portal - Your Own Internet Business Today! > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Jackson Dumas > 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: Boivin, Patrice J > INET: [EMAIL PROTECTED] > >Fat City Network S
Re_ Performance Issue on 817 !!!
Jared The O/S was upgraded to Win2000. It was not reinstalled, I mean disk was not formatted, this was done a fly from WinNT TO Win2k. Please tell me more about the problem with upgrading this as I don't know much about O/S issues. It's only one application out of 5 that is having this problem. >-Original Message- >Sent: Wednesday, January 08, 2003 1:55 PM >To: Multiple recipients of list ORACLE-L > > >Jackson, > >Was the OS actually upgraded to win2k, or was it reinstalled? > >because Win2k UPG != Win2k installed > >The concensus among SA's seems to be that upgrading from >NT to Win2k is not a good thing. Our SA's refuse to do it. > >Jared > > > > > > >"Jackson Dumas" <[EMAIL PROTECTED]> >Sent by: [EMAIL PROTECTED] > 01/07/2003 11:58 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED] >m> >cc: >Subject:Performance Issue on 817 !!! > > >Hi guys > >Compliments of the new season. > >We have a database running on Oracle 817 and there are around 4 to 5 >applications running there. Initially the platform was Windows NT, >service pack 6. Everything was working fine, now recently, the O/S was >upgraded to Windows 2000. Then our problems started, one of those 4 to >5 applications is having a severe performance problems, others are >working fine. Now the problem is that one does not know if the O/S >upgrade have an effect on this, or maybe that particular application >is having the problems. Help advice as now things are sour. What could >have been the main problem, on the Database, Application or Operating >system level ? I thought it could not be the database because other >applications are working fine on the very same database, I could be >wrong Please help .1 > >___ > http://www.webmail.co.za the South-African free email service > > NetWiseGurus.Com Portal - Your Own Internet Business Today! > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Jackson Dumas > 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: Boivin, Patrice J > 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). > ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jackson Dumas 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 Issue on 817 !!!
same here.
DLLs all over the place, not the best situation to be in.
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services| Services technologiques
Informatics Branch | Direction de l'informatique
Maritimes Region, DFO | Région des Maritimes, MPO
E-Mail: [EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, January 08, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L
Jackson,
Was the OS actually upgraded to win2k, or was it reinstalled?
because Win2k UPG != Win2k installed
The concensus among SA's seems to be that upgrading from
NT to Win2k is not a good thing. Our SA's refuse to do it.
Jared
"Jackson Dumas" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/07/2003 11:58 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Performance Issue on 817 !!!
Hi guys
Compliments of the new season.
We have a database running on Oracle 817 and there are around 4 to 5
applications running there. Initially the platform was Windows NT,
service pack 6. Everything was working fine, now recently, the O/S was
upgraded to Windows 2000. Then our problems started, one of those 4 to
5 applications is having a severe performance problems, others are
working fine. Now the problem is that one does not know if the O/S
upgrade have an effect on this, or maybe that particular application
is having the problems. Help advice as now things are sour. What could
have been the main problem, on the Database, Application or Operating
system level ? I thought it could not be the database because other
applications are working fine on the very same database, I could be
wrong Please help .1
___
http://www.webmail.co.za the South-African free email service
NetWiseGurus.Com Portal - Your Own Internet Business Today!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jackson Dumas
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: Boivin, Patrice J
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 Issue on 817 !!!
Jackson,
Was the OS actually upgraded to win2k, or was it reinstalled?
because Win2k UPG != Win2k installed
The concensus among SA's seems to be that upgrading from
NT to Win2k is not a good thing. Our SA's refuse to do it.
Jared
"Jackson Dumas" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/07/2003 11:58 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:Performance Issue on 817 !!!
Hi guys
Compliments of the new season.
We have a database running on Oracle 817 and there are around 4 to 5
applications running there. Initially the platform was Windows NT,
service pack 6. Everything was working fine, now recently, the O/S was
upgraded to Windows 2000. Then our problems started, one of those 4 to
5 applications is having a severe performance problems, others are
working fine. Now the problem is that one does not know if the O/S
upgrade have an effect on this, or maybe that particular application
is having the problems. Help advice as now things are sour. What could
have been the main problem, on the Database, Application or Operating
system level ? I thought it could not be the database because other
applications are working fine on the very same database, I could be
wrong Please help .1
___
http://www.webmail.co.za the South-African free email service
NetWiseGurus.Com Portal - Your Own Internet Business Today!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jackson Dumas
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 Issue on 817 !!!
Jackson Dumas, Maybe we need to know whether your os upgrade caused oracle upgrade? I mean if you did oracle exp/imp, patch, migration etc? I mean if there is anything else is changed ? Make a statspack and check what is the oracle doing. That will be helpful:) Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(ChinaOracle User Group) === 2003-01-07 23:58:00 ,you wrote£º=== >Hi guys > >Compliments of the new season. > >We have a database running on Oracle 817 and there are around 4 to 5 >applications running there. Initially the platform was Windows NT, >service pack 6. Everything was working fine, now recently, the O/S was >upgraded to Windows 2000. Then our problems started, one of those 4 to >5 applications is having a severe performance problems, others are >working fine. Now the problem is that one does not know if the O/S >upgrade have an effect on this, or maybe that particular application >is having the problems. Help advice as now things are sour. What could >have been the main problem, on the Database, Application or Operating >system level ? I thought it could not be the database because other >applications are working fine on the very same database, I could be >wrong Please help .1 > >___ > http://www.webmail.co.za the South-African free email service > > NetWiseGurus.Com Portal - Your Own Internet Business Today! > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Jackson Dumas > 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: chao_ping 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 Issue on 817 !!!
Hi Jackson Dumas, Have check the virtual memory (paging space) of the OS ? Because sometimes it's related. And, how big memory that you used in your server ? Thank's Bernardus Deddy Hoeydiono. -Original Message- Dumas Sent: Wednesday, January 08, 2003 2:59 PM To: Multiple recipients of list ORACLE-L Hi guys Compliments of the new season. We have a database running on Oracle 817 and there are around 4 to 5 applications running there. Initially the platform was Windows NT, service pack 6. Everything was working fine, now recently, the O/S was upgraded to Windows 2000. Then our problems started, one of those 4 to 5 applications is having a severe performance problems, others are working fine. Now the problem is that one does not know if the O/S upgrade have an effect on this, or maybe that particular application is having the problems. Help advice as now things are sour. What could have been the main problem, on the Database, Application or Operating system level ? I thought it could not be the database because other applications are working fine on the very same database, I could be wrong Please help .1 ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jackson Dumas 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: Bernardus Deddy Hoeydiono 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 Issue on 817 !!!
>Hi guys > >Compliments of the new season. > >We have a database running on Oracle 817 and there are around 4 to 5 >applications running there. Initially the platform was Windows NT, >service pack 6. Everything was working fine, now recently, the O/S was >upgraded to Windows 2000. Then our problems started, one of those 4 to >5 applications is having a severe performance problems, others are >working fine. Now the problem is that one does not know if the O/S >upgrade have an effect on this, or maybe that particular application >is having the problems. Help advice as now things are sour. What could >have been the main problem, on the Database, Application or Operating >system level ? I thought it could not be the database because other >applications are working fine on the very same database, I could be >wrong Please help .1 > Jackson, First thing to do is to qualify your 'performance problem'. Usually in IT it gets down to CPU, I/Os or network. If everything is fine then it may be lock or latch related, and the suitable V$ views should help you check this. Check V$WAITSTAT, V$SYSTEM_EVENT, V$SQL (queries most heavy on buffer gets) and try to narrow the scope of your problem. HTH Stephane Faroult -- Diese E-Mail wurde mit http://www.mail-inspector.de verschickt Mail Inspector ist ein kostenloser Service von http://www.is-fun.net Der Absender dieser E-Mail hatte die IP: 195.115.41.103 -- 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).
Performance Issue on 817 !!!
Hi guys Compliments of the new season. We have a database running on Oracle 817 and there are around 4 to 5 applications running there. Initially the platform was Windows NT, service pack 6. Everything was working fine, now recently, the O/S was upgraded to Windows 2000. Then our problems started, one of those 4 to 5 applications is having a severe performance problems, others are working fine. Now the problem is that one does not know if the O/S upgrade have an effect on this, or maybe that particular application is having the problems. Help advice as now things are sour. What could have been the main problem, on the Database, Application or Operating system level ? I thought it could not be the database because other applications are working fine on the very same database, I could be wrong Please help .1 ___ http://www.webmail.co.za the South-African free email service NetWiseGurus.Com Portal - Your Own Internet Business Today! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jackson Dumas 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 ISSUE
Seema, See the problem that is arising here on this post ? It could be this and it could be that ... what is the # of cpus ? what o/s ? try this and try that and on and on we go asking question after question until you say "Enough, I am not getting anywhere with this." This is why you gotta drill down to the problem area. Mike -Original Message- Sent: Thursday, June 20, 2002 12:20 PM To: Multiple recipients of list ORACLE-L It could be memory paging problem. Are you using UFS, VFS, etc? If yes, see if there is any options where you can mount them in the direct mode (bypass the fs cache layer) Waleed -Original Message- Sent: Thursday, June 20, 2002 12:39 PM To: Multiple recipients of list ORACLE-L Hi I am having some problem regarding performance.The performance problem get solved after reboot.Whenever the db unix server keep running more than 72 hrs the performance looks like slow.one of cause I found some memory leakage.But the box are having 2 instances and for another instance performance is looking good.If I see all database ratios are looking great.What could be reason? thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 ISSUE
It could be memory paging problem. Are you using UFS, VFS, etc? If yes, see if there is any options where you can mount them in the direct mode (bypass the fs cache layer) Waleed -Original Message- Sent: Thursday, June 20, 2002 12:39 PM To: Multiple recipients of list ORACLE-L Hi I am having some problem regarding performance.The performance problem get solved after reboot.Whenever the db unix server keep running more than 72 hrs the performance looks like slow.one of cause I found some memory leakage.But the box are having 2 instances and for another instance performance is looking good.If I see all database ratios are looking great.What could be reason? thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 ISSUE
Title: PERFORMANCE ISSUE
Seema,
Questions:
1. On what OS you are running your db?
2. How did u find memory leakage?
Regards,
Rao
-Original Message- From:
[EMAIL PROTECTED]@SUNGARD on behalf of "Seema Singh"
<[EMAIL PROTECTED]> Sent: Thu 6/20/2002 12:38 PM
To: Multiple recipients of list ORACLE-L Cc:
Subject: PERFORMANCE ISSUE
Hi I am
having some problem regarding performance.The performance problem get
solved after reboot.Whenever the db unix server
keep running more than 72 hrs the
performance looks like slow.one of cause I found some memory
leakage.But the box are having 2 instances and
for another instance performance is
looking good.If I see all database ratios are looking great.What could be reason? thx -Seema
_
Send and receive Hotmail on your mobile device:
http://mobile.msn.com
-- Please
see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema
Singh INET:
[EMAIL PROTECTED]
Fat City Network Services --
(858) 538-5051 FAX: (858) 538-5051 San Diego, California --
Public Internet access / Mailing Lists
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).
æ¬zǶ¨}ø©ND ±@Bm§ÿðÃ
+iöªrºØh¬DNf¡z̪ڢ˧ªÝr
jТ·#^·
+'«¾' ³Î|ç9ÓE9óüçNuI©Ãè( ©b~ç£înX"{^®wiÇ ²ÏÌj)bâ²Û¡ 9Q2¢êìzWß®+&j)b b²Û ֧Ƣg¬±¨ ¶â²Ñ®®çÚµÈÉÊ&^pNÊ^X§ËËFº»Ø§¶¦zËàN
§r[y©bç(Ö¢)àPÔä@± ¢»azv¦zæj)b b²Ü¨»§¶Úz·¦¢÷~º&b¦k&¥² Ûaxq =Ê&©Ý~趫wè®fåG¬¹» ®&â
RE: PERFORMANCE ISSUE
Well, you don't give us much to go on and I fully agree with the other comment about buying and using a good tuning book. However, there is one experiment you might try the next time your instance gets slow. Try flushing the shared pool (alter system flush shared_pool). If that works, you can do it once every two or three days to give you a chance to buy and read the tuning book and find out why it works and how to fix the underlying problem. If it doesn't work, don't do it any more -- keep rebooting every few days until you have read the book then diagnosed and fixed the underlying problem. Kevin Kennedy First Point Energy Corporation -Original Message- Sent: Thursday, June 20, 2002 9:39 AM To: Multiple recipients of list ORACLE-L Hi I am having some problem regarding performance.The performance problem get solved after reboot.Whenever the db unix server keep running more than 72 hrs the performance looks like slow.one of cause I found some memory leakage.But the box are having 2 instances and for another instance performance is looking good.If I see all database ratios are looking great.What could be reason? thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 ISSUE
Seema, So what does that tell you about how useful database ratios are in diagnosing your problem ? I would suggest you check out some sites about detecting waits in your system. The information given here gives no one a starting point to help you diagnose the problem. Consider buying Gaja Krishna Vaidyanatha and Kirti Deshpande's book "Oracle Performance Tuning 101" as it has been a big help to me. Other things you might find helpful would be www.hotsos.com which goes into great detail on the 10046 event trace and they also have an excellent clinic I just went too. Good Luck and Peace ! Mike -Original Message- Sent: Thursday, June 20, 2002 9:39 AM To: Multiple recipients of list ORACLE-L Hi I am having some problem regarding performance.The performance problem get solved after reboot.Whenever the db unix server keep running more than 72 hrs the performance looks like slow.one of cause I found some memory leakage.But the box are having 2 instances and for another instance performance is looking good.If I see all database ratios are looking great.What could be reason? thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
PERFORMANCE ISSUE
Hi I am having some problem regarding performance.The performance problem get solved after reboot.Whenever the db unix server keep running more than 72 hrs the performance looks like slow.one of cause I found some memory leakage.But the box are having 2 instances and for another instance performance is looking good.If I see all database ratios are looking great.What could be reason? thx -Seema _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 issue , OCI related ? - urgent , please
Stephane , thanks a lot ! actualy , i do not think that this is the case , because it would take relatively much more time to construct the result set (my SQL contains massive 'group by' and 'order by' and therefore the whole result set needs to be constructed before returning even the 1st row), than to skip over several records ot bring the next 5000 rows... DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Wed, May 22, 2002 12:53 PM To: Multiple recipients of list ORACLE-L >Dear gurus ! >We are facing a severe performance problems here >that i have no idea of how >to address. >There is a C++ program , which uses OCI , that does >the following. >It initiates a long running SQL select statement, >and then fetches the >result set from the DB in buffers of 5000 records. >Now , the problem is that it takes significantly >(exponentially) more time >to fetch each subsequent result set. >I.e. , it takes a second to bring the first 5000 >records, it takes 2 >seconds to bring the next 5000 rows , it takes 3 >seconds to bring the third >set of 5000 rows and it takes a hour to bring >the 100th set of 5000 >rows. >My main question is : what actually happens within >the DB engine when you >execute a huge select and then fetch the results >buffer-by-buffer ? > >Thanks a lot !!! > > >DBAndrey > Hmm, I don't think that the problem is with the OCI. Normally (and that what the 'cursor' of old meant) you do at execution any processing (sorts included) which may be required to determine the result set, then move a kind of pointer forward that result set. Not much different from a succession of read() - the size of the buffer, performance questions apart, just means more or less moving a pointer. The behaviour you witness is more typical of a statement which would be executed again and again, skipping 0 lines on the first execution, 5000 on the second one, 1 on the third one ... so as to always return 5000 lines. Wanna bet ? Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 issue , OCI related ? - urgent , please
Thanks a lot Jonathan ! I TKPROFed these statements - nothing outstanding, it looks like the cursor is not getting closed eafter each fetch. Anyway , i would not expect the 100th fetch to take 100s times more time than the first one Also please note that the select contains 'group by' and 'order by' , which AFAIK forces the engine to construct the whole result set before returning rows. Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Wed, May 22, 2002 12:43 PM To: Multiple recipients of list ORACLE-L Can you switch on SQL trace whilst this is running, or peek into v$sql. It sounds to me as if your code is "fetching an array" by re-opening the cursor, fetching and discarding all the rows up to the required point, and then returning the required rows. To answer your direct question - when doing array fetching correctly, the time for the Nth fetch should be constant (although the first fetch time may include sorts, hashes etc. and therefore be atypical). You might expect some slow-down as time passes if the fetched data is also subject to update, and increasing amounts of undo have to be applied as the fetch progresses to preserve read-consistency, but I wouldn't expect the effect to be as extreme as your figures indicate. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 22 May 2002 10:33 |Dear gurus ! |We are facing a severe performance problems here that i have no idea of how |to address. |There is a C++ program , which uses OCI , that does the following. |It initiates a long running SQL select statement, and then fetches the |result set from the DB in buffers of 5000 records. |Now , the problem is that it takes significantly (exponentially) more time |to fetch each subsequent result set. |I.e. , it takes a second to bring the first 5000 records, it takes 2 |seconds to bring the next 5000 rows , it takes 3 seconds to bring the third |set of 5000 rows and it takes a hour to bring the 100th set of 5000 |rows. |My main question is : what actually happens within the DB engine when you |execute a huge select and then fetch the results buffer-by-buffer ? | |Thanks a lot !!! | | |DBAndrey | |* 03-9254520 |* 058-548133 |* mailto:[EMAIL PROTECTED] | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 issue , OCI related ? - urgent , please
Do you keep the each batch of 5000 rows ? If yes, are you running out of memory ? Anjo. Andrey Bronfin wrote: > Dear gurus ! > We are facing a severe performance problems here that i have no idea of how > to address. > There is a C++ program , which uses OCI , that does the following. > It initiates a long running SQL select statement, and then fetches the > result set from the DB in buffers of 5000 records. > Now , the problem is that it takes significantly (exponentially) more time > to fetch each subsequent result set. > I.e. , it takes a second to bring the first 5000 records, it takes 2 > seconds to bring the next 5000 rows , it takes 3 seconds to bring the third > set of 5000 rows and it takes a hour to bring the 100th set of 5000 > rows. > My main question is : what actually happens within the DB engine when you > execute a huge select and then fetch the results buffer-by-buffer ? > > Thanks a lot !!! > > DBAndrey > > * 03-9254520 > * 058-548133 > * mailto:[EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 issue , OCI related ? - urgent , please
Can you switch on SQL trace whilst this is running, or peek into v$sql. It sounds to me as if your code is "fetching an array" by re-opening the cursor, fetching and discarding all the rows up to the required point, and then returning the required rows. To answer your direct question - when doing array fetching correctly, the time for the Nth fetch should be constant (although the first fetch time may include sorts, hashes etc. and therefore be atypical). You might expect some slow-down as time passes if the fetched data is also subject to update, and increasing amounts of undo have to be applied as the fetch progresses to preserve read-consistency, but I wouldn't expect the effect to be as extreme as your figures indicate. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 22 May 2002 10:33 |Dear gurus ! |We are facing a severe performance problems here that i have no idea of how |to address. |There is a C++ program , which uses OCI , that does the following. |It initiates a long running SQL select statement, and then fetches the |result set from the DB in buffers of 5000 records. |Now , the problem is that it takes significantly (exponentially) more time |to fetch each subsequent result set. |I.e. , it takes a second to bring the first 5000 records, it takes 2 |seconds to bring the next 5000 rows , it takes 3 seconds to bring the third |set of 5000 rows and it takes a hour to bring the 100th set of 5000 |rows. |My main question is : what actually happens within the DB engine when you |execute a huge select and then fetch the results buffer-by-buffer ? | |Thanks a lot !!! | | |DBAndrey | |* 03-9254520 |* 058-548133 |* mailto:[EMAIL PROTECTED] | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 issue , OCI related ? - urgent , please
>Dear gurus ! >We are facing a severe performance problems here >that i have no idea of how >to address. >There is a C++ program , which uses OCI , that does >the following. >It initiates a long running SQL select statement, >and then fetches the >result set from the DB in buffers of 5000 records. >Now , the problem is that it takes significantly >(exponentially) more time >to fetch each subsequent result set. >I.e. , it takes a second to bring the first 5000 >records, it takes 2 >seconds to bring the next 5000 rows , it takes 3 >seconds to bring the third >set of 5000 rows and it takes a hour to bring >the 100th set of 5000 >rows. >My main question is : what actually happens within >the DB engine when you >execute a huge select and then fetch the results >buffer-by-buffer ? > >Thanks a lot !!! > > >DBAndrey > Hmm, I don't think that the problem is with the OCI. Normally (and that what the 'cursor' of old meant) you do at execution any processing (sorts included) which may be required to determine the result set, then move a kind of pointer forward that result set. Not much different from a succession of read() - the size of the buffer, performance questions apart, just means more or less moving a pointer. The behaviour you witness is more typical of a statement which would be executed again and again, skipping 0 lines on the first execution, 5000 on the second one, 1 on the third one ... so as to always return 5000 lines. Wanna bet ? Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Performance issue , OCI related ? - urgent , please
Dear gurus ! We are facing a severe performance problems here that i have no idea of how to address. There is a C++ program , which uses OCI , that does the following. It initiates a long running SQL select statement, and then fetches the result set from the DB in buffers of 5000 records. Now , the problem is that it takes significantly (exponentially) more time to fetch each subsequent result set. I.e. , it takes a second to bring the first 5000 records, it takes 2 seconds to bring the next 5000 rows , it takes 3 seconds to bring the third set of 5000 rows and it takes a hour to bring the 100th set of 5000 rows. My main question is : what actually happens within the DB engine when you execute a huge select and then fetch the results buffer-by-buffer ? Thanks a lot !!! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Oracle on AIX performance issue
> Oracle 8.1.7 on AIX. > Our database is rapidly expanding. Now it's approximately 80GB and for few > months it's going to be at least double of that size. We are planning some > reorganizations, so I'm wondering could you give me some advice. > Is it better to have several big file systems or lot of smaller? > > TIA, > Sonja > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?ISO-8859-2?Q?Sonja_=A9ehovi=E6?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 issue on this sql ???
I need to get the adue_cd and group by the sum of the adue_amt- amtpd based
on the decoded code ??
SELECT DECODE(adue_cd,'DS','STM','IS','INS','MS','OUI',
'PA','OD1','PN','OD2','PP','OD1')
,SUM(adue_amt - amt_pd)
FROM BSADUE
WHERE la_no = v_la_no
AND adue_cd IN ('DS','IS','MS','PA','PN','PP')
GROUP BY adue_cd
order by DECODE(adue_cd,'DS','STM','IS','INS','MS','OUI',
'PA','OD1','PN','OD2','PP','OD1')
here is my explain plan generate from TOAD.
Operation Object Name RowsBytes CostTQ In/Out
PStart PStop
SELECT STATEMENT
SORT ORDER BY
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID BSADUE
INDEX RANGE SCANBSADUE_LANO
Raymond Lee
Infopro Sdn Bhd
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raymond Lee Meng Hong
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Snapshot performance issue
I forgot to tell you that I'm working on release 8.1.7 and if I'm not mistaken indexes like 'SNAP_$' do no more exist. Please, correct me if I'm mistaken. -Message d'origine- De : Mike J Kurth [mailto:[EMAIL PROTECTED]] Envoyé : mardi 11 septembre 2001 08:25 À : Multiple recipients of list ORACLE-L Objet : Re: Snapshot performance issue It may be that the name of your index has changed. It is probably something like I_snap$_E I_REF_EMETTEUR. You will need to change the hint to reflect the change. 01 01:40 AM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: I have a view based on the query below which is executed in a reasonable way on the master site but this same view takes hours on the snapshot site. Any idea CREATE OR REPLACE VIEW V_ENTITE_EMETTEUR2 AS select /*+ ORDERED USE_NL(E A SI SY O LF MO AM MR ST) INDEX(E I_REF_EMETTEUR) */ E.COMP_EMETTEUR, E.NOM_STATION, E.REF_EMETTEUR, E.PIRE, E.PIRE_CALCULEE, E.HAUT_SOL_ANT, E.AZIMUT, E.ELEVATION, E.OWNER, E.CODE_INTERNE_TS, E.CODE_INTERNE_TC, E.DEP_CODE_GESTION, SY.COMP_SYSTEME, SY.NOM_SYSTEME, O.COMP_OPERATEUR, O.CODE_OPERATEUR, SI.COMP_SITE, SI.REF_SITE, SI.NOM_SITE, A.COMP_ANTENNE, A.NOM_ANT, MO.REF_MODU, AM.REF_AMPLI, MR.REF_MAT_RECEPT, LF.CODE_ETAT, ST.CODE from t_station E, t_antena A, t_site SI, t_system SY, t_operator O, t_lf_states LF, v_station_modu MO, v_station_ampli AM, v_station_recept MR, t_status ST where E.COMP_SYSTEME = SY.COMP_SYSTEME and E.COMP_OPERATEUR = O.COMP_OPERATEUR and E.COMP_SYSTEME = O.COMP_SYSTEME and E.COMP_SITE = SI.COMP_SITE and E.COMP_ANTENNE = A.COMP_ANTENNE and E.COMP_EMETTEUR = MO.COMP_EMETTEUR (+) and E.COMP_EMETTEUR = AM.COMP_EMETTEUR(+) and E.COMP_EMETTEUR = MR.COMP_EMETTEUR(+) and not (E.SUPPR_A_ANALYSER_FAE is not null and E.SUPPR_A_ANALYSER_FAE = 'O') and E.TYPE_STATION = LF.COMP_ETATS and E.COMP_STATUS = ST.COMP_STATUS(+) and exists (select 1 from v_mygroup_users where E.owner=user_name and rownum<=1); - Abiy Alemu Phone: (+33) 1-58 17 04 56 Fax: (+33) 1-58 17 04 54 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike J Kurth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: ALEMU Abiy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Snapshot performance issue
It may be that the name of your index has changed. It is probably something like I_snap$_E I_REF_EMETTEUR. You will need to change the hint to reflect the change. 01 01:40 AM PST Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> cc: I have a view based on the query below which is executed in a reasonable way on the master site but this same view takes hours on the snapshot site. Any idea CREATE OR REPLACE VIEW V_ENTITE_EMETTEUR2 AS select /*+ ORDERED USE_NL(E A SI SY O LF MO AM MR ST) INDEX(E I_REF_EMETTEUR) */ E.COMP_EMETTEUR, E.NOM_STATION, E.REF_EMETTEUR, E.PIRE, E.PIRE_CALCULEE, E.HAUT_SOL_ANT, E.AZIMUT, E.ELEVATION, E.OWNER, E.CODE_INTERNE_TS, E.CODE_INTERNE_TC, E.DEP_CODE_GESTION, SY.COMP_SYSTEME, SY.NOM_SYSTEME, O.COMP_OPERATEUR, O.CODE_OPERATEUR, SI.COMP_SITE, SI.REF_SITE, SI.NOM_SITE, A.COMP_ANTENNE, A.NOM_ANT, MO.REF_MODU, AM.REF_AMPLI, MR.REF_MAT_RECEPT, LF.CODE_ETAT, ST.CODE from t_station E, t_antena A, t_site SI, t_system SY, t_operator O, t_lf_states LF, v_station_modu MO, v_station_ampli AM, v_station_recept MR, t_status ST where E.COMP_SYSTEME = SY.COMP_SYSTEME and E.COMP_OPERATEUR = O.COMP_OPERATEUR and E.COMP_SYSTEME = O.COMP_SYSTEME and E.COMP_SITE = SI.COMP_SITE and E.COMP_ANTENNE = A.COMP_ANTENNE and E.COMP_EMETTEUR = MO.COMP_EMETTEUR (+) and E.COMP_EMETTEUR = AM.COMP_EMETTEUR(+) and E.COMP_EMETTEUR = MR.COMP_EMETTEUR(+) and not (E.SUPPR_A_ANALYSER_FAE is not null and E.SUPPR_A_ANALYSER_FAE = 'O') and E.TYPE_STATION = LF.COMP_ETATS and E.COMP_STATUS = ST.COMP_STATUS(+) and exists (select 1 from v_mygroup_users where E.owner=user_name and rownum<=1); - Abiy Alemu Phone: (+33) 1-58 17 04 56 Fax: (+33) 1-58 17 04 54 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike J Kurth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Snapshot performance issue
I have a view based on the query below which is executed in a reasonable way on the master site but this same view takes hours on the snapshot site. Any idea ? CREATE OR REPLACE VIEW V_ENTITE_EMETTEUR2 AS select /*+ ORDERED USE_NL(E A SI SY O LF MO AM MR ST) INDEX(E I_REF_EMETTEUR)*/ E.COMP_EMETTEUR, E.NOM_STATION, E.REF_EMETTEUR, E.PIRE, E.PIRE_CALCULEE, E.HAUT_SOL_ANT, E.AZIMUT, E.ELEVATION, E.OWNER, E.CODE_INTERNE_TS, E.CODE_INTERNE_TC, E.DEP_CODE_GESTION, SY.COMP_SYSTEME, SY.NOM_SYSTEME, O.COMP_OPERATEUR, O.CODE_OPERATEUR, SI.COMP_SITE, SI.REF_SITE, SI.NOM_SITE, A.COMP_ANTENNE, A.NOM_ANT, MO.REF_MODU, AM.REF_AMPLI, MR.REF_MAT_RECEPT, LF.CODE_ETAT, ST.CODE from t_station E, t_antena A, t_site SI, t_system SY, t_operator O, t_lf_states LF, v_station_modu MO, v_station_ampli AM, v_station_recept MR, t_status ST where E.COMP_SYSTEME = SY.COMP_SYSTEME and E.COMP_OPERATEUR = O.COMP_OPERATEUR and E.COMP_SYSTEME = O.COMP_SYSTEME and E.COMP_SITE = SI.COMP_SITE and E.COMP_ANTENNE = A.COMP_ANTENNE and E.COMP_EMETTEUR = MO.COMP_EMETTEUR(+) and E.COMP_EMETTEUR = AM.COMP_EMETTEUR(+) and E.COMP_EMETTEUR = MR.COMP_EMETTEUR(+) and not (E.SUPPR_A_ANALYSER_FAE is not null and E.SUPPR_A_ANALYSER_FAE = 'O') and E.TYPE_STATION = LF.COMP_ETATS and E.COMP_STATUS = ST.COMP_STATUS(+) and exists (select 1 from v_mygroup_users V where E.owner=user_name and rownum<=1); - Abiy AlemuPhone: (+33) 1-58 17 04 56Fax: (+33) 1-58 17 04 54
RE: 6 databases/one box/performance issue
Connie, HPUX 11.0 is a bit memory hungry. You would need to leave some memory for the OS before allocating to the SGAs else your system will start swapping / paging. Before allocating memory to the SGA's leave some to the HPUX buffer, Unix OS, users logging onto the box. Check what size the the Unix buffer is set to. Use top and glance performance monitor to check how memory is already used (and free) even before your allocate to the SGA's. HTH Suhen We have 6 databases on one HPUX box (not my idea) that has 3GB RAM. In this type of situation, can the total of the 6 databases SGAs add up to 3GB? Are there any special types of calculations that should be done in this type of situation to determine optimal performance sizing for the databases? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Suhen Pather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: 6 databases/one box/performance issue
> 3 GB RAM, how big to set SGA? How about starting at ~ 2 GB for oracle server processes, and allow ~1 GB for user processes? (I have no idea as to how much memory HP-UX uses just to breathe ... leave it room) How much memory does the OS use before your instances are started? (baseline) set the oratab to not startup any instances, cycle the system and see how much memory is in use. How much paging will you tolerate? Do you have any users actually connecting, or any batch jobs running? These processes will require memory (PGA/UGA) What is the typical number of concurrent sessions? Dedicated or shared servers? What is the typical sort_area_size that you use? (e.g. 1 MB) Can you obtain statistics for average values for user process memory usage? I've never seen an HP-UX box, but I do have a copy of Don Burleson's Unix for Oracle DBAs pocket reference. He suggests the utility "glance" for process memory usage (or sar). There's a copy of SAMS unix system administration unleashed at http://ftp.cnt.ru/~ftp/EBooks/Vol_1/ch22.htm. Not that I recommend this title, it just showed up in a google search ... hth, Paul Connie Milliken wrote: > > We have 6 databases on one HPUX box (not my idea) that has 3GB RAM. In > this type of situation, can the total of the 6 databases SGAs add up to > 3GB? Are there any special types of calculations that should be done > in this type of situation to determine optimal performance sizing for > the databases? > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Connie Milliken > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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.com -- Author: Paul Drake INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
6 databases/one box/performance issue
We have 6 databases on one HPUX box (not my idea) that has 3GB RAM. In this type of situation, can the total of the 6 databases SGAs add up to 3GB? Are there any special types of calculations that should be done in this type of situation to determine optimal performance sizing for the databases? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 issue
Try to rebuild your index and use another tablespace for that (It'd be better having a different disk). Also, be aware about how defrag is your data onto disk. That affects tremendously the performance. HTH, Jordi -Original Message- From: Pampati, Kiran [mailto:[EMAIL PROTECTED]] Sent: Monday, April 02, 2001 04:36 PM To: Multiple recipients of list ORACLE-L Subject: Performance issue HI, I have a table with 1 million records, If I search for one value of a column it comes very fast and if I do the same query for a different value on the same column it takes more time. I did not understand why it behaves like that. Any ideas.. Thanks kiran -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pampati, Kiran INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Jordi Sanmarti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 issue
Title: RE: Performance issue > -Original Message- > From: Pampati, Kiran [mailto:[EMAIL PROTECTED]] > > I have a table with 1 million records, If I search for one > value of a column > it comes very fast and if I do the same query for a > different value on the > same column it takes more time. I did not understand why it > behaves like > that. Any ideas.. Have you looked at the "explain plan" for both queries? In SQL*Plus, type in set autotrace traceonly explain and then run the query. You will need to have the PLAN_TABLE created. The script to create that table can be found in $ORACLE_HOME/rdbms/admin/utlxplan.sql
Performance issue
HI, I have a table with 1 million records, If I search for one value of a column it comes very fast and if I do the same query for a different value on the same column it takes more time. I did not understand why it behaves like that. Any ideas.. Thanks kiran -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pampati, Kiran INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: confused about # of extents per segment performance issue
Besides DDL like tuncate and drop will take longer with large # of extents Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Tue, 27 Feb 2001 10:31:24 -0800 This really depends on the type of access. If it's random using indexes, the extents don't matter much. If you do a lot of full table scans, and the extents are scattered all over, there may be a performance degradation. YMMV as always. >>> [EMAIL PROTECTED] 02/27/01 01:06PM >>> Apparently, it's a widely held myth that a large # of extents (let's say "BETWEEN 5 AND 1000") per table segment is bad for performance. Yet the same sources who label the belief mistaken persist in pushing for fitting all of a table in the INITIAL extent. And that confuses the heck out of me. What gives? Is it that fetching a new extent on the fly, the act of obtaining a new extent, creates too much overhead? And finis, no more concern beyond this? If you approached a database for the first time and it already contained a segment with 500 extents, would that segment's number of extents not be a performance concern unless you expected it to continue grabbing new extents? Let's say it would never have a new insert, update, or delete. Would there be any performance value in crushing the segment down to fit in one extent? What's the definitive answer on this? I remain confused about coalescing as well; anyone know of a good whitepaper or article on coalescing/fragmentation for Oracle 8 and beyond? Thanks. - Dana __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dana mn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: confused about # of extents per segment performance issue
This really depends on the type of access. If it's random using indexes, the extents don't matter much. If you do a lot of full table scans, and the extents are scattered all over, there may be a performance degradation. YMMV as always. >>> [EMAIL PROTECTED] 02/27/01 01:06PM >>> Apparently, it's a widely held myth that a large # of extents (let's say "BETWEEN 5 AND 1000") per table segment is bad for performance. Yet the same sources who label the belief mistaken persist in pushing for fitting all of a table in the INITIAL extent. And that confuses the heck out of me. What gives? Is it that fetching a new extent on the fly, the act of obtaining a new extent, creates too much overhead? And finis, no more concern beyond this? If you approached a database for the first time and it already contained a segment with 500 extents, would that segment's number of extents not be a performance concern unless you expected it to continue grabbing new extents? Let's say it would never have a new insert, update, or delete. Would there be any performance value in crushing the segment down to fit in one extent? What's the definitive answer on this? I remain confused about coalescing as well; anyone know of a good whitepaper or article on coalescing/fragmentation for Oracle 8 and beyond? Thanks. - Dana __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dana mn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Tim Sawmiller INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
confused about # of extents per segment performance issue
Apparently, it's a widely held myth that a large # of extents (let's say "BETWEEN 5 AND 1000") per table segment is bad for performance. Yet the same sources who label the belief mistaken persist in pushing for fitting all of a table in the INITIAL extent. And that confuses the heck out of me. What gives? Is it that fetching a new extent on the fly, the act of obtaining a new extent, creates too much overhead? And finis, no more concern beyond this? If you approached a database for the first time and it already contained a segment with 500 extents, would that segment's number of extents not be a performance concern unless you expected it to continue grabbing new extents? Let's say it would never have a new insert, update, or delete. Would there be any performance value in crushing the segment down to fit in one extent? What's the definitive answer on this? I remain confused about coalescing as well; anyone know of a good whitepaper or article on coalescing/fragmentation for Oracle 8 and beyond? Thanks. - Dana __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dana mn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
