Re: poor clob performance with very high CPU
Date: Tue, 3 Nov 2009 22:11:11 +0800
Message-ID: <4602f23c0911030611i6f0ed351ydebdeeda54945ce2_at_mail.gmail.com>
When the wait interface and SQL trace don't provide enough detail then the next two steps to look into are:
- Session performance counters - what do they say? How many LIOs per second they report etc - you can use Snapper for this
- 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:
Tanel.
On Tue, Nov 3, 2009 at 6:02 AM, Josh Collier <Josh.Collier_at_banfield.net>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_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
>
>
>
-- Tanel Poder http://blog.tanelpoder.com -- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 03 2009 - 08:11:11 CST