一次活动进程过多的诊断

SQL> select blocking_session,P2,COUNT(*)
  2       from v$active_session_history
  3      where sample_time between to_date('20120322 1515', 'yyyymmdd hh24:mi') and
  4            to_date('20120322 1520', 'yyyymmdd hh24:mi')
  5       AND event='read by other session'
  6       GROUP BY  blocking_session,P2;

BLOCKING_SESSION         P2   COUNT(*)
---------------- ---------- ----------
              56     165321        107
             392     159884          9
             392     166551          9
             917     164893         95
             355     161167        107
             438     161757        107
              34     161981        107
                     267490          1
              89     159884         82
             356     166551         63
             438     499141          1

BLOCKING_SESSION         P2   COUNT(*)
---------------- ---------- ----------
             873     159844        107
             392     164893          1
             359     159091         45
             166     267490         64
            1081     163174        107
             148     267490          3
             166     161269          7
            1047     163927        107
             416     165291         20
             308     165291         76
             252     165305         89

BLOCKING_SESSION         P2   COUNT(*)
---------------- ---------- ----------
              82     165433          4
              34     161269         79
             873     162037        107
             274     102976         14
             263     165433         51
           
    
    
select  blocking_session,sql_id,p1,p1text,p2,p2text,p3,p3text
     from v$active_session_history
    where sample_time between to_date('20120322 1515', 'yyyymmdd hh24:mi') and
          to_date('20120322 1520', 'yyyymmdd hh24:mi')
     AND event='read by other session'
     and BLOCKING_SESSION=1047
    
    
    
BLOCKING_SESSION SQL_ID                P1 P1TEXT             P2 P2TEXT             P3 P3TEXT
---------------- ------------- ---------- ---------- ---------- ---------- ---------- ----------
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#
             873 5m6a13qjh5yct         96 file#          159844 block#              1 class#


SQL> select  blocking_session,sql_id,p1,p1text,p2,p2text,p3,p3text
  2       from v$active_session_history
  3      where sample_time between to_date('20120322 1515', 'yyyymmdd hh24:mi') and
  4            to_date('20120322 1520', 'yyyymmdd hh24:mi')
  5       AND event='read by other session'
  6       and BLOCKING_SESSION=1047;

BLOCKING_SESSION SQL_ID                P1 P1TEXT             P2 P2TEXT             P3 P3TEXT
---------------- ------------- ---------- ---------- ---------- ---------- ---------- ----------
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#
            1047 5m6a13qjh5yct         96 file#          163927 block#              1 class#            
            
--查看sql            
 select * from table(dbms_xplan.display_awr('5m6a13qjh5yct'))
 
 
 PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID 5m6a13qjh5yct
--------------------
         select feed_id        feedId,                  oragin
oragin,                  feed_type      feedType,
user_id        userId,                  nick_name      nickName,
          add_time       addTime,                  nvl(friend,0)
friend,                  hash_template  hashTemplate,
hash_data      hashData,                  image_1        image1,
          image_1_link   image1Link,                  image_2
image2,                  image_2_link   image2Link,
image_3        image3,                  image_3_link   image3Link,
            image_4        image4,                  image_4_link
image4Link,                  target_ids     targetIds,
title_template titleTemplate,                  title_data
titleData,                  body_template  bodyTemplate,
  body_data      bodyData,                  body_general   bodyGeneral
 from       (select                feed_id,                  oragin,
              feed_type,                  user_id,
nick_name,                  add_time,                  nvl(friend,0)
friend,                  hash_template,                  hash_data,
             image_1,                  image_1_link,
image_2,                  image_2_link,                  image_3,
           image_3_link,                  image_4,
image_4_link,                  target_ids,
title_template,                  title_data,
body_template,                  body_data,
body_general,            rownum row_num             from t_feed f
      where f.oragin = 1
 and f.feed_type in (4)
         and rownum <= (:1 + :2)                   and f.add_time >
sysdate - 7                      )      where row_num > :3

Plan hash value: 3814582130

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |       |       |   153 (100)|          |       |       |
|   1 |  VIEW                                |                 |   128 |  1212K|   153   (5)| 00:00:02 |       |       |
|   2 |   COUNT STOPKEY                      |                 |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR          |                 |   128 |  1211K|   153   (5)| 00:00:02 |   KEY |1048575|
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_FEED          |   128 |  1211K|   153   (5)| 00:00:02 |   KEY |1048575|
|   5 |      INDEX RANGE SCAN                | IX_FEED_ADDTIME | 11495 |       |     9  (78)| 00:00:01 |   KEY |1048575|
------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


 
 SQL> select segment_name
  2    from dba_extents
  3   where file_id=96
  4   and    165291
  5   between block_id and (block_id +  blocks - 1);

SEGMENT_NAME
---------------------------------------------------------------------------------
T_FEED 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16147313/viewspace-719306/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16147313/viewspace-719306/