Shulou(Shulou.com)05/31 Report--

This article focuses on "Oracle slow SQL monitoring script code sharing", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "Oracle slow SQL monitoring script code sharing"!

Online Oracle is ready to implement a monitoring script similar to MySQL slow query to send email alarms regularly when the query time exceeds the specified SQL. The implementation process is recorded as follows:

The main idea is to obtain the slow SQL of the snapshot per hour through several views of DBA_HIST. In order not to affect the online environment, the script is deployed on its own monitoring side, and the DBLINK regularly grabs the data of the online production database to the monitoring database. After simple processing, the report in csv format is obtained and sent to the mailbox.

The scheduled script queries once an hour.

00 * / opt/scripts/oracle/get_slow_query.sh

The script content is as follows

[oracle@59-Mysql-Test ~] $cat / opt/scripts/oracle/get_slow_query.sh

#! / bin/bash

Errlog= "/ opt/scripts/oracle/sqlerror.log"

Sq_data= "/ opt/scripts/oracle/slow_query_data.xls"

Check_file= "/ opt/scripts/oracle/slowsql_check.log"

Send_mail_check= "/ opt/scripts/oracle/send_mail.chk"

Export ORACLE_BASE=/u01/app/oracle

Export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

Export ORACLE_SID=oramon

Export PATH=/usr/sbin:$PATH

Export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:$PATH

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

Export CLASSPATH=/u01/app/oracle/product/11.2.0/db_1/JRE:/u01/app/oracle/product/11.2.0/db_1/jlib:/u01/app/oracle/product/11.2.0/db_1/rdbms/jlib

Cd / opt/scripts/oracle/

$ORACLE_HOME/bin/sqlplus-S sqmon/oracle @ main > ${errlog}

Cat ${errlog} | grep-v 'Call completed.' | grep-v' > ${check_file}

[- s ${check_file}] & & / bin/mail-s "Oracle slow query check error" xxx@xxx.com < ${check_file}

Cat ${sq_data} | grep-v'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.