如何查看并行进程的coordinator信息

myownstars

OS: linux 2.6
Oracle: 10.2.0.5.0 两节点RAC
症状： 用户抱怨第一节点的CPU资源消耗殆尽，要求尽快解决；
登陆后使用TOP命令，输出如下
top - 09:54:50 up 34 days, 17:27, 13 users,  load average: 10.15, 10.10,
Tasks: 286 total,  12 running, 274 sleeping,   0 stopped,   0 zombie
Cpu(s): 84.6% us, 15.4% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.1% si  
Mem:  17517944k total, 16596828k used,   921116k free,  1355284k buffers  
Swap:  2097144k total,    50744k used,  2046400k free, 12154200k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
20344 oracle    25   0 6287m  23m  17m R 50.2  0.1   4418:21 oracle
20309 oracle    25   0 6289m  40m  34m R 49.8  0.2   4418:45 oracle                                                              20315 oracle    25   0 6289m  23m  17m R 49.8  0.1   4418:01 oracle
20332 oracle    25   0 6287m  23m  17m R 49.2  0.1   4412:39 oracle                                                              10664 oracle    17   0 6477m 664m 473m R 43.8  3.9   0:06.01 oracle
20340 oracle    25   0 6287m  23m  17m R 33.5  0.1   4417:03 oracle
20348 oracle    25   0 6287m  23m  17m R 33.5  0.1   4420:16 oracle
20292 oracle    25   0 6289m  23m  17m R 33.2  0.1   4414:04 oracle                                                                                                     20357 oracle    25   0 6287m  23m  17m R 28.9  0.1   4418:29 oracle                                                                                                     
20336 oracle    25   0 6287m  23m  17m R 27.2  0.1   4419:38 oracle
根据列出的top oracle进程spid，登陆数据库查看发现它们都在进行i/o slave wait等待，使用oradebug  dump errorstack 3发现都在执行如下语句
select /*+ parallel(t,14) parallel_index(t,14) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*),count(distinct "ID"),sum(sys_op_o
pnsize("ID")),count(distinct "TYPE"),sum(sys_op_opnsize("TYPE")),count(distinct "TASK_ID"),sum(sys_op_opnsize("TASK_ID")),count("ATTR1"),count(distinct "ATTR1"),sum(sys_op_opnsi
ze("ATTR1")),substrb(dump(min(substrb("ATTR1",1,32)),16,0,32),1,120),substrb(dump(max(substrb("ATTR1",1,32)),16,0,32),1,120),count("ATTR2"),count(distinct "ATTR2"),sum(sys_op_op
nsize("ATTR2")),substrb(dump(min(substrb("ATTR2",1,32)),16,0,32),1,120),substrb(dump(max(substrb("ATTR2",1,32)),16,0,32),1,120),count("ATTR3"),count(distinct "ATTR3"),sum(sys_op
_opnsize("ATTR3")),substrb(dump(min(substrb("ATTR3",1,32)),16,0,32),1,120),substrb(dump(max(substrb("ATTR3",1,32)),16,0,32),1,120),count(case when "ATTR4" is null then null else
1 end),sum(sys_op_opnsize("ATTR4")),count("ATTR5"),count(distinct "ATTR5"),sum(sys_op_opnsize("ATTR5")),substrb(dump(min(substrb("ATTR5",1,32)),16,0,32),1,120),substrb(dump(max
(substrb("ATTR5",1,32)),16,0,32),1,120),count(case when "OTHER" is null then null else 1 end),sum(sys_op_opnsize("OTHER")) from "SYS"."WRI$_ADV_OBJECTS" t
SQL> select event,count(*) from v$session group by event order by 2;
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
Streams AQ: waiting for messages in the queue                             1
Streams AQ: qmn coordinator idle wait                                     1
Streams AQ: waiting for time management or cleanup tasks                  1
class slave wait                                                          1
pmon timer                                                                1
smon timer                                                                1
ges remote message                                                        1
DIAG idle wait                                                            1
ASM background timer                                                      1
jobq slave wait                                                           2
Streams AQ: qmn slave idle wait                                           2
gcs remote message                                                        2
SQL*Net message to client                                                 2
i/o slave wait                                                            14
rdbms ipc message                                                        14
SQL*Net message from client                                              16
SQL> select QCSID,SID ,SERVER_GROUP,SERVER_SET  from V$PX_SESSION;
     QCSID        SID SERVER_GROUP SERVER_SET
---------- ---------- ------------ ----------
       474        452            1          2
       474        453            1          2
       498        443            1          2
       498        445            1          2
       474        464            1          2
       498        447            1          2
       498        444            1          2
       498        437            1          2
       498        438            1          2
       498        439            1          2
       498        440            1          2
我的问题如下：
1．
Sql执行在第一节点，第二节点压力正常，但是v$session.machine均显示为第二节点的机器名，什么条件下会发生这种状况？
这些session都在执行sql，却为何v$session.sql_id/ PREV_SQL_ID均为空？
2．
我们知道，并行查询需要一个coordinator，但如何获取该进程sid？
此时貌似除了kill session，暂时没有好的解决办法，总共14个并行进程，是否需要一一kill，还是只需要kill coordinator进程？
烦劳麦克林恩解答一下



[ 本帖最后由 myownstars 于 2012-5-18 16:39 编辑 ]

Maclean Liu(刘相兵

请使用以下脚本找出Coordinater :

1. col username for a12
   
2. col "QC SID" for A6
   
3. col "SID" for A6
   
4. col "QC/Slave" for A8
   
5. col "Req. DOP" for 9999
   
6. col "Actual DOP" for 9999
   
7. col "Slaveset" for A8
   
8. col "Slave INST" for A9
   
9. col "QC INST" for A6
   
10. set pages 300 lines 300
    
11. col wait_event format a30
    
12. select
    
13. decode(px.qcinst_id,NULL,username,
    
14. ' - '||lower(substr(pp.SERVER_NAME,
    
15. length(pp.SERVER_NAME)-4,4) ) )"Username",
    
16. decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
    
17. to_char( px.server_set) "SlaveSet",
    
18. to_char(s.sid) "SID",
    
19. to_char(px.inst_id) "Slave INST",
    
20. decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,
    
21. case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
    
22. decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
    
23. to_char(px.qcinst_id) "QC INST",
    
24. px.req_degree "Req. DOP",
    
25. px.degree "Actual DOP"
    
26. from gv$px_session px,
    
27. gv$session s ,
    
28. gv$px_process pp,
    
29. gv$session_wait sw
    
30. where px.sid=s.sid (+)
    
31. and px.serial#=s.serial#(+)
    
32. and px.inst_id = s.inst_id(+)
    
33. and px.sid = pp.sid (+)
    
34. and px.serial#=pp.serial#(+)
    
35. and sw.sid = s.sid
    
36. and sw.inst_id = s.inst_id
    
37. order by
    
38.   decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
    
39.   px.QCSID,
    
40.   decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
    
41.   px.SERVER_SET,
    
42.   px.INST_ID
    
43. /

复制代码

使用范例：

1. SQL> col username for a12
   
2. SQL> col "QC SID" for A6
   
3. SQL> col "SID" for A6
   
4. SQL> col "QC/Slave" for A8
   
5. SQL> col "Req. DOP" for 9999
   
6. col "Actual DOP" for 9999
   
7. col "Slaveset" for A8
   
8. col "Slave INST" for A9
   
9. col "QC INST" for A6
   
10. SQL> SQL> SQL> SQL> SQL> set pages 300 lines 300
    
11. SQL> col wait_event format a30
    
12. SQL> select
    
13.   2  decode(px.qcinst_id,NULL,username,
    
14.   3  ' - '||lower(substr(pp.SERVER_NAME,
    
15.   4  length(pp.SERVER_NAME)-4,4) ) )"Username",
    
16.   5  decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
    
17.   6  to_char( px.server_set) "SlaveSet",
    
18.   7  to_char(s.sid) "SID",
    
19.   8  to_char(px.inst_id) "Slave INST",
    
20.   9  decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,
    
21. 10  case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
    
22. 11  decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
    
23. 12  to_char(px.qcinst_id) "QC INST",
    
24. 13  px.req_degree "Req. DOP",
    
25. 14  px.degree "Actual DOP"
    
26. 15  from gv$px_session px,
    
27. 16  gv$session s ,
    
28. 17  gv$px_process pp,
    
29. 18  gv$session_wait sw
    
30. 19  where px.sid=s.sid (+)
    
31. 20  and px.serial#=s.serial#(+)
    
32. 21  and px.inst_id = s.inst_id(+)
    
33. 22  and px.sid = pp.sid (+)
    
34. 23  and px.serial#=pp.serial#(+)
    
35. 24  and sw.sid = s.sid
    
36. 25  and sw.inst_id = s.inst_id
    
37. 26  order by
    
38. 27    decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
    
39. 28    px.QCSID,
    
40. 29    decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
    
41. 30    px.SERVER_SET,
    
42. 31    px.INST_ID
    
43. 32  /
    
44. 
    
45. Username     QC/Slave SlaveSet SID    Slave INS STATE                            WAIT_EVENT                     QC SID QC INS Req. DOP Actual DOP
    
46. ------------ -------- -------- ------ --------- -------------------------------- ------------------------------ ------ ------ -------- ----------
    
47. SYS          QC                14     1         WAIT                             PX Deq: Execute Reply          14
    
48. - p001      (Slave)  1        27     1         NOT WAIT                                                        14     1             4          4
    
49. - p003      (Slave)  1        17     1         NOT WAIT                                                        14     1             4          4
    
50. - p002      (Slave)  1        125    1         NOT WAIT                                                        14     1             4          4
    
51. - p000      (Slave)  1        157    1         NOT WAIT                                                        14     1             4          4
    
52. - p004      (Slave)  2        151    1         WAIT                             PX Deq: Execution Msg          14     1             4          4
    
53. - p006      (Slave)  2        158    1         WAIT                             PX Deq: Execution Msg          14     1             4          4
    
54. - p005      (Slave)  2        22     1         WAIT                             PX Deq: Execution Msg          14     1             4          4
    
55. - p007      (Slave)  2        30     1         WAIT                             PX Deq: Execution Msg          14     1             4          4

复制代码

FROM http://www.oracledatabase12g.com ... rallel-queries.html