Re: Performance problems after moving to new hardware
- From: Lothar Flatz <l.flatz@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 04 Mar 2015 15:55:11 +0100
Hi Sandra,
send a sql Monitor report from this and you will get answers. Normally
on plan changes a bad cardinality estimate is the root cause. To find
out compare estimated rows versus atctual rows in sql monitor.
That technique is called cardinality feedback. If the cardinality
estimate is wrong I/O calibration won't solve your root cause. It will
just shift cost calculation a bit.
If I/O calibration cures your issue you have a good chance the cure
won't last.
Thanks
Lothar
On 04.03.2015 14:25, 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
--
--
//www.freelists.org/webpage/oracle-l
Other related posts:
