Statistics Optimizer

SQL> grant dba to home identified by home;

 Grant succeeded.

 

SQL> conn home/home

Connected.

 

SQL> create table sales as select

           rownum as id,

           mod(rownum,5) as channel_id,

           mod(rownum,1000) as cust_id,

          5000 as amount_sold,

          sysdate as time_id

          from dual connect by level<=1e7;

 Table created.

 

SQL> create index sales_idx on sales(id) nologging;

 Index created.

 

SQL> select segment_name,bytes/1024/1024 as mb from user_segments;

 SEGMENT_NAME                                                              MB

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

SALES                                                                                  318

SALES_IDX                                                                           192

 

SQL> select num_rows,blocks from user_tables;

   NUM_ROWS     BLOCKS

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

 

SQL> select table_name,stats_update_time from user_tab_stats_history;

 no rows selected

 

SQL> exec dbms_stats.gather_table_stats('HOME','SALES')

 PL/SQL procedure successfully completed.

 

SQL> select table_name,stats_update_time from user_tab_stats_history;

 TABLE_NAME                     STATS_UPDATE_TIME

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

SALES                          29-JUN-10 01.25.31.921000    PM +06:00

SQL> select num_rows,blocks from user_tables;

   NUM_ROWS                     BLOCKS

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

   9984114                                                39839

 

SQL> select count(*) from sales;

   COUNT(*)

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

  10000000

 

SQL> set timing on

SQL> select amount_sold from sales where id=999999;

 AMOUNT_SOLD

-----------

       5000

 

Elapsed: 00:00:00.21

 

SQL> explain plan

           for

           select amount_sold from sales where id=999999;

 Explained.

 

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxpls.sql

 

SQL> exec dbms_stats.set_table_stats('HOME','SALES',numrows=>100,numblks=>1)

 PL/SQL procedure successfully completed.

 

SQL> select num_rows,blocks from user_tables;

   NUM_ROWS     BLOCKS

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

       100                                                    1

Elapsed: 00:00:00.78

 

SQL> select amount_sold from sales where id=999999;

 AMOUNT_SOLD

-----------

       5000

 

Elapsed: 00:00:08.57

 

SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='yyyy-mm-dd:hh24:mi:ss';

Session altered.

 

SQL> explain plan

           for

           select amount_sold from sales where id=999999;


Explained.

 

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxpls.sql


SQL>  select table_name,stats_update_time from user_tab_stats_history;

TABLE_NAME                     STATS_UPDATE_TIME

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

SALES                          2010-06-29:13:25:31

SALES                          2010-06-29:13:40:50

 

SQL> begin

           dbms_stats.restore_table_stats('HOME','SALES',

            to_timestamp('2010-06-29:13:25:31','yyyy-mm-dd:hh24:mi:ss'));

           end;

            /

 

PL/SQL procedure successfully completed.

 

 

SQL> select amount_sold from sales where id=999999;

AMOUNT_SOLD

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

       5000

 

Elapsed: 00:00:00.12

Comments