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 |