Batch operation performance Impact diagnostics in Oracle

Branch of the database performance problems, help to help diagnose.

The database is a Oracle10.2.0.3 RAC environment, but is now in a single node working state. The current problem is that page access is slow and no further information is available.

According to the description, the landing server first checked the system's health by using top, and found that the system was not very busy:

bash-3.00$/usr/local/bin/top

Load averages:1.76, 1.63, 1.59; Up 19+23:45:48 16:12:20

194 processes:189 sleeping, 5 on CPU

CPU states:78.6% Idle, 17.5% user, 3.9% kernel, 0.0% iowait, 0.0% swap

Memory:32gphys mem,8755mfree Mem,31gswap,31gfree Swap

PID USERNAME LWP PRI Nice SIZE RES the state time CPU COMMAND

1151 Oracle 1 1 0 20G 20Gcpu 0:19 27.59% Oracle

26096 Oracle 0 20G 20Gsleep 3:07 24.00% Oracle

1177 Oracle 0 20G 20Gcpu 2:17 20.57% Oracle

1187 Oracle 0 20G 20Gcpu 2:03 18.42% Oracle

25298 Oracle 0 20G 20Gsleep 3:06 15.12% Oracle

1557 Oracle 1 0 20G 20Gsleep 1:57 13.64% Oracle

27086 Oracle 0 20G 20Gsleep 3:15 10.45% Oracle

1175 Oracle 1 0 20G 20Gsleep 0:19 6.12% Oracle

1551 Oracle 1 0 20G 20Gcpu 1:28 4.56% Oracle

27076 Oracle 1 100-20 20G 20Gsleep 14:56 4.02% Oracle

27080 Oracle 1 100-20 20G 20Gsleep 14:29 3.20% Oracle

27090 Oracle 2 0 20G 20Gsleep 2:48 2.49% Oracle

10672 Oracle 1 0 3800K 2536K CPU 0:00 0.41% Top

27131 Oracle 1 0 20G 20Gsleep 0:19 0.34% Oracle

27123 Oracle 0 20G 20Gsleep 0:59 0.32% Oracle

Iowait is 0, the user part is only 17.5%, and idle reaches nearly 80%, this situation should be the system is very patronagejob.

Depending on the judgment and system pressure, the problem should occur at the database level, and the operating system level is working properly.

bash-3.00$ Sqlplus/as SYSDBA

Sql*plus:release10.2.0.3.0-production on Wednesday September 15 16:12:28 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connect to:

Oracle database10genterprise Edition release10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options

Sql> Select Event, COUNT (*)

2 from V$session

3 where username!= user

4 GROUP BY Event

5 having COUNT (*) > 4;

EVENT COUNT (*)

---------------------------------------------------------------- ----------

Enq:tx-row Lock Contention 7

This column more highlights: http://www.bianceng.cn/database/Oracle/