topic Re: Bad performance in my database in Operating System - HP-UX

Bad performance in my database

yasser_2 — Wed, 02 Jul 2003 23:36:57 GMT

I am installing Oracle 9i on an HP9000 system running HP-UX 11 As part of the installation a database is created. . We are installing the 64bit version of Oracle 9i. Database it still seems very slow.
The system has 1.5 GB of memory. Deadlock
What the past parameters must be configure in the Unix system and init.ora file

Re: Bad performance in my database

twang — Wed, 02 Jul 2003 23:44:58 GMT

Some Kernel Parameter recommended by Oracle for the HP-UX Operating system running Oracle 9.X Enterprise Edition:

KSI_ALLOC_MAX=(NPROC * 8)
- Defines the system wide limit of queued signal that can be allocated.
MAXDSIZ=1073741824 bytes
- Refers to the maximum data segment size for 32-bit systems. Setting this value
too low may cause the processes to run out of memory.
MAXDSIZ_64=2147483648 bytes
- Refers to the maximum data segment size for 64-bit systems. Setting this value
too low may cause the processes to run out of memory.
MAXSSIZ=134217728 bytes
- Defines the maximum stack segment size in bytes for 32-bit systems.
MAXSSIZ_64BIT=1073741824
- Defines the maximum stack segment size in bytes for 64-bit systems.
MAXSWAPCHUNKS=(available memory)/2
- Defines the maximum number of swap chunks where SWCHUNK is the swap chunk size
(1 KB blocks). SWCHUNK is 2048 by default.
MAXUPRC=(NPROC + 2)
- Defines maximum number of user processes.
MSGMAP=(NPROC + 2)
- Defines the maximum number of message map entries.
MSGMNI=NPROC
- Defines the number of message queue identifiers.
MSGSEG=(NPROC * 4)
- Defines the number of segments available for messages.
MSGTQL=NPROC
- Defines the number of message headers.
NCALLOUT=(NPROC + 16)
- Defines the maximum number of pending timeouts.
NCSIZE=((8 * NPROC + 2048) + VX_NCSIZE)
- Defines the Directory Name Lookup Cache (DNLC) space needed for inodes.
(VX_NCSIZE is by default 1024.)
NFILE=(15 * NPROC + 2048)
- Defines the maximum number of open files.
NFLOCKS=NPROC
- Defines the maximum number of files locks available on the system.
NINODE=(8 * NPROC + 2048)
- Defines the maximum number of file locks.
NKTHREAD=(((NPROC * 7) / 4) + 16)
- Defines the maximum number of kernel threads supported by the system.
NPROC=4096
- Defines the maximum number of processes.
SEMMAP=((NPROC * 2) + 2)
- Defines the maximum number of semaphore map entries.
SEMMNI=(NPROC * 2)
- Defines the maximum number of semaphore sets in the entire system.
SEMMNS=(NPROC * 2) * 2
- Sets the number of semaphores in the system. The default value of SEMMNS is
128, which is, in most cases, too low for Oracle9i software.
SEMMNU=(NPROC - 4)
- Defines the number of semaphore undo structures.
SEMVMX=32768
- Defines the maximum value of a semaphore.
SHMMAX=Available physical memory
- Defines the maximum allowable size of one shared memory segment. The SHMMAX
setting should be large enough to hold the entire SGA in one shared memory
segment. A low setting can cause creation of multiple sharedmemory segments
which may lead to performance degradation.
SHMMNI=512
- Defines the maximum number of shared memory segments in the entire system.
SHMSEG=32
- Defines the maximum number of shared memory segments one process canattach.
VPS_CEILING=64
- Defines the maximum System-Selected Page Size in kilobytes.

Re: Bad performance in my database

Con O'Kelly — Wed, 02 Jul 2003 23:59:38 GMT

Hi

Can you clarify what you mean by "Bad Performance". What is you CPU, Memory, Swap Utilisation??
When is the DB Performance Bad? All the time or only when certain jobs, reports are running.
Are there other applications running on the system??

At this stage its only possible to provide extremely general advice.

1) Check all the kernel parameters that Oracle specifies in its install guide.
2) Check your Buffer Cache size.
3) Your init.ora file is used to size your SGA. You need to size this according to DB requirements and the available memory on the system. Sometimes increasing the database Buffers can improve performance but again this is limited by the amount of memory available on the system.

Could you post some performance metrics, your kernel parameters and your init.ora settings.

Cheers
Con

Re: Bad performance in my database

twang — Thu, 03 Jul 2003 00:10:24 GMT

If you believe that you are facing performance problem in database. First of all, you should run statpack to collected the statistics. See the attached.

Re: Bad performance in my database

Bill Hassell — Thu, 03 Jul 2003 01:20:34 GMT

Your performance issues can easily be solved. 1.5Gb is pitifully small for Oracle 64bit. The BARE MINIMUM should be 4Gb, but for best performance, install 6-8Gb of RAM. Otherwise, all the money you spent on Oracle will be wasted because the RAM is so small. With Oracle 64bit, your DBA can use 2-3Gb for SGA and avoid disk I/O for row insertions and temp sort files.

Sice HP-UX is a virtual memory system, your DBA may already be using a multi-gigabyte SGA but since it won't fit into the 1.5Gb space, your system will be paging (swapping) at a very high rate (use vmstat and look at "po" which is page outs). Page out rates more than 3 digits will degrade performance 100:1 or more.

With Oracle, there is no substitute for RAM (except wait time).

Re: Bad performance in my database

Steven E. Protter — Thu, 03 Jul 2003 01:27:45 GMT

I love Bill's posts, so true.

The best you are going to do here is to set up 3 GB of swap. Any more the system will grind to a halt with Oracle.

It would be intersting to know what kind of processor you have and how many.

With 4.5 GB of total memory, you should be able to to a fair amount of processing, so long as you don't have too many users or too many instances. If you do, Bill is right, its time to get memory. If you have an open processor slot, fill it, just be aware that Oracle's license policy is per processor. Nasty expensive business dealing witht he license.

To the central issue.

Of your 4.5 GB of memory, you should allocate shmmax to about 25% of the that total. Any more and the system will ignore you anyway. You need to measure performance so you can set shmseg to a reasonable level where you never run out.

To measure performance, use the attached scripts, and act on the bottlenecks.

If the processor is being overworked, look at dbc_max_pct (check the spelling on that no box at home yet), to a low number like 5. The minimum should be pretty close to that. It takes a lot of CPU to handle higher numbers.

Also expensive are inodes settings in the kernel.

I'm attaching the performance measurement scripts, which run background, collect data in a file, which will be an eyeopenning experience for you.

SEP

Re: Bad performance in my database

Yogeeraj_1 — Thu, 03 Jul 2003 02:57:15 GMT

hi,

on top of the above replies, i would suggest that you run a STATSPACK report to see what is happening with you Oracle database.

This should give you some ideas about the bottlenecks too.

regards
Yogeeraj

Re: Bad performance in my database

yasser_2 — Thu, 03 Jul 2003 17:09:50 GMT

Re: Bad performance in my database

yasser_2 — Thu, 03 Jul 2003 17:20:14 GMT

thanks all to help me
i use the vmstat and i found every thing is ok
free re at pi po fr de
6977 21 0 3 3 1 0
sr in

39 182