详解ORACLE-HINT之pq_distribute

我就是我 2023-07-21 08:47 11阅读 0赞

# 详解ORACLE-HINT之pq\_distribute #

原创 [雪隐千寻][Link 1] 最后发布于2019-05-30 12:07:00  阅读数 103 收藏

发布于2019-05-30 12:07:00

分类专栏： [ SQL优化 ][SQL_]

本文链接： [https://blog.csdn.net/m0\_37941193/article/details/90694811][https_blog.csdn.net_m0_37941193_article_details_90694811]

展开

这次给大家说明一个新奇的hint：

pq\_distribute (tablename,outer\_distribution inner\_distribution )  
个人翻译：并行查询hint，用于提高并行连接性能  
官方文档说明：  
The PQ\_DISTRIBUTE hint instructs the optimizer how to distribute rows of joined tables among producer   
and consumer query servers. Such distribution can improve the performance of parallel join operations.  
tablename顾名思义 表名  
outer\_distribution 外环表分布状态  
inner\_distribution 内环表分布状态

分布状态的值是HASH，BROADCAST，PARTITION，和NONE。  
只有以下六种组合表分布有效：

1.【HASH， HASH】  
使用连接键上的哈希函数将每个表的行映射到使用者查询服务器。映射完成后，  
每个查询服务器都会在一对结果分区之间执行连接。当表的大小相当并且通过散列连接或排序合并连接实现连接操作时，建议使用此分发。  
2.BROADCAST， NONE  
外部表的所有行都广播到每个查询服务器。内表行是随机分区的。  
当外表与内表相比非常小时，建议使用此分布。作为一般规则，当内部表大小乘以查询服务器的数量大于外部表大小时，请使用此分发。  
3.NONE， BROADCAST  
内部表的所有行都广播到每个使用者查询服务器。外表行是随机分区的。  
当内表与外表相比非常小时，建议使用此分布。作为一般规则，当内部表大小乘以查询服务器数小于外部表大小时，请使用此分发。  
4.NONE， PARTITION  
使用外部表的分区来映射内部表的行。必须在连接键上对外表进行分区。当外表的分区数等于或几乎等于查询服务器数的倍数时，建议使用此分发; 例如，14个分区和15个查询服务器。

注意：如果外部表未在分区键上进行分区或未等分，则优化程序将忽略此提示。  
5.NONE， PARTITION  
使用外部表的分区来映射内部表的行。必须在连接键上对外表进行分区。当外表的分区数等于或几乎等于查询服务器数的倍数时，建议使用此分发; 例如，14个分区和15个查询服务器。

注意：如果外部表未在分区键上进行分区或未等分，则优化程序将忽略此提示。  
6.NONE， NONE  
每个查询服务器在一对匹配的分区之间执行连接操作，每个表中有一个分区。两个表必须在连接键上均分。

官方文档案例：  
例如，给定两个表r并s使用散列连接进行连接，以下查询包含使用散列分布的提示：

SELECT / \* + ORDERED PQ\_DISTRIBUTE（s HASH，HASH）USE\_HASH（s）\* / column\_list   
  FROM r，s   
  WHERE rc = sc;

要广播外部表r，查询是：

SELECT / \* + ORDERED PQ\_DISTRIBUTE（s BROADCAST，NONE）USE\_HASH（s）\* / column\_list   
  FROM r，s   
  WHERE rc = sc;  
    
本人实际遇到案例（反面教材）：

（涉及保密，做了部分修饰，大家主要看HINT与执行计划即可）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxMTkz_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxMTkz_size_16_color_FFFFFF_t_70 1][]

可以看到执行计划明显不对，居然先NL再hash分组，time也高达132+（虽然不准，但是数字太大一定是有问题的）  
写出这个SQL的选手，明显就是一顿乱秀操作，看上去那么长hint好像很NB，实际效果，240秒以上.

首先去掉hint，发现存在位图索引转换，但是速度，依然达到100S+

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxMTkz_size_16_color_FFFFFF_t_70 2][]

关闭位图索引转换，

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxMTkz_size_16_color_FFFFFF_t_70 3][]

实际执行5S+，速度得到明显提升。

由此可知，hint不要随便乱玩，尤其是涉及并行的，有时候并不是你加了效果一定很好，引以为戒。

[Link 1]: https://me.csdn.net/m0_37941193
[SQL_]: https://blog.csdn.net/m0_37941193/category_7183191.html
[CC 4.0 BY-SA]: http://creativecommons.org/licenses/by-sa/4.0/
[https_blog.csdn.net_m0_37941193_article_details_90694811]: https://blog.csdn.net/m0_37941193/article/details/90694811
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxMTkz_size_16_color_FFFFFF_t_70]: /images/20230528/c279fa03d3004292b08893879b860670.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxMTkz_size_16_color_FFFFFF_t_70 1]: /images/20230528/b873b7f34095489ba75a446627c659fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxMTkz_size_16_color_FFFFFF_t_70 2]: /images/20230528/8a18d00a50534a728c957cb122927a51.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3OTQxMTkz_size_16_color_FFFFFF_t_70 3]: /images/20230528/9b019c57707640c1ab7ab8d1d9bfe2ad.png