Oracle监控脚本.docVIP

Oracle监控脚本 oracle数据库的sql执行情况的SQL语句采集 进行系统的top sql语句的采集 1：某段时间sql执行花费时间(按秒) 参考sql语句，如执行时间大于3秒的sql语句的抓取脚本： --某段时间sql执行花费时间(按秒) select a.username, a.sql_text, round( a.elapsed_time_s ,1 ),花费处理，按秒 as remark from ( select ds.username,Sql_Text,round((sum(sa.elapsed_time) / sum(sa.EXECUTIONS)) / 1000000, 3) as Elapsed_Time_S from v$sqlarea sa inner join dba_users ds on sa.PARSING_USER_ID = ds.user_id where sa.EXECUTIONS 0 and (sa.elapsed_time / sa.EXECUTIONS) / 1000000 1 --and upper(ds.username) like %USERNAMe% ---特定用户下执行的SQL语句。实际执行根据系统替换掉。 group by ds.username, Sql_Text order by elapsed_time_s desc ) a where a.elapsed_time_s 3 --大于3秒 2：某段时间sql执行时消耗内存(按K) 参考sql语句，如sql执行耗用内存大于256K的sql语句的抓取脚本： --某段时间sql执行时消耗内存(按K) select a.username, a.sql_text, round(a.usedmemory/1024,1),内存耗,按 K as remark from ( select ds.username,Sql_text,round(sum(sa.sharable_mem ) / sum(sa.EXECUTIONS), 3) as UsedMemory from v$sqlarea sa inner join dba_users ds on sa.PARSING_USER_ID = ds.user_id where sa.EXECUTIONS 0 --and upper(ds.username) like %USERNAMe% ---特定用户下执行的SQL语句。实际执行根据系统 替换掉。 group by ds.username, Sql_text order by UsedMemory desc ) a where a.usedmemory / 1024 256 --大于256k 3：某段时间sql执行的CPU、IO、执行次数、耗用内存等抓取 参考sql脚本语句： 采集结果的数据存储的表结构定义： create table MSMS_CURRENT_SQL ( SQL_ADDRESS RAW(32) not null, SQL_HASH_VALUE NUMBER not null, CREATE_TIME TIMESTAMP(6), STATUS VARCHAR2(8), MACHINE VARCHAR2(64), SQL_TEXT VARCHAR2(4000), CPU_TIME NUMBER, IOR NUMBER, LINES NUMBER, MEMS NUMBER, TIMES NUMBER ); create table MSMS_CURRENT_SQL_SUM ( SQL_ADDRESS RAW(32) not null, SQL_HASH_VALUE NUMBER not null, CREATE_TIME TIMESTAMP(6), STATUS VARCHAR2(8), MACHINE VARCHAR2(64), SQL_TEXT VARCHAR2(4000), CPU_TIME NUMBER, IOR NUMBER, LINES NUMBER, MEMS NUMBER, TIMES NUMBER ); oracle命令行窗口中定义的sql抓取脚本： declare sqlstatement varchar2(4000); curtime timestamp; len number; str varchar2(64); CURSOR cursor_msms_current_sql IS SELECT * FROM msms_current_sql; CURSOR sqlrec(addr0 raw,hashvalue0 number) is select sql_text from v$