| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: CPU upgrade caused application slow down
Is your shop using system statistics (dbms_stats) in its plans. If so did
you regenerate them after you up upgraded the cpu?
If the cpu's are faster then more data can be rushed to the end-user network which can only handle the same load as before the cpu upgrade since I gather you only increased the speed to the disk and not to and from the customer IP's.
You mentioned rebuilding one large table, which brings up the possibility that some other objects also received maintenance during this time period. The resulting new statistics or failure to update statistics for affected objects may also be resulting in some less than optimal plans. This might be worth looking at since individual tests you ran show improvement.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of zhu chao
Sent: Friday, April 30, 2004 5:42 AM
To: oracle-l_at_freelists.org
Subject: CPU upgrade caused application slow down
Hi, list friends:
We did a server upgrade this month. And we hit something we cannot understand. So we refer to you guys for help , hoping you have similar experienceJ
We did a server upgrade this month. Biddb upgrade from 8*900MHZ FireV880 to 8*1200MHZ Fire V880, SAN HBA card also upgraded from 1gb to 2gb.
System Level parameter like /etc/system, oracle initsid.ora are same for biddb before and after server upgrade.(except for processes increased from 800 to 1200).SGA of database is reduced from 11.5GB to 11GB.But disk read did not increase as we reorganized a table with 29M records to 13M records(CTAS, drop old table).
The upgrade result is good, CPU usage dropped about
20%-30% percent. But the application response time does not drop. It even
slow down slightly. :(. This is not what we expect. Statspack does not
show much difference before and after upgrade(load profile , wait event
etc).
Later we did some test, hoping to find out how can this happen, but we cannot draw a conclusion from our test result.
1.. According to Cary Millsap!?s theory, upgrade CPU *CAN* make performance worse. In his case, SQL*Net was the bottleneck. Our server network traffic is only at 15-20Mb/Second. This seems not like the bottleneck, Though from 10046 trace report, sqlnet wait is the NO.1 wait event, but this is normal for most applications. I also tried to change the tnsnames.ora and listener.ora with larger SDU/TDU of 8KB, restarted tuxedo service and oracle listener. And compare the performance data leter. This does not make much difference for application response time.
2.. We did some pure simple SQL test. Result in appendix 1. SAME SQL in 1200MHZ CPU does run faster.
2.. We write a simple tuxedo service run the same SQL for 1000 times. Everytime the SQL is transferred through SQL*Net and result is fetched into host variable. The result still shows that it runs faster on 1200MHZ CPU. The average response time in 1200MHZ server is 12.12ms ,and the average response time in 900mhz server is 14.20ms.
We cannot find out the root cause of application slow down. Though the slow down is very small (around 5% percent, maybe), it is not happy to see such result and we are interested in knowing why.
If you have similar experience before and do find out how this can happen, we would be very happy.
Thanks for your time.
Regards
Zhu Chao.
Appendix 1:
Eachdb2: (900mhz CPU)
1 declare v_result number;
2 begin
3 for x in 1..1000 loop
4 select count(*) into v_result from testsystem;
5 end loop;
6* end;
09:38:13 7 /
PL/SQL procedure successfully completed.
Elapsed: 00:02:17.37
09:40:31 SQL> Biddb (1200MHZ CPU)
1 declare v_result number;
2 begin
3 for x in 1..1000 loop
4 select count(*) into v_result from testsystem;
5 end loop;
6* end;
09:42:29 SQL> 09:42:30 SQL> 09:42:30 SQL> / PL/SQL procedure successfully completed.
Elapsed: 00:01:57.99
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |