以下脚本主要针对oracle 11g版本
Find_sql.sql: 找出需要监控SQL脚本,输入值sql_text。
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,report_sql_monitor.sql:报告相关的执行计划等信息
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 ' '
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/354732/viewspace-711019/