select *
from v$sysmetric a
where a.METRIC_NAME in
(‘Database CPU Time Ratio’, ‘Database Wait Time Ratio’)
and a.INTSIZE_CSEC = (select max(intsize_csec) from v$sysmetric);
————————————-查询数据库中过去30分钟引起最多等待的sql语句—————-
select ash.USER_ID,
u.username,
sum(ash.WAIT_TIME) ttl_wait_time,
s.SQL_TEXT
from v$active_session_history ash, v$sqlarea s, dba_users u
where ash.SAMPLE_TIME between sysdate – 60 / 2880 and sysdate
and ash.SQL_ID = s.SQL_ID
and ash.USER_ID = u.user_id
group by ash.USER_ID, s.SQL_TEXT, u.username
order by ttl_wait_time desc
—————————————–查询数据库中的等待事件———————-
select event, count(*)
from v$session_wait
group by event
order by count(*) desc
—————————————查询数据库过去15分钟最重要的等待事件—————
select ash.EVENT, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
from v$active_session_history ash
where ash.SAMPLE_TIME between sysdate – 30 / 2880 and sysdate
group by event
order by total_wait_time desc
—————————————-在过去15分钟哪些用户经历了等待———————
select s.SID,
s.USERNAME,
sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time
from v$active_session_history ash, v$session s
where ash.SAMPLE_TIME between sysdate – 30 / 2880 and sysdate
and ash.SESSION_ID = s.SID
group by s.SID, s.USERNAME
order by total_wait_time desc;
————————————-查询等待时间最长的对象—————————————
select a.CURRENT_OBJ#,
d.object_name,
d.object_type,
a.EVENT,
sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
from v$active_session_history a, dba_objects d
where a.SAMPLE_TIME between sysdate – 30 / 2880 and sysdate
and a.CURRENT_OBJ# = d.object_id
group by a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT
order by total_wait_time desc;
——————————————–查询过去15分钟等待时间最长的sql语句—————————
select a.USER_ID,
u.username,
s.SQL_TEXT,
sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
from v$active_session_history a, v$sqlarea s, dba_users u
where a.SAMPLE_TIME between sysdate – 30 / 2880 and sysdate
and a.SQL_ID = s.SQL_ID
and a.USER_ID = u.user_id
group by a.USER_ID, s.SQL_TEXT, u.username
order by total_wait_time desc;
——————————————那些SQL消耗更多的IO————————————–
select *
from (select s.PARSING_SCHEMA_NAME,
s.DIRECT_WRITES,
substr(s.SQL_TEXT, 1, 500),
s.DISK_READS
from v$sql s
order by s.DISK_READS desc)
where rownum < 20
—————————————查看哪些会话正在等待IO资源————————————-
SELECT username, program, machine, sql_id
FROM V$SESSION
WHERE EVENT LIKE ‘db file%read’;
———————————-查看正在等待IO资源的对象———————————–
SELECT d.object_name, d.object_type, d.owner
FROM V$SESSION s, dba_objects d
WHERE EVENT LIKE ‘db file%read’
and s.ROW_WAIT_OBJ# = d.object_id
—————————查看redo日志切换频率———————————————
Select round(FIRST_TIME, ‘DD’), THREAD#, Count(SEQUENCE#)
From v$log_history
Group By round(FIRST_TIME, ‘DD’), THREAD#
Order By 1, 2
SELECT trunc(first_time) “Date”,
to_char(first_time, ‘Dy’) “Day”,
count(1) “Total”,
SUM(decode(to_char(first_time, ‘hh24′),’00’,1,0)) “h0”,
SUM(decode(to_char(first_time, ‘hh24′),’01’,1,0)) “h1”,
SUM(decode(to_char(first_time, ‘hh24′),’02’,1,0)) “h2”,
SUM(decode(to_char(first_time, ‘hh24′),’03’,1,0)) “h3”,
SUM(decode(to_char(first_time, ‘hh24′),’04’,1,0)) “h4”,
SUM(decode(to_char(first_time, ‘hh24′),’05’,1,0)) “h5”,
SUM(decode(to_char(first_time, ‘hh24′),’06’,1,0)) “h6”,
SUM(decode(to_char(first_time, ‘hh24′),’07’,1,0)) “h7”,
SUM(decode(to_char(first_time, ‘hh24′),’08’,1,0)) “h8”,
SUM(decode(to_char(first_time, ‘hh24′),’09’,1,0)) “h9”,
SUM(decode(to_char(first_time, ‘hh24′),’10’,1,0)) “h10”,
SUM(decode(to_char(first_time, ‘hh24′),’11’,1,0)) “h11”,
SUM(decode(to_char(first_time, ‘hh24′),’12’,1,0)) “h12”,
SUM(decode(to_char(first_time, ‘hh24′),’13’,1,0)) “h13”,
SUM(decode(to_char(first_time, ‘hh24′),’14’,1,0)) “h14”,
SUM(decode(to_char(first_time, ‘hh24′),’15’,1,0)) “h15”,
SUM(decode(to_char(first_time, ‘hh24′),’16’,1,0)) “h16”,
SUM(decode(to_char(first_time, ‘hh24′),’17’,1,0)) “h17”,
SUM(decode(to_char(first_time, ‘hh24′),’18’,1,0)) “h18”,
SUM(decode(to_char(first_time, ‘hh24′),’19’,1,0)) “h19”,
SUM(decode(to_char(first_time, ‘hh24′),’20’,1,0)) “h20”,
SUM(decode(to_char(first_time, ‘hh24′),’21’,1,0)) “h21”,
SUM(decode(to_char(first_time, ‘hh24′),’22’,1,0)) “h22”,
SUM(decode(to_char(first_time, ‘hh24′),’23’,1,0)) “h23”
FROM V$log_history
group by trunc(first_time), to_char(first_time, ‘Dy’)
Order by 1