Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”. This article also participates in the “Digitalstar Project” to win the creation gift package and challenge the creation incentive money
How to manage awR by scraping, which I have written before, will not be covered. Juejin. Cn/post / 700204… Juejin. Cn/post / 700242… This paper mainly discusses how to use AWR report in Oracle standalone environment and analyze database problems according to actual environment.
The background,
The operation and maintenance personnel reported that the system was stuck in the morning and the page could not be loaded and used. In the afternoon, the system automatically returned to normal. The cause was not found at the software level, so they requested assistance to check the database level.
Second, analysis and positioning
2.1 Confirm the time range
It is confirmed with the operation and maintenance personnel that the abnormal service period in the morning is 9:30-11:30, and the normal service period in the afternoon is after 13:30.
2.2 Capturing AWR Reports
Run script @? On database server / RDBMS /admin/awrrpt. SQL, capture AWR reports of two periods. Because the default generation frequency of AWR snapshots is 1 hour, capture AWR reports of normal periods 10:00-11:00 and abnormal periods 14:00-15:00.
2.3 Viewing AWR Reports
2.3.1 Checking the Database Pressure
What is shown at the beginning of the AWR report.
The smaller Elapsed/DB Time, the greater the database pressure.
The default generation frequency of AWR snapshots is 1 hour. Therefore, you can view the DB Time directly. A larger value changes the database pressure.
Database pressure when services are normal: 
The busier the service is, the higher the DB Time is, but the DB Time usually does not exceed 2000(mins).
2.3.2 Viewing database Waiting Events
Waiting for events when business is normal: 
Enq: HW – Contention for the above direct query exception. (The causes and handling methods of most Oracle wait events can be found online.)
The lock provided to prevent multiple processes from modifying HWM at the same time is called HW lock. A process that wants to move an HWM must acquire an HW lock. If contention occurs during HW lock acquisition, wait for ENQ: HW-Contention event. HW lock contention is mostly caused by a large number of INSERTS, but occasionally HW lock contention occurs in the rollback segment due to a large number of updates. In the case of update, the size of the middle segment of the table is small, but rapid expansion of the rollback segment is required during the creation of the rollback data. HW lock contention is a common wait phenomenon during rapid space expansion and can sometimes cause serious performance degradation.
According to the above waiting event information, it can be determined that the existence of SQL in the database causes lock contention.
2.3.3 Checking the SQL Time of the Database
SQL time when services are normal: 
Executions: indicates the number of SQL Executions.
Elapsed Time per Exec (s) : Elapsed Time per SQL execution.
%Total: indicates the percentage of SQL time consumed.
In normal times, the first SQL was executed 1,838 times within an hour (snapshot time), 0.94s for each execution, 1,733.81s for a total time, the normal speed. Within one hour (snapshot capture time), the SQL with the highest ranking is executed 2,972 times (108.43 seconds each time). The total elapsed time is 322,249.95 seconds. The percentage of SQL that takes up 96.42% of abnormal time periods is displayed. Combined with the above waiting events, it can be determined that the changed SQL causes contention for database locks.
2.3.4 Viewing SQL Information about capturing the Database
Click sql_ID to go to the detailed SQL page.
Third, the captured SQL to the system r&d personnel
The captured SQL will be handed over to the system research and development personnel, combined with business logic optimization.