performance issue

I have performance issue always on this database, and the current stats are

Database Wait Time Ratio      90.2433823716884
Database CPU Time Ratio      9.75661762831162

how should I proceed to find out the root cause.
dba1234Asked:
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.

Mark GeerlingsDatabase AdministratorCommented:
Those two pieces of information by themselves are not *NEARLY* enough information for us to make any recommendations!  You will also have to tell us:
1. Which version of Oracle is this?
2. What is the O/S version?
3. Is this a 32-bit or 64-bit system?
4. How much RAM does the server have?
5. What is the SGA_MAX_SIZE?
6. Is the server dedicated to running just this one instance of Oracle, or are there multiple instances, or does the server also function as a web server, print server, etc.?
7. What kind of application is this (OLTP, Data Warehouse or mixed)?
8. Is this a purchased application (if yes, which one) or is this a custom application?
9. How do the users interact with the database (via direct client/server connections or via one or more application servers)?
10. How many concurrent user connections does this system support?
11. What is the approximate size of the database?
12. How long has performance been a problem, or when was it working well without a performance problem?
0

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
MikeOM_DBACommented:
And what do the diagnostic reports tell you?
What do ADDM, ASH, Memory advisor, etc... have to say?
:p
0
Sanjeev LabhDatabase ConsultantCommented:
The best place to start of performance tuning when you are not sure that where the problem are by looking at some of the diagnostic reports as MikeOM_DBA has asked. Primarily AWR, ADDM and ASH reports would provide you information mostly that you would be seeking. Sometimes, if you still are not able to ascertain where to start of, then the recommendation provided by ADDM are the best place to start tuning of DB.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dba1234Author Commented:
Addm reporting is suggesting to tune some of the queries and to increse SGA. I have increased the SGA FROM 5 TO 7 GB and still it is sugesting to increase SGA.
0
Mark GeerlingsDatabase AdministratorCommented:
If the queries are poorly designed (that is: they weren't written to use indexes efficiently) just increasing the SGA by 20% won't help you much if the database is large.

You haven't given us any of the specifics I had asked for earlier.  Without some of that specific information, it is really diffcult for us to give you specific suggestions.
0
Sanjeev LabhDatabase ConsultantCommented:
You can correspond the sql ids mentioned in ADDM to that in the AWR. By that you can see how much % of DB time the query is taking. In fact you can see the impact of the query in other perspective too such as waits related to i/o, network, concurrency etc. Time related to parses Hard or soft etc. and of course checking out the explain plan of the query in the actual DB. If the level of impact of query in AWR is high enough that would be actually affecting your memories as well as CPUs.
0
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
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.