如何通过执行 union all 来减少同时运行的查询的 CPU 利用率

Question

生成报告时向我报告了高 CPU 利用率问题。 在与报表相关的过程中，已发现通过执行 UNION ALL 执行 6 到 7 种类型的查询（每个查询的逻辑不同），如 SQL 查询中所示。每个查询使用 10 到 12 个表并相互连接。所有输出列都引用每个查询中使用的几乎所有表。

整个查询执行时间约为30秒到40秒。查询性能没有问题。但仅此查询的 CPU 利用率才达到 50%。如果多个用户同时生成相同的报告，则CPU利用率>99%。

我需要有关如何降低 CPU 利用率的建议。

注意：提供示例查询仅供参考，不会执行。

SELECT SUM(QTY) QTY, SUM(RATE) RATE, COALESCE(CITY_ADDR,STATE_ADDR) ADDR,
       LISTAGG(DISTINCT PRODUCT, ', ') WITHIN GROUP(ORDER BY PRODID) PRODUCT
       C.CUSTOEMR
FROM TBLINVENTORY I 
INNER JOIN TBLPRODUCT P
INNER JOIN TBLCUSTOMER C
LEFT JOIN TBLADDRESS A
--- LIKE THIS JOINING 12 TABLES 
WHERE I.ID = 1234 AND C.CUSTOMERID = 4567
GROUP BY ADDR,PRODUCT, CUSTOMER

UNION ALL

SELECT SUM(QTY) QTY, SUM(RATE) RATE, COALESCE(CITY_ADDR,STATE_ADDR) ADDR,
       LISTAGG(DISTINCT PRODUCT, ', ') WITHIN GROUP(ORDER BY PRODID) PRODUCT
       C.CUSTOEMR
FROM TBLINVENTORY I 
INNER JOIN TBLPRODUCT P
INNER JOIN TBLCUSTOMER C
LEFT JOIN TBLADDRESS A
--- LIKE THIS JOINING 12 TABLES 
WHERE I.ID = 1234 AND C.CUSTOMERID = 4567
GROUP BY ADDR,PRODUCT, CUSTOMER

UNION ALL
...

Answer 1

由于 并发执行 union all。从 12.1 开始，使用 UNION ALL 的查询可以并行运行每个分支以提高性能。要关闭该功能，请使用提示 /*+ NO_PQ_CONCURRENT_UNION */。如果使用不同类型的并行性，/*+ NO_PARALLEL */ 提示可能会有所帮助。

要检查是否正在使用并行性，请运行以下查询以查找相关语句的 SQL_ID，然后生成该 SQL_ID 的执行计划。在执行计划中，如果您看到名称类似于“PX”的操作，则说明正在使用并行性。

select sql_id, sql_fulltext from gv$sql where lower(sql_fulltext) like lower('%LISTAGG(DISTINCT PRODUCT%');
select * from table(dbms_xplan.display_cursor(sql_id => 'Enter SQL_ID here'));

或者，可能有一种方法可以从查询中重构 UNION ALL，例如使用 UNPIVOT。