RE: CPU upgrade caused application slow down
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Apr 2004 08:31:29 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of zhu chao
Sent: Friday, April 30, 2004 5:42 AM
To: oracle-l@xxxxxxxxxxxxx
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
