RE: poor clob performance with very high CPU
- From: Laimutis.Nedzinskas@xxxxxx
- To: Josh.Collier@xxxxxxxxxxxx
- Date: Tue, 3 Nov 2009 09:08:41 +0200
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@ban
field.net> To
Sent by: "oracle-l@xxxxxxxxxxxxx"
oracle-l-bounce@f <oracle-l@xxxxxxxxxxxxx>
reelists.org cc
Subject
2009.11.03 00:04 RE: poor clob performance with very
high CPU
Please respond to
Josh.Collier@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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
