Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Hi there , Any good tips regard 1
- Thread starter UDIT
- Start date
-
1
- #2
This question is too broad to answer completly. There are lengthy books written about Oracle performance tuning, so we can hardly hope to touch on more than one or two high points here.
1. For rollback segments it's important to make sure that you have created enough to adequately support the load on your system. A common rule of thumb is one rollback segment for every four transactions. Obviously you should measure transaction activity during a period of peak usage.
You should also check to see if your rollback segments are sized properly. OPTIMAL shouldn't be set so low that Oracle is doing a lot of shrinks. That could adversely affect performance. On the other hand OPTIMAL should be small enough that the rollback segment tablespace has enough free space to handle any large transaction that might be executed.
2. Just observing high CPU usage on your Oracle server doesn't narrow down where the CPU is being used. You will need to investigate further. Take a look at how much of the CPU time is spent parsing. A high parse rate may reflect a need to modify the application code to use bind variables (or the new CURSOR_SHARING = FORCE initialization parameter). Another candidate for high CPU usage is sql that does a lot gets from the db buffer cache.
3. If you see high i/o rates you should identify the sql that is the cause. These high cost sql statements can frequently be improved by creating indexes that improve the access path, or by rewriting the sql to retrieve data more efficiently.
1. For rollback segments it's important to make sure that you have created enough to adequately support the load on your system. A common rule of thumb is one rollback segment for every four transactions. Obviously you should measure transaction activity during a period of peak usage.
You should also check to see if your rollback segments are sized properly. OPTIMAL shouldn't be set so low that Oracle is doing a lot of shrinks. That could adversely affect performance. On the other hand OPTIMAL should be small enough that the rollback segment tablespace has enough free space to handle any large transaction that might be executed.
2. Just observing high CPU usage on your Oracle server doesn't narrow down where the CPU is being used. You will need to investigate further. Take a look at how much of the CPU time is spent parsing. A high parse rate may reflect a need to modify the application code to use bind variables (or the new CURSOR_SHARING = FORCE initialization parameter). Another candidate for high CPU usage is sql that does a lot gets from the db buffer cache.
3. If you see high i/o rates you should identify the sql that is the cause. These high cost sql statements can frequently be improved by creating indexes that improve the access path, or by rewriting the sql to retrieve data more efficiently.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question