Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

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

5 responses to “ORA-14092 Oracle 11g

  1. Chinar September 2, 2011 at 12:12

    Hi Aychin,in which version of 11g you got this error?
    But i worry about your workaround 🙂 because before dropping extended statistics or disabling dynamic sampling need deeply examination,due to even optimizer try to using dynamic sampling(DS) and it consider using DS is more efficient method,however there are available extends statistics,in this case you can got bad plan if you dropped multicolumn stats.Because the input arguments(other important statistics and optimizer parameter ,…) of optimizer can be missing.Before 11g there is not other efficient method of estimating selectivity of multicolumn related predicates(correlated columns).In this case DS is best way,in 11g this problem solved.Still i have not get this error but just want to say choosing one of above methods(dropping extends statistics or disabling dynamic sampling) must be careful(investigate EP and 10053 trace file at minimum). Hope you agree with me.

    • aychin September 2, 2011 at 13:18

      Dear Chinar,
      It is very advisable, to read article very carefully. I never write that it is sole solution to drop ext. stats or disable dynamic sampling. When I write something in this blog I imagine that, the readers of this posts is qualified enough to understand such simple things that You are talking about. I just concentrate on the core of the issue or topic. Considering that I hope that people after reading this topic will not go and drop their extended statistics with closed eyes. I classified this issue as a bug, because error message returned is marked as internal and in normal circumstances it must not be thrown to the user. This error means, that something goes wrong in the internal algorithm in some specific circumstances. I also, didn’t wrote that You will face this issue every time You will use ext. stats, I wrote that “You can face”. And sure it can be unacceptable for You to drop ext. stats or disable DS, then You have option to communicate to Oracle support or to refuse the problematic query or rewrite it but it will not helpful to You to investigate Execution Plan or trace it, because when You will try to do it You will get ORA-14092 and there will be nothing to investigate 🙂 Also, I want to wish You to never face this error.

      • Chinar September 2, 2011 at 13:30

        I carefully read this topic and carefully think about that,without this i can never add any comment 🙂 .And about investigating executing plan.As you mentioned you can use DS and extends statistics separately(not together),and i mean investigate plan for both cases individually about how CBO estimate selectivity accurately.

      • aychin September 2, 2011 at 14:03

        Dear Chinar,
        I want to note again, this topic is not about selectivity or benefits of the dynamic sampling or extended statistics. Also as I wrote above “You can face it in Oracle 11g, when trying to execute statement that contains partitioned table or get explain plan for it”, it means that when You will try to execute “explain plan for” statement You will get this error!

  2. AndyK September 14, 2016 at 11:06

    I know this is a very old post but somebody might find it on google and there is not much information about this. I found another reason why ORA-14092 occurs and this is because there are unusable partitions and/or subpartitions on a table in the query. Simply rebuild them to resolve this error.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: