Statspack report and Sam configuration note

Recently, I just made a performance optimization job. According to the one-week statspack report, 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, where, parse

Time CPU indicates the CPU time spent parsing SQL statements, and recursive CPU usage indicates recursive SQL statements.

CPU time consumed by the Operation. Other CPU usage indicates the CPU time consumed by logical reading.

Change the formula above. Other CPU usage = CPU time-Parse time CPU-recursive

CPU usage: bringing the data in our statspack report to this 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.

10% of the CPU time is spent on Parsing SQL statements. We can start with optimizing SQL statements to reduce the logic

Read. 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.

To be handled.

DB file sequential read wait event accounted for 19% of the total time, which indicates that the data is read physically, reducing the waiting time

You can consider the following two aspects: optimize the SQL statement to reduce unnecessary physical reads.

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.

You can optimize SQL statements to eliminate them.

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 usage.

Competition;

2) bind variables to some frequently executed SQL statements to reduce the CPU time and memory spent on Parsing SQL statements

Plug lock;

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

Several event interpretations of Oracle10g
Http://www.itpub.net/showthread.php? S = & postid = 8275075 # post8275075
Http://www.itpub.net/showthread.php? S = & threadid = 833280.
Case study Oracle error: ORA-00054
Http://searchdatabase.techtarget.com.cn/tips/332/2117332_2.shtml
Http://www.eygle.com/archives/2005/10/oracle_howto_kill_session.html
You can use kmtune to view
We recommend that you use Sam to adjust the file instead of modifying the file. It is troublesome and insecure. The Sam method is as follows:
# Sam
Press enter To Go To The Sam interface. The up and down arrows are used to adjust the options. Press enter to enter. The Tab key is used to return to the menu.
--->
Sam Kernel configuration-> enter
Select
Sam configurable parameters, press enter to go to the parameter adjustment page.
Select a parameter from the up/down arrow. press tab to move the cursor to actions and press enter to display the drop-down menu.

Select modify parameter able parameter... and press enter to go to the adjustment page.
The tab key moves the cursor. After Entering a new parameter, move the cursor to OK and press Enter. You may be prompted to modify another

Parameters. Exit after completion
Go to actions ----> process new kernel. Wait a few minutes and you will be prompted to restart the system.