Oracle performance tuning on HP-UX 11.11

[Amit Agarwal_1]

I am supposed to check the oracle (client-server) performance on 11.11 system. though I am aware of OS tuning, but am not aware of anything specific to Oracle. I searched on ITRC, but couldn't find any useful thread which cangive me alink to any good doc on this. In one of the post someone said that Bill Hassel has written a good document about this, but couldn't find that docuemnt itself.

Since the real server and applications are in real life use, initially the tuning has to be done on test ring which is suppose to emulate the real life transactions. If succesfful, these tuning would be taken over in the actual ring. As of now I have a high level idea of client-server structure that they have. The database server can communicate with app server only. There is one set of client which performs transaction through this app server i.e. 2 level below db server (db server --> app server --> clients). There is another set of clients which communicates with app server though a web server i.e. 3 level below db server (db server --> app server --> web server --> web clients).

I would like to know, what should be my approach to gauge the performance?
Is there any thing specific to IPCs that I should watch out for?
Should buffer cache be kept dynamic or static?
What should be good values for dbc_*_pct?
What if reads are high? What if writes are high?

I understand how to see sar, vmstat, swapinfo, top, glance output. But I am loking for oracle-specific tunings which I may not be able to think about by just looking at these tools output.

Thanks,
Amit

[Joseph Loo]

hi amit,

i use this reference, KBRC00000947, to guide me with the tuning:

http://www2.itrc.hp.com/service/cki/search.do?searchString=KBRC00000947&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchCrit=exactphrase&search.x=12&admit=-1335382922+1134112606040+28353475&category=c0&mode=id&search.y=10

for Oracle-specific tuning, specific patches and the kernel parameters are pivotal, an sample of it for oracle database 9.2.0:

KSI_ALLOC_MAX (NPROC*8)
MAX_THREAD_PROC 256
MAXDSIZ 1073741824 bytes
MAXDSIZ_64BIT 2147483648 bytes
MAXSSIZ 134217728 bytes
MAXSSIZ_64BIT 1073741824
MAXSWAPCHUNKS 16384*
MAXUPRC ((NPROC*9)/10)
MSGMAP (MSGTQL+2)
MSGMNI NPROC
MSGSEG 32767
MSGTQL NPROC
NCALLOUT (NKTHREAD+16)*
NCSIZE ((8*NPROC+2048)+VX_NCSIZE)*
NFILE (15*NPROC+2048)
NFLOCKS 4096
NINODE (8*NPROC+2048)
NKTHREAD (((NPROC*7)/4)+16)
NPROC 4096
SEMMAP (SEMMNI+2)*
SEMMNI 4096
SEMMNS (SEMMNI*2)
SEMMNU (NPROC - 4)
SEMVMX 32768
SHMMAX AvailMem
SHMMIN 1
SHMMNI 512
SHMSEG 32
VPS_CEILING 64

the usual suspect for database would be the memory as seen from the kernel para, thus, important commands like ipcs, vmstat r vital.

regards.

[Amit Agarwal_1]

Joseph,

The above link returns 0 results. I searched both by 'doc id' and 'keywords', but no results. :-(

-Amit

[Joseph Loo]

hi amit,

that link is for Asia Pacific user, i was able to get by keying KBRC00000947 under "search by doc ID" in:

http://www2.itrc.hp.com/service/cki/enterService.do

regards.

[Jean-Luc Oudart]

HI

from OS prospective I recall that 800MB-1200MB for buffer cache would do (this is assuming your oracle datafiles are on file systems, not raw).
Personally, I use a fix value.

see attachment UX perf cookbook.

Regards
Jean-Luc

[Jean-Luc Oudart]

Hi again,

from Oracle prospective you should build a baseline with statspack reports along with OS performance reports.

You should also use the Oracle advisory utilities to tune memory.
cf. this thread :
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=971064

Regards
Jean-Luc

[Yogeeraj_1]

hi amit,

for gauging your oracle database performance, you will need to investigate into Statspack.

It would again be advisable to capture information about your database performance before making any changes either at the OS level or database..

hope this helps too!

kind regards
yogeeraj

[Hein van den Heuvel]

Right on Yogeeraj. Oracle tuning starts with... drumroll... looking at oracle statistics data.
There is only so much you can do with OS tuning.

The big, yet simple. thing you need to know, is cpu time use. User should be highish, System time lowish 5 % - 15% of user more time, Idle time should be low on a busyish system
.
If usertime is say 80%, then it would seem clear that tuning the OS, reducing the system time, is not going to make a significant difference, and Oracle is where it's at.

If system time is > 40% then there is something wrong with the OS tuning/setup and that would need to be addressed.

If idle time is high, and the system is supposed to be busy, you may have a serious IO or network bottleneck.

Just some starting points.

Cheers,
Hein.

[Steven E. Protter]

Shalom Amit,

This is a large field.

I once did a call to the response center on this topic and received a great document, which in the past I posted. HP has played hide and seek a few times with it and I can't find it.

Important facts retained by my pea brain molecules:

dbc_max_pct
dbc_min_pct

They control buffer space. They should be set close to one and other. changing buffer space ie extremely expensive and can by itself slow Oracle and general system performance.

In production, I set the variables to 7 for max and 5 for min. What you set them to depends on your system, memory availability and type of oracle setup.

There's my advice.

SEP

[Amit Agarwal_1]

Thanks everyone for your timely response.

-Amit