预言机中查询的高 CPU 使用率

我有一个这样的查询...

SELECT cust_num, year,credit_cust FROM
(SELECT cust_num, year,credit_cust,
ROW_NUMBER () OVER(PARTITION BY cust_num, year ORDER BY credit_cust DESC) rnk
FROM credit_cust PARTITION (YEAR_2010)
)
WHERE rnk=1

执行此查询时，托管数据库的服务器的 CPU 使用率至少增加 35%。当服务器中的负载很高时，这确实令人担忧，因为 CPU 使用率有时会达到 100%。

要求：

数据如下...

Cust_num  Year    Credit_cust
  456     2010       Y
  456     2010       N
  456     2009       N
  456     2009       N

我希望查询仅返回下面给出的记录...

Cust_num  Year    Credit_cust
  456     2010       Y
  456     2009       N

注意：我使用 ROW_NUMBER（）作为 GROUP BY 子句的解决方法。如前所述，我认为它只是导致高 CPU 使用率的 GROUP BY 子句

备用查询：（也具有>35% 的 CPU 使用率）

SELECT   cust_num, YEAR, MAX (credit_cust)
    FROM credit_cust PARTITION (year_2010)
GROUP BY cust_num, YEAR

Server : Sun OS 5.10
Database : Oracle 11g (11.2.0.2.0 -64bit)

credit_cust表的结构如下...

CREATE TABLE CREDIT_CUST
(
  CUST_NUM          NUMBER,
  YEAR     NUMBER,
  CREDIT_CUST  CHAR(1)
)
TABLESPACE PARTITION_01
PARTITION BY RANGE (YEAR) 
SUBPARTITION BY HASH (CUST_NUM)
(  
  PARTITION YEAR_2009 VALUES LESS THAN (2010)
    SUBPARTITIONS 16 STORE IN (PARTITION_01,PARTITION_02,PARTITION_03,PARTITION_04,PARTITION_05,PARTITION_06,
                               PARTITION_01,PARTITION_02,PARTITION_03,PARTITION_04,PARTITION_05,PARTITION_06,
                               PARTITION_01,PARTITION_02,PARTITION_03,PARTITION_04),  
  PARTITION YEAR_2010 VALUES LESS THAN (2011)
    SUBPARTITIONS 1 STORE IN (PARTITION_01))
PARALLEL ( DEGREE 16 INSTANCES 1 );

请告诉我为什么会出现这个问题。如果需要更多详细信息，请告诉我。

解释计划：

PLAN_TABLE_OUTPUT
Plan hash value: 3927595547
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                                |    29M|  1179M|       |  3937   (3)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR                |                                |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10001                       |    29M|  1179M|       |  3937   (3)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    VIEW                        |                                |    29M|  1179M|       |  3937   (3)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|*  4 |     WINDOW SORT PUSHED RANK    |                                |    29M|   364M|   674M|  3937   (3)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                |                                |    29M|   364M|       |  3937   (3)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH             | :TQ10000                       |    29M|   364M|       |  3937   (3)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |
|*  7 |        WINDOW CHILD PUSHED RANK|                                |    29M|   364M|       |  3937   (3)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|   8 |         PX BLOCK ITERATOR      |                                |    29M|   364M|       |    34   (3)| 00:00:01 |     1 |     1 |  Q1,00 | PCWC |            |
|*  9 |          TABLE ACCESS FULL     | CREDIT_CUST                    |    29M|   364M|       |    34   (3)| 00:00:01 |   228 |   228 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("RNK"=1)
   4 - filter(ROW_NUMBER() OVER ( PARTITION BY "CUST_NUM","YEAR" ORDER BY INTERNAL_FUNCTION("CREDIT_CUST") DESC )<=1)
   7 - filter(ROW_NUMBER() OVER ( PARTITION BY "CUST_NUM","YEAR" ORDER BY INTERNAL_FUNCTION("CREDIT_CUST") DESC )<=1)
   9 - filter("YEAR"=2010)
Note
-----
   - dynamic sampling used for this statement (level=5)

我有两个建议：

正确分区表。您有一个分区细分，另一个不是（单个子分区）。在分区中，年份并不明显。我会按年份使用年度分区，按 cust 上的哈希使用子分区（每个分区 8 或 16 个子分区。如果表很大并且您有很多处理器，则为 16
您的查询将返回 2007、2008、2009、2010 年客户的结果。你确定要四年吗？如果您只想要 2010，则应where子句中指定year = 2010，并且不需要在解析函数中按年份划分，这有助于排序（消耗 CPU 和 TEMP 空间）。

查询如下所示：

SELECT cust_num, year,credit_cust 
FROM
  (SELECT cust_num, year,credit_cust,
   ROW_NUMBER () OVER(PARTITION BY cust_num ORDER BY credit_cust DESC) rnk
   FROM credit_cust
   WHERE YEAR=2010
   )
WHERE rnk=1;

一般来说，最好选择带有where子句的分区，而不是指定分区名（is也更容易）

由于并行性，此查询使用大量 CPU。该表设置为 DEGREE 16 。默认情况下，查询将使用 16 或 32 个并行服务器，具体取决于它们是否进行排序。

分区选项和查询执行计划在这里没有直接关系。即使它们不正确，"错误"查询通常也不会每秒消耗大量资源。

有许多可能的解决方案，具体取决于要禁用并行性的级别。以下是一些常见的解决方案：

语句级别：/*+ no_parallel(credit_cust) */
会话级别：alter session disable parallel query;
对象级别：alter table cust parallel 1;
系统级：alter system set parallel_max_servers = 0;

但在你改变任何事情之前，请仔细考虑。如果使用不当，其中一些解决方案可能是灾难性的。您需要充分了解并行性、系统资源以及系统的使用方式。像往常一样，一个好的起点是手册。

相关内容

最新更新

热门标签：