Re: Performance issue - Not sure if its disk or some thing - How can I narrow down this

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_at_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

--
http://www.freelists.org/webpage/oracle-l