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

#!/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"

[email protected]

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