RE: poor clob performance with very high CPU
Date: Tue, 3 Nov 2009 09:08:41 +0200
Message-ID: <OFB3903057.4548EBCF-ONC2257663.002711C8-C2257663.00272D7E_at_seb.lt>
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
<Josh.Collier_at_ban
field.net> To
Sent by: "oracle-l_at_freelists.org"
oracle-l-bounce_at_f <oracle-l_at_freelists.org>
reelists.org cc
Subject
2009.11.03 00:04 RE: poor clob performance with very
high CPU
Please respond to
Josh.Collier_at_banf
ield.net
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-lReceived on Tue Nov 03 2009 - 01:08:41 CST