An example of system performance failure caused by oracle Audit

When the CPU usage of the Database Host is too high several times, the alarm is reported, resulting in low performance of the host. After some investigation, the cause of the problem is found out and the fault is solved. The specific process is as follows:
Environment: Host suse linux 10 (64-bit) ORACLE11G (64-bit)
I. Alarm information (fault presentation)
• The current CPU usage of the suselinux server is 90.93, and the threshold value range is [90-100]. The occurrence time is 11:17:01.
• The current CPU usage of the suselinux server is 92.20, and the threshold value range is [90-100]. The occurrence time is 14:46:02.
• The current CPU usage of the suselinux server is 92.54, and the threshold value range is [90-100]. The occurrence time is 15:14:01.
 
Ii. analysis process (fault Investigation)
1. Use the TOP command to view system resources
 

Shift + P: sort by CPU usage in descending order shift + M: sort by memory usage in descending order
We found that the usage of ORACLE processes was high, and the CPU usage soared to more than 90% several times, seriously affecting system performance.
2. Use the SAR command to check IO and transfer information:
Note: To use the sar command in suse linux, install the sysstat package.

It is found that the amount of data read and written per second is large.
3. Analyze AWR reports
 

 

AWR reports show that audit tables are updated frequently and many reports are updated on the current day.
4. Connect to the database for audit and index Verification

If the index fails, data retrieval slows down, SQL statement execution efficiency is low, and CPU consumption is high.
5. Solve the Problem and disable audit re-Indexing

6. To be optimized later:
Check SQL statements to avoid excessive CPU resource consumption due to buffer cache conflicts caused by high concurrent execution of inefficient statements.