Playing with the Optimizer Statistics.

Testing query performance in the development environment can be challenging  at times. In the dev environment , you may / will not have all the records in the table to simulate the production scenario. 

Ideally , performance tuning has to start from the inception of the development ; not as afterthought. 

The easier way is  that to tell / fake  the optimizer that you have more records  and experiment with the execution plans.  

ORACLE
======

In Oracle , you can do this by using "set_table_stats" procedure in dbms_stats. 

Let us create a table "t" with 10 records and look at the plan to make sure it gets 10 records. 



SQL> drop table t ;

Table dropped.

SQL> Create table t as select * from all_objects where rownum <= 10 ;

Table created.

SQL> select count(*) from t ;

  COUNT(*)
----------
        10

SQL> exec dbms_stats.gather_table_stats(user , 'T') ;

PL/SQL procedure successfully completed.

SQL>  explain plan for select * from t;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |  1000 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |    10 |  1000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Now , let us tell the optmizer that this table has 9876540 records and look at the plan . 
As shown below , the estimated records is 9876K. 


SQL> exec dbms_stats.set_table_stats(ownname =>user , tabname => 'T' , numrows=>9876540) ;

PL/SQL procedure successfully completed.

SQL> explain plan for select * from t;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9876K|   941M|   129  (98)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |  9876K|   941M|   129  (98)| 00:00:01 |
--------------------------------------------------------------------------

To set it back to the original , we can gather table statistics by using dbms_stats.gather_table_stats


SQL SERVER
==========


In SQL Server , we can use 'update statistics'  with rowcount / page count. 

Let us create a table with 10 records and get the plan. 


C:\>sqlcmd -W
1> use testdb
2> go
Changed database context to 'testdb'.
1> Drop table t;
2> go
1> Select top 10 * into t from INFORMATION_SCHEMA.TABLES ;
2> go

(10 rows affected)



Here is the execution plan for the newly created table. As you can see the from the plan , the optimizer thinks that table has 10 records. 

Now , let us fake the record count with the command below and look at the execution plan again . 

1> update statistics t

2> with rowcount = 9876540 , pagecount = 587456;

3> go

To set it back to the original record count , we can run the following . 

1> dbcc updateusage(testdb , 't' ) with count_rows ;

2> go

DBCC UPDATEUSAGE: Usage counts updated for table 't' (index 't', partition 1):

        DATA pages (In-row Data): changed from (587456) to (2) pages.

        ROWS count: changed from (9876540) to (10) rows.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Comments welcome.