Oracle-L: RE: Performance Issue w/ Blob Data

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 31 Oct 2003 06:59:38 -0800
Message-ID: <F001.005D532E.20031031065938@fatcity.com>

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: lhahn_60_at_yahoo.com

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: ListGuru_at_fatcity.com (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: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------