Re: FW: Misleading Wait: db file scattered read ?
- From: "kyl elf" <kylelf@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxx
- Date: Wed, 20 Jun 2007 10:55:21 -0700
On 10.2 you could get a lot more info by running
@?/rdbms/admin/ashrpt.sql
for the time period of your batch. This script would give you a breakdown of
cpu and wait for the period indicating whether you can tune some waits, or
whether tuning the insert method is the only option.
I don't know what script you used to get the wait event info, but for one
it's point in time which is statistically inaccurate for analyis and two it
says "WAITED KNOWN TIME" which would normally mean it's not waiting but on
the CPU at that point in time.
Best Wishes
Kyle Hailey
http://perfvision.com
On 6/20/07, Ted Coyle <oracle-l@xxxxxxxxxxxx> wrote:
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:
