Re: Performance problems after moving to new hardware
- From: Sandra Becker <sbecker6925@xxxxxxxxx>
- To: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- Date: Wed, 4 Mar 2015 08:28:02 -0700
AWR from old server
Host CPU (CPUs: 32 Cores: 16 Sockets: 4)
Top 5 Timed Foreground Events
EventWaitsTime(s)Avg wait (ms)% DB timeWait Class db file parallel
read72,5704,3556050.98User I/O DB CPU 2,092 24.49 db file sequential
read387,1051,308315.31User I/O direct path write temp3,2275091585.96User
I/O db file scattered read133,0512362 2.27 User I/O
Operating System Statistics - Detail Snap
TimeLoad%busy%user%sys%idle%iowait 24-Feb 10:00:421.06 24-Feb
11:00:592.024.401.742.6695.600.00
AWR from new server
Host CPU (CPUs: 32 Cores: 4 Sockets: 1)
Top 5 Timed Foreground Events
EventWaitsTime(s)Avg wait (ms)% DB timeWait Class db file parallel
read46,33718,80840643.47User I/O db file sequential
read154,0626,8614515.86User I/O direct path write temp8,3943,2033827.40User
I/O log file sync3,0021,5645213.61Commit DB CPU 1,433 3.31
Operating System Statistics - Detail Snap
TimeLoad%busy%user%sys%idle%iowait 03-Mar 10:00:422.73 03-Mar
11:00:372.957.124.692.4392.880.00
I asked the SEs to look at both the server and storage for any errors,
anomalies, etc. They report no issues and the load on the server is lower
than it ever was on the old server, even with two additional databases on
the new server. Part of our consolidation project--fewer but bigger, more
powerful servers. I also asked them to take a look regarding the
information Al suggested regarding the EMC.
Sandy
On Wed, Mar 4, 2015 at 7:47 AM, Sandra Becker <sbecker6925@xxxxxxxxx> wrote:
> 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@xxxxxxxxx>
> 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@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
>>>
>>
>>
>>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
>
> --
> Sandy
> GHX
>
--
Sandy
GHX
Other related posts:
