# 查询当前的等待事件
select distinct a.sid,a.event,a.seconds_in_wait,a.wait_class,c.sql_text,d.spid,d.username,d.program
from v$session_wait a,v$session b,v$sqltext c,v$process d
where a.sid = b.sid
and a.state='WAITING'
and b.sql_hash_value=c.hash_value(+)
and b.paddr = d.addr
and a.wait_class<>'Idle'
and c.piece=0;
# 查询数据库等待时间和实际执行时间的相对百分比
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;
# 查看归档日志空间使用情况(实际和百分比)
select * from v$recovery_file_dest;
select * from v$flash_recovery_area_usage;
# 增加归档日志空间大小
alter system set db_recovery_file_dest_size=20G scope=both;
I loved your blog article. Really looking forward to read more. Awesome. Adorne Edan Zebulon
Very good article. I will be going through some of these issues as well.. Shauna Byrle Clemmie
You are my inspiration , I possess few blogs and occasionally run out from to post . Tomasina Yank Primalia
If you would like to take much from this post then you have to apply such techniques to your won webpage. Candi Hillary Audrit
This paragraph provides clear idea designed for the new users of blogging, that in fact how to do blogging. Shela Mychal Marler
Thanks really useful. Will share site with my pals. Corly Frank Seedman
There is noticeably a bundle to know about this. I assume you made certain good factors in options also. Eddie Wilbert Ignazio
Way cool! Some extremely valid points! I appreciate you writing this post and also the rest of the website is extremely good. Tabbie Christos Southard
Keep up the fantastic work , I read few articles on this website and I conceive that your blog is real interesting and holds sets of good info . Kassandra Matthieu Arel
I just could not depart your website prior to suggesting that I extremely loved the usual information a person provide for your guests? Is gonna be again continuously to check up on new posts Karna Bern Ezequiel
Dear Emily, a marvelous fascinating memoir! Thank you for sharing. I feel inspired by your encounters and your writing. You live an chanted interesting and magical life. Love, Seasi Korella Hadleigh Wiersma
we prefer to honor lots of other world wide web internet sites on the net, even when they arent linked to us, by linking to them. Underneath are some webpages worth checking out Ree Hercules Clough
Thanks for sharing, this is a fantastic blog. Really thank you! Really Cool. Raquel Doyle Camala
Pretty! This was a really wonderful article. Many thanks for supplying this info. Stormy Barret Lisetta
Hello, all the time i used to check website posts here early in the dawn, because i like to find out more and more.| Grace Geoffry Orme
This is my first time pay a quick visit at here and i am actually impressed to read all at alone place. Maritsa Bartholemy Hoskinson
Hi my friend! I want to say that this article is awesome, great written and include approximately all significant infos. I would like to see extra posts like this .| Benni Kendrick Ayn
I was out with a sweet friend today. I see her once a month or so. And she asked me how I was-of course I teared up, as much at the question as that someone was actually bothering to ask me. Mareah Rossie Elfie
The next time I read a blog, I hope that it does not fail me as much as this one. After all, Yes, it was my choice to read through, however I really thought you would have something interesting to talk about. All I hear is a bunch of complaining about something you could fix if you were not too busy seeking attention. Eugenia Em Trumaine
Have you ever considered writing an ebook or guest authoring on other blogs? Elsa Thaxter Beera
Very good post. I am facing a few of these issues as well.. Kristal Jaime Daniele
This could be because of the reason that it saves time water consumption and fertilizers which are used for planting. Iseabal Sigfried Jaeger
It’s going to be finish of mine day, however before end I am reading this fantastic
article to increase my know-how.