| SUSE Oracle high CPU usgae problem [message #347403] |
Thu, 11 September 2008 09:01  |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
A test system on Linux SUSE x86_64 GNU/Linux 2.6.5-7.97-smp/ Oracle Enterprise Edition 64 bit 10.2.3
#uname -a
Linux XXMACHINE 2.6.5-7.97-smp #1 SMP Fri Jul 2 14:21:59 UTC 2004 x86_64 x86_64 x86_64 GNU/Linux
opatch lsinventory all
Invoking OPatch 10.2.0.3.0
Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /home/oracle/product/10.2.0/db_1
Central Inventory : /home/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.3.0
OUI version : 10.2.0.3.0
OUI location : /home/oracle/product/10.2.0/db_1/oui
Log file location : /home/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch20
08-09-11_12-36-44PM.log
Lsinventory Output file location : /home/oracle/product/10.2.0/db_1/cfgtoollogs/
opatch/lsinv/lsinventory2008-09-11_12-36-44PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
There are 2 products installed in this Oracle Home.
Interim patches (7) :
Patch 6267873 : applied on Thu Sep 11 09:01:10 CST 2008
Created on 19 Feb 2008, 14:06:43 hrs US/Pacific
Bugs fixed:
5723260, 6067600, 6267873, 5575674, 5370578, 5883622, 6017440, 6064864
5087369, 5865842, 5602452, 5581472, 5206649, 5225733, 5170394, 5512840
5515703, 5867943
Patch 5629724 : applied on Thu Sep 11 08:59:38 CST 2008
Created on 29 Dec 2007, 08:36:10 hrs PST8PDT
Bugs fixed:
5629724
Patch 5621677 : applied on Thu Sep 11 08:58:49 CST 2008
Created on 3 Dec 2007, 21:49:25 hrs PST8PDT
Bugs fixed:
5621677
Patch 4905638 : applied on Thu Sep 11 08:55:54 CST 2008
Created on 6 Nov 2007, 06:02:47 hrs PST8PDT
Bugs fixed:
4905638
Patch 4722328 : applied on Thu Sep 11 08:54:34 CST 2008
Created on 1 Aug 2008, 00:35:15 hrs PST8PDT
Bugs fixed:
4722328
Patch 5556081 : applied on Wed Jul 23 10:02:51 CST 2008
Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
Bugs fixed:
5556081
Patch 5557962 : applied on Wed Jul 23 10:02:43 CST 2008
Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
Bugs fixed:
4269423, 5557962, 5528974
--------------------------------------------------------------------------------
OPatch succeeded.
Use a API tool to test a software based on SUSE/Oracle server.
A user login to do a serial create//query/retrieve/delete/update and the n logout, that will cost about 1 minute.
And the response time looks OK to me.
When 20 users do the same test case(start 1 after another every 30 seconds so as to avoid competion), strange scene happen:
From Client side All query take a long time. Create/Delete/Update are not affected.
And Server side is strange
As the server box is 4CPU (3 Intel xeron CPU), 4G memory and I think that is powerful enough.
Use vmstat I can see no page in/out so no memory issule in OS level. But process in queue are 24, far more than CPU number.
Use iostat I can see I distribute Oracle redo logs and Server software on sda,and oracle db files in sbd. and IO are balanced,and I think there are still idle IO in both 2 disks.
Use Top and I saw strange scene, cpu of user usage is 30% but for sys% is 70% and therefor the bottleneck of the system is CPU now.
I get statspack of the system and please check the attachment:
What I need to know is :
1) If this is a tunning issule, why statpack seems OK to me?
2) Why is that sys% > Usr%, Is it only happen on Linux/SUSE?
3) Anyone who are kind please help to check metalink if there are any known bugs of oracle
4) This bundle is planned and should be fixed in 10.2.0.3, so I do not wanna to update to 10.2.0.4, so only patch is OK.
5) API Proplem has bad manner of using Oracle, glad to hear that and hope to find root cause, seems some has to buy me supper 
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SUSE Oracle high CPU usgae problem [message #347493 is a reply to message #347403] |
Thu, 11 September 2008 20:45  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
I am not sure what magic bullet you expect from here.
Your system is CPU bound.
Realize context switching between PL/SQL & SQL is system overhead.
Realize context switching between OS processes is system overhead.
The AWR report shows that for most activity there is only a small delta between elapsed time & CPU time;
which means only a small amount of wait time.
Simply put this benchmark is CPU intensive & your existing system can't keep up with the load.
Doctor, it hurts when I poke myself in the eye.
Doctor, what can I do to stop the pain?
Perhaps you should reduce the load on the system or get a much, much, much faster system.
With free advice, you get what you paid for it.
[Updated on: Thu, 11 September 2008 20:45] by Moderator Report message to a moderator |
|
|
|
| Re: SUSE Oracle high CPU usgae problem [message #347565 is a reply to message #347493] |
Fri, 12 September 2008 02:57 |
crazypeter2005
Messages: 29
Registered: September 2008
Location: Peking China
|
Junior Member |
|
|
The reason I put here is that there are much more data in the DB now, which I did not know.
And I started 30 Virtual user according to my experience.
So the system is definitely over-load.
Yes, CPU is the bottleneck. I look inside oracle parameter, look out side OS CPU/MEM/DISKIO, just forget to look at the data size.
It is a lesson that I should learn from.
Thanks for explainning this, I appreciate.
Have a good day!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
