Re: Performance problems after moving to new hardware
Date: Wed, 4 Mar 2015 11:55:41 -0700
Message-ID: <CAJzM94BngPQhT6HRUphvOTxGEQVv_AuzZ1j_z9YDr-tNbn8NqA_at_mail.gmail.com>
Unfortunately, upgrading is not an option for another 2-3 months, if not longer.
Sandy
On Wed, Mar 4, 2015 at 11:42 AM, Mladen Gogala <dmarc-noreply_at_freelists.org> wrote:
> On 03/04/2015 08:25 AM, Sandra Becker 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
>>
>
> Hi Sandra,
> Calibrate only determines automatic degree of parallelism. I would gather
> new system statistics first. However, there was a bug in 11.2.0.2 with
> system statistics values being off chart. Can you upgrade to 11.2.0.4? You
> are using an old and unsupported version.
>
>
> --
> Mladen Gogala
> Oracle DBA
> http://mgogala.freehostia.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Sandy GHX -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 04 2015 - 19:55:41 CET