HP-UX Admin vs Oracle DBA!!

[Jonathan Caplette]

Hi guys...

I have an application (peoplesoft) running on one of my machine, but under that it have some Oracle DB running for a 100% of my CPU... The DBA told me that my machine was slow, I told him that is processes are running for 100% of my CPUs... So the DBA check with Oracles tool to see what time a request could take... I came back to me and said that my machine as a memory or a disk problem. I came with that cause he told me that a oracle request should take 0.2 sec to be execute and on that machine it took 0.8 sec... OK it's not a big difference but when it have over 10000 records, it became to be long...

So I used Glance to monitor my CPU, Mem and Disks... and when I check to my CPU screen I see always oracle processes using all the cpu.. For my mem it's used for about 20% and my disks I/O for about 15%... I believe that his oracle deamon are not configured properly...

Here is what's on my machine:
HP9000/D380
2 Cpu of 180Mhz
about 1.7 Gbytes of Memory
and my disks are in a jamaica case with a Fast/Wide SCSI interface..

So any idea of who could have right, me are my DBA??? And how can I get sure that there's no probleme with my mem or disks???

Thanks
Jonathan

[A. Clay Stephenson]

Hi Jonathan:

It appears that you have a CPU bottleneck but you could very easily simply have BAD SQL code. That is the most like answer; however; before you do anything else check that the setting of timeslice is 10 rather than 1.

You need to use Glance to see which processes are the CPU hogs and that will help lead you to the answer.

It would probably help to post a kmtune or sysdef listing.

[Jonathan Caplette]

Here is the output of a sysdef on my system...

[Christopher McCray_1]

Hello,

One thing I see off the bat is that you have dbc_max_pct set to the default of 50. You need to lower that to 10 or 15.

Hope this helps
Chris

[Jonathan Caplette]

dbc_max_pct?? What is this for, and what this will do???

[Sandip Ghosh]

I will suggest three changes in your kernel Parameters.
Bufpages 0
dbc_max_pct 15
nbuf 0

Change these three Parameters initially and see the performance. Then look for other things.

Sandip

[A. Clay Stephenson · Accepted solution]

Hi Jonathan:

You almost certainly have WAY too much buffer
cache for a box with only 1.7GB of memory. You have bufpages set to 229376. That is 229397 * 4096 ~ 896MB. Reduce bufpages to no more than about 80000 (320MB) and if you are using raw/io or the vxfs mount options convosync=direct,mincache=direct (which also bypass the unix buffers) then I would reduce then bufpages to perhaps 30720 (120MB).

That would be a good first step.

[A. Clay Stephenson]

Hi Jonathan:

If you like, you can set bufpages and nbuf to zero and then use dynamic buffer cache as has been mentioned. However, my preference is to use static buffer cache by setting bufpages to a non-zero value. My reasons are quite simple. It is much easier to tune other values when buffer cache is help constant but that is just me. Also, the best system tuner I know when speaking to you suggests static buffer cache in spite of what is written in his politically-correct paper.


I just noticed that your nbuf is non-zero, I would set bufpages to a non-zero value (e.g. 8000) but set nbuf to 0 as this is generally a good value (the system uses default behavior).

Regards, Clay

[Byron Myers]

Jonathan, The previous responses about the buffer cache (dcb_max_pct) are a big hit performance item. You will see a significant improvement when you reduce this - I recommend 3% for an Oracle server.

[SHABU KHAN]

Hi,

To check if you have any bad disks, do a diskinfo on all your disks ..

Here is a way to do it on all disks attached to your system:

for i in `/etc/ioscan -kfnC disk | awk '/dsk/{print $2}'`
do
/etc/diskinfo ${i}
done

If you have any issues with your disks then your diskinfo command will hang ...

-Shabu

[pap]

Hi,
You need to update following parameters.


dbc_max_pct 10
nbuf 0
maxdsiz (2GB) or 0x7B03A000



Hope this will helps.

[pap]

Hi,
It is better to provide "kmtune" output rather than "sysdef". Sysdef out put is not a good one for HP-UX 11.0.

Thanks,
-Piyush

[Scott Van Kalken]

Get your DBA to check some of the queries being run to see how many table scans are being performed. I've found that bad queries will quite often perform lots of table scans and performance goes through the floor.

This can be checked with oracle tool sets.

This does not mean that the machine isn't struggling though.

Scott.

[steven Burgess_2]

Hi

Here's a link to Kernel Parameter settings

http://docs.hp.com/hpux/onlinedocs/os/KCparams.OverviewAll.html

[Jonathan Caplette]

Hello guys...

Thanks for the help... I set the kernels parameters and I gain some speed... but for my DBA it's not enought, so I have convinced him to check is queries, etc...

Thanks again!!!
ciao!!
Jonathan

[Krishna Prasad]

Jonathan,

I also didn't see shmmax parm in your system file. This means you are using the default value which is usally not enough to run a big Oracle Application.

I also only saw maxdsize not maxdsize_64bit.
Is your D380 runing 10.20,11.0 32bit, or 11.0 64bit?

If you are running 32bit that explains why there is no maxdsize_64bit. However, you may need to increase maxdsize it seems a little low. This will depend on what your DBA has the SGA set at. Ask your DBA what his cache hit % is for the SGA. If it's not in the 90+ range I would increase the SGA. This may cause you to increase maxdsize or maxdsize_64bit, shmmax, and possible maxswapchunks to allocate more swap.

Also in general a D380 is not that big or fast of a machine anymore. I have more expierence with SAP then PeopleSoft, but I know they are both big applications. There is no way we would run even just SAP HR on a D380 with 2 cpu's.

[Roger Baptiste]

hi,

Check :

From how long did the system become CPU intensive??? Was it fine all along and suddenly one fine day it started hogging CPU.
If so, what jobs/databases was added to the system? This you can check up using measureware statistics and look at perfview history. (easy to use).

WHat are the top cpu intensive processes? Who is the userid of those processes? is it just oracle user id or users running sql jobs? If it is users jobs, then make a list of the userids , their pids and check with the users on what they are running. (many times i have seen users running poor sql codes which hog cpu).

Are there any runaway procesess? do ps-ef and see whether there is any user process which is eating cpu time rapidly?

How is your swapusage (swapinfo -mt) ?

is the system paging out (vmstat -n and po value). This gives you an idea, whether the CPU bottleneck is due to memory shortage.

My gut feeling is it is a CPU issue and you would need to make sure whether it is a valid one or a user-created one which can be cleared up by working at the database level and getting users to clear up their sql scripts.

OR for all it matters, your system may be actually CPUbound because it doest have enough cpu power to drive the load. In that case you may need to consider moving to a faster system (n-class).


HTH
raj