1 2 Previous Next 26 Replies Latest reply: Sep 3, 2010 9:52 AM by OraDBA02 RSS

    Weird execution plan

    OraDBA02
      I have EBS running on Oracle 10.2.0.4 .

      One of my query is getting degraded due to un-know reason.
      All table and index stats are recent. I have also tried collecting TKRPROF.

      AMZ_SUPPLIER_BALANCE_STAGE table is sized at 7 MB and the other table is 15 MB. Both of them are NON-PARTITIONED tables.
      SQL :
      
      SELECT  ASBS.OFA_VENDOR_ID, ASBS.PAYEE_CODE, ASBS.CURRENCY_CODE, ASBS.BALANCE,
      ASBS.OFA_ORG_ID FROM INTG_USER.AMZ_SUPPLIER_BALANCE_STAGE ASBS WHERE
      (ASBS.OFA_VENDOR_ID, ASBS.CURRENCY_CODE, ASBS.OFA_ORG_ID) NOT IN 
      ( SELECT 
      ASB1.OFA_VENDOR_ID, ASB1.CURRENCY_CODE, ASB1.OFA_ORG_ID 
      FROM INTG_USER.AMZ_SUPPLIER_BALANCE ASB1 WHERE ASB1.OFA_VENDOR_ID = ASBS.OFA_VENDOR_ID AND
      ASB1.CURRENCY_CODE = ASBS.CURRENCY_CODE AND ASB1.OFA_ORG_ID = ASBS.OFA_ORG_ID
      );
      SET AUTOTRACE TRACEONLY
      190 rows selected.
      
      Elapsed: 00:43:51.51
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3284118087
      
      -------------------------------------------------------------------------------------------------
      | Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |                            |   152K|  4020K|    52M  (2)|173:58:23 |
      |*  1 |  FILTER            |                            |       |       |            |          |
      |   2 |   TABLE ACCESS FULL| AMZ_SUPPLIER_BALANCE_STAGE |   152K|  4020K|   160   (2)| 00:00:02 |
      |*  3 |   TABLE ACCESS FULL| AMZ_SUPPLIER_BALANCE       |     1 |    14 |   352   (2)| 00:00:05 |
      -------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "INTG_USER"."AMZ_SUPPLIER_BALANCE"
                    "ASB1" WHERE "ASB1"."OFA_VENDOR_ID"=:B1 AND "ASB1"."OFA_ORG_ID"=:B2 AND
                    "ASB1"."CURRENCY_CODE"=:B3 AND LNNVL("ASB1"."OFA_VENDOR_ID"<>:B4) AND
                    LNNVL("ASB1"."CURRENCY_CODE"<>:B5) AND LNNVL("ASB1"."OFA_ORG_ID"<>:B6)))
         3 - filter("ASB1"."OFA_VENDOR_ID"=:B1 AND "ASB1"."OFA_ORG_ID"=:B2 AND
                    "ASB1"."CURRENCY_CODE"=:B3 AND LNNVL("ASB1"."OFA_VENDOR_ID"<>:B4) AND
                    LNNVL("ASB1"."CURRENCY_CODE"<>:B5) AND LNNVL("ASB1"."OFA_ORG_ID"<>:B6))
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
        225473027  consistent gets        ----------------> Too large !
             1965  physical reads
               64  redo size
             6098  bytes sent via SQL*Net to client
              470  bytes received via SQL*Net from client
               14  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
              190  rows processed
      TKPROF
      ********************************************************************************
      
      SELECT ASBS.OFA_VENDOR_ID, ASBS.PAYEE_CODE, ASBS.CURRENCY_CODE, ASBS.BALANCE,
      ASBS.OFA_ORG_ID FROM INTG_USER.AMZ_SUPPLIER_BALANCE_STAGE ASBS WHERE
      (ASBS.OFA_VENDOR_ID, ASBS.CURRENCY_CODE, ASBS.OFA_ORG_ID) NOT IN
      ( SELECT
      ASB1.OFA_VENDOR_ID, ASB1.CURRENCY_CODE, ASB1.OFA_ORG_ID
      FROM INTG_USER.AMZ_SUPPLIER_BALANCE ASB1 WHERE ASB1.OFA_VENDOR_ID = ASBS.OFA_VENDOR_ID AND
      ASB1.CURRENCY_CODE = ASBS.CURRENCY_CODE AND ASB1.OFA_ORG_ID = ASBS.OFA_ORG_ID
      )
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        0      0.00       0.00          0          0          0           0
      Execute      0      0.00       0.00          0          0          0           0
      Fetch       12   2317.19    2277.38       1730  200298710          0         174
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total       12   2317.19    2277.38       1730  200298710          0         174
      
      Misses in library cache during parse: 0
      Parsing user id: 353
      
      Elapsed times include waiting on following events:
        Event waited on                             Times   Max. Wait  Total Waited
        ----------------------------------------   Waited  ----------  ------------
        latch: cache buffers chains                   217        0.02          0.14
        db file scattered read                         66        0.05          1.27
        SQL*Net message from client                    13        0.00          0.02
        SQL*Net message to client                      12        0.00          0.00
        latch free                                      3        0.00          0.00
        db file sequential read                         6        0.00          0.04
      ********************************************************************************
      Table Stats
      select table_name,PCT_FREE,PCT_USED,INI_TRANS,FREELISTS,FREELIST_GROUPS,NUM_ROWS,BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,DEGREE
      from dba_tables where table_name in ('AMZ_SUPPLIER_BALANCE','AMZ_SUPPLIER_BALANCE_STAGE');
      
      TABLE_NAME                       PCT_FREE   PCT_USED  INI_TRANS  FREELISTS FREELIST_GROUPS   NUM_ROWS     BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN DEGREE
      ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- ---------- ---------- ---------- ----------- ------------------------------
      AMZ_SUPPLIER_BALANCE                   10         40          1          1               1     151500       1796          0          0          38          1
      AMZ_SUPPLIER_BALANCE_STAGE             10         40          1          1               1     152500        807          0          0          33          1
      Indexes
      Enter value for owner: INTG_USER
      Enter value for table_name: AMZ_SUPPLIER_BALANCE_STAGE
      
      INDEX_NAME                LAST_ANALYZED        COLUMN_NAME          COLUMN_POSITION TABLESPACE_NAME                STATUS   INDEX_TYPE   NUM_ROWS     BLEVEL CLUSTERING_FACTOR DESCEND
      ------------------------- -------------------- -------------------- --------------- ------------------------------ -------- ---------- ---------- ---------- ----------------- ----------
      AMZ_SUPPLIER_BALANCE_STAG 01-sep-2010:02:37:07 OFA_VENDOR_ID                      1 AMZ_SPACE                      VALID    NORMAL         151362          1            151045 ASC
      E_N1
      
      AMZ_SUPPLIER_BALANCE_STAG 01-sep-2010:02:37:07 CURRENCY_CODE                      2 AMZ_SPACE                      VALID    NORMAL         151362          1            151045 ASC
      E_N1
      
      AMZ_SUPPLIER_BALANCE_STAG 01-sep-2010:02:37:07 OFA_ORG_ID                         3 AMZ_SPACE                      VALID    NORMAL         151362          1            151045 ASC
      E_N1
      
      Enter value for owner: INTG_USER
      Enter value for table_name: AMZ_SUPPLIER_BALANCE
      
      INDEX_NAME                LAST_ANALYZED        COLUMN_NAME          COLUMN_POSITION TABLESPACE_NAME                STATUS   INDEX_TYPE   NUM_ROWS     BLEVEL CLUSTERING_FACTOR DESCEND
      ------------------------- -------------------- -------------------- --------------- ------------------------------ -------- ---------- ---------- ---------- ----------------- ----------
      AMZ_SUPPLIER_BALANCE_U1   01-sep-2010:02:37:14 BALANCE_ID                         1 AMZ_SPACE                      VALID    NORMAL         151243          2              7185 ASC
      INIT.ORA
      Enter value for parameter_mame: opti
      
      NAME                                                                   VALUE
      ---------------------------------------------------------------------- --------------------------------------------------------------------------------
      filesystemio_options                                                   setall
      plsql_optimize_level                                                   2
      object_cache_optimal_size                                              102400
      optimizer_features_enable                                              10.2.0.4
      optimizer_mode                                                         ALL_ROWS
      _optimizer_cost_based_transformation                                   OFF
      optimizer_index_cost_adj                                               100
      optimizer_index_caching                                                0
      optimizer_dynamic_sampling                                             2
      optimizer_secure_view_merging                                          TRUE
      MBRC is 32
      i want to understand why this simple query on very small tables are taking 45 min ? When i ran it, i was the only user on the system and still i got high wait on CBC.
      I tried to give index hint on STAG table, but CBO is ignoring the hint for unknown reason.
      What is causing large consistent gets (225473027) ?
      How come execution plan is not showing any JOIN Method ? Join Elimination is happening here ? Both of theses tables does not have any parent/child relationship though.


      Any help here would be highly appreciated.
        • 1. Re: Weird execution plan
          user503699
          OraDBA02 wrote:
          Any help here would be highly appreciated.
          My first suspect is non-default value for undocumented parameter optimizercost_based_transformation (OFF). Any idea why ? You may want to change the parameter value to default (ON) at a session level and try your query.
          • 2. Re: Weird execution plan
            OraDBA02
            Thanks for the reply.

            This is an OFA application. I dont know the reason WHY CBQT is set to OFF. Though i tried setting it to ON at session level, But there is no change in execution plan.
            • 3. Re: Weird execution plan
              user503699
              OraDBA02 wrote:
              Thanks for the reply.

              This is an OFA application. I dont know the reason WHY CBQT is set to OFF. Though i tried setting it to ON at session level, But there is no change in execution plan.
              That is strange (and not quite what I expected). WOuld you mind showing how you did it and tkprof details?
              Also, I might be missing something here but isn't your query same as following?
              SELECT  ASBS.OFA_VENDOR_ID, ASBS.PAYEE_CODE, ASBS.CURRENCY_CODE, ASBS.BALANCE,
              ASBS.OFA_ORG_ID FROM INTG_USER.AMZ_SUPPLIER_BALANCE_STAGE ASBS WHERE
              NOT EXISTS
              ( SELECT 
              NULL 
              FROM INTG_USER.AMZ_SUPPLIER_BALANCE ASB1 WHERE ASB1.OFA_VENDOR_ID = ASBS.OFA_VENDOR_ID AND
              ASB1.CURRENCY_CODE = ASBS.CURRENCY_CODE AND ASB1.OFA_ORG_ID = ASBS.OFA_ORG_ID
              );
              • 4. Re: Weird execution plan
                user503699
                OraDBA02 wrote:
                Thanks for the reply.

                This is an OFA application. I dont know the reason WHY CBQT is set to OFF. Though i tried setting it to ON at session level, But there is no change in execution plan.
                Also, can you show the "Row Source Operation" section of TkProf as well?
                • 5. Re: Weird execution plan
                  gjilevski1
                  Hi,

                  There are few EBS specifics

                  1. Init parameters - Can you make sure that you comply with the notes:
                  * NOTE 216205.1 - Database Initialization Parameters for Oracle Applications Release 11i
                  * NOTE 396009.1 - Database Initialization Parameters for Oracle Applications Release 12
                  2. Statistics
                  Use fnd_stats instead of dbms_stats

                  Regards,
                  • 6. Re: Weird execution plan
                    OraDBA02
                    My query does not have NOT EXIST. It has NOT IN.
                    Since it is EBS, I can neither put any hint nor change the query code !

                    I modified that parameter at session level using :

                    alter session set "_optimizer_cost_based_transformation"=on;

                    Session altered.

                    and did : SET AUTOT TRACEONLY and executed the query.
                    • 7. Re: Weird execution plan
                      OraDBA02
                      I did try using explain plan for NOT EXIST and query took 1 sec BUT DID NOT RETURN ANY ROW.! Orginal query returned 190 rows.
                      explain plan for
                      SELECT  ASBS.OFA_VENDOR_ID, ASBS.PAYEE_CODE, ASBS.CURRENCY_CODE, ASBS.BALANCE,
                      ASBS.OFA_ORG_ID FROM INTG_USER.AMZ_SUPPLIER_BALANCE_STAGE ASBS WHERE
                      NOT EXISTS
                      ( SELECT 
                      NULL 
                      FROM INTG_USER.AMZ_SUPPLIER_BALANCE ASB1 WHERE ASB1.OFA_VENDOR_ID = ASBS.OFA_VENDOR_ID AND
                      ASB1.CURRENCY_CODE = ASBS.CURRENCY_CODE AND ASB1.OFA_ORG_ID = ASBS.OFA_ORG_ID
                      );
                      
                      Plan hash value: 738320903
                      
                      -----------------------------------------------------------------------------------------------------------
                      | Id  | Operation            | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                      -----------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT     |                            |   151K|  6078K|       |   978   (2)| 00:00:12 |
                      |*  1 |  HASH JOIN RIGHT ANTI|                            |   151K|  6078K|  3744K|   978   (2)| 00:00:12 |
                      |   2 |   TABLE ACCESS FULL  | AMZ_SUPPLIER_BALANCE       |   147K|  2015K|       |   352   (2)| 00:00:05 |
                      |   3 |   TABLE ACCESS FULL  | AMZ_SUPPLIER_BALANCE_STAGE |   151K|  4003K|       |   159   (2)| 00:00:02 |
                      -----------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         1 - access("ASB1"."OFA_VENDOR_ID"="ASBS"."OFA_VENDOR_ID" AND
                                    "ASB1"."CURRENCY_CODE"="ASBS"."CURRENCY_CODE" AND "ASB1"."OFA_ORG_ID"="ASBS"."OFA_ORG_ID")
                      
                      SET AUTOT TRACEONLY
                      no rows selected
                      
                      Elapsed: 00:00:01.40
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 738320903
                      
                      -----------------------------------------------------------------------------------------------------------
                      | Id  | Operation            | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                      -----------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT     |                            |   151K|  6078K|       |   978   (2)| 00:00:12 |
                      |*  1 |  HASH JOIN RIGHT ANTI|                            |   151K|  6078K|  3744K|   978   (2)| 00:00:12 |
                      |   2 |   TABLE ACCESS FULL  | AMZ_SUPPLIER_BALANCE       |   147K|  2015K|       |   352   (2)| 00:00:05 |
                      |   3 |   TABLE ACCESS FULL  | AMZ_SUPPLIER_BALANCE_STAGE |   151K|  4003K|       |   159   (2)| 00:00:02 |
                      -----------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         1 - access("ASB1"."OFA_VENDOR_ID"="ASBS"."OFA_VENDOR_ID" AND
                                    "ASB1"."CURRENCY_CODE"="ASBS"."CURRENCY_CODE" AND "ASB1"."OFA_ORG_ID"="ASBS"."OFA_ORG_ID")
                      
                      
                      Statistics
                      ----------------------------------------------------------
                                1  recursive calls
                                0  db block gets
                             2626  consistent gets
                             2600  physical reads
                             1472  redo size
                              472  bytes sent via SQL*Net to client
                              327  bytes received via SQL*Net from client
                                1  SQL*Net roundtrips to/from client
                                0  sorts (memory)
                                0  sorts (disk)
                                0  rows processed
                      Unfortunately, there is no ROW-SOURCE information captured in tkrprof. I will try to re-generate it with explicit session close after query execution. I will post it in short while.

                      Edited by: OraDBA02 on Sep 1, 2010 3:59 AM
                      • 8. Re: Weird execution plan
                        user503699
                        OraDBA02 wrote:
                        My query does not have NOT EXIST. It has NOT IN.
                        Since it is EBS, I can neither put any hint nor change the query code !
                        But aren't those 2 queries equivalent?
                        BTW, the issue is due to the NULLability of either (or all) of the columns OFA_VENDOR_ID, CURRENCY_CODE, OFA_ORG_ID in both the tables.
                        You will have to either
                        a) change the tables to define these columns as NOT NULL or
                        b) change your query to NOT EXISTS or
                        c) add a "<column> IS NOT NULL" condition to both the tables in your query
                        • 9. Re: Weird execution plan
                          user503699
                          OraDBA02 wrote:
                          I did try using explain plan for NOT EXIST and query took 1 sec BUT DID NOT RETURN ANY ROW.! Orginal query returned 190 rows.
                          What is the specification/requirement behind this query? My understanding of the requirement is "find all rows in AMZ_SUPPLIER_BALANCE_STAGE table that do not have corresponding rows in AMZ_SUPPLIER_BALANCE table when compared for the combination of OFA_VENDOR_ID, CURRENCY_CODE and OFA_ORG_ID columns".
                          Does your output of 190 rows correct? Does it contain any rows where any of the columns have NULL values?
                          • 10. Re: Weird execution plan
                            user503699
                            A small test case
                            SQL> select * from v$version ;
                            
                            BANNER
                            ----------------------------------------------------------------
                            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
                            PL/SQL Release 10.2.0.4.0 - Production
                            CORE    10.2.0.4.0      Production
                            TNS for Solaris: Version 10.2.0.4.0 - Production
                            NLSRTL Version 10.2.0.4.0 - Production
                            
                            SQL> create table t1 (d1 varchar2(10), d2 varchar2(10)) ;
                            
                            Table created.
                            
                            SQL> create table t2 (d3 varchar2(10), d4 varchar2(10)) ;
                            
                            Table created.
                            
                            SQL> insert into t1 values ('ABC', 'DEF') ;
                            
                            1 row created.
                            
                            SQL> insert into t1 values ('BCD', NULL) ;
                            
                            1 row created.
                            
                            SQL> insert into t1 values (NULL, 'EFG') ;
                            
                            1 row created.
                            
                            SQL> insert into t1 values (NULL, NULL) ;
                            
                            1 row created.
                            
                            SQL> insert into t2 values ('GHI', 'JKL') ;
                            
                            1 row created.
                            
                            SQL> insert into t2 values ('HIJ', NULL) ;
                            
                            1 row created.
                            
                            SQL> insert into t2 values (NULL, 'KLM') ;
                            
                            1 row created.
                            
                            SQL> insert into t2 values (NULL, NULL) ;
                            
                            1 row created.
                            
                            SQL> commit ;
                            
                            Commit complete.
                            
                            SQL> select * from t1 where (d1, d2) not in (select d3, d4 from t2) ;
                            
                            no rows selected
                            
                            SQL> select * from t1 where not exists (select null from t2 where t2.d3 = t1.d1 and t2.d4 = t1.d2) ;
                            
                            
                            D1         D2
                            ---------- ----------
                            
                            BCD
                                       EFG
                            ABC        DEF
                            
                            SQL> select * from t1 where (d1, d2) not in (select d3, d4 from t2 where t2.d3 = t1.d1 and t2.d4 = t
                            1.d2) ;
                            
                            D1         D2
                            ---------- ----------
                            ABC        DEF
                            BCD
                                       EFG
                            • 11. Re: Weird execution plan
                              OraDBA02
                              Here is TKPROF with Row-source
                              It took 22 min to return 24 records.
                              ********************************************************************************
                              
                              SELECT  ASBS.OFA_VENDOR_ID, ASBS.PAYEE_CODE, ASBS.CURRENCY_CODE, ASBS.BALANCE,
                              ASBS.OFA_ORG_ID FROM INTG_USER.AMZ_SUPPLIER_BALANCE_STAGE ASBS WHERE
                              (ASBS.OFA_VENDOR_ID, ASBS.CURRENCY_CODE, ASBS.OFA_ORG_ID) NOT IN
                              ( SELECT
                              ASB1.OFA_VENDOR_ID, ASB1.CURRENCY_CODE, ASB1.OFA_ORG_ID
                              FROM INTG_USER.AMZ_SUPPLIER_BALANCE ASB1 WHERE ASB1.OFA_VENDOR_ID = ASBS.OFA_VENDOR_ID AND
                              ASB1.CURRENCY_CODE = ASBS.CURRENCY_CODE AND ASB1.OFA_ORG_ID = ASBS.OFA_ORG_ID
                              )
                              
                              call     count       cpu    elapsed       disk      query    current        rows
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              Parse        1      0.00       0.00          0          0          0           0
                              Execute      1      0.00       0.00          0          0          0           0
                              Fetch        3   1330.43    1307.78        610  114116846          0          24
                              ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                              total        5   1330.44    1307.78        610  114116846          0          24
                              
                              Misses in library cache during parse: 1
                              Optimizer mode: ALL_ROWS
                              Parsing user id: 353
                              
                              Rows     Row Source Operation
                              -------  ---------------------------------------------------
                                   24  FILTER  (cr=114116846 pr=610 pw=0 time=1190671319 us)
                               151386   TABLE ACCESS FULL AMZ_SUPPLIER_BALANCE_STAGE (cr=809 pr=610 pw=0 time=1543073 us)
                               151362   TABLE ACCESS FULL AMZ_SUPPLIER_BALANCE (cr=114116037 pr=0 pw=0 time=1304366376 us)
                              
                              
                              Elapsed times include waiting on following events:
                                Event waited on                             Times   Max. Wait  Total Waited
                                ----------------------------------------   Waited  ----------  ------------
                                SQL*Net message to client                       3        0.00          0.00
                                db file sequential read                        27        0.02          0.27
                                db file scattered read                         39        0.03          0.51
                                latch: cache buffers chains                   111        0.00          0.03
                                SQL*Net message from client                     3        0.00          0.00
                                latch free                                      2        0.00          0.00
                              ********************************************************************************
                              • 12. Re: Weird execution plan
                                user503699
                                OraDBA02 wrote:
                                Here is TKPROF with Row-source
                                It took 22 min to return 24 records.
                                Did you read my earlier response? Re: Weird execution plan
                                The performance problem is being caused since some or all of the columns being compared allow NULL values, which is preventing optimizer from using efficient anti-hash join.
                                Also, as I showed above, the NOT EXISTS query gave the same output as your NOT IN query in my Re: Weird execution plan.
                                You may want to revisit your query logic and requirements to see if they match.

                                Edited by: user503699 on Sep 1, 2010 6:14 PM
                                • 13. Re: Weird execution plan
                                  795160
                                  May be reading this link will help you.

                                  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:442029737684

                                  Regards

                                  Raj
                                  • 14. Re: Weird execution plan
                                    riedelme
                                    I did try using explain plan for NOT EXIST and query took 1 sec BUT DID NOT RETURN ANY ROW.! Orginal query returned 190 rows.
                                    Good job checking the result set - its ALWAYS possible to make a query run faster but usually hard to get the right results too :(

                                    Both the timing and plan were much better. The EXISTS option has great promise if you can get the right results. Check the WHERE clause you used VERY carefully. Also check the original query to make sure that it was right - its possible (though unlikely) that your new query is right and the original one was wrong. Finally, make sure the data is still there - just Monday somebody wiped out our test data leaving me wondering why my queries didn't return anything :)

                                    One other idea - some kind of query transformation is occuring since you didn't code the NOT EXISTS listed in the plan. Just for fun, what happens if you use the NO_QUERY_TRANSFORMATION hint?
                                    1 2 Previous Next