Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Monthly Archives: August 2011

ORA-14092 Oracle 11g

There is no information about ORA-14092: “Number of expressions is not equal to the number of partitioning columns” in Oracle documentation neither Oracle Support (Metalink), except the description, that can be found from oerr:

bash$ oerr ora 14092
14092, 00000, "number of expressions is not equal to the number of partitioning columns"
// *Document: NO
// *Cause:  Number of expressions submitted to tbl$or$idx$part$num differs
//          from the number of partitioning columns of the table or index
//          mapping into partition(s) of which is to be determined
// *Action: Supply a correct number of expressions to be used in
//          determining mapping into partition(s) of a specified
//          table or index

As You can see it has option Document: NO, it means that this error message is for internal use only and is not documented. You can face it in Oracle 11g, when trying to execute statement that contains partitioned table or get explain plan for it. Sure, this error message is the result of the oracle code bug. The function tbl$or$idx$part$num is used internally in the time of dynamic statistics sampling and also can be used in the process of hard parsing, when You execute or try to explain plan for the statement. Also, You must consider that in 11g if query use parallel execution the default value of optimizer_dynamic_sampling parameter that equals to 2 will be ignored! Optimizer will automatically decide to use dynamic sampling or not and on which level. It will depend on sizes of the tables and complexity of predicates. Another beautiful feature of 11g is ability to create extended statistics on group of columns or expressions. The information about extended statistics on specified table You can check from the view dba_stat_extensions, for example:

SQL> select * from dba_stat_extensions where table_name='MYTAB';

OWNER             TABLE_NAME                     EXTENSION_NAME                 EXTENSION                      CREATOR DROPPABLE
----------------- ------------------------------ ------------------------------ ------------------------------ ------- ---------
SCOTT             MYTAB                          SYS_STUUW0KZSEKWUQ5FOHQPPDGRIG ("ID","NAME")                  USER    YES

It is very useful feature to estimate right cardinality. But in conjunction with dynamic sampling in some circumstances, it can produce ORA-14092.

This bug can be seen in different situantions, for example when CURSOR_SHARING is set to default EXACT value or when dynamic sampling with extended statistics is used or in any other case where internal tbl$or$idx$part$num function can be used.

Workarounds:

  1. Set CURSOR_SHARING=FORCE and flush old parsed sql from the shared pool
  2. Drop extended statistics on table columns, using dbms_stats.drop_extended_stats
  3. Disable dynamic sampling, by setting optimizer_dynamic_sampling parameter to 0 on session or system level, or by hint on whole cursor or specific table.
  4. Or open a case to Oracle Support and wait for patch.
To drop extended statistics:
SQL> exec dbms_stats.drop_extended_stats('SCOTT','MYTAB','(id,name)');

(c) Aychin Gasimov, 08/2011, Azerbaijan Republic


Advertisements