Monday 28 July 2014

ISSUE IS APPLICATIONS ARE RUNNING SLOW
______________________________________

--> To find out the cause, acion plan as follows

    *Take awr,ash and addm reports
    *Find out the cause by using reports, if it is query take a sqlid
    *Then take a SQLT(SQLTXPLAIN) tool report for that query (Doc ID 215187.1)
 
--> SOLUTION:

A) From AWR report, seems you have high waits on "Latch: cache buffer chain":

Latch: cache buffer chain may occur due to high buffer get queries/hot blocks/high concurrent requests for similar data.

The more rows are in one block the higher the risk for cache buffer chain contentions when many sessions accessing the same block concurrently.
Reducing the number of rows per block may help to overcome cache buffer chain contention as sessions may not request to access the same block so frequently.

Well we need to find the hot block for latch: cache buffers chains.
See : How To Identify a Hot Block Within The Database Buffer Cache. (Doc ID 163424.1)

In order to reduce contention for this object the following mechanisms can be put in place:

1) Examine the application to see if the execution of certain DML and SELECT statements can be reorganized to eliminate contention on the object. See:
Note:1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention
2) Decrease the buffer cache -although this may only help in a small amount of cases.
3) DBWR throughput may have a factor in this as well. If using multiple DBWR's then increase the number of DBWR's. See:
Note:62172.1 - Understanding and Tuning Buffer Cache and DBWR
4) Increase the PCTFREE for the table storage parameters via ALTER TABLE or rebuild. This will result in less rows per block.
5) Consider implementing reverse key indexes (if range scans aren't commonly used against the segment)

B) About the top sql consumer with sqlid =a49xsqhv0h31b (Eg sqlid)

Optimizer statistics for table "APPLSYS"."FND_CONC_REQUEST_ARGUMENTS" are stale.
The optimizer requires up-to-date statistics for the table and its indices in order to select a good execution plan.

- Consider collecting optimizer statistics for this table and its indices.

execute dbms_stats.gather_table_stats(ownname => 'APPLSYS', tabname =>
'FND_CONC_REQUEST_ARGUMENTS', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO', cascade => TRUE);

No comments:

Post a Comment