Oracle查询计划效率问题

Question

以下查询在PL/SQL 过程中给出。

SELECT e.data FROM extra e WHERE e.external_id in
    (SELECT * FROM TABLE (p_external_ids)).

p_external_ids 的类型是创建或替换类型“VARCHAR2TABLE”作为 VARCHAR2(4000 CHAR) 表。

Oracle 使用全表扫描执行查询的效率很低。 查询提示没有帮助，必要的索引已就位。使用硬编码 ID 替换 SELECT * 部分可将查询运行时间减少 20 倍，当表中的行数为200 000时。

作为参考，使用 SELECT * FROM TABLE 子句执行大约需要 0.3 秒，对于单个硬编码 ID 大约需要 0.015 毫秒。

建议使用哪些有效方法（键搜索）来编写存储过程以从表中提取多个 id 的数据？提供的集合类型必须用于将 id 列表传递给存储过程。

Answer 1

您尝试了哪些提示？
你能发布快速和慢速查询计划吗？

在 SQL 中使用 PL/SQL 集合的常见问题之一是 CBO 经常错误地猜测集合中的元素数量，并因此选择错误的计划。在这些情况下，使用 CARDINALITY 提示通常很有帮助，即

SELECT e.data 
  FROM extra e
 WHERE e.external_id IN (
    SELECT /*+ cardinality(ids 10) */ *
      FROM TABLE( p_external_ids ) ids
  )

告诉优化器期望 P_EXTERNAL_IDS 中有 10 个元素。

Tom Kyte 对 askTom 上的基数提示和 PL/SQL 集合。

EXTERNAL_ID 列的数据类型是什么？您的集合是字符串的集合，但 EXTERNAL_ID 往往意味着数字。这里真的存在数据类型不匹配吗？

仅当问题是优化器在引用集合时无法获得准确的基数估计，但在引用临时表时可以获得准确的基数估计时，将集合复制到临时表才会有帮助。如果您正确指定了 CARDINALITY 提示并且不会改变性能，则意味着问题不在于优化器的基数估计。

您能发布快速和慢速查询计划吗？
您能否发布您正在使用的包含 CARDINALITY 提示的确切 SQL 语句（可能存在语法错误）

Answer 2

我相信它正在进行全面扫描，因为它无法预测 p_external_ids 是否会大于或小于盈亏平衡点。

我的意思是：

如果执行单个索引查找的成本为 200，执行全表扫描的成本为 100000，如果您查找 20 个值，则总成本将为 4000（小于 100000）。但如果您要查找 1000 个值，则使用索引的总成本将为 200000。

Answer 3

这个问题在设置管道和表的基数上得到了非常令人满意的回答函数，所以请阅读全文！

---

摘要：

方法：可扩展优化器

可扩展优化器由 Oracle Data Cartridge 实现（本质上是一种对象类型，称为接口类型，包含一个或多个定义良好的结构化方法）。此功能使我们能够设计自己的基数计算（作为接口类型中的规定方法），然后将它们与我们的表或管道函数相关联。 CBO 在查询优化期间调用该类型的基数方法来确定管道函数或表函数的行计数。

以下引用和示例来自文章，但经过一定程度的调整以一致地回答问题。

1）制作一个包装函数

我们将创建一个小函数，它将接收并返回通用 VARCHAR2TABLE 类型的集合。该函数对集合本身不执行任何操作；它只是它的一个包装。

SQL> CREATE FUNCTION card_varchar2(
  2                  p_collection IN varchar2table
  3                  ) RETURN varchar2table IS
  4  BEGIN
  5     RETURN p_collection;
  6  END card_varchar2;
  7  /

Function created.

2）创建接口类型

其次，我们将创建一个与简单的 card_varchar2 函数关联的接口类型规范，如下所示。

SQL> CREATE TYPE card_varchar2_ot AS OBJECT (
  2
  3     dummy_attribute NUMBER,
  4
  5     STATIC FUNCTION ODCIGetInterfaces (
  6                     p_interfaces OUT SYS.ODCIObjectList
  7                     ) RETURN NUMBER,
  8
  9     STATIC FUNCTION ODCIStatsTableFunction (
 10                     p_function   IN  SYS.ODCIFuncInfo,
 11                     p_stats      OUT SYS.ODCITabFuncStats,
 12                     p_args       IN  SYS.ODCIArgDescList,
 13                     p_collection IN varchar2table
 14                     ) RETURN NUMBER
 15
 16  );
 17  /

Type created.

和主体

SQL> CREATE TYPE BODY card_varchar2_ot AS
  2
  3     STATIC FUNCTION ODCIGetInterfaces (
  4                     p_interfaces OUT SYS.ODCIObjectList
  5                     ) RETURN NUMBER IS
  6     BEGIN
  7        p_interfaces := SYS.ODCIObjectList(
  8                           SYS.ODCIObject ('SYS', 'ODCISTATS2')
  9                           );
 10        RETURN ODCIConst.success;
 11     END ODCIGetInterfaces;
 12
 13     STATIC FUNCTION ODCIStatsTableFunction (
 14                     p_function   IN  SYS.ODCIFuncInfo,
 15                     p_stats      OUT SYS.ODCITabFuncStats,
 16                     p_args       IN  SYS.ODCIArgDescList,
 17                     p_collection IN  varchar2table
 18                     ) RETURN NUMBER IS
 19     BEGIN
 20        p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
 21        RETURN ODCIConst.success;
 22     END ODCIStatsTableFunction;
 23
 24  END;
 25  /

Type body created.

3) 将函数与接口类型相关联，如下所示。

SQL> ASSOCIATE STATISTICS WITH FUNCTIONS card_varchar2 USING card_varchar2_ot;

Statistics associated.

4）现在像这样使用这个函数：

SQL> SELECT *
  2  FROM   TABLE(card_varchar2('A','B','C'));