Re: Performance problems after moving to new hardware
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: sbecker6925@xxxxxxxxx
- Date: Wed, 4 Mar 2015 14:49:10 +0100
hi
may be you can share with us awr from before and after the changes?
thanks
On Wed, Mar 4, 2015 at 2:25 PM, Sandra Becker <sbecker6925@xxxxxxxxx> 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
>
Other related posts:
