Oracle /HP-UX tuning question.

[ben_43]

Team:

I am currently working on hp-ux 11.o environment and quite familiar with all aspects of system administrationn and trouble shooting skills with about 5 years experience. Now i have got a change of Job and looks like in the new Job they require HP-UX with oracle tuning experience. Can someone throw more light on this subject? I mean what exactly would this mean and what are the new things i might have to be familiar with. This is a System Admin Job.

Thanks

Ben.

[Stuart Abramson_2]

The HP-UX tuning is the same as always: glance, perfview, sar, iostat, vmstat, etc. CPU utilization, disk utilization, memory bottlenecks, etc.

The Oracle part means particular to running Oracle on HP-UX. You will have to ask DBAs about this: Is the SGA large enough, are the oracle file systems layed out properly (indices and the tables on different disks, etc.).

[harry d brown jr]

Ben,

I think your best source of information on oracle tuning can be had right here with a simple search on oracle and tuning. The funny thing is that once the DB is set up and the machine "tuned", then there isn't a lot to do but sit back and monitor the system. Now if you are going to get into tuning SQL, that's another story for which SQLLAB is a good thing to know how to use.

live free or die
harry

[Ravi_8]

Hi Ben

Oracle8i and 9i are all pretuned, you no need to tune as we did in oarcle 7x. New job requires a person who is well versed with HP-UX admin and Oracle DBA

[Bill Hassell]

This is a very ambiguous job description. Do they want you to be an expert Oracle DBA so you can adjust the Oracle startup configuration for maximum performance? Or do they want you to tweak HP-UX parameters to fix Oracle misconfigurations? The reality is that HP-UX doesn't need much tuning except to accomodate large Oracle instances. dFor instance, a large Oracle SGA can fix a multitude of DBA sins (like poorly designed SQL statements and/or missing, corrupted or unbalanced indexes) so you need to know about shared memory parameters in the kernel. And if you're stuck with Oracle running in 32bit mode, there are a number of limitations and workarounds in the kernel to help with that.

Upper management (and many DBAs too) think that if the Oracle code produces correct answers then all is well and the SysAdmin for HP-UX must improve performance. That is almost never the case. Instead, the SysAdmin must point out to the DBA when massive disk I/O is being generated by Oracle and work on reducing I/O with better memory usage opr eliminating needless I/O through the use of indexes and SGA-based sort areas.

There are numerous examples of fine tuning Oracle and HP-UX here in the forums. Look for keywords like: oracle shared memory sga sql performance

[Jean-Luc Oudart]

How much would you need on the Oracle tuning skills ?
I suppose i you come for sysadmin , you would need to understand the oracle architecture & installation, impact on kernel parameters, memory usage , IO spread,...

Will you be working with a DBA ?
Will you be trained ???

in the mean time , a few web sites :
http://www.ixora.com.au/tips/

http://www.dbasupport.com/oracle/scripts/Performance_Tuning/Memory_Tuning/

Rgds,
JL

[W.C. Epperson]

Bill has it right. I was an Oracle DBA from V4-7 and have sysadminned on multiuser operating systems from MPE to VMS to Primos to *nix. And now manage systems and network engineering for an HP-UX/Oracle shop.

With respect to OS tuning, there's not so much to do, as noted. Monitoring and diagnosing is the key activity for the sysadmin on an Oracle box, mainly i/o activity and memory usage. In particular, we've recently come to understand that Oracle Applications (e.g. Financials) fragments memory in ways that will seriously degrade performance without being visible using standard memory monitoring. Search on "fragmentation" here for scripts to help with spotting that.

What I'd like to see happening more is for sysadmins to undertake to understand the Oracle SGA and its parameters better, well enough to recommend or implement tuning measures within the Oracle configuration. This is traditionally a DBA activity, but the DBA is more and more absorbed with application function issues, like writing and troubleshooting PL-SQL code for triggers, packages, etc., and less and less oriented to systems issues. The application duties were one of the reasons I walked away from being a DBA a few years ago.

[Steven E. Protter]

