yexinru/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
Files
sgode-patch-2
doc-exports/docs/dws/dev/dws_04_0420.html
luhuayi 177cd61a57 DWS DEVG 910.211 version 
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: luhuayi <luhuayi@huawei.com>
Co-committed-by: luhuayi <luhuayi@huawei.com>
2025-05-05 07:44:03 +00:00

66 lines
25 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<a name="EN-US_TOPIC_0000002088734269"></a><a name="EN-US_TOPIC_0000002088734269"></a>
<h1 class="topictitle1">SMP Parallel Execution</h1>
<div id="body8662426"><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p118391659351">Complex queries may take a long time. In a system with low concurrency support, this can be a problem. SMP is used to implement operator-level parallel execution, which can effectively speed up queries, improving query performance and resource utilization.</p>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a0be9acf22ce94f9ab1e46e57cbb66e7b">The SMP feature improves performance through operator parallelism but may drive more resource usage, including CPU, memory, network, and I/O. In essence, SMP is a method that trades resources for time, meaning it accelerates queries at the cost of additional resources. It improves system performance in appropriate scenarios and when resources are sufficient, but may also deteriorate performance if used inappropriately. Furthermore, compared with serial processing, SMP generates more candidate plans, which is more time-consuming and may hurt performance.</p>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p1843618557617">The SMP feature of GaussDB(DWS) is controlled by the GUC parameter <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b1486125371212">query_dop</strong>. Users use this parameter to specify an appropriate degree of query parallelism.</p>
<div class="section" id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_s54e19b618557485f8e756dfdd231861e"><a name="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_s54e19b618557485f8e756dfdd231861e"></a><a name="en-us_topic_0000001188642080_s54e19b618557485f8e756dfdd231861e"></a><h4 class="sectiontitle">Application Scenarios and Constraints for SMP</h4><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p16127535306"><strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b4604104625017">Applicable Scenarios</strong></p>
<ul id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_ud29c07046f244e729cfc047f35168a2f"><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lf4fa5f134ac44c36a75b1405f198e52b">Operators supporting parallel processing are used.<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a24e68c5cf0f340ed990a72456b2bfab1"><a name="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lf4fa5f134ac44c36a75b1405f198e52b"></a><a name="en-us_topic_0000001188642080_lf4fa5f134ac44c36a75b1405f198e52b"></a>The execution plan contains the following operators:</p>
<ol id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_o5fb7b182c579415ba21ad3beb084ac50"><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_laf70cd2d1b4b4307bea582b1ba3e4d72">Scan: Row Storage common table and a line memory partition table sequential scanning, column-oriented storage ordinary table and column-oriented storage partition table sequential scanning, HDFS internal and external table sequence scanning. Surface scanning GDS data can be imported at the same time. All of the above does not support replication tables.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lde4507ea1df64c1eb5d6d07dbdf37c91">Join: HashJoin, NestLoop</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l6a4a6079c6cf46a0abe3a7190d2c90fe">Agg: HashAgg, SortAgg, PlainAgg, and WindowAgg, which supports only <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b187342862263924">partition by</strong>, and does not support <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b68843711163924">order by</strong>.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l196400ca241e444bb1d0a5637c77ee0b">Stream: Redistribute, Broadcast</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_en-us_topic_0059778619_li4869371589">Other: Result, Subqueryscan, Unique, Material, Setop, Append, VectoRow, RowToVec</li></ol>
</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l9c825454343146699651a26853346ff6">SMP-unique operators are used.<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a90a1d7ba624b4c20b94c124f29676a84"><a name="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l9c825454343146699651a26853346ff6"></a><a name="en-us_topic_0000001188642080_l9c825454343146699651a26853346ff6"></a>To execute queries in parallel, Stream operators are added for data exchange for the SMP feature. These new operators can be considered as the subtypes of Stream operators.</p>
<ol id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_oa4620d63b05c4c1484e66112b87dc51d"><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lff4e2b36ff7d4e47a176f883afdfd696">Local Gather aggregates data of parallel threads within a DN</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l2c583b6aa6b549f19f2167eb4a5154a9">Local Redistribute redistributes data based on the distributed key across threads within a DN</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l98dc4877106f44bfb0496bafa58c6d21">Local Broadcast broadcasts data to each thread within a DN.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lc2d848fade6f486dae93ce8fdc3604ca">Local RoundRobin distributes data in polling mode across threads within a DN.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l4cbf7e18cf6e4cc7851a1d64fd754d5e">Split Redistribute redistributes data across parallel threads on different DNs.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lf2903049a05b4d5784d43405fff59078">Split Broadcast broadcasts data to all parallel DN threads in the cluster.</li></ol>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_ada7f842e3fa84f129e32cc41cd4558d3">Among these operators, Local operators exchange data between parallel threads within a DN, and non-Local operators exchange data across DNs.</p>
</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_le6192453cdba44c987f4a7bd6dbb99fa">Example<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_ad4519bbb00f84510bdd930fce5227ca6"><a name="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_le6192453cdba44c987f4a7bd6dbb99fa"></a><a name="en-us_topic_0000001188642080_le6192453cdba44c987f4a7bd6dbb99fa"></a>The TPCH Q1 parallel plan is used as an example.</p>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a7e5ff07429ec446eb197ca6bf5db6ffb"><span><img id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_i329e374b014147689c645b39e0013d19" src="figure/en-us_image_0000002079863805.jpg"></span></p>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a47fc52469fed44c9963b7c09a6bc54e5">In this plan, implement the Hdfs Scan and HashAgg operator parallel, and adds the Local Gather and Split Redistribute data exchange operator.</p>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a1a161706f7bb484e88ceb52f20a581ac">In this example, the sixth operator is Split Redistribute, and <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b51098869063924">dop: 4/4</strong> next to the operator indicates that the degree of parallelism of the sender and receiver is 4. 4 No operator is Local Gather, marked dop: 1/4 above, this operator sender thread parallel degree is 4, while the receiving end thread parallelism degree to 1, that is, lower-layer 5 number Hash Aggregate operators according to the 4 parallel degree, while the working mode of the port on the upper-layer 1 to 3 number operator according to the executed one by one, 4 number operator is used to achieve intra-DN concurrent threads data aggregation.</p>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a8fc5779fe1de499a9a300b4b2344e824">You can view the parallelism situation of each operator in the dop information.</p>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p193173175218"></p>
</li></ul>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p1961391822"><strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b145722290166">Non-Applicable Scenarios</strong></p>
</div>
<ol id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_o0964266665a94ceea2d4e66a125109f4"><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li934447102211">Small queries are performed, where plan generation may account for a significant portion of the total query time.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lcd43716a9750445c8bb6ba458cebb081">Operators are processed on CNs.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_la4a51353ec18489fbdb99d586170ea0d">Statements that cannot be pushed down are executed.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l5c857045abc3414dbd904e120e117538">The <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b66131458163924">subplan</strong> of a query and operators containing a subquery are executed.</li></ol>
<div class="section" id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_section109102011125514"><h4 class="sectiontitle">Impact of Resource Availability on SMP Performance</h4><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_aef77ef36ce5e4657b150994e51b6454b">The SMP architecture accelerates queries at the cost of additional resources. After the plan parallelism is executed, more resources are consumed, including the CPU, memory, I/O, and network bandwidth. As the DOP grows, the resource consumption also increases. If these resources become a bottleneck, SMP cannot improve performance. On the contrary, it may do exactly the opposite. Adaptive SMP is provided to dynamically select the optimal parallel degree for each query based on the resource usage and query requirements. The following information describes the situations that the SMP affects theses resources:</p>
<ul id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_ue33955cabd3047a39fd806e438313e97"><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lcf894ae2eb9b44398aeb4a220fb56036"><strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a9d170eedc2d04a00bf8ddcf157c112da">CPU resources</strong><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_af314a6af5d9e47b5a79c4e45222da065">In a general customer scenario, the system CPU usage rate is not high. Using the SMP parallelism architecture will fully use the CPU resource to improve the system performance. If the number of CPU kernels of the database server is too small and the CPU usage is already high, enabling the SMP parallelism may deteriorate the system performance due to resource compete between multiple threads.</p>
</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lb461ddcb00b54dc6b22ab706c266b02c"><strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a99251ded5a894733bfb3f05ddac2380a">Memory resources</strong><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a986e73d7ea6041f0b4efa57320e272c4">The query parallel causes memory usage growth, but the memory upper limit used by each operator is still restricted by <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b173234973663924">work_mem</strong>. Assume that <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b99562646063924">work_mem</strong> is 4 GB, and the degree of parallelism is 2, then the memory upper limit of each concurrent thread is 2 GB. When <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b143217008463924">work_mem</strong> is small or the system memory is sufficient, running SMP parallelism may push data down to disks. As a result, the query performance deteriorates.</p>
</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l75b45f2b03f140dea83f0628015863d7"><strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a607b5ff08f3149d4b608d210403afcbd">Network bandwidth resources</strong><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a068fb59120d2427ba1af6a4084d33ca4">To execute queries in parallel, data exchange operators are added. Local stream operators exchange data between threads within a DN. Data is exchanged in memory, so it does not impact network performance. Non-local operators exchange data over the network and increase the network load. If the capacity of a network resource has already become a bottleneck, parallelism may hurt performance.</p>
</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_la67e696aa7b049f2bbaf97be35a09bb0"><strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_af7044c93a25448ceb81b2f97459953b2">I/O resources</strong><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_abb035553dfb445f0a615917558557369">A parallel scan increases I/O resource consumption. It can improve performance only when I/O resources are sufficient.</p>
</li></ul>
</div>
<div class="section" id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_section13757102511555"><h4 class="sectiontitle">Other Factors Impacting SMP Performance</h4><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_abf29dd59e3b54645adee28de88360782">Besides the resource factor, other factors may also impact SMP performance, such as uneven data distribution across tables and the degree of system parallelism.</p>
<ul id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_u617e155d19cf417d8e0c92f860656843"><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_lcd8ac8e5f5b1499d9cd09f2447c749e3"><strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a4445af6f5c1045fca625670638e3e115">Impact of data skew on SMP performance</strong><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p2065453510185">Serious data skew deteriorates parallel execution performance. For example, if the data volume of a value in the join column is much more than that of other values, the data volume of a parallel thread will be much more than that of others after Hash-based data redistribution, resulting in the long-tail issue and poor parallelism performance.</p>
</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l807cbc910d99447385968fe409db8b31"><strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_a1b6f9746321b48b58640870d90a6ec4f">Impact of system parallelism degree on SMP performance</strong><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_af263312de20444a1a56a8b3da4ca3736">The SMP feature uses more resources, and unused resources are decreasing in a high concurrency scenario. Therefore, enabling the SMP parallelism will result in serious resource compete among queries. Once resource competes occur, no matter the CPU, I/O, memory, or network resources, all of them will result in entire performance deterioration. In the high concurrency scenario, enabling the SMP will not improve the performance effect and even may cause performance deterioration.</p>
</li></ul>
</div>
<div class="section" id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_section86355183563"><h4 class="sectiontitle">Suggestions for SMP Parameter Settings</h4><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p0603935401">To enable the SMP adaptation function, set <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b126173896163924">query_dop</strong> to <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b136916033463924">0</strong> and adjust the following parameters to obtain an optimal DOP selection:</p>
<ul id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_ul540441719011"><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li19404617605">comm_usable_memory<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p1678013529291"><a name="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li19404617605"></a><a name="en-us_topic_0000001188642080_li19404617605"></a>If the system memory is large, the value of <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b13672227163924">max_process_memory</strong> is large. In this case, you are advised to set the value of this parameter to 5% of <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b72030362263924">max_process_memory</strong>, that is, 4 GB by default.</p>
</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li9550424709">comm_max_stream<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p884213531438"><a name="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li9550424709"></a><a name="en-us_topic_0000001188642080_li9550424709"></a>The recommended value for this parameter is calculated as follows: comm_max_stream = Min(dop_limit x dop_limit x 20 x 2, max_process_memory (bytes) x 0.025/Number of DNs/260). The value must be within the value range of <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b203382074663924">comm_max_stream</strong>.</p>
</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li349920291409">max_connections<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p286713341669"><a name="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li349920291409"></a><a name="en-us_topic_0000001188642080_li349920291409"></a>The recommended value for this parameter is calculated as follows: max_connections = dop_limit x 20 x 6 + 24. The value must be within the value range of <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b176588434663924">max_connections</strong>.</p>
<div class="caution" id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_note99110550251"><span class="cautiontitle"><img src="public_sys-resources/caution_3.0-en-us.png"> </span><div class="cautionbody"><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p59111555202514">In the preceding formulas, <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b122641816463924">dop_limit</strong> indicates the number of CPUs corresponding to each DN in the cluster. It is calculated as follows: <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b157505754663924">dop_limit</strong> = Number of logical CPU cores of a single server/Number of DNs of a single server.</p>
</div></div>
</li></ul>
</div>
<div class="section" id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_section1743813195572"><h4 class="sectiontitle">SMP Configuration Procedure</h4><div class="notice" id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_note14389720175119"><span class="noticetitle"><img src="public_sys-resources/notice_3.0-en-us.png"> </span><div class="noticebody"><p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p1238962015118">The CPU, memory, I/O, and network bandwidth resources are sufficient. In essence, SMP is a method that trades resources for time. After the plan parallelism is executed, resource consumption increases. When these resources become a bottleneck, SMP may deteriorate, rather than improve performance. In addition, it takes a longer time to generate SMP plans than serial plans. Therefore, in TP services that mainly involve short queries or in case resources are insufficient, you are advised to disable SMP by setting <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b151354532463924">query_dop</strong> to <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b18487198063924">1</strong>.</p>
</div></div>
<p id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_p1382162413584"><strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b17302113875818">Procedure</strong>:</p>
<ol id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_o38bef8205e6a4ebdbc8d342579496bec"><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l832746b6d01c46e89e86d77b43e825e2">Observe the current system load situation. If the resource is sufficient (the resource usage ratio is smaller than 50%), perform step 2. Otherwise, exit this system.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l07d708eca14e4efbb41b93676e4e5f02">Set <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b137568577863924">query_dop</strong> to <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b87206306063924">1</strong> (default value). Use <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b23854577563924">explain</strong> to generate an execution plan and check whether the plan can be used in scenarios described in <a href="#EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_s54e19b618557485f8e756dfdd231861e">Application Scenarios and Constraints for SMP</a>. If the plan can be used, go to the next step.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li616455562717">Set <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b186589300863924">query_dop=–</strong><em id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_i35519079963924">value</em>. The value range of the parallelism degree is [1, <em id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_i33413007563924">value</em>].</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li68414552917">Set <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b74762385463924">query_dop=</strong><em id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_i109109470863924">value</em>. The parallelism degree is 1 or <em id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_i81805359363924">value</em>.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_leb68f29ff73849fea97d93002ccde5c1">Before the query statement is executed, set <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b76848960763924">query_dop</strong> to an appropriate value. After the statement is executed, set <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b45807361663924">query_dop</strong> to <strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b25726500563924">off</strong>. For example:<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_sdfd7f8a465f74db1800a456d46c0a7b0"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">SET</span><span class="w"> </span><span class="n">query_dop</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">0</span><span class="p">;</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">t1</span><span class="w"> </span><span class="k">GROUP</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="p">;</span>
<span class="p">......</span>
<span class="k">SET</span><span class="w"> </span><span class="n">query_dop</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">1</span><span class="p">;</span>
</pre></div></td></tr></table></div>
</div>
<div class="note" id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_n6caf1640edea4f0aad7d80cdf99fc6f0"><img src="public_sys-resources/note_3.0-en-us.png"><span class="notetitle"> </span><div class="notebody"><ul id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_ude64952f02ff42a9bb19110ca02692f9"><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_l3855f80ad72d457eadf561cd85f04fd7">If resources are sufficient, the higher the degree of parallelism, the better the performance improvement result.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_ld43f208342d947ef8374e2d38bced86a">The SMP parallelism degree supports a session level setting and you are advised to enable SMP before executing queries that meet the requirements. After the execution is complete, disable SMP. Otherwise, SMP may affect services during peak hours.</li><li id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_li165091153143210">SMP adaptability (<strong id="EN-US_TOPIC_0000002088734269__en-us_topic_0000001188642080_b74949138345">query_dop</strong> ≤ 0) depends on resource management. If resource management is disabled, only plans with parallelism degree of only 1 or 2 will be generated.</li></ul>
</div></div>
</li></ol>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="dws_04_0414.html">System Optimization</a></div>
</div>
</div>
View Git Blame Copy Permalink