RE: poor clob performance with very high CPU
- From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Nov 2009 14:02:44 -0800
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@xxxxxxxxxxxx]
Sent: Monday, November 02, 2009 1:24 PM
To: Josh Collier; Greg Rahn
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Josh Collier
Sent: Monday, November 02, 2009 3:57 PM
To: Greg Rahn
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx]
Sent: Monday, November 02, 2009 11:38 AM
To: Josh Collier
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
