Re: Performance issue - Not sure if its disk or some thing - How can I narrow down this
- From: "Arul Ramachandran" <contactarul@xxxxxxxxx>
- To: bnsarma@xxxxxxxxx
- Date: Tue, 4 Dec 2007 10:50:33 -0800
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@xxxxxxxxx> 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
Other related posts:
