Re: Performance problems after moving to new hardware

Sandy

On Thu, Mar 5, 2015 at 3:40 PM, Ls Cheng <exriscer_at_gmail.com> wrote:

> you can run addm using the script
>
> ?/rdbms/admin/addmrpt.sql
>
> On Thu, Mar 5, 2015 at 9:19 PM, Sandra Becker <sbecker6925_at_gmail.com>
> wrote:
>
>> Oracle support had me run the IO calibration then gather system stats.
>> They said the new stats look good. They now want us to run our queries
>> then provide them with AWR and ADDM reports. Not sure how to get the ADDM
>> report yet since we don't have OEM set up yet, but I'll figure it out. I
>> will definitely look at the doc you recommended.
>>
>> Sandy
>>
>> On Thu, Mar 5, 2015 at 11:02 AM, Wayne Smith <wts_at_maine.edu> wrote:
>>
>>> Sorry if I missed another suggestion of this in this thread, but ...
>>>
>>> 11.2.0.2 is notorious for a system stats bug where SREADTIM and MREADTIM
>>> are 10,000 times larger than they should be. Once broken, you can
>>> manually (using DBMS_STATS.SET_SYSTEM_STATS ) set these values to more
>>> sane value
>>> s
>>> (10,000 times smaller) to avoid the terrible performance that can
>>> occur. See doc 9842771.8.
>>>
>>> Cheers, Wayne
>>>
>>> On Thu, Mar 5, 2015 at 10:11 AM, Sandra Becker <sbecker6925_at_gmail.com>
>>> wrote:
>>>
>>>> Correct, the new server waits significantly longer than the old
>>>> server. I have a ticket open with Oracle support. At this point, we're
>>>> leaning towards the server configuration rather than the storage. We
>>>> migrated our lower environment databases to the same type of server and
>>>> simply detached the storage from the old server and attached it to the new
>>>> server. They are seeing the same problem in the lower environments.
>>>>
>>>> Since we have moved several production databases to the new hardware, I
>>>> want to run a few AWR reports on the other databases migrated to this
>>>> server to see what the waits are, regardless of the fact no one is
>>>> reporting issues with any other database.
>>>>
>>>> Sandy
>>>>
>>>> On Thu, Mar 5, 2015 at 1:30 AM, Iliya Peregoudov <iperegudov_at_cboss.ru>
>>>> wrote:
>>>>
>>>>> I think I correctly decrypted AWR stats.
>>>>>
>>>>> AWR from old server
>>>>>
>>>>> Host CPU (CPUs: 32 Cores: 16 Sockets: 4)
>>>>>
>>>>> Event Waits Time(s) Avg wait (ms) % DB time
>>>>> Wait Class
>>>>> ----------------------- ------- ------- ------------- ---------
>>>>> ----------
>>>>> db file parallel read 72,570 4,355 60 50.98
>>>>> User I/O
>>>>> DB CPU 2,092 24.49
>>>>> db file sequential read 387,105 1,308 3 15.31
>>>>> User I/O
>>>>> direct path write temp 3,227 509 158 5.96
>>>>> User I/O
>>>>> db file scattered read 133,051 236 2 2.27
>>>>> User I/O
>>>>>
>>>>> Snap Time Load %busy %user %sys %idle %iowait
>>>>> --------------- ----- ----- ----- ----- ----- -------
>>>>> 24-Feb 10:00:42 1.06
>>>>> 24-Feb 11:00:59 2.02 4.40 1.74 2.66 95.60 0.00
>>>>>
>>>>> AWR from new server
>>>>>
>>>>> Host CPU (CPUs: 32 Cores: 4 Sockets: 1)
>>>>>
>>>>> Event Waits Time(s) Avg wait (ms) % DB time
>>>>> Wait Class
>>>>> ----------------------- ------- ------- ------------- ---------
>>>>> ----------
>>>>> db file parallel read 46,337 18,808 406 43.47
>>>>> User I/O
>>>>> db file sequential read 154,062 6,861 45 15.86
>>>>> User I/O
>>>>> direct path write temp 8,394 3,203 382 7.40
>>>>> User I/O
>>>>> log file sync 3,002 1,564 521 3.61
>>>>> Commit
>>>>> DB CPU 1,433 3.31
>>>>>
>>>>> Snap Time Load %busy %user %sys %idle %iowait
>>>>> --------------- ----- ----- ----- ----- ----- -------
>>>>> 03-Mar 10:00:42 2.73
>>>>> 03-Mar 11:00:37 2.95 7.12 4.69 2.43 92.88 0.00
>>>>>
>>>>>
>>>>> New server waits for I/O much more per hour (30k seconds vs 6k
>>>>> seconds). Average read waits are also 10 times larger on new server (406ms
>>>>> vs 60ms, 45ms vs 3ms). CPU on new server is under-loaded I think because of
>>>>> waits. It seems that old server was better balanced in IO/CPU throughput.
>>>>>
>>>>>
>>>>> On 04.03.2015 18:48, Ls Cheng wrote:
>>>>>
>>>>>> I cant read anything useful, cant you format the output or paste a
>>>>>> screenshot :-?
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> http://www.freelists.org/webpage/oracle-l
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Sandy
>>>> GHX
>>>>
>>>
>>>
>>
>>
>> --
>> Sandy
>> GHX
>>
>
>

-- 
Sandy
GHX

--
http://www.freelists.org/webpage/oracle-l