1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

A dip into Oracle Performance. Bind Peeking

Discussion in 'Server Administration and Options' started by simply_dba, Oct 14, 2008.

  1. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    It is universally accepted that using bind varibles in sql/plsql queries reduces hard parsing thereby optimising the code and improving the query response time. So it is a very bad habit to use literals on where clauses and it pays to use bind variables in these.

    But not always.

    There are situations where using bind variables kills the performance of the query to a very large extent. This happens if you have very skewed distribution of data in one or more of the columns you are using in your query.The problem is that whenever the sql is executed for the first time, the appropiate plan is formed by replacing the bind variable with value and subsequently the same plan is used for any other values. This is known as bind peeking.In presence of high skewness of data, bind peeking may be disastrous.

    Let me explain this with help of examples


    Let us first create a table with highly skewed data in one of the columns.
    Code (Text):
    SQL> create table skewed_table as select 'M' sex,object_name from dba_objects;

    Table created.

    SQL> insert into skewed_table select * from skewed_table;

    32662 rows created.

    SQL> r
      1* insert into skewed_table select * from skewed_table

    65324 rows created.

    SQL> r
      1* insert into skewed_table select * from skewed_table

    130648 rows created.

    SQL> commit;

    Commit complete.

    SQL> insert into skewed_table select 'F',object_name from dba_objects where rownum <11;

    10 rows created.

    SQL> commit;

    Commit complete.

    SQL> select sex,count(*) from skewed_table group by sex;

    S   COUNT(*)
    - ----------
    F         10
    M     261296
     
    Ok. Now let us create an index on the column sex.

    Code (Text):
    SQL> create index skewed_col_ind on skewed_table(sex);

    Index created.
    And analyze the table and index
    Code (Text):

    SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'SKEWED_TABLE',cascade=>true,method_opt=>'for all indexed columns size 2');

    PL/SQL procedure successfully completed.
     
    It can be easily observed from here that if we query with column sex= 'M', a full table scan will be better, while if we query with sex='F', the use of index will give us better response time.
    But what happens exactly ? Let us see.
    First, run the query with value of the bind variable as 'M';

    Code (Text):
    SQL> set autot trace exp stat

    SQL> variable b1 varchar2(1);
    SQL> exec :b1:='M';

    PL/SQL procedure successfully completed.

    SQL> select * from skewed_table where sex=:b1;

    261296 rows selected.


    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=111 Card=130653 Byte
              s=3266325)

       1    0   TABLE ACCESS (FULL) OF 'SKEWED_TABLE' (Cost=111 Card=13065
              3 Bytes=3266325)





    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
          36356  consistent gets
              0  physical reads
              0  redo size
       10676018  bytes sent via SQL*Net to client
         192264  bytes received via SQL*Net from client
          17421  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
         261296  rows processed
     
    So far so good. With the bind variable value as 'M' the query should go for a full table scan.
    Now let us run the query again with the value as 'F'. Here we would expect the query to go for a index range scan.But .....

    Code (Text):
    SQL> exec :b1:='F';

    PL/SQL procedure successfully completed.

    SQL> select * from skewed_table where sex=:b1;

    10 rows selected.


    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=111 Card=130653 Byte
              s=3266325)

       1    0   TABLE ACCESS (FULL) OF 'SKEWED_TABLE' (Cost=111 Card=13065
              3 Bytes=3266325)





    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              5  consistent gets
              0  physical reads
              0  redo size
            950  bytes sent via SQL*Net to client
            655  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             10  rows processed
     

    We can see very well here that for the value 'F' also it went for a Full table scan.
    Let us now see what happens when we use literel instead of bind values.

    Code (Text):
    SQL> select * from skewed_table where sex='F';

    10 rows selected.


    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=250)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SKEWED_TABLE' (Cost=2 Ca
              rd=10 Bytes=250)

       2    1     INDEX (RANGE SCAN) OF 'SKEWED_COL_IND' (NON-UNIQUE) (Cos
              t=1 Card=10)





    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              5  consistent gets
              0  physical reads
              0  redo size
            950  bytes sent via SQL*Net to client
            655  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             10  rows processed
     
    See ! Now it uses the index.

    Moral of the story ?
    This case study was not meant to say that you should not be using bind variables at all.That is not true. Generally, using bind variables gives you immense advantage over using literels.This case study was conducted to suggest that we should not have blind faith over any performance measures, however true it may sound,that no universal rules can be defined when it comes to oracle performance. The only rule that is applicable is that you should have a profund knowledge of the application and it's data distribution you are tuning for.
    That's it

    Cheers
    :D
     
  2. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Good example of skewing. However in the example there possibly copy paste problem in the last two autotrace statistics.