1.演示一个表分析后执行计划比动态采样更准确的例子。
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> set autotrace on;
SQL> set linesize 200
SQL> select count(*) from t;
COUNT(*)
----------
400882
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1572 (1)| 00:00:19 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 345K| 1572 (1)| 00:00:19 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
5790 consistent gets
5655 physical reads
0 redo size
337 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> set autotrace on;
SQL> select count(*) from t;
COUNT(*)
----------
400882
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1573 (1)| 00:00:19 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 400K| 1573 (1)| 00:00:19 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5694 consistent gets
0 physical reads
0 redo size
337 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--EOF--
2.演示在缺乏直方图时，CBO计算出错误的执行计划的例子，并给出最后正确的执行计划。
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects where rownum < 101;
Table created.
SQL> update t set object_id=99;
100 rows updated.
SQL> update t set object_id=1 where rownum = 1;
1 row updated.
SQL> select object_id,count(*) from t group by object_id;
OBJECT_ID COUNT(*)
---------- ----------
1 1
99 99
SQL> create index idx_t_objid on t(object_id);
Index created.
SQL> exec DBMS_STATS.GATHER_table_STATS ( user, 'T', cascade=>true,METHOD_OPT => 'FOR all COLUMNS SIZE 1' );
PL/SQL procedure successfully completed.
SQL> select * from t where object_id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3638283050
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3750 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 50 | 3750 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJID | 50 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1193 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from t where object_id = 99; -- 不准确估计cardinality, 选择了错误的执行计划
99 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3638283050
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3750 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 50 | 3750 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJID | 50 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=99)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
4255 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
SQL> exec DBMS_STATS.GATHER_table_STATS ( user, 'T', cascade=>true,METHOD_OPT => 'FOR all COLUMNS SIZE 254' );
PL/SQL procedure successfully completed.
SQL> select * from t where object_id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3638283050
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 75 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1193 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from t where object_id = 99; -- 准确估计cardinality, 选择了正确的执行计划
99 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 7425 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 99 | 7425 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=99)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
4255 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
SQL>
--EOF--
3.演示在分区表上，全局信息和分区信息是如何影响执行计划的，给出演示过程。
SQL> drop table part1 purge;
Table dropped.
SQL> CREATE TABLE part1
2 (
3 object_id INT
4 ,object_name VARCHAR2 (50)
5 )
6 PARTITION BY RANGE (object_id)
7 (PARTITION p1
8 VALUES LESS THAN (1000)
9 ,PARTITION p2
10 VALUES LESS THAN (2000));
Table created.
SQL> insert into part1 select object_id,object_name from dba_objects where object_id < 1000;
958 rows created.
SQL> EXEC dbms_stats.gather_table_stats(user,'PART1',granularity=> 'GLOBAL');
--如果没有表上ＧＬＯＢＡＬ信息，单独收集分区信息也会更新表的统计信息，为方便实验这里先单独收集表的统计信息
PL/SQL procedure successfully completed.
SQL> EXEC dbms_stats.gather_table_stats(user,'PART1',granularity=> 'PARTITION');
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks,global_stats from user_tables where table_name='PART1';
NUM_ROWS BLOCKS GLO
---------- ---------- ---
958 13 YES
SQL> select partition_name, num_rows,blocks,global_stats from user_tab_partitions where table_name='PART1';
PARTITION_NAME NUM_ROWS BLOCKS GLO
------------------------------ ---------- ---------- ---
P2 0 0 YES
P1 958 13 YES
SQL> commit;
Commit complete.
SQL> insert into part1 select object_id,object_name from dba_objects where object_id > 1000 and object_id < 2000;
984 rows created.
SQL> EXEC dbms_stats.gather_table_stats(user,'PART1',granularity=> 'PARTITION');
--当表已有GLOBAL信息后，单独收集分区信息不会更新表的统计信息
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select num_rows,blocks,global_stats from user_tables where table_name='PART1';
NUM_ROWS BLOCKS GLO
---------- ---------- ---
958 13 YES
SQL> select partition_name, num_rows,blocks,global_stats from user_tab_partitions where table_name='PART1';
PARTITION_NAME NUM_ROWS BLOCKS GLO
------------------------------ ---------- ---------- ---
P2 984 13 YES
P1 958 13 YES
SQL> set autotrace on;
SQL> select count(*) from part1 where object_id < 1000;
COUNT(*)
----------
958
Execution Plan
----------------------------------------------------------
Plan hash value: 1660407118
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | | | |
| 2 | PARTITION RANGE SINGLE| | 958 | 3832 | 5 (0)| 00:00:01 | 1 | 1 |
| 3 | TABLE ACCESS FULL | PART1 | 958 | 3832 | 5 (0)| 00:00:01 | 1 | 1 |
-------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
336 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from part1 where object_id < 2000; --进行跨分区查询时会错估cardinality
COUNT(*)
----------
1942
Execution Plan
----------------------------------------------------------
Plan hash value: 1564279961
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | | | |
| 2 | PARTITION RANGE ALL| | 958 | 3832 | 5 (0)| 00:00:01 | 1 | 2 |
| 3 | TABLE ACCESS FULL | PART1 | 958 | 3832 | 5 (0)| 00:00:01 | 1 | 2 |
----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
336 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--EOF--
4.演示用extended statistics 解决列相关性的例子，给出演示过程。
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects where rownum<101;
Table created.
SQL> update t set object_id=1 where rownum<51;
50 rows updated.
SQL> update t set object_type='TABLE' where object_id=1;
50 rows updated.
SQL>
SQL> update t set object_id =2,object_type='INDEX' where object_id!=1;
50 rows updated.
SQL> commit;
Commit complete.
SQL> select count(*) from t where object_id=1 and object_type='TABLE';
COUNT(*)
----------
50
SQL> select count(*) from t where object_id=2 and object_type='INDEX';
COUNT(*)
----------
50
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> select * from user_stat_extensions where table_name='T';
no rows selected
SQL> set autotrace on
SQL> select count(*) from t where object_id=1 and object_type='TABLE';
--没有统计extended statistics时，ＣＢＯ不能根据ｗｈｅｒｅ条件中列的相关性正确估计cardinality
COUNT(*)
----------
50
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| T | 25 | 225 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from t where object_id=2 and object_type='TABLE';
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| T | 25 | 225 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=2 AND "OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
334 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'FOR COLUMNS (object_id, object_type)'); --收集extended statistics
PL/SQL procedure successfully completed.
SQL> set autotrace off
SQL> select * from user_stat_extensions where table_name='T';
TABLE_NAME EXTENSION_NAME EXTENSION CREATO DRO
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------ ---
T SYS_STU9Z8DHHOQ14VIABSHPZPKG2G ("OBJECT_ID","OBJECT_TYPE") USER YES
SQL> set autotrace on
SQL> select count(*) from t where object_id=1 and object_type='TABLE';
--有了extended statistics信息后，对这条ｓｑｌ的cardinality估计是正确了( 但是对下面那条ｓｑｌ的cardinality估计还是错误的，为什么? )
COUNT(*)
----------
50
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| T | 50 | 450 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=1)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from t where object_id=2 and object_type='TABLE';
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| T | 25 | 225 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=2 AND "OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
334 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--EOF--
5.对一张表进行导出/导入，看它的统计信息是否发生改变，给出演示过程。
在APPS下准备导出表Ｔ：
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> select num_rows,blocks,avg_row_len,last_analyzed from user_tables where table_name = 'T';
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- ------------------
400885 5754 97 20-NOV-13
SQL> insert into t select * from t;
400885 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
801770
SQL> select num_rows,blocks,avg_row_len,last_analyzed from user_tables where table_name = 'T';
--因为我们在收集完统计信息后又插入了一遍Ｔ表，所以这里的统计信息不是最新的
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- ------------------
400885 5754 97 20-NOV-13
SQL> exit
导出Ｔ表：
-bash-3.2$ exp apps/apps@tm12 file=t.dmp tables=T
Export: Release 10.1.0.5.0 - Production on Wed Nov 20 18:37:04 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T 801770 rows exported
Export terminated successfully without warnings.
-bash-3.2$ ls -l t.dmp
-rw-r--r-- 1 aptm12 dba 91611136 Nov 20 18:37 t.dmp
-bash-3.2$
接下来，准备把Ｔ表导入到ｓｃｏｔｔ下：
-bash-3.2$ sqlplus scott/tiger
SQL*Plus: Release 10.1.0.5.0 - Production on Wed Nov 20 18:40:18 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select num_rows,blocks,avg_row_len,last_analyzed from dba_tables where table_name = 'T' and owner='SCOTT';
no rows selected
SQL> exit
导入Ｔ表到ｓｃｏｔｔ下：
-bash-3.2$ imp userid=scott/tiger tables=T file=t.dmp
Import: Release 10.1.0.5.0 - Production on Wed Nov 20 18:44:08 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.01.00 via conventional path
Warning: the objects were exported by APPS, not by you
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing APPS's objects into SCOTT
. . importing table "T" 801770 rows imported
Import terminated successfully without warnings.
-bash-3.2$ sqlplus scott/tiger
SQL*Plus: Release 10.1.0.5.0 - Production on Wed Nov 20 18:44:26 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select num_rows,blocks,avg_row_len,last_analyzed from dba_tables where table_name = 'T' and owner='SCOTT';
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- ------------------
400885 5754 97 20-NOV-13
SQL> select owner,num_rows,blocks,avg_row_len,last_analyzed from dba_tables where table_name = 'T' ;
OWNER NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------------------------ ---------- ---------- ----------- ------------------
SCOTT 400885 5754 97 20-NOV-13
APPS 400885 5754 97 20-NOV-13
SQL>
--EOF--
803
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
