Oracle 19C 设置优化器统计信息：示例

原创 Asher.HU 2021-02-04

461

本示例说明如何收集表的优化器统计信息，设置人工统计信息，然后比较优化器根据不同统计信息选择的计划。

本示例假定：

您以具有DBA特权的用户身份登录到数据库。
您想测试优化器何时选择索引扫描。

创建一个名为的表contractors，并对该salary列进行索引。

CREATE TABLE contractors (
  con_id    NUMBER,
  last_name VARCHAR2(50),
  salary    NUMBER,
  CONSTRAINT cond_id_pk PRIMARY KEY(con_id) );

CREATE INDEX salary_ix ON contractors(salary);

在此表中插入一行。

INSERT INTO contractors VALUES (8, 'JONES',1000);
COMMIT;

收集表的统计信息。

EXECUTE DBMS_STATS.GATHER_TABLE_STATS( user, tabname => 'CONTRACTORS' );

查询表和索引的行数（包括示例输出）：

SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'CONTRACTORS'; 

  NUM_ROWS
----------
         1

SQL> SELECT NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME =  'SALARY_IX'; 

  NUM_ROWS
----------
         1

使用dynamic_sampling提示禁用动态采样来查询薪水为1000的承包商：

SELECT /*+ dynamic_sampling(contractors 0) */ * 
FROM   contractors 
WHERE  salary = 1000;

查询优化器选择的执行计划（包括示例输出）：

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

SQL_ID	cy0wzytc16g9n, child number 0
-------------------------------------
SELECT /*+ dynamic_sampling(contractors 0) */ * FROM contractors WHERE
salary = 1000

Plan hash value: 5038823

----------------------------------------------------------------------
| Id  | Operation         | Name        |Rows|Bytes|Cost (%CPU)| Time|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |   |    | 2 (100)|          |
|*  1 |  TABLE ACCESS FULL| CONTRACTORS | 1 | 12 | 2   (0)| 00:00:01 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SALARY"=1000)

19 rows selected.

由于表中仅存在1行，因此优化程序将在索引范围扫描中选择全表扫描。

使用SET_TABLE_STATS和SET_INDEX_STATS模拟具有10个数据块中存储的2000行的表的统计信息：

BEGIN
  DBMS_STATS.SET_TABLE_STATS( 
    ownname => user
  , tabname => 'CONTRACTORS'
  , numrows => 2000
  , numblks => 10 );
END;
/

BEGIN 
  DBMS_STATS.SET_INDEX_STATS( 
    ownname => user
  , indname => 'SALARY_IX'
  , numrows => 2000 );
END;
/

查询表和索引的行数（包括示例输出）：

SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'CONTRACTORS'; 

  NUM_ROWS
----------
      2000

SQL> SELECT NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME =  'SALARY_IX'; 

  NUM_ROWS
----------
      2000

现在，优化器认为该表在10个块中包含2000行，即使在一个块中实际上仅存在1行也是如此。

刷新共享池以消除计划重用的可能性，然后执行以下查询contractors：

ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ dynamic_sampling(contractors 0) */ * 
FROM   contractors 
WHERE  salary = 1000;

根据人工统计信息查询优化器选择的执行计划（包括示例输出）：

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

SQL_ID	cy0wzytc16g9n, child number 0
-------------------------------------
SELECT /*+ dynamic_sampling(contractors 0) */ * FROM contractors WHERE
salary = 1000

Plan hash value: 996794789

---------------------------------------------------------------------------------
|Id| Operation                           | Name      |Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------
| 0| SELECT STATEMENT                    |           |    |     |3(100)|        |
| 1|  TABLE ACCESS BY INDEX ROWID BATCHED|CONTRACTORS|2000|24000|3 (34)|00:00:01|
|*2|   INDEX RANGE SCAN                  |SALARY_IX  |2000|     |1  (0)|00:00:01|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SALARY"=1000)

20 rows selected.

基于人为生成的行数和块分布的统计信息，优化器认为索引范围扫描更具成本效益。

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

关注作者

Oracle 19C 设置优化器统计信息：示例

评论