Extensible Optimizer and ORA-03113 (end-of-file on communication channel)
在测试通过extensible optimizer方法来解决table function的cardinality不准确的问题的时候 (关于什么是extensible optimizer以及如何设置可以参见这里) 居然遇到了ORA-03113的错误！
事情是这样的....
首先创建了一个table function...
CREATE OR REPLACE FUNCTION get_tab (p_cardinality IN INTEGER DEFAULT 1)
RETURN t_tf_tab PIPELINED AS
BEGIN
FOR i IN 1 .. 100 LOOP
PIPE ROW (t_tf_row(i, 'Description for ' || i));
END LOOP;
RETURN;
END;
/
注意这个函数有个参数叫p_cardinality，这个参数的目的就是用来告诉extensible optimizer这个table function会返回多少条记录。注意到这个参数有个默认值为1，其实要强烈推荐不要给这个参数设置默认值。因为这个默认值在很大程度上会导致ORA-03113的错误，这个下面会提到。
创建了table function,现在来创建一个object type来实现ODCI中相关的接口函数....
CREATE OR REPLACE TYPE t_ptf_stats AS OBJECT (
dummy INTEGER,
STATIC FUNCTION ODCIGetInterfaces (
p_interfaces OUT SYS.ODCIObjectList
) RETURN NUMBER,
STATIC FUNCTION ODCIStatsTableFunction (
p_function IN SYS.ODCIFuncInfo,
p_stats OUT SYS.ODCITabFuncStats,
p_args IN SYS.ODCIArgDescList,
p_cardinality IN INTEGER
) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY t_ptf_stats AS
STATIC FUNCTION ODCIGetInterfaces (
p_interfaces OUT SYS.ODCIObjectList
) RETURN NUMBER IS
BEGIN
p_interfaces := SYS.ODCIObjectList(
SYS.ODCIObject ('SYS', 'ODCISTATS2')
);
RETURN ODCIConst.success;
END ODCIGetInterfaces;
STATIC FUNCTION ODCIStatsTableFunction (
p_function IN SYS.ODCIFuncInfo,
p_stats OUT SYS.ODCITabFuncStats,
p_args IN SYS.ODCIArgDescList,
p_cardinality IN INTEGER
) RETURN NUMBER IS
BEGIN
p_stats := SYS.ODCITabFuncStats(p_cardinality);
RETURN ODCIConst.success;
END ODCIStatsTableFunction;
END;
/
注意函数ODCIStatsTableFunction中有一个自定义的参数，名为p_cardinality，这个名字必须跟上面定义的table function的参数名保持一致。ODCIStatsTableFunction的其他参数都是oracle预定义的参数，不能更改。
完成了这两步，接下来要把这个object type和刚才定义的table function关联起来，这样就可以通过这个object type来告诉CBO这个table function的cardinality该设置成多少。
ASSOCIATE STATISTICS WITH FUNCTIONS get_tab USING t_ptf_stats;
这里顺便提一下，如果要将table function和object type的关联解除，可以通过命令DISASSOCIATE STATISTICS FROM， 如下所示...
disassociate statistics from functions get_tab;
接下来就可以测试了。因为既然函数get_tab有个默认的参数值为1，那么可不可以不要设置这个参数呢？ （虽然get_tab实际上是返回100条数据）
SQL> select * from table(get_tab);
Execution Plan
----------------------------------------------------------
ERROR:
ORA-03113: end-of-file on communication channel
SP2-0612: Error generating AUTOTRACE EXPLAIN report
结果诡异的事情发生了！居然出现了end-of-file的错误，当前session被无情地关闭了！
搞了半天，最后发现当我设置了参数p_cardinality的时候，这个错误就消失了...
SQL> select * from table(get_tab(100));
Execution Plan
----------------------------------------------------------
Plan hash value: 3023735445
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 200 | 24 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB | | | | |
---------------------------------------------------------------------------------------------
SQL> select * from table(get_tab(100000));
Execution Plan
----------------------------------------------------------
Plan hash value: 3023735445
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 195K| 25 (4)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB | | | | |
---------------------------------------------------------------------------------------------
SQL> select * from table(get_tab(10));
Execution Plan
----------------------------------------------------------
Plan hash value: 3023735445
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 20 | 24 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB | | | | |
---------------------------------------------------------------------------------------------
SQL> select * from table(get_tab(1));
Execution Plan
----------------------------------------------------------
Plan hash value: 3023735445
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 24 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB | | | | |
---------------------------------------------------------------------------------------------
所以在调用get_tab的时候必须给参数赋值，这个真的很奇怪！
另外还有个问题很奇怪，不能像如下这种方式调用get_tab,
SQL> select * from table(get_table(p_cardinality=>100));
也就是不能指定参数名p_cardinality， 否则就会报出一个更加无厘头的错误....
SQL> select * from table(get_table(p_cardinality=>100));select * from table(get_table(p_cardinality=>100))*ERROR at line 1:
ORA-00907: missing right parenthesis
哎，看来用extensible optimizer还是有风险的~~~
--------------------------------------
Regards,
FangwenYu
