Actively monitor the performance of Oracle 9i using scripts and queries

Monitoring over 250 configuration parameters and thousands of measurements is not an easy task for Oracle administrators to monitor the overall health of their Oracle databases. Oracle provides a variety of tools to monitor performance, but this is still too much. To effectively monitor the health of your Oracle database, you need to familiarize yourself with the following scripts and queries:

• Data Buffer hit rate alertThe number of times the data buffer hit rate is lower than the preset threshold value is reported.
• Redo log space request alertAn error is prompted when the number of requests is greater than 0. If this happens, you may need to addLog_bufferParameter value.
• Shared Pool contention alertYou will be prompted for shared pool contention and lock-related issues.
• System wait alarmThe query will ask about the Oracle event structure to determine that there are too many waiting events due to Contention.
• Database buffer failure alertThe query will find the database buffer failure rate. If the database buffer failure rate exceeds. 02, you need to increaseShared_pool_size.
• Database writer contention alertCheck Points for the total length of bad queries, write requests, and database writer DBWR. When the Write Request Length is greater than 3 or greater than the number of DBWR checkpoint waits, you need to adjust the database writer process.
• Data Dictionary failure rate alertYou will be prompted for too many requests for the data dictionary metadata. Sometimes you can addShared_pool_sizeinit.oraThe value of this parameter to alleviate this problem.
• Data Dictionary object warningThe report will reveal the internal contention for Oracle data dictionaries and the number of times that dictionary metadata requests are too high.

Take a closer look

Now let's take a closer look at how these scripts work. The STATSPACK tool processes Oracle configuration information by time and records the information in multiple tables. The names of these tables reflect the Oracle internal view table v $.Stats $ sysstatAndStats $ SQL _summary. With this in mind, you can write some simple Oracle queries that will display performance trends. Then you can process the performance information and send it to the prediction model, such as linear regression, which accurately tells you to change the Global region of your System, System Global Area, SGA) the number of times the internal structure is correct.
ListingExamples of using such performance information are provided. This script will generate a continuous total of database buffer failure rates over a period of time and referenceStats $ librarycacheTable.
The output of this script indicates that you need to passCron jobOrDbms_jobIsShared_pool_sizeSchedule additional memory, seeFigureA.

Figure

Library Buffer failure rate Script output

Dynamic Performance Reconfiguration

TableYou can easily view some major events that can cause dynamic configuration reconfiguration. To illustrate this, I will only focus on the main areas of the SGA that appear in the table.
Table

Memory Area	Too small	Big case
Sharing pool	Database buffer failed	No failure
Data Buffer Zone	Hit rate <90%	Hit rate> 95%
PGA collection	The number of times of multiple executions is too high.	100% best execution

Major re-configuration triggers

Obviously, the failure rate of the database buffer is too high, indicating that the shared pool is too small. If the data buffer hit rate of any of the seven data buffer pools in Oracle is lower than 90%, you should split the memory from other areas of the database, re-allocate to the data buffer. For sorting operations, you should look at the percentage of the best execution in the Global Program Global Area, PGA) of the Program, the target parameter value of the PGA set is increased when the optimal execution rate of sorting operations is lower than 95%.
The rules for data buffer and shared pool size are straightforward, and the newPga_aggregate_targetParameters can ensure further research on this information. As a general rule, you must changePga_aggregate_targetValue:

• WhenV $ sysstatValue-usedEstimation of one passPGAStatic MemoryEstimated PGA memory for one-pass) ExceedsPga_aggregate_targetYou need to addPga_aggregate_target.
• WhenV $ sysstatValue-used for staticWorkspace execution-Multiple passesWorkarea executions-multipass) If it is greater than 1%, the database will be able to benefit from the extra memory.

You may allocate too much memory for the PGA.V $ sysstatValue of the data column ――Workspace execution-BestWorkarea executions-optimal) When it reaches 100%, you may need to consider reducingPga_aggregate_target.
As you can see, active monitoring of Oracle databases is quite complex. Since hundreds of measurements and parameters need to be monitored and reset, Oracle adjustment is very challenging. However, with the Oracle performance testing tool and the knowledge of resetting the server, you can start to adjust everything.