How Does an execution plan can suddenly change in Oracle for Statistics

Sometimes DBA’s or developers might wonder why suddenly an execution plan change where nothing  has been change in database only  the data has grown. Here I have showed a very simple example,  why not refreshing statistics has caused the CBO to suddenly change an execution plan for no apparent reason.

 

 SQL>create table test_plan (id number, test_plan_date date, name varchar2(10));

Table created.

 

SQL>

declare

    v_count  number;

    begin

    v_count:=0;

    for i in 1..1830 loop

       for j in 1..1000 loop

       v_count:= v_count+1;

       insert into test_plan values (v_count, sysdate-i, 'test_plan');

       end loop;

   end loop;

   commit;

   end;

   /

PL/SQL procedure successfully completed.

 

SQL> create index test_plan_i on test_plan(test_plan_date);

  Index created.

 

SQL> exec dbms_stats.gather_table_stats(ownname=>'TMP_NAZMUL', tabname=>'test_plan', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

 

PL/SQL procedure successfully completed.

  

Now the table have data with latest statistics. Lets check the execution plan.

 

SQL>EXPLAIN PLAN FOR select * from test_plan where test_plan_date > sysdate - 365;

Explained.

 

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2068845065

 -------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |   364K|  8180K|  2050   (4)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_PLAN |   364K|  8180K|  2050   (4)| 00:00:01 |

-------------------------------------------------------------------------------

 Predicate Information (identified by operation id):

---------------------------------------------------

 PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

    1 - filter("TEST_PLAN_DATE">SYSDATE@!-365)

 

Note

-----

   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel thre

shold

 17 rows selected.

 

 

Now let’s add another year of data into the table to simulate how the data in the table will be grown in a year’s of time

 

SQL>

declare

    v_count  number;

    begin

    v_count:=1830000;

    for i in 1..365 loop

       for j in 1..1000 loop

       v_count:= v_count+1;

       insert into TEST_PLAN values (v_count, sysdate+i, 'TEST_PLAN');

       end loop;

   end loop;

   commit;

   end;

   /

 

PL/SQL procedure successfully completed.

 

  We have not collect any statistics, let’s check the execution plan of same query.  In a year’s time, we will be 365 days past the current sysdate. So, to mimic the identical query I have added 365 days to the sysdate.   

  

SQL> EXPLAIN PLAN FOR select * from test_plan where test_plan_date > (sysdate+365) - 365;

 Explained.

 

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2963293120

 ---------------------------------------------------------------------------------------------------

 | Id  | Operation                           | Name        | Rows  | Bytes | Cost

 (%CPU)| Time     |

 ---------------------------------------------------------------------------------------------------

 PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |             |   968 | 22264 |    1

0   (0)| 00:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_PLAN   |   968 | 22264 |    1

0   (0)| 00:00:01 |

 

|*  2 |   INDEX RANGE SCAN                  | TEST_PLAN_I |   968 |       |

5   (0)| 00:00:01 |

 ---------------------------------------------------------------------------------------------------

 PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 Predicate Information (identified by operation id):

---------------------------------------------------

    2 - access("TEST_PLAN_DATE">SYSDATE@!+365-365)

 Note

-----

   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 18 rows selected.

 

So, you can see the execution plan has been changed for the identical query. Now let’s gathered the statistics and check the execution plan again.

  

SQL> exec dbms_stats.gather_table_stats(ownname=>'TMP_NAZMUL', tabname=>'test_plan', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

 PL/SQL procedure successfully completed.

 

SQL> EXPLAIN PLAN FOR select * from test_plan where test_plan_date > (sysdate+365) - 365;

 Explained.

 

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

 PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2068845065

 -------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |   364K|  8554K|  2569   (5)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_PLAN |   364K|  8554K|  2569   (5)| 00:00:01 |

-------------------------------------------------------------------------------

 Predicate Information (identified by operation id):

---------------------------------------------------

 PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

    1 - filter("TEST_PLAN_DATE">SYSDATE@!+365-365)

 Note

-----

   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel thre

shold

 

17 rows selected.

 

 

After gathering the statistics, the execution plan for the identical query change and now the execution plan same as earlier. So, we can see from the above example an execution plan change if the data volume change in the table and if the statistics is not gathered.  

Comments