Oracle AWR Snapshot

よく忘れるので備忘として。

snapの手動実行
SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
シェルから直近のAWR Reportを出力

# get recent snapshot id
snap_to_tmp=”sqlplus -s / as sysdba << EOF
set feedback off
set head off
set verify off
select max(snap_id) from dba_hist_snapshot;
exit 0;
EOF

snap_from=expr ${snap_to_tmp} - 1
snap_to=expr ${snap_from} + 1
awr_out_text=awrrpt_${snap_from}_${snap_to}.txt

# create awr report (TEXT)
sqlplus -s / as sysdba << EOF
@@${awrrpt}
TEXT
0
${snap_from}
${snap_to}
/tmp/${awr_out_text}
exit 0;
EOF

シェルから直近期間にて処理時間の長いSQL TOP10のSQL Reportを出力
# get recent snapshot id
snap_to_tmp=”sqlplus -s / as sysdba << EOF
set feedback off
set head off
set verify off
select max(snap_id) from dba_hist_snapshot;
exit 0;
EOF

snap_from=expr ${snap_to_tmp} - 1
snap_to=expr ${snap_from} + 1
# get sql_id
sql_ids=”sqlplus -s / as sysdba << EOF
set feedback off
set head off
set verify off
select sql_id from
(
select
SUBSTR(TO_CHAR(DBMS_LOB.SUBSTR(sql_text,4000,1)),1,100)
,elapsed_time_delta/1000/1000 as elapsed_sec
,txt.sql_id
,rank() over(PARTITION BY SUBSTR(TO_CHAR(DBMS_LOB.SUBSTR(sql_text,4000,1)),1,100) ORDER BY (elapsed_time_delta) DESC) AS rank
from
dba_hist_sqlstat stat
,dba_hist_sqltext txt
,dba_hist_snapshot snap
where
snap.snap_id=${snap_to}
and stat.sql_id=txt.sql_id
and stat.snap_id=snap.snap_id
and parsing_schema_name like '%'
)
where rank < 11 ;
exit 0;
EOF
“if [ “${sql_ids}” != “” ]
then
while read line
do
sqlplus -s / as sysdba << EOF
@?/rdbms/admin/awrsqrpt.sql
TEXT
0
${snap_from}
${snap_to}
${line}
/tmp/awrsqrpt_${line}_${snap_from}_${snap_to}.txt
exit 0;
EOF
done <<END
${sql_ids}
END
else
echo “There is no sql_id.”
fi

シェアする

  • このエントリーをはてなブックマークに追加

フォローする