查询Oracle等待事件

# 查询当前的等待事件
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;

推荐文章

23条评论

  1. I loved your blog article. Really looking forward to read more. Awesome. Adorne Edan Zebulon

  2. Very good article. I will be going through some of these issues as well.. Shauna Byrle Clemmie

  3. You are my inspiration , I possess few blogs and occasionally run out from to post . Tomasina Yank Primalia

  4. If you would like to take much from this post then you have to apply such techniques to your won webpage. Candi Hillary Audrit

  5. This paragraph provides clear idea designed for the new users of blogging, that in fact how to do blogging. Shela Mychal Marler

  6. Thanks really useful. Will share site with my pals. Corly Frank Seedman

  7. There is noticeably a bundle to know about this. I assume you made certain good factors in options also. Eddie Wilbert Ignazio

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. Thanks for sharing, this is a fantastic blog. Really thank you! Really Cool. Raquel Doyle Camala

  14. Pretty! This was a really wonderful article. Many thanks for supplying this info. Stormy Barret Lisetta

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. Have you ever considered writing an ebook or guest authoring on other blogs? Elsa Thaxter Beera

  21. Very good post. I am facing a few of these issues as well.. Kristal Jaime Daniele

  22. This could be because of the reason that it saves time water consumption and fertilizers which are used for planting. Iseabal Sigfried Jaeger

  23. It’s going to be finish of mine day, however before end I am reading this fantastic
    article to increase my know-how.

发表评论

邮箱地址不会被公开。 必填项已用*标注