Re: Performance problems after moving to new hardware
Date: Wed, 4 Mar 2015 16:48:24 +0100
Message-ID: <CAJ2-Qb_yz0fWBuhpcx1xPKSntQMC9yMyQL=CUH6pw5raweDi0w_at_mail.gmail.com>
I cant read anything useful, cant you format the output or paste a screenshot :-?
On Wed, Mar 4, 2015 at 4:28 PM, Sandra Becker <sbecker6925_at_gmail.com> wrote:
> 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_at_gmail.com>
> 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_at_gmail.com>
>> 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_at_gmail.com>
>>> 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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 04 2015 - 16:48:24 CET