Thats kind of my job now. The primary reason my HP-9000 servers exist is for Oracle. I have to tune the OS for oracle and work with and back up the dba on database tuning issues.

As far as the OS goes, this doc is a great start.

http://www1.itrc.hp.com/service/cki/search.do?category=c0&mode=id&searchString=UPERFKBAN00000726&search.x=28&search.y=8&searchCrit=allwords&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty

DOC ID
UPERFKBAN00000726

SEP

[twang]

Attached note lists commonly misconfigured HP-UX kernel parameters for oracle.

[twang]

Add to above reply, to configure shared memory, each segment os shared memory is limited by 'shmmax'. For oracle, shared memory parameter should be set so that SGA will fit in one segment if possible.

[Volker Borowski]

Hi,

from my experience, you will have a much easier job to make users happy by tuning oracle than by tuning OS (HP-UX or else).

Reason: With OP-Sys tuning, you are only able to fix "general" problems, but you will never be able to fix "special" problems, and there are tons of them caused by application design or data-modell. Beside a truly bad kernel misconfiguration (which should never happen if one reads the fine manual upon installation) you will never have the opportunity to speed up a business process by a factor of 10000 (plus/minus one or two zeros) with the parameters the operating system gives you.
But by optimizing bad queries with indexes you easyly can do this and there are lot's of them out there.

So be happy, to get a multiplexed admin/dba job, which I always find to be a thrilling job due to it's complexness. The disadvantage is, you will not be able to dive too deep into it if you are specialized with only one of these topics. I am a SAP consultant. I know a part of the op-sys, part of database and part of the application SAP. This gives me a good chance to optimize an application process with in the first 30%-60% (and if it comes to exchange a full table scan with a direct index-access it can not be measured in percent any more :-)
That is the part I like most !

But I have to admit: If you are finished with basics (or averages after a couple of years), I envy the true specialists that have the deep knowledge of their special area. I had the glory opportunity at the beginning of the year to watch some real Oracle-Tuning-Gurus at work and that was an amazing thing.

But this just occurs in 5% of my environment, so I am happy with the other setup an yell for help in this rare cases :-)

Be happy with your new and expanded tasks
Volker

[A. Clay Stephenson]

I strongly urge you to take an Oracle database administration class and you may need the kindergarten SQL class as well if you are not familiar/proficient with SQLPlus.

In the vast majority of cases, the performance problems lie in the SQL but unless you have some level of proficiency in Oracle, you are not going to have the credibility to assert that claim or really know how to discern the Oracle from the OS problems.

[Con O'Kelly]

Hi Ben

As most of the others have said, the HP-UX side is pretty straight forward in the sense that it involves tuning a few kernel parameters, tuning the system for general performance & being aware of a few mount options available with Online JFS that can sometimes improve Oracle performance.

One thing I'd advise is becoming very familiar with your system performance & to keep historic performance data (eg through MWA or sar?).

The majority of Oracle performance issues are resolved on the Oracle side ( eg a change to the SQL code, change an SGA parameter(s) etc). You need to be able to eliminate pretty quickly any OS issues such as CPU, Memory, Disk or Network bottlenecks.

To me Oracle Tuning is one of the more advanced areas of Oracle DBA work. I'd certainly advise getting some good basic knowledge of Oracle so that you can confidently talk with the DBA's about their SGA configurations & be able to reliably tell them the performance issue is not OS related.

One last issue if you're running 32bit Oracle is to become familiar with Shared memory operations with HP-UX as this is often a limiting factor in SGA size etc.

Good luck, sounds like a fun & challenging job.

Cheers
Con

[Khalid A. Al-Tayaran]

Hi,

Ben, The best way to learn Oracle is to work with Oracle. The most important aspects in Oracle are installing, tuning and recovery from backups. Also, reading on otn.oracle.com will help. Take a course in Oracle 9i DBA if you can, very helpful.

Get RedHat 9 and download Oracle 9i from oracle.com and play with it.

You may be interested in these sites also:

http://www.orafaq.net/

http://searchoracle.techtarget.com/