7 Replies Latest reply on Mar 5, 2013 8:39 AM by Hoek

    Query returning unexpected results.

    845836
      Hi,
      One of our query which use to run successfully is suddenly failing in one of the environment. Oracle Version: 11.2.0.2.0. Can someone please suggest me why the query is not behaving as expected and works fine when using hint?

      The below query doesn't return any rows.
      select ctran.* from f_transaction_released tran, F_CHARGE_CODE_RELEASED CTRAN where 
      id_liability_cif in ('727873','141515') and  TRAN.ID_TRANSACTION_RELEASED = CTRAN.ID_TRANSACTION_RELEASED 
      The same query with hint return rows.
      select /*+ optimizer_features_enable('9.2.0.8') */ ctran.* from f_transaction_released tran, F_CHARGE_CODE_RELEASED CTRAN where 
      id_liability_cif in ('727873','141515') and  TRAN.ID_TRANSACTION_RELEASED = CTRAN.ID_TRANSACTION_RELEASED 
      Explain plan of both the queries are:

      1st Query:
      Plan hash value: 196119809
       
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                          |                            |   750 | 96000 |   551   (1)| 00:00:07 |       |       |        |      |            |
      |   1 |  PX COORDINATOR                           |                            |       |       |            |          |       |       |        |      |            |
      |   2 |   PX SEND QC (RANDOM)                     | :TQ10001                   |   750 | 96000 |   551   (1)| 00:00:07 |       |       |  Q1,01 | P->S | QC (RAND)  |
      |*  3 |    HASH JOIN                              |                            |   750 | 96000 |   551   (1)| 00:00:07 |       |       |  Q1,01 | PCWP |            |
      |   4 |     BUFFER SORT                           |                            |       |       |            |          |       |       |  Q1,01 | PCWC |            |
      |   5 |      PX RECEIVE                           |                            |   444 |  5328 |   330   (0)| 00:00:04 |       |       |  Q1,01 | PCWP |            |
      |   6 |       PX SEND BROADCAST                   | :TQ10000                   |   444 |  5328 |   330   (0)| 00:00:04 |       |       |        | S->P | BROADCAST  |
      |   7 |        PARTITION RANGE ALL                |                            |   444 |  5328 |   330   (0)| 00:00:04 |     1 |    96 |        |      |            |
      |   8 |         INLIST ITERATOR                   |                            |       |       |            |          |       |       |        |      |            |
      |   9 |          TABLE ACCESS BY LOCAL INDEX ROWID| F_TRANSACTION_RELEASED     |   444 |  5328 |   330   (0)| 00:00:04 |     1 |    96 |        |      |            |
      |  10 |           BITMAP CONVERSION TO ROWIDS     |                            |       |       |            |          |       |       |        |      |            |
      |* 11 |            BITMAP INDEX SINGLE VALUE      | F_TRANSACTION_RELEASE_BMX9 |       |       |            |          |     1 |    96 |        |      |            |
      |  12 |     PX BLOCK ITERATOR                     |                            |  7579K|   838M|   219   (1)| 00:00:03 |     1 |     3 |  Q1,01 | PCWC |            |
      |* 13 |      TABLE ACCESS FULL                    | F_CHARGE_CODE_RELEASED     |  7579K|   838M|   219   (1)| 00:00:03 |     1 |     3 |  Q1,01 | PCWP |            |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         3 - access("TRAN"."ID_TRANSACTION_RELEASED"="CTRAN"."ID_TRANSACTION_RELEASED")
        11 - access("ID_LIABILITY_CIF"=141515 OR "ID_LIABILITY_CIF"=727873)
        13 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"CTRAN"."ID_TRANSACTION_RELEASED"))
      2nd query:
      Plan hash value: 4037684686
       
      -----------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                 | Name                       | Rows  | Bytes | Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      -----------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                          |                            |     2 |   256 |   299 |       |       |        |      |            |
      |   1 |  PX COORDINATOR                           |                            |       |       |       |       |       |        |      |            |
      |   2 |   PX SEND QC (RANDOM)                     | :TQ10002                   |     2 |   256 |   299 |       |       |  Q1,02 | P->S | QC (RAND)  |
      |*  3 |    HASH JOIN BUFFERED                     |                            |     2 |   256 |   299 |       |       |  Q1,02 | PCWP |            |
      |   4 |     BUFFER SORT                           |                            |       |       |       |       |       |  Q1,02 | PCWC |            |
      |   5 |      PX RECEIVE                           |                            |     1 |    12 |   203 |       |       |  Q1,02 | PCWP |            |
      |   6 |       PX SEND HASH                        | :TQ10000                   |     1 |    12 |   203 |       |       |        | S->P | HASH       |
      |   7 |        PARTITION RANGE ALL                |                            |     1 |    12 |   203 |     1 |    96 |        |      |            |
      |   8 |         INLIST ITERATOR                   |                            |       |       |       |       |       |        |      |            |
      |   9 |          TABLE ACCESS BY LOCAL INDEX ROWID| F_TRANSACTION_RELEASED     |     1 |    12 |   203 |     1 |    96 |        |      |            |
      |  10 |           BITMAP CONVERSION TO ROWIDS     |                            |       |       |       |       |       |        |      |            |
      |* 11 |            BITMAP INDEX SINGLE VALUE      | F_TRANSACTION_RELEASE_BMX9 |       |       |       |     1 |    96 |        |      |            |
      |  12 |     PX RECEIVE                            |                            |  7579K|   838M|    96 |       |       |  Q1,02 | PCWP |            |
      |  13 |      PX SEND HASH                         | :TQ10001                   |  7579K|   838M|    96 |       |       |  Q1,01 | P->P | HASH       |
      |  14 |       PX BLOCK ITERATOR                   |                            |  7579K|   838M|    96 |     1 |     3 |  Q1,01 | PCWC |            |
      |  15 |        TABLE ACCESS FULL                  | F_CHARGE_CODE_RELEASED     |  7579K|   838M|    96 |     1 |     3 |  Q1,01 | PCWP |            |
      -----------------------------------------------------------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         3 - access("TRAN"."ID_TRANSACTION_RELEASED"="CTRAN"."ID_TRANSACTION_RELEASED")
        11 - access("ID_LIABILITY_CIF"=141515 OR "ID_LIABILITY_CIF"=727873)
       
      Note
      -----
         - cpu costing is off (consider enabling it)
        • 1. Re: Query returning unexpected results but gives result when used
          845836
          Do anyone has an idea why the output differs?
          • 2. Re: Query returning unexpected results.
            Hoek
            It might very well be a bug, did you check MetaLink/MyOracleSupport already?
            On the other hand:
            One of our query which use to run successfully is suddenly failing in one of the environment
            'Suddenly failing' is often a synonym for 'something has changed'.
            Are you absolutely sure nothing has changed?
            What made you decide to use that hint anyway?
            And are the tables 'normal, ordinary tables' or does the F_ prefix indicate something special?
            • 3. Re: Query returning unexpected results.
              845836
              We've hit similar situation in past and recreating the table works. But not sure what's the root cause of the issue. If you notice the same query with hint gives expected result.

              There's only one difference I see in explain pla, i.e
                13 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"CTRAN"."ID_TRANSACTION_RELEASED"))
              • 4. Re: Query returning unexpected results.
                Hoek
                Hence my questions, which you didn't address:
                1) It might very well be a bug, did you check MetaLink/MyOracleSupport already?

                'Suddenly failing' is often a synonym for 'something has changed'.

                2)Are you absolutely sure nothing has changed?

                3)What made you decide to use that hint anyway?

                4)And are the tables 'normal, ordinary tables' or does the F_ prefix indicate something special?

                And to add another one:
                5) Did you or your DBA noticed strange errors or messages in the alert.log?
                • 5. Re: Query returning unexpected results.
                  845836
                  Hence my questions, which you didn't address:
                  1) It might very well be a bug, did you check MetaLink/MyOracleSupport already?
                  I didn't check that yet
                  'Suddenly failing' is often a synonym for 'something has changed'.

                  2)Are you absolutely sure nothing has changed?
                  Nothing has changed unless some stats or index got corrupted which we are assuming.
                  3)What made you decide to use that hint anyway?
                  We read somewhere there is a bug with Oracle 11G hash joins and trying with 9i does help. 
                  4)And are the tables 'normal, ordinary tables' or does the F_ prefix indicate something special?
                  It's fact tables
                  And to add another one:
                  5) Did you or your DBA noticed strange errors or messages in the alert.log?
                  Have to check on that.
                  • 6. Re: Query returning unexpected results.
                    845836
                    Any suggestions?
                    • 7. Re: Query returning unexpected results.
                      Hoek
                      My suggestion:
                      Let us know the outcome of 1) and 5), have you checked already?