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