Hi Expert,

How to diagnose oracle DB  performance in IBM AIX machine .

Please let me know if need additional information.

Your suggestion always rel-time valuable for me .

Thanks in Advance..
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Oracle database performance can be monitored and diagnosed using Oracle Enterprise Manager. You need to be on the enterprise edition and you need to purchase the tuning pack add ons of course.

OEM gives very comprehensive details like Top Activity, Throughput, I/O, SQL Details, Active Session History, Wait events etc etc.

Documentation here

Management packs in OEM
slightwv (䄆 Netminder) Commented:
This is a much larger question that can normally be handled by a Q&A site like this.  People make entire careers out of nothing but performance tuning.

It is more than just clicking a few buttons on a GUI.  You need to understand what the GUI is telling you and if/when it makes suggestions, you need to know if those are actually good ones.  Some of them might actually hurt overall performance.

On some systems, you never need the GUI.  Just looking at system level performance and running a couple SQL scripts is all you may need.

For example:  The system is swapping memory to disk.  You don't need any GUI to tell you this is bad.
There are many aspects in this question.
First of all investigate the RAM usage.
As slight mentioned there shouldn't be swapping.
Even more - in Unix systems 30% of the RAM should be free for caching OS activities (and data blocks read from Oracle! - very important).
After that analyze the SGA size and SHARED_POOL size. Increase them if they are small.
Analyze the pools in the SGA - sometimes they are malformed.

Check the parameter cursor_sharing. If it is EXACT change it to FORCE.

ALTER SYSTEM   flush  shared_pool;

Investigate the statistics. If it isn't refreshed recently compute new statistics.
Flush the SHARED POOL after that.

IN AIX check the count of processors in WLM (Work Load Manager), increase it it set the parameters for parallel processing.

And of your instance is let me know to cut asap defect functionalities in this version.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
In one word : ITSS
It's The Stupid Sql

If the querries aren't written well, they perform badly.
Alas, not all devs have time to write them correctly, or test them for best performance
And in the beginning there isn't much data, and bad querries aren't that bad yet. Time will bring those to "the bad querries" list.

Setting the cursor sharing to Force doesn't always help
> If you're querries use bind variables where they should and not where they shouldn't they there is no point in doing that

You have asked this question repeatedly.
There is no tools which can fully automate tuning yet, contrary to what Larry says ...
At the moment it requires someone to look at performance regularly.
You'll have to find someone to do that, or dig in regularly and do it yourself
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.