How to write an automated script for troubleshooting Oracle performance problems

Editor to share with you how to write Oracle performance troubleshooting automation scripts, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

Enter Oracle Performance troubleshooting automation scripts: Perfbot Maria

Function: execute the script periodically, and send the waiting event of SQL statement for more than n minutes to SQL and the related execution plan and SQL monitor information to the operator in the form of e-mail.

Main script: pbm_wait.sh

#! / bin/bash

Export ORACLE_SID=$1

Export SQL='sqlplus / as sysdba'

Export EXEC_MIN=5

Export ORACLE_HOME=/opt/app/oracle/product/11GR2

Export PATH=$PATH:$ORACLE_HOME/bin

Export DIR=/home/oracle/dba/pb_maria

# export MY_DATE= `date'+% d% b% yearly% k% M``

Export LOG=$ {DIR} / pbm_$ {ORACLE_SID} _ wait.log

Export RESULT=pbm_$ {ORACLE_SID} _ wait.result

Export TEMP_RESULT=pbm_$ {ORACLE_SID} _ wait_temp.result

Export FINAL_RESULT=pbm_wait_$ {ORACLE_SID} _ final.result

Export GRACE=pbm_$ {ORACLE_SID} _ grace.log

Export COUNT=1

Export MAIL='mailx-s "Perfbot maria report of DB $ORACLE_SID"'

Cd $DIR

$SQL > / dev/null $RESULT

For i in `awk'{print $2}'$RESULT`

Do

Array [$COUNT] = $I

COUNT=$ (($COUNT+1))

Done

If [$COUNT-ne 1]

Then

For ((iTun1; I > $TEMP_RESULT)

Fi

Done

Fi

If [[- e $TEMP_RESULT]]; then

Sort-u $TEMP_RESULT > $FINAL_RESULT

COUNT=1

For i in `awk'{print $1}'$FINAL_ RESULT`

Do

Array [$COUNT] = $I

COUNT=$ (($COUNT+1))

Done

For ((iTunes 1; I / dev/null'& & 1century Magazine type = > 'text') from dual

/

Attachments do not seem to be supported here. Note as follows:

Note that the script must be used in the / home/oracle/dba/pb_maria path; create results and reports paths under that path to store historical information.

Cronjob I set it every 5 minutes.

This script requires the Oracle database server to connect to the Internet in order to send mail. If it is a private network machine, you can consider adding a mail server where both private and public networks are located, and transmit the information of the mail you need to send to this mail server, and then send the mail regularly.

These are all the contents of the article "how to write automated scripts for troubleshooting Oracle performance problems". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!