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: [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).
