恢复旧的统计信息步骤

http://uhesse.wordpress.com/2010/05/18/restoring-old-optimizer-statistics-for-troubleshooting-purpose/

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> grant dba to adam identified by adam;
Grant succeeded.

SQL> connect adam/adam
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<=3e7;
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                           942
SALES_IDX                       566

SQL> select num_rows,blocks from user_tables;

 NUM_ROWS     BLOCKS
---------- ----------
"NULL values here"

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

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables;

 NUM_ROWS     BLOCKS
---------- ----------
 29933962     119585

SQL> select count(*) from sales;

 COUNT(*)
----------
 30000000

SQL> set timing on
SQL> select amount_sold from sales where id=4711;

AMOUNT_SOLD
-----------
 5000

Elapsed: 00:00:00.00

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

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables;

 NUM_ROWS     BLOCKS
---------- ----------
 100          1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/354732/viewspace-665822/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-665822/