Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Understanding from AWR

girishkaleMar 13 2020 — edited Mar 16 2020

Hello Experts:

I have a DB performance issue and got AWR reports in my hand.

The OS Statistics and Time model is as below:

TimeModel.jpg

Top foreground events are as under:

TopTimedForegroundEvents.jpg

System Statistics show the below:

SystemStatistics.jpg

And Segment statistics show the below:

SegmentStatistics_1.jpg

SegmentStatistics_2.jpg

SegmentStatistics_3.jpg

While the Global system statistics show leaf block splits

GlobalSystemStatistics.jpg

SQL Order by cluster wait shows a high value for some inserts

SQLByCluster.jpg

What i make out of it is:

  1. Figure out the usability of indexes on the table job_run_event. ITL wait events in segment statistics suggest that too many rows being added leading to lead node split? Thus at first question the number of so many indexes on the same table?
  2. Reduce the number of updates on job_run and job_result_device if possible?
  3. Considerable amount of DB time is actually spent on cluster sync up than actually heavily performing anything on the DB.

Please share your thoughts.

If you need any other screen shots, please do let me know. I'll get them.

Thank you.

This post has been answered by Jonathan Lewis on Mar 16 2020
Jump to Answer
Comments
Post Details
Added on Mar 13 2020
4 comments
960 views