This discussion is archived
6 Replies Latest reply: Jan 20, 2011 9:47 AM by 832273 RSS

Histograms and bind variables

832273 Newbie
Currently Being Moderated
Hi guys,
When a SQL is using bind variables how histograms afftect the excution plan?

For example
There is a table "TEST" has column a number, b varchar2(100),c char(100).
100000 rows in this table and 90000 rows column a value is 1 the others value is 2-10 per 100rows.

Now this column a has histogram and a btree index on a;

following is example code
var a_v number;
exec :a_v:=10;

selelct * from test where a=:a_v;

The excution plan is full table scan.

if i do not use bind variable. it will scan the index.


i remember a document mentioned that "do not use histograms when using bind variables".

but why?

even i disabled bind variable peeking?


Thanks guys.Waiting forward for your reply.
  • 1. Re: Histograms and bind variables
    654150 Explorer
    Currently Being Moderated
    Oracle tries many permutation and combination bases on the where calusae, and if you put hard coded values instead of Bind variable, oracle caculates what kind of data filter you are trying to do, e.g. you are looking for rows created today and you haver not used the bind varible then oracle choose different explain plan. but over a period same query may not perform good. So always a good idea is to use bind variable to see the estimated executation plan. most of the time if you use bind varible, it is likely to pick same exexcution plan.
  • 2. Re: Histograms and bind variables
    Centinul Guru
    Currently Being Moderated
    Here is a good, concise, explanation from Jonathan Lewis:

    Oracle Scratchpad - Philosophy – 1
  • 3. Re: Histograms and bind variables
    832273 Newbie
    Currently Being Moderated
    Hi SKavi ,
    your answer is helpful to me but not related to my questions actually...
  • 4. Re: Histograms and bind variables
    832273 Newbie
    Currently Being Moderated
    Thank you Centinul this article is useful to me , but it doesn't explaned why histograms can't work well with bind variables.

    Thanks again.
  • 5. Re: Histograms and bind variables
    CharlesHooper Expert
    Currently Being Moderated
    Longfei Wei wrote:
    Thank you Centinul this article is useful to me , but it doesn't explaned why histograms can't work well with bind variables.

    Thanks again.
    Longfei,

    Jonathan Lewis' article is very good - take another look at that article. Histograms can work with bind variables, but the end result is typically not the desired outcome. Bind variables are used to reduce the number of different execution plans. Histograms are used to find what is supposed to be the best execution plan for the supplied predicates, and in the case of bind variables, those are the peeked values of the bind variables. So, if you have a histogram on a column and for the hard parse of a SQL statement and the most common value in that column is submitted in the bind variable - that execution plan is considered by the optimizer to be the "best" execution plan for the supplied bind variable values. Now assume that instead the least popular value in the column is specified - the optimizer could produce a very different execution plan for the same SQL statement, one that is optimized for the least popular value (this might be an index range scan, rather than a full table scan). Now assume that the execution plan cannot change when the bind variable values change - if you have a single popular value and many unpopular values, if the hard parse is performed with the single popular value, you could find that all future executions of that SQL statement perform full table scans, even when only a couple of rows from the table are selected.

    Here is a quick test case on Oracle Database 11.2.0.2 to demonstrate:
    CREATE TABLE T1 (
      C1 NUMBER,
      C2 NUMBER,
      C3 VARCHAR2(300));
     
    INSERT INTO
      T1
    SELECT
      *
    FROM
      (SELECT
        ROWNUM C1,
        DECODE(MOD(ROWNUM,100),99,99,1) C2,
        RPAD('A',300,'A') C3
      FROM
        DUAL
      CONNECT BY
        LEVEL <= 1000000)
    ORDER BY
      C2;
     
    CREATE INDEX IND_T1_C2 ON T1(C2);
     
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254')
    The above created a table with 1,000,000 rows where 99% of the rows have a value of 1 in column C2 and 1% have a value of 99, and the rows are inserted with a perfect clustering factor due to the ORDER BY clause. A histogram was created on the indexed column.

    Let's try a test, we will pick an unpopular value, the 2 for the bind variable:
    VARIABLE N1 NUMBER
    EXEC :N1:=2
      
    SELECT /*+ GATHER_PLAN_STATISTICS */
      C1,
      C2
    FROM
      T1
    WHERE
      C2 = :N1;
     
    no rows selected
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
      
    SQL_ID  c7su63uw7nch6, child number 0
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T1 WHERE   C2 =
    :N1
     
    Plan hash value: 236868917
      
    ------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    ------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       3 |      1 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   5957 |      0 |00:00:00.01 |       3 |      1 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |      1 |   5957 |      0 |00:00:00.01 |       3 |      1 |
    ------------------------------------------------------------------------------------------------------------
      
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C2"=:N1)
    So, there were no rows selected, the optimizer predicted that 5,957 rows would be returned and an index access path was selected. Would this index access path also be appropriate for the bind variable value 1? Let's continue the test, this time picking the value 99 for the bind variable:
    EXEC :N1:=99
    SET TIMING ON
      
    SELECT /*+ GATHER_PLAN_STATISTICS */
      C1,
      C2
    FROM
      T1
    WHERE
      C2 = :N1;
     
    ...
    10000 rows selected.
     
    Elapsed: 00:00:05.35
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    SQL_ID  c7su63uw7nch6, child number 0
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T1 WHERE   C2 =
    :N1
     
    Plan hash value: 236868917
     
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |      1 |        |  10000 |00:00:00.02 |    1783 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   5957 |  10000 |00:00:00.02 |    1783 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |      1 |   5957 |  10000 |00:00:00.01 |     690 |
    ---------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C2"=:N1)
    Once again, the optimizer predicted that 5,957 rows would be retrieved even though 10,000 rows were retrieved. Notice also that the child number is still 0. Let's continue the test, this time with a bind variable value of 1:
    EXEC :N1:=1
     
    SET AUTOTRACE TRACEONLY STATISTICS
      
    SELECT /*+ GATHER_PLAN_STATISTICS */
      C1,
      C2
    FROM
      T1
    WHERE
      C2 = :N1;
     
    990000 rows selected.
     
    Elapsed: 00:00:18.78
     
    Statistics
    ---------------------------------------------------
              1  recursive calls
              1  db block gets
         108571  consistent gets
              0  physical reads
             96  redo size
       21958348  bytes sent via SQL*Net to client
         726508  bytes received via SQL*Net from client
          66001  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         990000  rows processed
      
    SET AUTOTRACE OFF
    Because I used AUTOTRACE to prevent the 990,000 rows from scrolling on screen, I have to specify the SQL_ID and CHILD_NUMBER to retrieve the execution plan:
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('c7su63uw7nch6',0,'ALLSTATS LAST'));
     
    SQL_ID  c7su63uw7nch6, child number 0
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T1 WHERE   C2 =
    :N1
     
    Plan hash value: 236868917
     
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |      1 |        |  10000 |00:00:00.02 |    1783 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   5957 |  10000 |00:00:00.02 |    1783 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |      1 |   5957 |  10000 |00:00:00.01 |     690 |
    ---------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C2"=:N1)
    That cannot be the execution plan that was used because it still shows that 10,000 rows were retrieved. Let's try again, this time with CHILD_NUMBER 1:
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('c7su63uw7nch6',1,'ALLSTATS LAST'));
      
    SQL_ID  c7su63uw7nch6, child number 1
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T1 WHERE   C2 =
    :N1
     
    Plan hash value: 3617692013
     
    ------------------------------------------------------------------------------------
    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |      1 |        |    990K|00:00:00.83 |     108K|
    |*  1 |  TABLE ACCESS FULL| T1   |      1 |    988K|    990K|00:00:00.83 |     108K|
    ------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter("C2"=:N1)
    The above shows the actual plan that was used. Adaptive cursor sharing (first available with Oracle Database 11.1) stepped in and forced the re-evaluation of the execution plan to prevent a very slow retrieval through the index - that will not happen prior to Oracle Database 11.1.

    Just to demonstrate:
    ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.4';
     
    VARIABLE N1 NUMBER
    EXEC :N1:=2
     
    SELECT /*+ GATHER_PLAN_STATISTICS */
      C1,
      C2
    FROM
      T1
    WHERE
      C2 = :N1;
     
    no rows selected
     
    Elapsed: 00:00:00.00
      
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    SQL_ID  c7su63uw7nch6, child number 2
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T1 WHERE   C2 =
    :N1
     
    Plan hash value: 236868917
     
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       3 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   5957 |      0 |00:00:00.01 |       3 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |      1 |   5957 |      0 |00:00:00.01 |       3 |
    ---------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C2"=:N1)
    Note in the above that the CHILD_NUMBER is now 2.

    Continuing:
    EXEC :N1:=99
    SET TIMING ON
      
    SELECT /*+ GATHER_PLAN_STATISTICS */
      C1,
      C2
    FROM
      T1
    WHERE
      C2 = :N1;
     
    10000 rows selected.
    
    Elapsed: 00:00:05.31
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    SQL_ID  c7su63uw7nch6, child number 2
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T1 WHERE   C2 =
    :N1
    
    Plan hash value: 236868917
    
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |      1 |        |  10000 |00:00:00.02 |    1783 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   5957 |  10000 |00:00:00.02 |    1783 |
    |*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |      1 |   5957 |  10000 |00:00:00.01 |     690 |
    ---------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C2"=:N1)
    The CHILD_NUMBER is still 2.

    Continuing:
    EXEC :N1:=1
     
    SET AUTOTRACE TRACEONLY STATISTICS
      
    SELECT /*+ GATHER_PLAN_STATISTICS */
      C1,
      C2
    FROM
      T1
    WHERE
      C2 = :N1;
      
    990000 rows selected.
     
    Elapsed: 00:00:16.91
     
    Statistics
    ---------------------------------------------------
              0  recursive calls
              0  db block gets
         175927  consistent gets
              0  physical reads
              0  redo size
       21958348  bytes sent via SQL*Net to client
         726508  bytes received via SQL*Net from client
          66001  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         990000  rows processed
      
    SET AUTOTRACE OFF
     
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('c7su63uw7nch6',2,'ALLSTATS LAST'));
     
    SQL_ID  c7su63uw7nch6, child number 2
    -------------------------------------
    SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T1 WHERE   C2 =
    :N1
     
    Plan hash value: 236868917
     
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |      1 |        |    990K|00:00:01.63 |     175K|
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |   5957 |    990K|00:00:01.63 |     175K|
    |*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |      1 |   5957 |    990K|00:00:00.68 |   67932 |
    ---------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C2"=:N1)
    The above is the execution plan for CHILD_NUMBER 2 - notice that this time it is reporting 990,000 rows retrieved, so this is the execution plan that was used - adaptive cursor sharing did not take effect and force the re-evaluation of the execution plan - the execution plan was NOT changed to a full table scan. That is the risk that you take if you allow histograms to exist on columns that have unequal distributions of values and bind variables are used in the WHERE clause that references the column.

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 6. Re: Histograms and bind variables
    832273 Newbie
    Currently Being Moderated
    Thanks a lot Charles.Your help me to figure this out and clearly described the difference between 11 and 10.

    My database version is 10.2.0.5.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points