cluster factor对执行计划的影响

最新推荐文章于 2024-01-16 08:58:10 发布

你好我是李白

最新推荐文章于 2024-01-16 08:58:10 发布

阅读量401

点赞数

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/jwocnimabi/article/details/106556041

版权

本文通过测试环境探讨了Oracle数据库中cluster factor对执行计划的影响，特别是在12c R1和19c的新特性下。19c引入了real-time statistics，帮助优化器生成更优的执行计划。cluster factor是衡量索引顺序与表数据一致性的重要指标，其值影响索引扫描成本。当cluster factor较高时，全表扫描可能成为更优选择。通过调整cluster factor，如重建表并排序，可以降低该值，从而改变执行计划。

摘要由CSDN通过智能技术生成

cluster factor对执行计划的影响

测试环境：Linux 7.6 + ORACLE 19.6.1

1.创建测试环境

1.1 创建测试表并插入数据

CZH@czhpdb > create table test_ffs as select * from hr.employees;
 
Table created.
 
CZH@czhpdb > insert into test_ffs select * from test_ffs;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 296244252
 
---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |          |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL         | TEST_FFS |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |          |   107 |  7383 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL             | TEST_FFS |   107 |  7383 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: statistics for conventional DML
 
 
Statistics
----------------------------------------------------------
         72  recursive calls
         89  db block gets
         81  consistent gets
         12  physical reads
      21576  redo size
        195  bytes sent via SQL*Net to client
        394  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
        107  rows processed

# 上面autotrace执行计划可以看到两个新特性：

1.2 12c R1与19c两个新特性

1.2.1 12c R1新特性OPTIMIZER STATISTICS GATHERING：

# OPTIMIZER STATISTICS GATHERING：12cR1以后的新特性，direct path load时，空表第一次加载数据时会自动收集统计信息。

# Oracle Database 12c introduced online statistics gathering for CREATE TABLE AS SELECT statements and direct-path inserts.

1.2.2 19c新特性real-time statistics

Oracle Database 19c introduces real-time statistics
, which extend online support to conventional DML statements
. Because statistics can go stale between DBMS_STATS jobs, real-time statistics helps the optimizer generate more optimal plans.Whereas bulk load operations gather all necessary statistics, real-time statistics augment rather than replace traditional statistics.

·Oracle introduced new parameters

·"_optimizer_gather_stats_on_conventional_dml" and "_optimizer_use_stats_on_conventional_dml" which are true by default

·"_optimizer_stats_on_conventional_dml_sample_rate" at 100%

·How does real time statistics works?