topic Re: db performance in Operating System - HP-UX

db performance

william_39 — Wed, 08 Dec 2004 10:24:43 GMT

Hi all,
Major of my filesystem used by oracle database is ~98 % utilized, does this makes any perf. impact on the db.
Practically my query response time has gone down drastically.

Thanks in adv.
William.

Re: db performance

Jean-Luc Oudart — Wed, 08 Dec 2004 10:31:50 GMT

Hi William,

1) Would you have performance report form your Oracle instance (statspack)

2) Any OS report (sar -d), Qlen on disk(s) ?

3) How do you mount your fs ?
Do you have OnlineJFS

Regards
Jean-Luc

Re: db performance

Alzhy — Wed, 08 Dec 2004 10:31:54 GMT

DB Filesystems nearing capacity should not impact performance as DB datafiles are mostly static and pre-allocated in the first place.

If you've response time issues

1. Is this the first time you've encountered this issue?
2. Other changes lately? (network, applications, storage, etc?
3. Any increase in the number of users
4. Is your batch overlapping onlines if you've such a scenario?
5. Any DB changes by the DBA?
6. Do you reboot the systems periodically and did you miss those reboots? (Oh yes, unbelivebal but there are still environments out there that simply needs reboots on a periodic basies..)

If you think his is a storage issue - observe I/O performance during periods of "slow response time". Do a :

sar -b 5 10
sar 5 10
vmstat 5 10
swapinfo -atm

Re: db performance

Steven E. Protter — Wed, 08 Dec 2004 10:39:09 GMT

Yes it does. A filesystem needs at least 10% free to perform optimally.

I'd collect some data and compare it to a system running oracle in another state.

See attached script set. It runs background and is very configurable.

SEP

Re: db performance

Alzhy — Wed, 08 Dec 2004 10:45:12 GMT

SEP.. these are DB Filesystems so the concerns about fragmentation and filesystem "breathing room" do not apply. DB Data files on coked filesystems are preallocated and mostly static. Updates are within the allocated extents.. not on the filesystem level.

I allow my DB Filesystems to grow to their maximum - even to 100% as long as I've a few bytes left for VxFS to use to grow the filesystems if I need to.