Disk utilization on 9i oracle.

[Charles Temple]

When doing alot of reads on the database. We get the disk utilization 100%. All the .dbf files are in this one filesystem(441 files). I know we need to break them out. I just got into this environment recently. But is there another reason why when we do reads disk util is 100%? Some ideas would be great. Kernel parms?
Thanks in advance,
Charlie

[Charles Temple]

Just a followup. We are running this on an EVA disk solution and the disks for the database are raid 0+1.

[RAC_1]

As you know, ne thing is you need to split the FS over multiple disks.

How is cache set up?? And ststic or dynamic??

[TwoProc]

Charles, you'll need to run a statspack to see what's up with all the reads. My guess is you've got a really good share of full table scans - due to untuned code, missing indexes, etc. Besides fixing the problem that all .dbf files are in one file system - you'll need to tune it up too. Try to get your database on as many drive spindles, controllers, etc. that you can spare. Separate data from indexes, redo-logs from archive-logs, temp files from undo (redo) files, etc.

[Charles Temple]

HI,

The redos and archives are separte filesystems. Just all the .dbf files are in one big filesystem.
There is only 512 cache on those EVA's so it has to go to disk all the time. we have 16gig of memory on the server side.

Also, 90% of connection to the EVA goes over one controller on the EVA side. We have to split that up.

Any other idea's? Is there lock limit it could be hitting with all .dbf files in one directory?

Thanks,
Charlie

[Eric Antunes]

Hi Charles,

What values in $ORACLE_HOME/dbs/init.ora for the following (equivalent in 9i)?

db_block_buffers
shared_pool_size
log_checkpoint_interval
log_buffer
db_file_multiblock_read_count
sort_area_size

What values for dbc_max_pct and dbc_min_pct?? What output for:

#sar -b 6 30


Best Regards,

Eric

[TwoProc]

Try setting the scsi queue depth ( I know it's not real scsi - it uses the same/similar code base for parts of the driver)

scsictl -m queue_depth /dev/rdsk/..
for each LUN for the drives involved in the disk set.

The default is 8 - try 32.

This can be changed interactively - if you like it change it "permanently" via an rc script on startup.

[Hein van den Heuvel]

The disk utilization 100% may not be significant. It's just a visual misread. Try this for a thought: If every time you issue an IO there is an other IO active, then the disk is 100% busy right? Well, if it was a traditional disk that would be true. But if it is an intellegent controller, like the EVA, then it can handle hundred of IOs to the same 'disk' concurrently! IMHO the OS just can not tell you how busy an EVA lun really it. There might be 8 physical drives behind it, there might be 80. That 10x the IO potential with no way to tell from the OS.

RAC>>> As you know, ne thing is you need to split the FS over multiple disks.

I beg to differ. As per above. That mountpoint might have all, or dozens of disks, supporting it. To carve a large lun up into multiple smaller ones is not likely to help all that much (if any)
Of course, as suggested, you need to allow for more than 8 concurrent IO on an EVA lun.

Anyway,

Instead of doing the math in %busy, please try to get a handle on IO/sec and KB/sec and read-write ratios. Then see if those are in a supportable range.

The suggestion for statspack is good. Check out the READ (and write) TIMES in the per file / per tablespace areas. Is it in the very low (1ms) range for writes? (WB cache).
Is is in the lowish ( 3ms - 10ms) for reads?
Those statspack tables will also tell you which things to move, if you still think you need to move stuff around.

hth,
Hein.

[Yoann Mainguy]

I agree with Hein,

The % uilisation on this type of bay is not a good (only) indicator, you should look at.

We have Oracle 9i running on HP-UX 11.11 and an EVA. In Glance our disk utilization is constantly 100%.

In glance try looking at the disk queue length (type u after you start glance). Although our utilizaton is always 100% the queue is always 0. If the EVA was getting behind this would increase.

Also try "sar -d 5 5" and look at avwait and avserv.

Cheers Simon.

[Charles Temple]

Thanks for all the reply's. I did the sar -d 5 5 and got about 6-7 for avwait. Also in glance the NO WAIT is only 85-90% meaning there is a wait of 1-2 about 15% of the time. That means the eva is getting behind right?

scsictl -m queue_depth on the disk(LUN) that is being 100% is at 8. What impact could I cause if I increase that to 32? Also, how would that resolve the disk utilization being at 100%.
Thanks again.