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?

Improve company productivity with a Business Account.Sign Up

x
 
Mark GeerlingsConnect With a Mentor Database 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
 
MikeOM_DBAConnect With a Mentor Commented:
And what do the diagnostic reports tell you?
What do ADDM, ASH, Memory advisor, etc... have to say?
:p
0
 
Sanjeev LabhConnect With a Mentor Database 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 GeerlingsConnect With a Mentor Database 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 LabhConnect With a Mentor Database 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
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.

All Courses

From novice to tech pro — start learning today.