Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>sqlplus tt/tt
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 9月 30 16:16:24 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
tt_143@ORCL> drop table big_table;
表已删除。
tt_143@ORCL> create table big_table as select * from all_objects;
表已创建。
tt_143@ORCL> create index idx_big_table on big_table(object_id);
索引已创建。
tt_143@ORCL> exec dbms_stats.gather_table_stats(user,'big_table',cascade=>true);
PL/SQL 过程已成功完成。
tt_143@ORCL> set autotrace on exp
tt_143@ORCL> select count(*) from big_table;
COUNT(*)
----------
52578
执行计划
----------------------------------------------------------
Plan hash value: 3793668374
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 63 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_BIG_TABLE | 52578 | 63 (5)| 00:00:01 |
-------------------------------------------------------------------------------
tt_143@ORCL> exec dbms_stats.set_table_stats(user,tabname => 'big_table',numrows => 1,numblks =>10);
PL/SQL 过程已成功完成。
tt_143@ORCL> select count(*) from big_table;
COUNT(*)
----------
52578
执行计划
----------------------------------------------------------
Plan hash value: 599409829
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 1 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------
tt_143@ORCL> --【注释】这里说明,统计信息的变化引起了执行计划的改变
tt_143@ORCL> set autotrace off
tt_143@ORCL> alter system flush shared_pool;
系统已更改。
tt_143@ORCL> exec dbms_stats.delete_table_stats(user,'big_table',cascade_indexes => true);
PL/SQL 过程已成功完成。
tt_143@ORCL> exec dbms_stats.gather_table_stats(user,tabname => 'big_table',cascade=>true);
PL/SQL 过程已成功完成。
tt_143@ORCL> select count(*) from big_table;
COUNT(*)
----------
52578
tt_143@ORCL> exec dbms_stats.set_table_stats(user,tabname => 'big_table',numrows => 1,numblks =>10);
PL/SQL 过程已成功完成。
tt_143@ORCL> select count(*) from big_table;
COUNT(*)
----------
52578
tt_143@ORCL> select s.SQL_TEXT, s.HASH_VALUE, s.CHILD_NUMBER, s.FETCHES, s.EXECUTIONS
2 from v$sql s
3 where s.SQL_TEXT like '%select %count(*) from big_table%'
4 and s.SQL_TEXT not like '%v$%';
SQL_TEXT HASH_VALUE CHILD_NUMBER FETCHES EXECUTIONS
------------------------ --------- ------------ ---------- ----------
select count(*) from big_table 1326693165 0 2 2
【问题】通过前半部分脚本的验证,改变统计信息确实该变了执行计划,为什么v$sql中还是共享了相同的子游标?求解惑。