Troubleshooting of performance faults caused by high version count of Oracle SQL

The user reports that the front-end business response is slow. log on to the database to obtain the awr report. The system recovers after kill related sessions. For more information about kill sessions, see metalink id 786507.1.
In addition to Rollback per transaction %, Load Profile reaches 95%. Other indicators seem normal.




As a result, we can further view the transaction rollback information. We can see that the number of transaction rollbacks per second (transaction rollbacks) is only 145.13, which is far smaller than the user rollbacks (2,454.12)
Statistic Total per Second per Trans
Rollback changes-undo records applied 830,757 688.73
Rollbacks only-consistent read gets 152,722 126.61
Transaction rollbacks 175,061 145.13 0.06
Transaction tables consistent read rollbacks 322 0.27
Transaction tables consistent reads-undo records applied 96,665 80.14 0.03
User commits 168,522 139.71 0.05
User rollbacks 2,960,222 2,454.12 0.95
In distributed transactions, for example, using dblink to obtain data, the base of transaction rollbacks is increased in session statistics regardless of whether the user issues a commit or rollback. Commit or rollback
Transaction rollbacks will add 1.
When a user issues the rollback command, no matter whether there is a Transaction user rollbacks, it will increase. From the numerical point of view
Rollback per transaction % should be equal to user rollbacks/(user commits + user rollbacks), so the high Rollback rate should not be the main cause of performance problems.

1 2 Next Page