FW: Misleading Wait: db file scattered read ?

From: Ted Coyle <oracle-l_at_webthere.com>
Date: Wed, 20 Jun 2007 12:46:08 -0400
Message-ID: <000c01c7b35a$80e29530$4621a8c0@medecision.com>

Machine Info:

---

v11e for AIX53

 2 - CPUs currently

 2 - CPUs configured

1654 - MHz CPU clock rate

 PowerPC_POWER5 - Processor

  64 bit - Hardware

  64 bit - Kernel

  Dynamic - Logical Partition

  5.3.0.30 ML02 - AIX Kernel Version  

| DB_VERSION: 10.2.0.3.0

| PLATFORM: AIX-Based Systems (64-bit)

| STARTUP: 06/19/2007 17:02:00

| STATUS: OPEN

| OPEN_MODE: READ WRITE

| LOG_MODE: NOARCHIVELOG
  Current wait:

     HOURS|       SID|EVENT                   |        P1|        P2|

P3| WAIT_TIME|SECONDS_IN_WAIT|STATE

----------|----------|------------------------|----------|----------|-------
---|----------|---------------|-------------------

12.4277778|       306|db file scattered read  |        26|     14646|
15|         8|             33|WAITED KNOWN TIME

 

v$session_wait_history:

Count | Event                                                          |

Session

---

---

1     |db file sequential read                                         |

306

2     |latch: cache buffers chains                                     |

306

7     |db file scattered read                                          |

306  

 select status,start_time from v$transaction;

STATUS |START_TIME ACTIVE |06/19/07 22:49:21   I have a process running 12hrs for a rather large INSERT ALL statement on a performance test box.

Query from v$session_wait shows that db file scattered read is the current wait.

There are no other client sessions in the database right now, so concurrency is not an issue.

Disk activity is 0% across the board.

CPU is 100%.

Occasional Cache Buffer Chains Latches indicating hot block but disk reads/writes/utilization is essentially 0.  

I can rip through partition tables using select count(*) tests in seconds throttling disk kbps through the roof, so disk is not slow and checks out fine at all levels.

No other session issues exist and all else on the system appears well.  

So it appears this process is bound on CPU, so why the USER I/O wait class when the disk is really not the bottleneck?

What views could I use to know what is really going on?  

Also any opinions on INSERT ALL pros/cons are appreciated.  

Thanks,

Ted

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 20 2007 - 11:46:08 CDT