时间戳

首页 > 解决方案 > Oracle Cost-Based Optimizer：当标准在维度表上时如何使用事实表外键列的统计信息

oracle - Oracle Cost-Based Optimizer：当标准在维度表上时如何使用事实表外键列的统计信息

问题描述

我是这个社区的新手，我确实搜索过这个问题。抱歉，如果我问的是以前有人问过的问题。

我正在研究具有事实表和维度的维度数据仓库。这是一个 Oracle 12 数据库。维度有一个代理键列，这也是事实表中出现的内容，然后是一个具有业务价值的列，它与代理键（以及其他属性列）一一对应。事实表有许多外键，然后是一些要聚合的列。这些外键中的一些具有非常不均匀分布的数据，因此我们在事实表中的这些列上生成了直方图，因此基于成本的优化器知道何时选择了一个非常常见的值作为它不会非常有选择性的标准。例如，我们的一个维度值在我们的事实数据中大约 85% 的时间是“空白”的，但其他 15% 的行有 20,000 个不同的值。我们的维度表中有一行带有代理键，它表示该维度值的“空白”，以及其他 20,000 个值的行。如果没有直方图，优化器会认为这 20,000 个值是均匀分布的，因此当有人指定空白值时，它可能会做出非常糟糕的选择。

当我使用事实表上指定的条件运行查询时，这可以正常工作。优化器识别直方图统计数据并提供正确的基数估计。但是，如果我在连接的维度侧指定业务键的条件，则不会使用统计信息，并且基数估计会偏离。

select *
from FACT
  , DIM
where FACT.surrogate_key = DIM.surrogate_key
  and DIM.surrogate_key = 0 -- (zero means blank)

解释计划基数估计：5300 万行（大约正确）。事实表中总共有大约 6500 万行，其中大约 5300 万行表示该属性为空白的数据。

但是，如果我过滤用户实际执行的业务密钥，那么基数估计就很差了。

select *
from FACT
  , DIM
where FACT.surrogate_key = DIM.surrogate_key
  and DIM.business_key = '(blank)'

解释计划基数估计：14,000 行（甚至不接近）

当在维度表（而不是连接列）上指定条件时，如何让 CBO 使用直方图？

谢谢你。

标签： oraclequery-optimizationquery-performancesql-execution-plancost-based-optimizer

解决方案

您应该使用 oracle 10053 事件跟踪来跟踪您需要的信息。

请注意，只有硬解析会生成 10053 事件跟踪，

软解析不会生成 10053 事件跟踪。

但即使你得到 10053 事件跟踪并仔细阅读，

仅基于 10053 事件跟踪，oracle cbo 的机制尚不清楚。

以下是关于 10053 事件跟踪示例的示例：

sqlplus / as sysdba

SQL*Plus：2018 年 5 月 17 日星期四 09:36:41 发布 11.2.0.4.0 生产版

版权所有 (c) 1982, 2013，甲骨文。版权所有。

连接到：Oracle 数据库 11g 企业版 11.2.0.4.0 - 64 位生产，具有分区、OLAP、数据挖掘和真正的应用程序测试选项

SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> alter session set tracefile_identifier='yaoweijq';

会话已更改。

SQL> select * from obj$ where obj# = 0;

未选择任何行

那么你可以执行

显示参数 user_dump_dest

输出将包含一个目录，在该目录下，

ls -ltr yaoweijq

以 .trc 结尾的文件是 10053 事件跟踪文件。

推荐阅读