Oracle查询计划效率问题

你试过什么暗示? 你能发布快速和慢速查询计划吗?

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

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

告诉优化器在P_EXTERNAL_id中需要10个元素。

Tom Kyte对 the cardinality hint and PL/SQL collections 顺便问一下。

外部ID列的数据类型是什么?您的集合是字符串的集合,但外部ID往往意味着一个数字。这里真的有数据类型不匹配吗?

如果问题是优化器在引用集合时无法获得准确的基数估计值,但在引用临时表时可以获得准确的估计值,则将集合复制到临时表中只会有帮助。如果您正确地指定了基数提示,而这并没有改变性能,那么这就意味着问题不在优化器的基数估计上。

你能发布快速和慢速查询计划吗? 你能发布你正在使用的包含基数提示的SQL语句吗(可能有语法错误)

我相信它正在进行一次全面扫描,因为它无法预测p_外部id是否会大于或小于盈亏平衡点。

我的意思是:

如果做一个索引查找要花费200英镑,做一个完整的表扫描要花费100000英镑,如果你要查找20个值,总花费将是4000英镑(少于100000英镑)。但如果你要查找1000个值,使用这些指数的总成本将是200000。

这个问题的回答非常令人满意 setting cardinality for pipelined and table functions ,所以请去看全文!

总结:

方法: 可扩展优化程序

可扩展优化程序由Oracle数据盒带实现(它本质上是一种对象类型,称为接口类型,包含一个或多个定义良好的结构化方法)。此功能使我们能够设计自己的基数计算(作为接口类型中的指定方法),然后将它们与表或流水线函数关联。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'));