发表于: 2008.04.17 12:13
分类: 性能优化
出处: http://warehouse.itpub.net/post/777/460038
---------------------------------------------------------------
可以通过dbms_stats提供的set_table_stats来修改表的统计信息从而使cbo产生我们希望的执行计划！
SQL> edit
已写入 file afiedt.buf
1 DECLARE
2 num_rows NUMBER;
3 num_blocks NUMBER;
4 avg_row_len NUMBER;
5 BEGIN
6 -- retrieve the values of table statistics on OE.ORDERS
7 -- statistics table name: OE.SAVESTATS statistics ID: TEST1
8 DBMS_STATS.GET_TABLE_STATS('XYS','TT',null,
9 'MY_STAT_TAB','TEST_20080416',
10 num_rows,num_blocks,avg_row_len);
11 -- print the values
12 DBMS_OUTPUT.PUT_LINE('num_rows='||num_rows||',num_blocks='||num_blocks||
13 ',avg_row_len='||avg_row_len);
14* END;
15 /
num_rows=5,num_blocks=5,avg_row_len=7
PL/SQL 过程已成功完成。
SQL> EDIT
已写入 file afiedt.buf
1 DECLARE
2 num_rows NUMBER :=100;
3 num_blocks NUMBER :=200;
4 avg_row_len NUMBER :=300;
5 BEGIN
6 -- retrieve the values of table statistics on OE.ORDERS
7 -- statistics table name: OE.SAVESTATS statistics ID: TEST1
8 DBMS_STATS.SET_TABLE_STATS('XYS','TT',null,
9 'MY_STAT_TAB','TEST_20080416',
10 num_rows,num_blocks,avg_row_len);
11 -- print the values
12 DBMS_OUTPUT.PUT_LINE('num_rows='||num_rows||',num_blocks='||num_blocks||
13 ',avg_row_len='||avg_row_len);
14* END;
SQL> /
num_rows=100,num_blocks=200,avg_row_len=300
PL/SQL 过程已成功完成。
SQL> DECLARE
2 num_rows NUMBER;
3 num_blocks NUMBER;
4 avg_row_len NUMBER;
5 BEGIN
6 -- retrieve the values of table statistics on OE.ORDERS
7 -- statistics table name: OE.SAVESTATS statistics ID: TEST1
8 DBMS_STATS.GET_TABLE_STATS('XYS','TT',null,
9 'MY_STAT_TAB','TEST_20080416',
10 num_rows,num_blocks,avg_row_len);
11 -- print the values
12 DBMS_OUTPUT.PUT_LINE('num_rows='||num_rows||',num_blocks='||num_blocks||
13 ',avg_row_len='||avg_row_len);
14 END;
15 /
num_rows=100,num_blocks=200,avg_row_len=300
PL/SQL 过程已成功完成。
SQL>
