ORACLE Performance / Shared Resources Issues

[Ralph Grothe]

Hello Oracle/HP-UX experts,

we have a dated 9000/898/K370 that hosts HP-UX 11.00, and which is used as an Oracle testing / staging platform.

This machine isn't very well equipped as far as physical memory is concerned (have a look at attachment).

Up until recently on it ran two Oracle 8.1.7 instances.

About two weeks ago I was asked to prepare the machine for installation of Oracle9i rel. 9.2.0.
Thus I collected any required patches (and dependants) as recommended by Oracle from the HP patch database on the web, downloaded and installed them.

Apart from this I reconfigured the kernel and adjusted those tunables to the values specified, again according to Oracle recommendations for Oracle9i installation on HP-UX 11.00 (see current tunables list in attachment).

WIth those settings and a slightly modified LPATH (which I suggested to include /usr/lib/pa20_64 at the beginning) the DBAs were finally after several failed link attempts due to unresolved libcl calls able to install Oracle9i on this box.

But now the DBAs complain about a massive performance degradation (which I blame on the low memory equipment as well as maybe unsound kernel tunable settings).
So far they avoid to have an Oracle9i instance running at all.
But they now claim to be even hardly able to run the two Oracle8i instances that used to run before to some extent.

I also included the init file of one of the two old instances, in which a DBA had to reduce the processes parameter to a value of 80 from 200 in order to be able to start the instance at all.

The DBA also asked me if I could free up shared memory claimed by root that showed up as 0 NATTACHED by issueing an ipcrm.

I wasn't sure if it were safe to do so, because I don't know if some vital process relied on some shared address space that might inadvertently kill it if not available anymore.

A lot of questions that probably require more background information to be answered satisfactorily.

Anyway I'd appreciate your thoughts since I do know almost nothing about Oracle innards and its SGA or usage of IPC.

Regards

Ralph

[David_246]

Hi Ralph,

Great explanation I think.

I always use a script that shows me some more info about the claimed memory.

#!/bin/ksh
# This script is written to retreive the total memory claimed
# Written by D.M. van der Geer # 11 februari 2003

user="root"
/usr/bin/clear
t=0
for i in `UNIX95= ps -e -o vsz=Kbytes -o ruser -o pid,args=Command-Line | sort -rnk1 | grep -v Kbytes | grep " $user" | awk '{print $1}'`
do
t=`expr $t + $i`
done
echo "\n In total there is $t Kbytes of memory claimed by user $user \n"

echo "Swapinfo :"
/usr/sbin/swapinfo
echo "\n"

When changing the user var you can find out how much memory is claimed by that specific user (not what is actualy in use !!) when leaving blank you should see all the memory claimed.

Swapinfo tells you if the system is swapping. If so, you should be able to explain the performance issue.

PLease attach a swapinfo output.


Best Regs David

[LE_1]

Please give results
of :
vmstat 5 20
when performance is bad

and what is the blocksize of the database ?

[LE_1]

Can you give the error message when trying to start with processes=200 in init.ora
?

I see this :

sort_area_size = 64000000
sort_area_retained_size = 656400000

it's too big !!
each session can take 640Mo for its sorts
64K-5Mo is enough

[Ralph Grothe]

David,

nice variant to parse the process table for a summation of the processes' core image sizes.
Maybe a slight "improvement"?
You can avoid a few greps by simply replacing the -e switch in ps by -u $user.
But I would stick with -e to fetch all processes' stats and instead let Perl do the parsing (btw. there is a great Proc::ProcessTable module) and collect it in a hash with account names as keys.

LE,

of course I forgot to provide swap info.
At the moment, where there is just one instance (with the 80 processes restriction is running) the swap usage is such

# swapinfo -ta
Kb Kb Kb PCT START/ Kb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 1126400 342892 783508 30% 0 - 1 /dev/vg00/lvol2
reserve - 543932 -543932
memory 708972 357624 351348 50%
total 1835372 1244448 590924 68% - 0 -

[Ralph Grothe]

Oops,
the dbc_max_pct escaped my notice somehow.
I know to have restricted this value to 10% on other db servers which do their own caching.

[Ralph Grothe]

LE,

they hid the setting of db_block_size in yet another config file.
It seems to be 8K and set in here:

# grep block configS00.ora
#db_block_size =
db_block_size = 8192


I will convey your proposal about the exeggerated sort_area sizes to the DBAs.

[Uday_S_Ankolekar]

One thing you can look in to is kernel parameters.

Your dbc_max_pct is set to 50 . This is abslolutely killing.
Change this parameter to 10 .
(This require reboot)

-USA..

[Jean-Luc Oudart]

Ralph,

I suppose if the databases worked fine before the upgrade, apart from the OS parameters (still good to have them well tuned !) you should also look at the differences between 9i and 8i regarding memory usage.

1) Does the application use Java , if not you could reduce your Java pool size parameter

2) sort_area _size (already mentioned), sort_area_reatined_size

3) Could you run perfstat and attach a perfstat report after you run the application ?

4) What type of application is this.
Cursor management policy you implemented ?

Rgds,
JL

[Steven E. Protter]

HP-UX Oracle uses SHLIB_PATH, not LPATH as indicated above.

If you have swap twice physical memory you should be able to do the installation.

You can't free up memory attached by root shown in ipcrm, unless you stop using the applications that attach the memory.

apache chews up a lot.

You can increase shared memory by increasing the shmmax and related kernel parameters and you should do so.

Memory is still available for your box, getting more might help.

SEP