oracle的monitor脚本在哪,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详细的使用说明参看以下：