FW: Misleading Wait: db file scattered read ?

  • From: "Ted Coyle" <oracle-l@xxxxxxxxxxxx>
  • To: "'oracle-l'" <Oracle-L@xxxxxxxxxxxxx>
  • Date: Wed, 20 Jun 2007 12:46:08 -0400

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

Other related posts: