RE: poor clob performance with very high CPU ~ Solved

Thanks for all the suggestions. I ran truss on the process and found lots of this....

/4:     lwp_park(0x00000000, 0)         (sleeping...)
/5:     lwp_park(0x00000000, 0)         (sleeping...)
/1:     fdsync(273, FSYNC)                              = 0

I searched Metalink for lwp_park and fdsync and found this bug:
Bug No.	6830427
And and this note
Doc ID:	393780.1

Where upon I tested the offending CTAS in a tablespace built upon a mount with the following settings to enable Direct I/O on VxFS:

mincache=direct,convosync=direct

and the thing works faster than ever.

It appears that the settings were lost during a migration to a new set of mounts between the good run and the bad run.

Thanks again!

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Josh Collier Sent: Monday, November 02, 2009 2:03 PM
To: oracle-l_at_freelists.org
Subject: RE: poor clob performance with very high CPU

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 [mailto:Mark.Bobak_at_proquest.com] Sent: Monday, November 02, 2009 1:24 PM
To: Josh Collier; Greg Rahn
Cc: oracle-l_at_freelists.org
Subject: RE: poor clob performance with very high CPU

Josh,

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,

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Josh Collier Sent: Monday, November 02, 2009 3:57 PM
To: Greg Rahn
Cc: oracle-l_at_freelists.org
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 [mailto:greg_at_structureddata.org] Sent: Monday, November 02, 2009 11:38 AM To: Josh Collier
Cc: oracle-l_at_freelists.org
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 <Josh.Collier_at_banfield.net> 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.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l