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" <ORACLE-L_at_fatcity.com> 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: 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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