RE: poor clob performance with very high CPU

From: <>
Date: Tue, 3 Nov 2009 09:08:41 +0200
Message-ID: <>

try using v$sql_plan_statistics_all - it shows which sql step consumes what.

as a side note: caching lobs may be a good idea if lobs are a few blocks in size.

Please consider the environment before printing this e-mail

             Josh Collier                                                  
   >                                                 To 
             Sent by:                  ""            
             oracle-l-bounce_at_f         <>            
             2009.11.03 00:04          RE: poor clob performance with very 
                                       high CPU                            
             Please respond to                                             

Here is the sql syntax

CREATE TABLE dwstg.mrh_tmp NOLOGGING TABLESPACE dwsmld AS SELECT a.*, DE CODE(RANK() OVER (PARTITION BY mrhky, cliky order by change_date desc, ROWID des
c NULLS LAST) ,1,1,0) as DW_CURR_ROW_IND FROM dwstg.mrh_gt a

trace and a run thru the hotsos profiler only tells me that it used all CPU. very neglible recursive sql or i/o waits of any sort.

The execution plan isn't complicated, its always been a full table scan of the mrh_gt table.

-----Original Message-----
From: Bobak, Mark [] Sent: Monday, November 02, 2009 1:24 PM
To: Josh Collier; Greg Rahn
Subject: RE: poor clob performance with very high CPU


Without much to go on, I'll just say that, given lots of CPU consumption, I'd be looking at the "S" in CTAS. That is, look at your select statement. If your execution plan has gone south in some way, it's easy to burn a *lot* of CPU due to a very poor execution plan. For example, not using an index, or only partially utilizing it, could cause lots more buffer gets than would optimally be required, particularly if there's a nested loops join.

If you have a specific, focused piece of SQL that you know is causing a problem, doing a SQL_TRACE (alter session set events '10046 trace name context forever, level 8';) and then running the results through TkProf or Hotsos profiler, or one of the freely available profilers, may get you a better picture of what's happening than looking at instance level summaries like AWR.

Do you know what the execution plan was, when your process ran in 180 minutes? Even if you don't, you may be able to look at the current execution plan, and be able to determine where it's going wrong.

If you need help understanding/interpreting it, please post it here, along w/ the SQL statement.

Finally, if you're taking the execution plan of just the select portion of your CTAS, you may need to add an "ALL_ROWS" hint. If you do "select ... from tab1, tab2 where ....", that statement will be optimized based on (among other things), the value of "OPTIMIZER_MODE". But, if you look at execution plan of "create table blah as select ... from tab1, tab2 where .....", well, in that case, the optimizer can see that an ALL_ROWS strategy is best, and will optimize using that strategy, regardless of the value of OPTIMIZER_MODE. My point is, if you look at a CTAS, and you want to know the execution plan of the select statement, make sure you do "explain plan for create table blah as select .... from tab1, tab2 where ....." or do "explain plan for select /*+ ALL_ROWS */ .... from tab1, tab2 where .....", or you may get inconsistent results.

Hope that helps,


-----Original Message-----
From: [] On Behalf Of Josh Collier
Sent: Monday, November 02, 2009 3:57 PM
To: Greg Rahn
Subject: RE: poor clob performance with very high CPU

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
> 180 minutes. Recently it started taking > 5 hours. The data and rowcounts
> are very similar, the trace shows only CPU consumption and very little
> 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 Tue Nov 03 2009 - 01:08:41 CST

Original text of this message