ITPub博客

首页 > 数据库 > Oracle > 监控数据库脚本

监控数据库脚本

原创 Oracle 作者:jelephant 时间:2015-10-27 15:39:09 0 删除 编辑
 select count(*),status from v$session group by status;

select sql_id,event,count(*) from v$session where wait_class<>'Idle' group by sql_id,event order by 3;
select sql_id,event,count(*) from v$session where wait_class<>'Idle' and event<>'SQL*Net message to client' group by sql_id,event order by 3 ;
select sql_text from v$sqlarea where sql_id='0zvx4nubub7ta';

set long 9999999
set long 99999
select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&object_owner')) from dual; 


--查询锁的sql
set linesize 160
set pagesize 0
col SQL_TEXT for a50
select
o.owner,
object_name,
object_type,
machine,
s.username,
s.sid,
s.serial#,
p.spid,
sql.SQL_TEXT
from v$locked_object l,
  dba_objects o ,
  v$session s,
  v$process p,
  v$sql sql
where l.object_id=o.object_id
and l.session_id=s.sid
and p.addr=s.paddr
and s.sql_id=sql.sql_id
order by 6;

--查询运行时间长的sql
set linesize 400
col PROGRAM for a20
col SQL_EXEC_START for a10
col for sid a10
col username for a20
col elapsed_time/1e6 for 9999999999
col sql_text for a40
col elapsed_time for a10
col cpu_time for a10
col username for a10
select
PROGRAM,
SQL_EXEC_START,
sid,
username,    
sql_id,
elapsed_time/1e6,
cpu_time/1e6,
buffer_gets,
disk_reads,
sql_text
from v$sql_monitor
where status = 'EXECUTING'
order by elapsed_time;


--查找事件
set linesize 500
select
sql_id,event,PROGRAM,count(*)
from DBA_HIST_ACTIVE_SESS_HISTORY
where
sample_time
between
to_date('2014/11/28 14:00:00','yyyy/MM/dd HH24:MI:SS')
and
to_date('2014/11/28 15:00:00','yyyy/MM/dd HH24:MI:SS')
--and event='latch free'
group by sql_id,event,PROGRAM
order by count(*);
select MACHINE,PORT,REMOTE_INSTANCE#,SESSION_TYPE,wait_time,TIME_WAITED/1e6,CURRENT_OBJ#,PROGRAM from DBA_HIST_ACTIVE_SESS_HISTORY
where sql_id='bwk615747kt6y' order by TIME_WAITED;
select event,count(*)
from DBA_HIST_ACTIVE_SESS_HISTORY
where
sample_time
between
to_date('2014/11/05 02:40:00','yyyy/MM/dd HH24:MI:SS')
and
to_date('2014/11/05 03:20:00','yyyy/MM/dd HH24:MI:SS')
group by event;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29337971/viewspace-1816617/,如需转载,请注明出处,否则将追究法律责任。

上一篇: RAC故障诊断脚本
下一篇: 多路径配置
请登录后发表评论 登录
全部评论

注册时间:2013-12-07

  • 博文量
    143
  • 访问量
    626873