Oracle 19C 为特定工作负载检测有用的列组

DROP TABLE customers_test;
CREATE TABLE customers_test AS SELECT * FROM customer;
EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');

在另一个SQL * Plus会话中，连接为SYS并运行以下PL / SQL程序以启用监视300秒：

BEGIN
  DBMS_STATS.SEED_COL_USAGE(null,null,300);
END;
/

以下示例显示了customers_test表中两个查询的解释计划：

EXPLAIN PLAN FOR
  SELECT *
  FROM   customers_test
  WHERE  cust_city = 'Los Angeles'
  AND    cust_state_province = 'CA'
  AND    country_id = 52790;
 
SELECT PLAN_TABLE_OUTPUT 
FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
 
EXPLAIN PLAN FOR
  SELECT   country_id, cust_state_province, count(cust_city)
  FROM     customers_test
  GROUP BY country_id, cust_state_province;
 
SELECT PLAN_TABLE_OUTPUT 
FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

输出示例如下：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 4115398853
 
----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
----------------------------------------------------
 
8 rows selected.
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3050654408
 
-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |  1949 |
|   1 |  HASH GROUP BY     |                |  1949 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------
 
9 rows selected.

第一个计划显示返回932行的查询的基数为1行。第二个计划显示返回145行的查询的1949行基数。

调用该DBMS_STATS.REPORT_COL_USAGE函数以生成报告：

SET LONG 100000
SET LINES 120
SET PAGES 0
SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
FROM   DUAL;

该报告显示如下：

LEGEND:
.......
 
EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...........................................................................
 
###########################################################################
 
COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
.........................................
 
1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY
###########################################################################

在上一个报告中，前三个列在第一个受监视的查询的相等谓词中使用：

...
WHERE  cust_city = 'Los Angeles'
AND    cust_state_province = 'CA'
AND    country_id = 52790;

所有这三列都出现在同一WHERE子句中，因此该报告将它们显示为组过滤器。在第二个查询中，该GROUP BY子句中出现了两列，因此报表将其标记为GROUP_BY。FILTER和GROUP_BY报表中的列集是列组的候选项。