oracle 11g監控SQL指令碼

以下指令碼主要針對oracle 11g版本

Find_sql.sql: 找出需要監控SQL指令碼，輸入值sql_text。

set verify off
set pagesize 999
col username format a13
col prog format a22
col sql_text format a41
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col etime format 9,999,999.99

select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username,
sql_text
from v$sql s, dba_users u
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and u.user_id = s.parsing_user_id
/

SQL_Monitor.sql： 列出所監控SQL語句的執行情況

col key format 999999999999
col sql_exec_start for a25
col sql_text for a60 trunc
break on sql_id on sql_text
set colsep '|'
break on sql_id on plan_hash_value
col sql_exec_start for a20
select sid, sql_id, sql_exec_id,
       to_char(sql_exec_start,'DD-Mon-YY HH24:MI:SS') sql_exec_start, 
       sql_plan_hash_value plan_hash_value,
       elapsed_time/1000000 etime, buffer_gets, disk_reads
from v$sql_monitor
where sid like nvl('&sid',sid)
and sql_id like nvl('&sql_id',sql_id)
and sql_exec_id like nvl('&sql_exec_id',sql_exec_id)
order by sql_id, sql_exec_id
/
set colsep ' '

report_sql_monitor.sql：報告相關的執行計劃等資訊

set long 999999999
set lines 280
col report for a279
accept sid  prompt "Enter value for sid: "
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   session_id=>nvl('&&sid',sys_context('userenv','sid')),
   session_serial=>decode('&&sid',null,null,
sys_context('userenv','sid'),(select serial# from v$session where audsid = sys_context('userenv','sessionid')),
null),
   sql_id=>'&sql_id',
   sql_exec_id=>'&sql_exec_id',
   report_level=>'ALL') 
as report
from dual;
set lines 155
undef SID

有關於11g Real-time SQL monitoring詳細的使用說明參看以下：

http://www.oracle-base.com/articles/11g/RealTimeSqlMonitoring_11gR1.php

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-711019/，如需轉載，請註明出處，否則將追究法律責任。