RE: Performance problems after moving to new hardware
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <rajendra.pande@xxxxxxx>, <sbecker6925@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Mar 2015 09:55:38 -0500
nods, re what LS Cheng and Rajendra have already written.
You wrote that you can't confirm the previous behavior. Is the old system
gone? You apparently know the particular query in question, so why can't you
just run it? Unless you're both preserving the last state of the old system
AND the query does an update such that you cannot prevent any eventual
commit, I don't see the problem with a diagnostic.
One other observation: you mentioned that you share storage with a lot of
other production databases. Therefore, unless you can contrast the behavior
of this query on the new system when the other production databases are idle
and no i/o intensive activities that have to do with the storage resource,
you cannot isolate performance to your database and your query. Unless of
course you're segregated on difference pieces of the storage machinery
throughout the stack to the spinning rust.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of rajendra.pande@xxxxxxx
Sent: Wednesday, March 04, 2015 9:07 AM
To: sbecker6925@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Performance problems after moving to new hardware
Looks like a tough one
Reasons I say it is I am not sure how IO calibration really solves the
issue? As I see it - it does not
Also it appears that someone is looking for a quick win without making any
effort J
The only real options are to (a) quickly go back and confirm the performance
on the old system (b) Proactively solve the problem as it exists
That said I believe and have no way to demonstrate it, that you should be
able to get some details around IO stats and table access from the history
views like dba_hist_filestatxs without doing an IO caliberation
Good luck !!
Regards
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Sandra Becker
Sent: Wednesday, March 04, 2015 8:26 AM
To: oracle-l
Subject: Performance problems after moving to new hardware
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:
