Oracle 19C 手动设置动态统计级别

原创 Asher.HU 2021-02-04

1265

确定对所有SQL语句都有利的数据库级设置可能很困难。

在设置动态统计信息的级别时，Oracle建议OPTIMIZER_DYNAMIC_SAMPLING在会话级别设置初始化参数。

假设条件

本教程假定以下内容：

您需要以下查询的正确选择性估计，该查询WHERE在两个相关列上有子句谓词：

  SELECT *
  FROM   sh.customers
  WHERE  cust_city='Los Angeles'
  AND    cust_state_province='CA';

前面的查询使用串行处理。
该sh.customers表包含符合查询条件的932行。
您已经在sh.customers表上收集了统计信息。
您在cust_city和cust_state_province列上创建了索引。
该OPTIMIZER_DYNAMIC_SAMPLING初始化参数设置为默认级别2。

要手动设置动态统计信息级别：

在SQL * Plus或SQL Developer中，以具有必要特权的用户身份登录数据库。

解释执行计划如下：

EXPLAIN PLAN FOR
  SELECT *
  FROM   sh.customers
  WHERE  cust_city='Los Angeles'
  AND    cust_state_province='CA';

查询计划如下：

SET LINESIZE 130
SET PAGESIZE 0
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY);

输出显示在下面（示例已重新格式化为适合页面的格式）：

-------------------------------------------------------------------------------
|Id| Operation                   | Name             |Rows|Bytes|Cost | Time   |
-------------------------------------------------------------------------------
| 0| SELECT STATEMENT            |                   | 53| 9593|53(0)|00:00:01|
| 1|  TABLE ACCESS BY INDEX ROWID|CUSTOMERS          | 53| 9593|53(0)|00:00:01|
|*2|   INDEX RANGE SCAN          |CUST_CITY_STATE_IND| 53| 9593| 3(0)|00:00:01|
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA')

WHERE子句中的列具有真实世界的相关性，但是优化器并不知道洛杉矶位于加利福尼亚，并且假定这两个谓词都会减少返回的行数。因此，该表包含932个满足条件的行，但优化器估计为53，如粗体所示。

如果数据库对该计划使用了动态统计信息，那么Note计划输出的这一部分将表明这一事实。优化器未使用动态统计信息，因为该语句是按顺序执行的，存在标准统计信息，并且参数OPTIMIZER_DYNAMIC_SAMPLING设置为默认值2。

4使用以下语句将动态统计信息级别设置为会话中的：
```
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=4;
```

再次说明该计划：

EXPLAIN PLAN FOR
  SELECT *
  FROM   sh.customers
  WHERE  cust_city='Los Angeles'
  AND    cust_state_province='CA';

新计划显示对行数的更准确的估计，如粗体值932所示：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2008213504
 
---------------------------------------------------------------------------
| Id  | Operation         | Name      |Rows | Bytes |Cost (%CPU)|Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 932 |   271K|   406 (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS | 932 |   271K|   406 (1)| 00:00:05 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CUST_CITY"='Los Angeles' AND "CUST_STATE_PROVINCE"='CA')
 
Note
-----
   - dynamic statistics used for this statement (level=4)

计划底部的注释表示采样级别为4。附加的动态统计信息使优化器了解到cust_city和cust_state_province列之间的实际关系，从而使它能够对行数产生更准确的估计：932，而不是53。

也可以看看：

Oracle Database SQL语言参考，以了解有关使用DYNAMIC_SAMPLING提示设置采样级别的信息
Oracle数据库参考，了解OPTIMIZER_DYNAMIC_SAMPLING初始化参数

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 手动设置动态统计级别

评论