Tru64 Performance issues

[DSSVB]

ES45 4P 16GB Mem, Oracle DB 500GB size. 10 to 20 users. DB Performance drastically dropped after reboot. CPU's moderate to low use. Can someone recommend changes:

Info:

ipc:
sem-mni = 128
sem-ume = 64
shm-max = 4278190080
shm-min = 1024
shm-mni = 512
shm-seg = 128
ssm-threshold = 0

proc:
max-per-proc-address-space = 8589934592
max-per-proc-data-size = 8589934592
max-per-proc-stack-size = 33554432
max-proc-per-user = 512
maxusers = 1024


inet:
tcp_keepidle = 1200
tcp_keepintvl = 60
generic:
memberid = 0
msgbuf_size = 8192
new_vers_high = 1445664276479072064
new_vers_low = 51969
version_avendor = COMPAQ
version_banner = Compaq Tru64 UNIX
version_product = Tru64 UNIX
version_vendor = Compaq Computer Corporation
rt:
aio-task-max-num = 4096
per-proc-address-space = 8589934592
per-proc-data-size = 8589934592
per-proc-stack-size = 33554432


vm:
new-wire-method = 0
swapdevice = /dev/vol/rootdg/swapvol, /dev/vol/rootdg/swap02
vm-swap-eager = 1
vm-ubcseqpercent = 30

[Rob Leadbeater]

Hi,

Was anything changed before the reboot ?

If not, I'd be reluctant to start altering kernel parameters before identifying what caused the slow down...

Are the any hardware issues ?

Cheers,

Rob

[Kapil Jha]

You say DB performance is slow it implies that DB is slow and not the systm.
If your system is running fine and DB is slow first check with DBA there could be some problem with DB , ask DBA to check oracle performance hit ratio,execution plan etc (m not good at DB).
Please check if some changes were made,any activity.
BR,
Kapil

[Vladimir Fabecic]

First I would check if there were some hardware issues.
Then I would check database.
It is most likely that something is wrong with database.
That kind of machine is really strong enough for 20 oracle users.
What about disk activity? Is is high?

[Hein van den Heuvel]

What Oracle/Tru64 versions?

Looks like you do not have GH sharing for the SGA, nor SSM, faling back on basic, and slow syste,-V sharing.

Fix that!

new-wire-method should probably be 1 also.

Read the whitepaper, and Oracle install notesL

http://h71028.www7.hp.com/ERC/downloads/4AA0-9949ENW.pdf

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Johan Brusche]

The parameter that will always give an improvement is......

vfs:
fifo_do_adaptive = 0


__ Johan.

[Pieter 't Hart]

The OS-config closely works together with the database-configuration.
So check values in sysconfigtab against corresponding items in the oracle-configuration file.

Sometimes a DBA miscalculates memory consumption of the database, not taking into account database-pages of 4Kbyte versus OS-pages of 512 byte or something.

check the boot-record to see all processors are active, (maybe one cpu-down? or one license inactive?
also check the oracle config, for use of multiple processors.

use sys_check (system management home-page) to check and generate some info/advice.

[DSSVB]

Thanks for all your help. Seems the vendor made a few changes without us knowing. Err..... vm-swap 1 to 0, shm-mni 512 to 1024, shm-seg 128 to 256. On reboot we got our suprise! Queries run better but large ones die. Based on reading the doc Hein referenced seems like lots of changes need to be made. Any recommendation on puting them in? Phased or all out? Can they be backed out if the system dumps? Do these new settings look right? Should Max address be that 0X4000.... value?



Our current Settings:
ipc:
shm-max = 4278190080
shm-min = 1024
shm-mni = 1024
shm-seg = 256
ssm-threshold = 0


proc:
max-per-proc-address-space = 8589934592
max-per-proc-data-size = 8589934592
max-per-proc-stack-size = 33554432
max-proc-per-user = 512
maxusers = 1024

rt:
aio-task-max-num = 4096
per-proc-address-space = 8589934592
per-proc-data-size = 8589934592
per-proc-stack-size = 33554432


vm:
new-wire-method = 0
vm-swap-eager = 0
vm-ubcseqpercent = 30



Tunning Document Recommendation
ipc:
shm-max = 4278190080
shm-min = 1
shm-mni = 256
shm-seg = 128
ssm-threshold = 8388608


proc:
max-per-proc-address-space = Phy + swap 16GB + 17.3GB?
max-per-proc-data-size = 4294967296
max-per-proc-stack-size =536870912
max-proc-per-user = 0
maxusers = 8192
Max_threads_per_user= 0



rt:
aio-task-max-num = 8193
per-proc-address-space = Physical + swap 16GB+17.3GB?
per-proc-data-size = 201326592
per-proc-stack-size = 33554432


vm:
new-wire-method = 0
vm-swap-eager = 0
vm-ubcseqpercent = 10

vfs:
fifo_do_adaptive= 0

[DSSVB]

P.S Oracle 9.2.07 and True64 V5.1 Patch 5

[Hein van den Heuvel]

Several are dynamic, but not all.

It doesn't matter much because there is one whiich will probably make a world of difference. Looks like you are indeed running with "ssm-threshold = 0" as suggested. That's VERY bad, performance wise, without alternatives like GH sharing.

Fix that, and be happy.
Try to figure out why adn when that changed.

Also, double check new-wire-method.
Long term it should be 1.
Short term (benchmarks!) we had better luck with 0, but the support setting for Oracle is 1 best I recall (It has been a while since I worked on that document).


Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting