How statistics affect optimizer

Statistics are EXACT measurements of the data itself,how large a table is and how useful an index
is.Remember out dated statistics cause optimizer to not functioning realistically.Out-of-date
statistics would have the same effect on all types of databases.

statistics contain number of rows in table,blocks used by table,empty blocks (i.e HWM),average
row length,indexes leaf blocks its blevel,number of buckets (i.e histogram).

i made a little observation regarding histograms stats that how statisitics affect optimizer.

SQL> create table t as select rownum rn,'A' status from all_objects
  2  /

Table created.

SQL> select count(*)
  2    from t
  3   group by status
  4  /

  COUNT(*)
----------
     38959

SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      38959

SQL> update t set status='B'
  2   where rn>=19479
  3  /

19481 rows updated.


SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      19478
B      19481

SQL> update t set status='C' where rn>=29218
  2  /

9742 rows updated.

SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      19478
B       9739
C       9742

SQL> commit
  2  /

Commit complete.


SQL> create index t_status_ndx on t (status)
  2  /

Index created.



SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      19478
B       9739
C       9742

SQL> delete plan_table
  2  /

2 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> exec dbms_stats.delete_table_stats('SCOTT','T')

PL/SQL procedure successfully completed.

SQL> explain plan for
  2   select *
  3     from t
  4    where status='A'
  5  /

Explained.

SQL> select * from table(dbms_xplan.display())
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 749696591

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 21272 |   332K|    19  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 21272 |   332K|    19  (11)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("STATUS"='A')

Note
-----
  - dynamic sampling used for this statement<-- Dynamic sampling reads 
                                                       a small number of 
                                                       number of blocks in a  
                                                       table to make a best 
                                                       guess at statistics.


17 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T')

PL/SQL procedure successfully completed.

SQL> delete plan_table
  2  /

2 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> explain plan for
  2   select *
  3     from t
  4    where status='A'
  5  /

Explained.

SQL> select * from table(dbms_xplan.display())
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 749696591

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 19257 |   112K|    19  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 19257 |   112K|    19  (11)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("STATUS"='A')

13 rows selected.

SQL> delete plan_table
  2  /

2 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> exec dbms_stats.gather_index_stats('SCOTT','T_STATUS_NDX')

PL/SQL procedure successfully completed.

SQL> explain plan for
  2   select *
  3     from t
  4    where status='A'
  5  /

Explained.

SQL> select * from table(dbms_xplan.display())
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 749696591

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 19257 |   112K|    19  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 19257 |   112K|    19  (11)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("STATUS"='A')

13 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T',method_opt=>'for all columns size auto')

PL/SQL procedure successfully completed.

SQL> delete plan_table
  2  /

2 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> explain plan for
  2   select *
  3     from t
  4    where status='A'
  5  /

Explained.

SQL> select * from table(dbms_xplan.display())
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 749696591

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 19478 |   114K|    19  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 19478 |   114K|    19  (11)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("STATUS"='A')

13 rows selected.


You can see 19478 is an exact row in table for status 'A'

SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      19478
B       9739
C       9742