7 Replies Latest reply: May 11, 2012 10:48 AM by rp0428 RSS

    Bind varialbe use resulting in FTS

    Ratnesh Sharma
      Hi,
      I have am working on following oracle 10g database and facing issue related to bind peeking.

      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, Data Mining and Real Application Testing options

      some other parameter values are set as below in the sesssion.

      SQL> show parameter optimizer_mode

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      optimizer_mode string ALL_ROWS
      SQL> show parameter cursor_sharing

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      cursor_sharing string EXACT
      SQL> show parameter optimpeek_user_binds

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      optimpeek_user_binds boolean TRUE
      SQL>

      Table

      CREATE TABLE GRSADM.LEGAL_ENTITIES_9APR_CHANGES
      (
      ENTITY_ID NUMBER NOT NULL,
      ------------------------------------------------------------
      ------------------------------------------------------------
      COL1 NUMBER,
      COL2 VARCHAR2(20 BYTE)
      )
      ;


      CREATE INDEX GRSADM.LEGAL_ENTITIES_9APR_IDX1 ON GRSADM.LEGAL_ENTITIES_9APR_CHANGES
      (COL1)
      ;


      CREATE INDEX GRSADM.LEGAL_ENTITIES_9APR_IDX2 ON GRSADM.LEGAL_ENTITIES_9APR_CHANGES
      (COL2)
      ;

      This table skewed data for col1, which is not using index when I use a bind variable.

      select col1,col2,count(distinct entity_id)
      from LEGAL_ENTITIES_9APR_CHANGES
      group by col1,col2;

      COL1 COL2 COUNT(DISTINCTENTITY_ID)
      ---------- -------------------- ------------------------
      1 1 421819
      2 2 1000

      I have tried histogram using following options, but none helped.

      begin
      dbms_stats.gather_table_stats(user,'LEGAL_ENTITIES_9APR_CHANGES',method_opt=> 'for all INDEXED columns size 254'
      );
      end;


      begin
      dbms_stats.gather_table_stats(user,'LEGAL_ENTITIES_9APR_CHANGES',method_opt=> 'for columns size auto col1'
      );
      end;

      begin
      dbms_stats.gather_table_stats(user,'LEGAL_ENTITIES_9APR_CHANGES',method_opt=> 'for all columns size auto'
      );
      end;


      --when I use a literal  value
      SQL> select entity_id from LEGAL_ENTITIES_9APR_CHANGES where col1=2;

      1000 rows selected.

      Elapsed: 00:00:00.04

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2666974094

      -----------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -----------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1065 | 10650 | 51 (10)| 00:00:01 |
      | 1 | TABLE ACCESS BY INDEX ROWID| LEGAL_ENTITIES_9APR_CHANGES | 1065 | 10650 | 51 (10)| 00:00:01 |
      |* 2 | INDEX RANGE SCAN | LEGAL_ENTITIES_9APR_IDX1 | 1078 | | 6 (17)| 00:00:01 |
      -----------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - access("COL1"=2)


      Statistics
      ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      171 consistent gets
      5 physical reads
      0 redo size
      19947 bytes sent via SQL*Net to client
      1214 bytes received via SQL*Net from client
      68 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1000 rows processed

      SQL>


      I am expecting the same output when I use a bind variable, but it is doint a FTS instead of index access.

      var a number;

      begin
      :a := 2;
      end;


      SQL> select entity_id from LEGAL_ENTITIES_9APR_CHANGES where col1=:a;

      1000 rows selected.

      Elapsed: 00:00:00.97

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 4125611035

      -------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 422K| 4121K| 10893 (33)| 00:00:41 |
      |* 1 | TABLE ACCESS FULL| LEGAL_ENTITIES_9APR_CHANGES | 422K| 4121K| 10893 (33)| 00:00:41 |
      -------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      1 - filter("COL1"=TO_NUMBER(:A))


      Statistics
      ----------------------------------------------------------
      0 recursive calls
      0 db block gets
      31500 consistent gets
      0 physical reads
      0 redo size
      19947 bytes sent via SQL*Net to client
      1214 bytes received via SQL*Net from client
      68 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1000 rows processed

      SQL>


      Please suggest how can I fix this issue and enable use of index ? I do not want to use a hint....
        • 1. Re: Bind varialbe use resulting in FTS
          Prabodh
          SQL> select entity_id from LEGAL_ENTITIES_9APR_CHANGES where col1=:a;
          is leading to
          1 - filter("COL1"=TO_NUMBER(:A))
          That means COL1 is a number .
          The moment you use a FUNCTION on the LHS or RHS in a condition the INDEX is not used. Look up the documentation for "WHY?"

          Change your code to
          SQL> select entity_id from LEGAL_ENTITIES_9APR_CHANGES where col1=to_number (:a);
          And then check.

          Regards,
          • 2. Re: Bind varialbe use resulting in FTS
            Ratnesh Sharma
            Thanks for your response Prabodh.
            but this is not helping.

            SQL> select entity_id from LEGAL_ENTITIES_9APR_CHANGES where col1=to_number (:a);




            1000 rows selected.

            Elapsed: 00:00:00.03

            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 4125611035

            -------------------------------------------------------------------------------------------------
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
            -------------------------------------------------------------------------------------------------
            | 0 | SELECT STATEMENT | | 422K| 4121K| 10893 (33)| 00:00:41 |
            |* 1 | TABLE ACCESS FULL| LEGAL_ENTITIES_9APR_CHANGES | 422K| 4121K| 10893 (33)| 00:00:41 |
            -------------------------------------------------------------------------------------------------

            Predicate Information (identified by operation id):
            ---------------------------------------------------

            1 - filter("COL1"=TO_NUMBER(:A))


            Statistics
            ----------------------------------------------------------
            1 recursive calls
            0 db block gets
            171 consistent gets
            0 physical reads
            0 redo size
            19947 bytes sent via SQL*Net to client
            1214 bytes received via SQL*Net from client
            68 SQL*Net roundtrips to/from client
            0 sorts (memory)
            0 sorts (disk)
            1000 rows processed

            SQL>
            • 3. Re: Bind varialbe use resulting in FTS
              Prabodh
              Which environment are you using to run this?
              Some of your code appears PL/SQL but the query seems to be a SQLPlus prompt.

              Regards,
              • 4. Re: Bind varialbe use resulting in FTS
                Ratnesh Sharma
                all sql and plsql are executed on sql prompt only.
                • 5. Re: Bind varialbe use resulting in FTS
                  Prabodh
                  In SQLPlus you should be using the following syntax to run the query
                  select entity_id from LEGAL_ENTITIES_9APR_CHANGES where col1=&1;
                  Enter the value for &1 when prompted.

                  Regards,
                  • 6. Re: Bind varialbe use resulting in FTS
                    Himanshu Binjola
                    Hi Ratnesh,

                    Bind Variable may generate incorrect plan in 10g if the data is skewed, histograms are available and query is executed using different bind values

                    1. First Time - The value associated with the bind variable is peeked and the correct plan is chosen based on optimizer statistics.
                    2. Second Time and Onwards - If the plan is already available in the library cache for query using bind variable the plan is chosen by peeking the value which generated the plan first time.

                    Lets try to see the peeked value for bind variable. If the plan is already available in the library cache then we can see the peeked bind value.

                    Run the statement as given below and send the complete output
                    SQL> SET LINESIZE 200 FEEDBACK ON TIMING ON;
                    SQL> VARIABLE A NUMBER;
                    SQL> EXEC :A := 2;
                    SQL> SELECT /*+ gather_plan_statistics */ COUNT(*) FROM LEGAL_ENTITIES_9APR_CHANGES WHERE COL1=:A;
                    SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED'));
                    
                    {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                    • 7. Re: Bind varialbe use resulting in FTS
                      rp0428
                      >
                      I have am working on following oracle 10g database and facing issue related to bind peeking.
                      >
                      Why are you concluding the issue is related to bind peeking?

                      The only example you show uses the same attribute value ('2') for the query and it is a value that, when hard-coded, gives the plan. So if Oracle peeked it would tend to use that same value and there wouldn't be a problem.

                      You do not show that you are collecting index statistics after the data is loaded. Collect statistics on the index and then verify that the index statistics were actually collected by querying the system views.