开启辅助访问

Bo's Oracle Station

Bo's Oracle Station › 博客首页 ›Oracle技术› SQL Tuning › 查看内容

【博客文章2021】多用户适配算法对多表连接并行度的影响

2021-6-12 16:08| 发布者: admin| 查看: 2367| 评论: 0|原作者: Bo Tang

摘要: 多用户适配算法对多表连接并行度的影响
【博客文章2021】多用户适配算法对多表连接并行度的影响

Author: Bo Tang

1. 查看表的DDL并行度定义：

SQL> select owner, table_name, degree from dba_tables where owner='SH' and table_name in ('SALES', 'TIMES', 'CUSTOMERS', 'CHANNELS');
---------------------------------------------------------------------------------------------------------------------------------------------------------------
SH TIMES          1
SH CHANNELS          1
SH CUSTOMERS          1
SH SALES          1
 
2. 提高表的DDL并行度定义：

SQL> alter table sales parallel 50;
Table altered.
SQL> alter table times parallel 10;
Table altered.
SQL> alter table customers parallel 30;
Table altered.
SQL> alter table channels parallel 10;
Table altered.
SQL> select owner, table_name, degree from dba_tables where owner='SH' and table_name in ('SALES', 'TIMES', 'CUSTOMERS', 'CHANNELS');
---------------------------------------------------------------------------------------------------------------------------------------------------------------
SH SALES         50
SH CHANNELS         10
SH CUSTOMERS         30
SH TIMES         10
 
3. 执行并行多表连接：

SQL>conn / as sysdba
Connected.
SQL> show parameter parallel_adaptive

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user         boolean     FALSE
SQL> alter system set parallel_adaptive_multi_user=true;

System altered.


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个SQL并行集同时有30个Slave进程帮助完成并行IO。

4.  多用户适配算法对并行度的影响： 

    语句的DOP由多用户适配算法调整。如果该算法设置成生效，那么系统会预留并行度执行能力给潜在的其他用户进程。我们关闭多用户适配算法看一看上面的语句是否可以得到更多的并行度：

SQL>conn / as sysdba
Connected.
SQL> show parameter parallel_adaptive

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user         boolean     FALSE
SQL> alter system set parallel_adaptive_multi_user=false;

System altered.


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
------------------------------ ------- ------------
Candler                        2000-01      4166.32
Blountstown                    2000-02     38052.58
Saint Marks                    2000-01     55781.37
Molino                         2000-01     18765.25
Ocala                          2000-02      7081.73
Sanibel                        2000-02     17908.67
Sugarloaf Key                  2000-01     12027.66
Sanibel                        2000-01     15870.34
Noma                           2000-02     23903.58
Blountstown                    2000-01      27024.7
Ocala                          2000-01      7146.73
CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Sugarloaf Key                  2000-02      9659.44
Molino                         2000-02      17144.7
Cypress Gardens                2000-01      3545.82
Evinston                       2000-02     62657.21
Evinston                       2000-01     53509.69
Saint Marks                    2000-02      48858.7
Winter Springs                 2000-02           20
Palmdale                       2000-01     37793.44
Candler                        2000-02      6493.94
Cypress Gardens                2000-02      4928.93
Palmdale                       2000-02     25829.24
CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Winter Springs                 2000-01        31.46
Noma                           2000-01     33572.55
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                        100             0          0
Allocation Height                      50             0          0
Allocation Width                        1             0          0
Local Msgs Sent                      4600          4600          0
Distr Msgs Sent                         0             0          0
Local Msgs Recv'd                    4600          4600          0
Distr Msgs Recv'd                       0             0          0
STATISTIC                      LAST_QUERY SESSION_TOTAL     CON_ID
------------------------------ ---------- ------------- ----------
DOP                                    50             0          0
Slave Sets                              2             0          0
13 rows selected.

 





路过
雷人
握手
鲜花
鸡蛋
收藏 邀请
上一篇：【博客文章2021】并行DML实践下一篇：【博客文章2021】使用SQL Monitor监控并行多表连接的SQL执行过程

QQ|手机版|Bo's Oracle Station   

GMT+8, 2022-6-17 10:36 , Processed in 0.017605 second(s), 21 queries .

返回顶部