Re: Performance problems after moving to new hardware
Date: Wed, 4 Mar 2015 07:47:51 -0700
Message-ID: <CAJzM94AdmFvmmamdtD7gUfmgrXCugSbyr73j97kNvDgpdsWa+g_at_mail.gmail.com>
Unfortunately, I cannot share the entire AWR report with you due to company policy. I do see that avg wait ms is higher for reads and writes now. I have another fire I have to fight, but will share those stats as soon as possible.
We do have a license for the tuning pack. I'll have to do it manually since I wasn't allowed to set up grid in the new environment. Waiting on DatAvail to come in and handle that maybe next week.
Some very good suggestions that I will work on. Thank you.
On Wed, Mar 4, 2015 at 7:24 AM, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:
> This does look like a tough one. However, calibrate_io shouldnt break
> anything. If you have the tuning pack, you might want to run the sql
> tuning advisor and see if it makes any suggestions. Also, you might want
> to look at the plan and verify whether or not it is running partition
> pruning. Did you check your init.ora settings and see if their are any
> major differences that could account for the problem?
>
> On Wed, Mar 4, 2015 at 7:25 AM, Sandra Becker <sbecker6925_at_gmail.com>
> wrote:
>
>> OS: Solaris Sparc 10 (64-bit)
>> Oracle: EE 11.2.0.2
>>
>> The OS and Oracle versions are identical on both the old and new
>> servers. Storage attached to the new server is a new EMC disk array.
>> Sorry I don't have any more details on the storage and the only additional
>> information I have on the server is that it is a T5.
>>
>> We created a standby on the new hardware and did a switchover last Friday
>> night. On Saturday I completed gathering stats on the application schema
>> tables as requested by the product manager. As usual, very little activity
>> on this database over the weekend. Yesterday morning we were contacted by
>> internal users that performance was much worse than on the old hardware for
>> a specific query on a really ugly view. A look at the execution plan shows
>> multiple full table scans on some partitioned tables, some very large.
>> There are about 15 tables joined to create the view, some more than once.
>> They claim the view is no longer doing partition pruning, as it did before
>> the switchover. I can't prove that it was/wasn't exhibiting this behavior
>> before the switchover. They are insisting we run I/O calibration. I'm not
>> familiar with it so I went to the docs. This database shares storage with
>> quite a few production databases so I want to be very careful how I go
>> about this.
>>
>> Questions:
>>
>> 1. What will running the I/O calibration do? Does it only provide
>> information on the I/O subsystem, or does it change the way the optimizer
>> behaves? The development team insists it will improve performance.
>> 2. I've looked at AWR reports before/after the switchover and see that
>> the query in question was doing a similar amount of I/O in both reports.
>> Is there any way for me to get more detail on the before execution plan?
>> 3. One of the large partitioned tables has no indexes. Would creating
>> an index be of any benefit? I understand that it's possible to negatively
>> affect other queries, so it should be considered with caution. Development
>> insists that indexing would be a waste of time and definitely cause
>> problems, although they have never tested it.
>> 4. I want to trace the query, but it runs in parallel and produces more
>> trace data that I have available disk to handle. Is there anything I can
>> do on that front to get a trace I can feed into my Method-R tool and supply
>> to oracle support?
>>
>> As I reviewed how the view, I recall them having issues with it before
>> and me suggesting it should be optimized. I was told no and here we are
>> again. The obvious concern is that the results would be different and
>> changes require a lot of testing they don't have time to do. Any other
>> recommendations would be appreciated.
>>
>> --
>> Sandy
>> GHX
>>
>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
-- Sandy GHX -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 04 2015 - 15:47:51 CET