oracle 执行计划 变化,转 oracle 监控执行计划突然变化

转

http://www.oracle-wiki.net/startscriptsplanmonitor

A Script to Monitor Plan Changes

Oracle Database » Script Library » A Script to Monitor Plan Changes

Description

The following script can be used monitor and alert on plan changes. Details of its use can be found in the headers of the script.

Plan_Change_Alert.ksh

#!/bin/ksh -x

############################################################################

#

# Author : Mark Ramsay

#

# History Date Name Reason

# ---- ---- ------

# 18 May 2011 Mark Ramsay Version 1.

#

# Description

#

# This script generates a report that shows if a SQL Plan has changed

# for a given SQL ID. It is useful for tracking plans for stubborn pieces

# of SQL that may have a few good plans and the occasional bad plan.

#

# The range of dates can be changed by setting SDS_range. However,

# this script would normally be scheduled each day the range will therefore

# be 1. i.e. Changes in the last 24hrs

#

# The user should set the variable SDS_sqlid to the SQLID that is being

# monitored. The variable SDS_hash_values should be set to the

# plan_hash_values that are acceptable for the given SQLID.

#

# If a new plan_hash_value is generated for the given SQLID, then

# the script will highlight this in the report.

#

# The report can then be mailed out to individuals to look into the plan

# change.

#

############################################################################

#

# Define Variables

#

export ORACLE_SID=MYSID

export ORACLE_HOME=$(grep ^$ORACLE_SID: /var/opt/oracle/oratab |awk -F\: '{print $2}')

export ORACLE_BASE=/u01/app/oracle

export PATH=.:/usr/local/bin:/bin:/usr/sbin:/usr/bin:$ORACLE_HOME/bin

SDS_date=`/bin/date '+%e_%B_%Y'|sed -e 's/ //'`

SDS_sqlid="'SQLID1','SQLID2'"

SDS_hash_values="HASH1,HASH2"

SDS_mail_addr=myemail@mydomain.com

SDS_range=1

SDS_output=`$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' <

set pagesize 0

set feedback off

set linesize 128

set heading off

set echo off

SELECT distinct PLAN_HASH_VALUE

FROM dba_hist_sqlstat q,

(

SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap

FROM dba_hist_snapshot ss

WHERE ss.begin_interval_time BETWEEN (SYSDATE - $SDS_range) AND SYSDATE

) s

WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap

AND q.sql_id IN ( $SDS_sqlid)

AND q.plan_hash_value not in

($SDS_hash_values)

/

exit;

EOF`

if [ -z "$SDS_output" ];

then

echo "All,

Explain Plan Change for SQLIDs: $SDS_sqlid - No

Regards

" | mailx -s "Explain Plan Alert Report $SDS_date" $SDS_mail_addr

else

echo "All,

Explain Plan Change for SQLIDs: $SDS_sqlid - Yes

DBA to investigate.

Plan Hash Values: $SDS_output

Regards

" | mailx -s "Explain Plan Alert Report $SDS_date" $SDS_mail_addr

fi

exit 0

dba_hist_snapshotdba_hist_sqlstatkshplanscripttuning

标签：bin,SDS,script,ORACLE,export,plan,监控,oracle,执行

来源： https://www.cnblogs.com/feiyun8616/p/11065379.html