Re: poor clob performance with very high CPU
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: Josh.Collier@xxxxxxxxxxxx
- Date: Tue, 3 Nov 2009 22:11:11 +0800
When the wait interface and SQL trace don't provide enough detail then the
next two steps to look into are:
1) Session performance counters - what do they say? How many LIOs per second
they report etc - you can use Snapper for this
2) See what exactly Oracle is doing by looking at which functions happen to
be on CPU the most - as you're on Solaris 10 then you can use my DStackProf
script for that (which uses DTrace internally). This gives important
evidence in which layer/module the CPU time is wasted and if we are dealing
with a bug then knowing the offending function names helps to search in
metalink better.
You can run both commands and send the output to me...
Link to DStackProf is here:
http://blog.tanelpoder.com/2008/09/02/oracle-hidden-costs-revealed-part2-using-dtrace-to-find-why-writes-in-system-tablespace-are-slower-than-in-others/
Tanel.
On Tue, Nov 3, 2009 at 6:02 AM, Josh Collier <Josh.Collier@xxxxxxxxxxxx>wrote:
> 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
>
>
>
--
Tanel Poder
http://blog.tanelpoder.com
Other related posts:
