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;
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博客 ” ，链接：https://blog.itpub.net/29337971/viewspace-1816617/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
