GATHER_TABLE_STATS and Execution plan in oracle

Question

Does running execution plan without gathering table statistics(using dbms_stat.gather_table_stat) and Gathering table statistics and then running execution plan will make any changes in execution plan.Can somebody please explain on gathering table statistics and how execution plan dependence on gathering statistics.

Answer 1

Execution plan is based on statistics. If you know it and example below isn't useful for you please say about it and I'll delete this post. Thanks. As example I create two tables:

create table ag_test1 (id1 number, val varchar2(50));
alter table ag_test1 add constraint pk_ag_test1 primary key (id1);
create table ag_test2 (id2 number, val varchar2(50));
alter table ag_test2 add constraint pk_ag_test2 primary key (id2);

After then I gather statistic for this tables:

begin
  dbms_stats.gather_table_stats(ownname => user,tabname => 'AG_TEST1', cascade =>true);
  dbms_stats.gather_table_stats(ownname => user,tabname => 'AG_TEST2', cascade =>true);
end;

And run query:

select /* test_query_1 */
       t1.val as tbl1, t2.val as tbl2
from ag_test1 t1
join ag_test2 t2 on (t1.id1 = t2.id2);

look on execution plan (as you remember tables contain 0 rows):

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS                |             |     1 |    80 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |     1 |    80 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | AG_TEST1    |     1 |    40 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_AG_TEST2 |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| AG_TEST2    |     1 |    40 |     0   (0)|          |
--------------------------------------------------------------------------------------------

Let's fill this table (1M rows into each table):

insert all
when 1=1 then 
  into ag_test1 (id1, val)
  values (lvl, val1)
when 1=1 then 
  into ag_test2 (id2, val)
  values (lvl, val2)
select level as lvl, 'TBL1_VAL_'||level as val1, 'TBL2_VAL_'||level as val2
from dual
connect by level <=1000000;
commit;

Run query again and look :

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |     2 (100)|          |
|   1 |  NESTED LOOPS                |             |     1 |    80 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |     1 |    80 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | AG_TEST1    |     1 |    40 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_AG_TEST2 |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| AG_TEST2    |     1 |    40 |     0   (0)|          |
--------------------------------------------------------------------------------------------

Nothing was changed. Execution plan for 1M row is equal 0 rows. Because Oracle use soft parse(find and use previous plan). Let's gather statistic with no_invalidate clause (no_invalidate => false):

begin
  dbms_stats.gather_table_stats(ownname => user,tabname => 'AG_TEST1', cascade =>true, no_invalidate => false);
  dbms_stats.gather_table_stats(ownname => user,tabname => 'AG_TEST2', cascade =>true, no_invalidate => false);
end;

And look on execution plan:

---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |       |  5120 (100)|          |
|*  1 |  HASH JOIN         |          |  1000K|    40M|    31M|  5120   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| AG_TEST1 |  1000K|    20M|       |   995   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| AG_TEST2 |  1000K|    20M|       |   995   (1)| 00:00:01 |
---------------------------------------------------------------------------------------