5 Replies Latest reply: Jul 23, 2012 5:05 AM by Dom Brooks RSS

    How and Which Hint will be able to replicate the plan?

    933257
      hi All, I am using 10.2.0.4.0.

      I am having one query which is having different plans in different environment(databases) as below. I want to verify the performance of the query by forcing the optimizer to use plan as that of 'Environment -2' in 'Environment -1' as below. So how can i force the optimizer(using hint) to use the 'CONCATENATION' path rather than 'BITMAP CONVERSION TO ROWIDS' path of execution. (Note-Query in 'Environment -1' completes in ~3 minutes but in 'Environment -2' completes in ~1.5 minutes.)

      Query:
      select 
      *
       FROM BASE XB,
            MAP CC
          WHERE (XB.BUYERPK = CC.PARENTPK
                OR XB.BUYERPK = CC.CHILDPK)
                and cc.CHILDPK= 3914297344;
      
                  
      Plan in Environment -1 :        
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1223568078
      
      -------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                         | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                  |                               | 14644 |  3846K|  2444   (7)| 00:00:13 |
      |   1 |  NESTED LOOPS                     |                               | 14644 |  3846K|  2444   (7)| 00:00:13 |
      |   2 |   TABLE ACCESS BY INDEX ROWID     | MAP                           |     1 |    14 |     1   (0)| 00:00:01 |
      |*  3 |    INDEX UNIQUE SCAN              | PARENT_CHILD_P1               |     1 |       |     1   (0)| 00:00:01 |
      |   4 |   TABLE ACCESS BY INDEX ROWID     | BASE                          | 14644 |  3646K|  2444   (7)| 00:00:13 |
      |   5 |    BITMAP CONVERSION TO ROWIDS    |                               |       |       |            |          |
      |   6 |     BITMAP OR                     |                               |       |       |            |          |
      |   7 |      BITMAP CONVERSION FROM ROWIDS|                               |       |       |            |          |
      |   8 |       SORT ORDER BY               |                               |       |       |            |          |
      |*  9 |        INDEX RANGE SCAN           | IDX_byuer_supplyPK            |       |       |     3  (34)| 00:00:01 |
      |  10 |      BITMAP CONVERSION FROM ROWIDS|                               |       |       |            |          |
      |  11 |       SORT ORDER BY               |                               |       |       |            |          |
      |* 12 |        INDEX RANGE SCAN           | IDX_byuer_supplyPK            |       |       |     3  (34)| 00:00:01 |
      -------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access("CC"."CHILDPK"=3914297344)
         9 - access("XB"."BUYERPK"="CC"."PARENTPK")
             filter("XB"."BUYERPK"="CC"."PARENTPK")
        12 - access("XB"."BUYERPK"="CC"."CHILDPK")
             filter("XB"."BUYERPK"="CC"."CHILDPK")
              
      Plan in Environment -2 :
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 931058577
      
      ---------------------------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |                               |   126K|    32M|  6173   (3)| 00:00:32 |
      |   1 |  CONCATENATION                |                               |       |       |            |       |
      |   2 |   NESTED LOOPS                |                               |   126K|    32M|  3086   (3)| 00:00:16 |
      |   3 |    TABLE ACCESS BY INDEX ROWID| MAP                           |     1 |    14 |     1   (0)| 00:00:01 |
      |*  4 |     INDEX UNIQUE SCAN         | PARENT_CHILD_P1               |     1 |       |     1   (0)| 00:00:01 |
      |   5 |    TABLE ACCESS BY INDEX ROWID| BASE                          |   126K|    30M|  3085   (3)| 00:00:16 |
      |*  6 |     INDEX RANGE SCAN          | IDX_byuer_supplyPK            |  7261 |       |     3  (34)| 00:00:01 |
      |   7 |   NESTED LOOPS                |                               |   164 | 43952 |  3086   (3)| 00:00:16 |
      |   8 |    TABLE ACCESS BY INDEX ROWID| MAP                           |     1 |    14 |     1   (0)| 00:00:01 |
      |*  9 |     INDEX UNIQUE SCAN         | PARENT_CHILD_P1               |     1 |       |     1   (0)| 00:00:01 |
      |  10 |    TABLE ACCESS BY INDEX ROWID| BASE                          |   164 | 41656 |  3085   (3)| 00:00:16 |
      |* 11 |     INDEX RANGE SCAN          | IDX_byuer_supplyPK            |  7261 |       |     3  (34)| 00:00:01 |
      ---------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         4 - access("CC"."CHILDPK"=3914297344)
         6 - access("XB"."BUYERPK"="CC"."CHILDPK")
         9 - access("CC"."CHILDPK"=3914297344)
        11 - access("XB"."BUYERPK"="CC"."PARENTPK")
             filter(LNNVL("XB"."BUYERPK"="CC"."CHILDPK"))
              
      
      Index 'IDX_byuer_supplyPK' is on (BUYERPK, supplypk);
      
       
        • 1. Re: How and Which Hint will be able to replicate the plan?
          mnsinger
          This can be done via a SQL Plan Baseline, I believe.

          I have seen similar problems in our environment and tried to document our fix here:

          http://mnsinger.wordpress.com/2012/02/12/creating-a-sql-plan-baseline-for-a-problem-query/
          • 2. Re: How and Which Hint will be able to replicate the plan?
            Dom Brooks
            See template tuning thread [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to Post a SQL tuning request.

            Rather than looking to force a plan initially with hints or baselines, I would suggest you look at differences in parameters and statistics, particularly histograms.

            Problems with b tree bitmap plan conversion operations are usually related to poor estimates by the optimizer which in turn are often traced back to parameters or statistics.

            You can turn this behaviour off by setting the parameter "_b_tree_bitmap plans" to false either in the session or using the opt_param hint, e.g.
            /*+ opt_param('_b_tree_bitmap_plans','false') */
            You could also try the USE_CONCAT hint.


            However why not just rewrite the SQL to the equivalent of the statement that the optimizer is running with the CONCATENATION plan?

            i.e. something like
            SELECT *
            FROM   BASE XB
            ,      MAP CC
            WHERE  XB.BUYERPK = CC.PARENTPK
            AND    cc.CHILDPK= 3914297344
            UNION 
            SELECT *
            FROM   BASE XB
            ,      MAP CC
            WHERE  XB.BUYERPK = CC.CHILDPK
            AND    cc.CHILDPK= 3914297344;
            • 3. Re: How and Which Hint will be able to replicate the plan?
              Jonathan Lewis
              Dom Brooks wrote:
              However why not just rewrite the SQL to the equivalent of the statement that the optimizer is running with the CONCATENATION plan?

              i.e. something like
              SELECT *
              FROM   BASE XB
              ,      MAP CC
              WHERE  XB.BUYERPK = CC.PARENTPK
              AND    cc.CHILDPK= 3914297344
              UNION 
              SELECT *
              FROM   BASE XB
              ,      MAP CC
              WHERE  XB.BUYERPK = CC.CHILDPK
              AND    cc.CHILDPK= 3914297344;
              Since we don't know about the data structures involved or the possible volume of data, the better generic solution is to have a UNION ALL and add the predicate: LNNVL("XB"."BUYERPK"="CC"."CHILDPK") to the second query block. (In this case you query looks as if it would produce the right answer efficiently - but the correctness depends on the various PK columns actually being primary keys, and we shouldn't assum that).


              Note: for comments on lnnvl() see http://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/

              Regards
              Jonathan Lewis
              • 4. Re: How and Which Hint will be able to replicate the plan?
                951071
                Hi

                plz try this. i think it can help you to go through concatination plan.

                alter session set "_no_or_expansion" = FALSE;

                select /*+ use_concat leading(CC) index(CC PARENT_CHILD_P1) */
                *
                FROM BASE XB,
                MAP CC
                WHERE (XB.BUYERPK = CC.PARENTPK
                OR XB.BUYERPK = CC.CHILDPK)
                and cc.CHILDPK= 3914297344;

                Regards
                Eswar
                • 5. Re: How and Which Hint will be able to replicate the plan?
                  Dom Brooks
                  Since we don't know about the data structures involved or the possible volume of data, the better generic solution is to have a UNION ALL and add the predicate: LNNVL("XB"."BUYERPK"="CC"."CHILDPK") to the second query block. (In this case you query looks as if it would produce the right answer efficiently - but the correctness depends on the various PK columns actually being primary keys, and we shouldn't assum that).
                  Hi Jonathan,

                  Thanks for pointing out this important oversight.

                  Just to clarify the difference with a noddy example...

                  If we do have duplicates:
                  SQL> create table t1
                    2  (par   number
                    3  ,child number);
                  
                  Table created.
                  
                  SQL> create table t2
                    2  (col1 number
                    3  ,col2 varchar2(1));
                  
                  Table created.
                  
                  SQL> insert into t1 values (1,1);
                  
                  1 row created.
                  
                  SQL> insert into t1 values (1,1);
                  
                  1 row created.
                  
                  SQL> insert into t2 values (1,'A');
                  
                  1 row created.
                  
                  SQL> select * from t1;
                  
                         PAR      CHILD
                  ---------- ----------
                           1          1
                           1          1
                  
                  SQL> select * from t2;
                  
                        COL1 C
                  ---------- -
                           1 A
                  
                  SQL> 
                  And this is the OR we want to rewrite:
                  SQL> select *
                    2  from   t2, t1
                    3  where  t2.col1 = par
                    4  or     t2.col1 = child;
                  
                        COL1 C        PAR      CHILD
                  ---------- - ---------- ----------
                           1 A          1          1
                           1 A          1          1
                  
                  2 rows selected.
                  
                  SQL> 
                  Then the UNION is not equivalent because a UNION removes duplicates:
                  SQL> select *
                    2  from   t2, t1
                    3  where  t2.col1 = par
                    4  union
                    5  select *
                    6  from   t2, t1
                    7  where  t2.col1 = child;
                  
                        COL1 C        PAR      CHILD
                  ---------- - ---------- ----------
                           1 A          1          1
                  
                  1 row selected.
                  
                  SQL> 
                  And a UNION ALL:
                  SQL> select *
                    2  from   t2, t1
                    3  where  t2.col1 = par
                    4  union all
                    5  select *
                    6  from   t2, t1
                    7  where  t2.col1 = child;
                  
                        COL1 C        PAR      CHILD
                  ---------- - ---------- ----------
                           1 A          1          1
                           1 A          1          1
                           1 A          1          1
                           1 A          1          1
                  
                  4 rows selected.
                  
                  SQL> 
                  requires the LNVL to avoid the duplicates across the two branches:
                  SQL> select *
                    2  from   t2, t1
                    3  where  t2.col1 = par
                    4  union all
                    5  select *
                    6  from   t2, t1
                    7  where  t2.col1 = child
                    8  and    lnnvl(t1.par=t1.child);
                  
                        COL1 C        PAR      CHILD
                  ---------- - ---------- ----------
                           1 A          1          1
                           1 A          1          1
                  
                  2 rows selected.
                  
                  SQL> 
                  Cheers,
                  Dominic