Introduction to Oracle Performance Optimization

Recently, I just made a performance optimization job. According to the statspack report of one week, I wrote an optimization solution.

I. Existing major performance problems of the system
According to the typical statspack report in the last week, the main wait events in the system are as follows:

Top 5 timed events

~~~~~~~~~~~~~~

Event waits time (s) % Total Ela time

-----------------------------------------------------------------------

CPU time 5,819 46.04

Wait for unread message on Broadcast Channel 3,356 3,374 26.70

DB file sequential read 1,100,056 2,454 19.41

Buffer busy waits 51,054 274 2.16

DB file scattered read 154,540 220 1.74

-------------------------------------------------------------

46% of the database time is spent on CPU-related operations. CPU time can be divided into three parts: CPU time = parse time CPU + recursive CPU usage + other CPU usage. Among them, parse time CPU indicates the CPU time spent parsing SQL statements, the recursive CPU usage indicates the CPU time consumed by recursive SQL operations. The other CPU usage indicates the CPU time consumed by logical reads.

Change the formula above. Other CPU usage = CPU time-Parse time CPU-recursive CPU usage, bringing the data in our statspack report to the formula other CPU usage = 5819-584-109 = 5126. It can be seen that 88% of the CPU time is spent on the Logical Data Reading operation, in addition, 10% of the CPU time is spent on Parsing SQL statements. We can start with optimizing SQL statements to reduce logical reading. In addition, we can also do some work to reduce the number of SQL parsing.

The wait event wait for unread message on broadcast channel is an idle event, which we do not need to handle.

DB file sequential read wait events account for 19% of the total time, indicating that the data is read physically. To reduce the waiting time, you can consider two aspects: optimize the SQL statements and reduce unnecessary physical reads; in addition, you can increase the data buffer to cache as much data as possible into the memory.

Another wait event buffer busy Waits is the memory wait caused by a large number of logical reads. This event can also be eliminated by optimizing SQL statements.

Ii. Solutions
Based on the above problems in the system, the following optimization scheme is proposed:

1) Optimize some SQL statements with more logical reads and physical reads, which can effectively reduce Data Reading and reduce memory contention;

2) bind variables to some frequently executed SQL statements to reduce the CPU time spent on Parsing SQL statements and the memory plug-in lock;

3) increase the data buffer size appropriately to reduce physical reads.