Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

ORACLE Performance / Shared Resources Issues

Ralph Grothe
Honored Contributor

ORACLE Performance / Shared Resources Issues

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
Madness, thy name is system administration
10 REPLIES
David_246
Trusted Contributor

Re: ORACLE Performance / Shared Resources Issues

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
@yourservice
LE_1
Advisor

Re: ORACLE Performance / Shared Resources Issues

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

and what is the blocksize of the database ?
LE_1
Advisor

Re: ORACLE Performance / Shared Resources Issues

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
Honored Contributor

Re: ORACLE Performance / Shared Resources Issues

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 -



Madness, thy name is system administration
Ralph Grothe
Honored Contributor

Re: ORACLE Performance / Shared Resources Issues

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.
Madness, thy name is system administration
Ralph Grothe
Honored Contributor

Re: ORACLE Performance / Shared Resources Issues

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.
Madness, thy name is system administration
Uday_S_Ankolekar
Honored Contributor

Re: ORACLE Performance / Shared Resources Issues

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..
Good Luck..
Jean-Luc Oudart
Honored Contributor

Re: ORACLE Performance / Shared Resources Issues

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

fiat lux
Steven E. Protter
Exalted Contributor

Re: ORACLE Performance / Shared Resources Issues

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Bill Hassell
Honored Contributor

Re: ORACLE Performance / Shared Resources Issues

Your box's 1Gb of RAM is crippling Oracle! You need to add 3Gb more RAM and make sure your Oracle instances are running 64bit mode so the huge limitations of 32bit programs is no longer an issue.

Also, have your DBAs read the two white papers in /usr/share/doc about memory management and process management. ipcs doesn't give you a clear picture of shared memory. Get a copy of shminfo from ftp://contrib:9unsupp8@hprc.esxternal.hp.com/sysadmin/programs/shminfo/


Bill Hassell, sysadmin