Using Oracle storage to analyze the performance of Unix systems (1)

Article Title: using Oracle storage to analyze Unix system performance (1 ). Linux is a technology channel of the IT lab in China. Includes basic categories such as desktop applications, Linux system management, kernel research, embedded systems, and open source.

As an Oracle database administrator in UNIX systems, it is undoubtedly very important to monitor the operating system performance at all times. Fortunately, UNIX systems provide a large number of monitoring commands, such as vmstat, iostat, sar, top and so on. These monitoring commands output results on the Character interface, coupled with the powerful shell Analysis Function in the UNIX system, so we only need to write some scripts to implement automatic background monitoring, when there is a problem, the system automatically sends an email to the DBA.

In fact, similar monitoring Scripts may be everywhere, but a new idea mentioned in this Article is to use the statspack space of the Oracle database to store the monitoring results, leveraging the inherent retrieval advantages of databases, it is easier and more intelligent than the monitoring result files generated by shell Analysis or manual analysis in the past, and can achieve more persistent and extensive monitoring.

Most of the materials in this article are sourced from Donald K. burleson's Oracle9i UNIX Administration Handbook, but some errors and unreasonable places in the script were modified, and some features were added, all the testing environments in this article are based on Sun Solaris 8-socket 64-bit + Oracle9.2.0.5.

The first version of this article mainly uses the vmstat command to monitor memory consumption and CPU wait as an example. It will add monitoring scripts for other commands such as iostat.

　◆ Train of Thought

First, install statspack, and then manually create a table for storing the output results of the vmstat command. Then, execute vmstat regularly as a shell to insert all the results into the database table, finally, an alarm is triggered when the threshold value is exceeded through the data in the SQL timing analysis table. At the same time, based on the data in the table, we can also make a system performance trend report.

　　◆ Vmstat script and steps

　　1. Install statspack

Sqlplus "/as sysdba"

SQL> @? /Rdbms/admin/spcreate. SQL

After setting the password for the perfstat user as prompted, I chose the SYSAUX tablespace as the default tablespace FOR THE perfstat user, instead of creating another tablespace.

　　2. Create a stats $ vmstat table

SQL> connect perfstat/perfstat

SQL> @ create_vmstat_tab. SQL

# Create_vmstat_tab. SQL

Drop table stats $ vmstat;

Create table stats $ vmstat

(

Start_date date,

Duration number,

Server_name varchar2 (20 ),

Running_queue number,

Waiting_queue number,

Swap_in number,

Swap_out number,

Kbytes_page_in number,

[1] [2] [3] Next page