監控資料庫指令碼

jelephant發表於2015-10-27
資料庫指令碼
 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章