Solved

performance issue

Posted on 2012-04-11
6
354 Views
Last Modified: 2012-04-26
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.
0
Comment
Question by:dba1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 200 total points
ID: 37837142
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
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 100 total points
ID: 37838367
And what do the diagnostic reports tell you?
What do ADDM, ASH, Memory advisor, etc... have to say?
:p
0
 
LVL 5

Assisted Solution

by:Sanjeev Labh
Sanjeev Labh earned 200 total points
ID: 37844135
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:dba1234
ID: 37844270
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 37844811
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
 
LVL 5

Assisted Solution

by:Sanjeev Labh
Sanjeev Labh earned 200 total points
ID: 37844828
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question