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.