Solved

performance issue

Posted on 2012-04-11
6
345 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
6 Comments
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dba1234
Comment Utility
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 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now