oracle 进程CPU使用过高，求原因，谢谢!
Topas Monitor for host: SZQX99813 EVENTS/QUEUES FILE/TTY
Fri Jul 15 11:03:10 2011 Interval: 2 Cswitch 12424 Readch 6466.6K
Syscall 82534 Writech 3350.6K
Kernel 8.5 |### | Reads 6867 Rawin 0
User 45.3 |############# | Writes 6441 Ttyout 403
Wait 1.5 |# | Forks 10 Igets 0
Idle 44.6 |############# | Execs 5 Namei 814
Runqueue 2.5 Dirblk 0
Network KBPS I-Pack O-Pack KB-In KB-Out Waitqueue 0.0
en1 6843.8 8107.0 6656.5 3179.2 3664.6
lo0 0.1 1.0 1.0 0.0 0.0 PAGING MEMORY
en0 0.0 0.0 0.0 0.0 0.0 Faults 8888 Real,MB 31616
Steals 0 % Comp 27.6
Disk Busy% KBPS TPS KB-Read KB-Writ PgspIn 0 % Noncomp 32.1
updisk0 69.5 8862.0 613.5 176.0 8686.0 PgspOut 0 % Client 32.1
updisk1 11.0 4504.0 161.5 2464.0 2040.0 PageIn 0
updisk2 13.0 400.0 25.0 264.0 136.0 PageOut 0 PAGING SPACE
hdisk1 0.0 0.0 0.0 0.0 0.0 Sios 0 Size,MB 8192
hdisk0 0.0 0.0 0.0 0.0 0.0 % Used 0.9
updisk4 0.0 0.0 0.0 0.0 0.0 NFS (calls/sec) % Free 99.0
cd0 0.0 0.0 0.0 0.0 0.0 ServerV2 0
updisk3 0.0 0.0 0.0 0.0 0.0 ClientV2 0 Press:
ServerV3 0 "h" for help
Name PID CPU% PgSp Owner ClientV3 0 "q" to quit
oracle 766212 24.6 5.2 #
oracle 524626 4.7 179.6 #
oracle 663718 3.7 6.1 #
oracle 655522 3.0 6.3 #
oracle 422064 2.9 6.0 #
oracle 803138 2.5 26.7 #
oracle 778724 2.5 6.1 #
oracle 459234 1.5 5.9 #
oracle 372752 1.2 4.9 #
oracle 585870 0.8 4.9 #
oracle 561188 0.8 4.8 #
oracle 799064 0.7 4.4 #
oracle 307282 0.5 5.3 #
tnslsnr 217592 0.5 13.4 #
oracle 233980 0.5 27.1 #
oracle 659696 0.3 5.1 #
oracle 430458 0.2 4.2 #
oracle 504208 0.2 5.8 #
oracle 356438 0.1 5.3 #
oracle 201190 0.1 68.9 #
System Model: IBM,8204-E8A
Machine Serial Number: 0648DD6
Processor Type: PowerPC_POWER6
Number Of Processors: 4
Processor Clock Speed: 3503 MHz
CPU Type: 64-bit
Kernel Type: 64-bit
LPAR Info: 1 06-48DD6
Memory Size: 31616 MB
Good Memory Size: 31616 MB
Platform Firmware level: EL350_085
Firmware Version: IBM,EL350_085
Console Login: enable
Auto Restart: true
Full Core: false
Volume Groups Information
==============================================================================
rootvg:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
hdisk0 active 546 217 109..12..00..00..96
hdisk1 active 546 109 13..00..00..00..96
==============================================================================
oravg:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
updisk0 active 2899 61 00..06..00..00..55
updisk1 active 2899 0 00..00..00..00..00
updisk2 active 2499 86 07..00..00..00..79
==============================================================================
datavg:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
updisk3 active 2499 1859 244..116..499..500..500
updisk4 active 2499 2115 500..116..499..500..500
==============================================================================
SQL> show sga
Total System Global Area 1.7180E+10 bytes
Fixed Size 2114248 bytes
Variable Size 1.5485E+10 bytes
Database Buffers 1677721600 bytes
Redo Buffers 14659584 bytes
SQL> exit
Fri Jul 15 11:03:10 2011 Interval: 2 Cswitch 12424 Readch 6466.6K
Syscall 82534 Writech 3350.6K
Kernel 8.5 |### | Reads 6867 Rawin 0
User 45.3 |############# | Writes 6441 Ttyout 403
Wait 1.5 |# | Forks 10 Igets 0
Idle 44.6 |############# | Execs 5 Namei 814
Runqueue 2.5 Dirblk 0
Network KBPS I-Pack O-Pack KB-In KB-Out Waitqueue 0.0
en1 6843.8 8107.0 6656.5 3179.2 3664.6
lo0 0.1 1.0 1.0 0.0 0.0 PAGING MEMORY
en0 0.0 0.0 0.0 0.0 0.0 Faults 8888 Real,MB 31616
Steals 0 % Comp 27.6
Disk Busy% KBPS TPS KB-Read KB-Writ PgspIn 0 % Noncomp 32.1
updisk0 69.5 8862.0 613.5 176.0 8686.0 PgspOut 0 % Client 32.1
updisk1 11.0 4504.0 161.5 2464.0 2040.0 PageIn 0
updisk2 13.0 400.0 25.0 264.0 136.0 PageOut 0 PAGING SPACE
hdisk1 0.0 0.0 0.0 0.0 0.0 Sios 0 Size,MB 8192
hdisk0 0.0 0.0 0.0 0.0 0.0 % Used 0.9
updisk4 0.0 0.0 0.0 0.0 0.0 NFS (calls/sec) % Free 99.0
cd0 0.0 0.0 0.0 0.0 0.0 ServerV2 0
updisk3 0.0 0.0 0.0 0.0 0.0 ClientV2 0 Press:
ServerV3 0 "h" for help
Name PID CPU% PgSp Owner ClientV3 0 "q" to quit
oracle 766212 24.6 5.2 #
oracle 524626 4.7 179.6 #
oracle 663718 3.7 6.1 #
oracle 655522 3.0 6.3 #
oracle 422064 2.9 6.0 #
oracle 803138 2.5 26.7 #
oracle 778724 2.5 6.1 #
oracle 459234 1.5 5.9 #
oracle 372752 1.2 4.9 #
oracle 585870 0.8 4.9 #
oracle 561188 0.8 4.8 #
oracle 799064 0.7 4.4 #
oracle 307282 0.5 5.3 #
tnslsnr 217592 0.5 13.4 #
oracle 233980 0.5 27.1 #
oracle 659696 0.3 5.1 #
oracle 430458 0.2 4.2 #
oracle 504208 0.2 5.8 #
oracle 356438 0.1 5.3 #
oracle 201190 0.1 68.9 #
System Model: IBM,8204-E8A
Machine Serial Number: 0648DD6
Processor Type: PowerPC_POWER6
Number Of Processors: 4
Processor Clock Speed: 3503 MHz
CPU Type: 64-bit
Kernel Type: 64-bit
LPAR Info: 1 06-48DD6
Memory Size: 31616 MB
Good Memory Size: 31616 MB
Platform Firmware level: EL350_085
Firmware Version: IBM,EL350_085
Console Login: enable
Auto Restart: true
Full Core: false
Volume Groups Information
==============================================================================
rootvg:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
hdisk0 active 546 217 109..12..00..00..96
hdisk1 active 546 109 13..00..00..00..96
==============================================================================
oravg:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
updisk0 active 2899 61 00..06..00..00..55
updisk1 active 2899 0 00..00..00..00..00
updisk2 active 2499 86 07..00..00..00..79
==============================================================================
datavg:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
updisk3 active 2499 1859 244..116..499..500..500
updisk4 active 2499 2115 500..116..499..500..500
==============================================================================
SQL> show sga
Total System Global Area 1.7180E+10 bytes
Fixed Size 2114248 bytes
Variable Size 1.5485E+10 bytes
Database Buffers 1677721600 bytes
Redo Buffers 14659584 bytes
SQL> exit
23同行回答
1. 找出占cpu的进程的PID
2. 找到存在问题的进程信息
ps -ef |grep pid
3. 捕获存在问题的SQL语句
select /*+ ORDERED */
sql_text
from v$sqltext a
where(a.HASH_VALUE,a.ADDRESS) in (
select decode(sql_hash_value,0,prev_hash_value,sql_hash_value),
decode(sql_hash_value,0,prev_sql_addr,sql_address)
from v$session b
where b.PADDR=(select addr from v$process c where c.spid='&pid'))
order by piece ASC;
看看是否需要优化SQL收起
2. 找到存在问题的进程信息
ps -ef |grep pid
3. 捕获存在问题的SQL语句
select /*+ ORDERED */
sql_text
from v$sqltext a
where(a.HASH_VALUE,a.ADDRESS) in (
select decode(sql_hash_value,0,prev_hash_value,sql_hash_value),
decode(sql_hash_value,0,prev_sql_addr,sql_address)
from v$session b
where b.PADDR=(select addr from v$process c where c.spid='&pid'))
order by piece ASC;
看看是否需要优化SQL收起
浏览1258