【博客文章2021】使用SQL Monitoring监控并行多表连接的SQL执行过程
Author: Bo Tang
1. 并行执行多表连接：
SQL> conn sh/oracle_4U
Connected.
SQL> SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
2 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
3 WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
4 AND c.cust_state_province = 'FL'
5 AND ch.channel_desc = 'Direct Sales'
6 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
7 t.calendar_quarter_desc; CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sanibel 2000-01 15870.34
Saint Marks 2000-01 55781.37
Cypress Gardens 2000-01 3545.82
Saint Marks 2000-02 48858.7
Evinston 2000-01 53509.69
Noma 2000-01 33572.55
Cypress Gardens 2000-02 4928.93
Sanibel 2000-02 17908.67
Winter Springs 2000-02 20
Evinston 2000-02 62657.21
Molino 2000-01 18765.25 CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Winter Springs 2000-01 31.46
Candler 2000-02 6493.94
Palmdale 2000-01 37793.44
Sugarloaf Key 2000-02 9659.44
Molino 2000-02 17144.7
Sugarloaf Key 2000-01 12027.66
Ocala 2000-02 7081.73
Candler 2000-01 4166.32
Noma 2000-02 23903.58
Blountstown 2000-01 27024.7
Ocala 2000-01 7146.73 CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Palmdale 2000-02 25829.24
Blountstown 2000-02 38052.58 24 rows selected. SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 1 1 0
DML Parallelized 0 0 0
DDL Parallelized 0 0 0
DFO Trees 1 1 0
Server Threads 60 0 0
Allocation Height 30 0 0
Allocation Width 1 0 0
Local Msgs Sent 2020 2020 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 2020 2020 0
Distr Msgs Recv'd 0 0 0 STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
DOP 30 0 0
Slave Sets 2 0 0 13 rows selected.
|
可以看出来并行服务进程数是60（Server Threads ）。 分配高度（Allocation Height）实际上就是语句运行的并行度（DOP）。 分配高度为30，服务器进程数为60说明：语句以并行度30运行，共分2个并行集，同时有30个Slave进程帮助完成并行IO。
2. 在Cloud Control定位到SQL Monitoring：
点击Performance->SQL Monitoring：
找到 SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount....这条SQL语句, 可以看到它的sql_fulltext，同时左上角看到它的Exection Plan有“30”的多人图标，说明它是并行执行:
可以看到Plan Statistics，也可以看到其每一步的执行步骤前面的多人图标，代表这一步是并行执行：
查看并行集中的并行进程详情：
还可以跳转到SQL详情，查看执行计划拓扑图： | 
