RE: poor clob performance with very high CPU

From: Josh Collier <>
Date: Mon, 2 Nov 2009 12:57:21 -0800
Message-ID: <C5671700C5F4EA47B08AB9E6DAE9539B37EB625948_at_M1EXCHANGE01.mmi.local>

I cannot really upgrade the db at this point.

I have tried in different tablespaces with larger extents and with ASSM and Manual SSM. To no avail.

The AWR report just says

The query used all cpu. the host was not cpu bound in any way.

-----Original Message-----
From: Greg Rahn [] Sent: Monday, November 02, 2009 11:38 AM To: Josh Collier
Subject: Re: poor clob performance with very high CPU

With any performance issue you need to analyze the performance data. What do the ASH/ADDM/AWR reports show?

On Mon, Nov 2, 2009 at 12:30 PM, Josh Collier <> wrote:
> I have a CTAS that involves a CLOB. The expected duration of this process is
> 180 minutes. Recently it started taking > 5 hours. The data and rowcounts
> are very similar, the trace shows only CPU consumption and very little else.
> I am wondering if you guys have any insight into how to diagnose a clob
> performance issue where the only information I have is excessive CPU
> consumption.
> The execution paths have not changed. These are staging tables and they
> never have statistics as we always want full tablescans. The tablespaces
> underlying the tables/clobs have not changed.  They are local  ASSM with
> uniform extent sizes of 128k.

Greg Rahn
Received on Mon Nov 02 2009 - 14:57:21 CST

Original text of this message