Solved

performance issue

Posted on 2012-04-11
6
360 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

696 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