DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
        b=cQEF5S31Esqcm3lLs4fkqWDfv39mR2vAQZqVQEtj9YKoBSxB0QV9wtpWj3YpCfyWXPbviDHmY5pC7r0O72G51Mo76QTJc+QxJxFifmUrc2eFbP1Wz30kAKn+/Zv5kq8+o3iO9wvkj6N0RLY+ZPoEKoejdaaskT/9OmX1SJcKOoI=
Message-ID: <1c1a62990712041050o271ea216jbea4d2742dd360e0@mail.gmail.com>
Date: Tue, 4 Dec 2007 10:50:33 -0800
From: "Arul Ramachandran" <contactarul@gmail.com>
To: bnsarma@gmail.com
Subject: Re: Performance issue - Not sure if its disk or some thing - How can I narrow down this
Cc: oracle-l <oracle-l@freelists.org>
In-Reply-To: <61292a9d0712040913p169e8ecbv8d648840269b7dd9@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_13908_25031175.1196794233487"
References: <61292a9d0712040913p169e8ecbv8d648840269b7dd9@mail.gmail.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: contactarul@gmail.com
------=_Part_13908_25031175.1196794233487
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

My 2 cents -

1. Identify if there were any system configuration changes made recently, if
any changes were done to disk storage config recently. If yes, then
investigate this - this could be a potential cause.

2. Pick the top few sqls and their hash value from statspack based on "Gets
per Exec" ( you can also compare statspack for a previous day/similar time
when the system was healthy )

3. You can either use this to get the plan from v$sql_plan,
v$sql_plan_statistics_all or you can run the sql to generate tkprof on 10046
trace/level 8 or 12 to check for row source operations and cpu, elapsed and
the wait events. (if the cpu and elapsed are way off, look at where the sql
is waiting)

4. What I am trying to get is, if the execution plan of the few top sqls are
efficient or not.

5. If the execution plan is not efficient or they have changed, investigate
this

6. If the execution plan is efficient, then see from the tkprof where the
sql is waiting on.


Thanks,
Arul


On Dec 4, 2007 9:13 AM, BN <bnsarma@gmail.com> wrote:

> Greetings
>
> Oracle 9i AIX 5L we are using SAN Stroage
>
> I see quite a few SQL in the STATSPACK report with high  Elapsed time,
>
> I notice the following:
>
> 1. Disk is 100% bussy - topas report
> 2. sar -d report  - Service time is  less than 20 msec, disks show 99 to
> 100% busy
> 3. STATSPACK Report for file io shows 3 digit numbers for Av rd (ms)
>
> 4. STATSPACK Report TOP SQL Shows high Elapsed time.
>     Some of the SQL are doing FTS
> What other stats should I look at to narrow down the issue
>
> Should I go behind Tuning the Queries or Storage, spreading datafiles
> across multiple disks.
>
> Apprecaite your thoughts
>
>
>
>
>
>
> --
> Regards & Thanks
> BN
>


-- 
Arul

------=_Part_13908_25031175.1196794233487
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

My 2 cents -<br><br>1. Identify if there were any system configuration changes made recently, if any changes were done to disk storage config recently. If yes, then investigate this - this could be a potential cause.<br><br>
2. Pick the top few sqls and their hash value from statspack based on &quot;Gets per Exec&quot; ( you can also compare statspack for a previous day/similar time when the system was healthy )<br><br>3. You can either use this to get the plan from v$sql_plan, v$sql_plan_statistics_all or you can run the sql to generate tkprof on 10046 trace/level 8 or 12 to check for row source operations and cpu, elapsed and the wait events. (if the cpu and elapsed are way off, look at where the sql is waiting)
<br><br>4. What I am trying to get is, if the execution plan of the few top sqls are efficient or not.<br><br>5. If the execution plan is not efficient or they have changed, investigate this<br><br>6. If the execution plan is efficient, then see from the tkprof where the sql is waiting on.
<br><br><br>Thanks,<br>Arul<br><br><br><div class="gmail_quote">On Dec 4, 2007 9:13 AM, BN &lt;<a href="mailto:bnsarma@gmail.com">bnsarma@gmail.com</a>&gt; wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>Greetings </div>
<div>&nbsp;</div>
<div>Oracle 9i AIX 5L we are using SAN Stroage</div>
<div>&nbsp;</div>
<div>I see quite a few SQL in the STATSPACK report with high&nbsp; Elapsed time, </div>
<div>&nbsp;</div>
<div>I notice the following:</div>
<div>&nbsp;</div>
<div>1. Disk is 100% bussy - topas report</div>
<div>2. sar -d report&nbsp; - Service time is&nbsp; less than 20 msec, disks show 99 to 100% busy </div>
<div>3. STATSPACK Report for file io shows 3 digit numbers for Av rd (ms)</div>
<div>&nbsp;</div>
<div>4. STATSPACK Report TOP SQL Shows high Elapsed time.</div>
<div>&nbsp;&nbsp;&nbsp; Some of the SQL are doing FTS</div>
<div>What other stats should I look at to narrow down the issue</div>
<div>&nbsp;</div>
<div>Should I go behind Tuning the Queries or Storage, spreading datafiles across multiple disks.</div>
<div>&nbsp;</div>
<div>Apprecaite your thoughts</div>
<div>&nbsp;</div>
<div>&nbsp;</div>
<div>&nbsp;</div>
<div>&nbsp;</div>
<div><br clear="all"><br>-- <br>Regards &amp; Thanks<br>BN </div>
</blockquote></div><br><br clear="all"><br>-- <br>Arul

------=_Part_13908_25031175.1196794233487--
--
http://www.freelists.org/webpage/oracle-l