Oracle11G多列收集统计信息实验

发布时间：2020-12-12 15:28:14 所属栏目：百科来源：网络整理

导读：在上一篇文章中解决了Oracle11g 多列收集统计信息--直方图的问题，现在来做一个实验验证，这是Oracle 11g的新特性。 SQL select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Databa

在上一篇文章中解决了Oracle11g 多列收集统计信息--直方图的问题，现在来做一个实验验证，这是Oracle 11g的新特性。

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> drop table test purge;

SQL> create table test as select * from dba_objects;
SQL> create index ind_tt_oo on test(owner,object_type);
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);
SQL> select owner,object_type,count(1)
from test
where owner = 'SYS'
and object_type = 'CLUSTER'
group by owner,object_type;
OWNER OBJECT_TYPE COUNT(1)
------- -------------- ----------

SYS CLUSTER 10

--这种组合数据有10条

SQL> set autotrace traceonly
SQL> select * from test where owner='SYS' and object_type='CLUSTER';
执行计划
----------------------------------------------------------
Plan hash value: 3811756094
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 298 | 28906 | 15 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 298 | 28906 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TT_OO | 298 | | 2 (0)| 00:00:01 |--而评估出来是298条
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='CLUSTER')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
2450 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

SQL> set autotrace off
SQL> declare
v_name varchar2(30);
begin
v_name := dbms_stats.create_extended_stats(user,'(owner,object_type)');
end;
/
SQL> set autotrace traceonly
SQL> exec dbms_stats.gather_table_stats(user,cascade => true,method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (owner,object_type)',no_invalidate=>FALSE);
SQL> select * from test where owner='SYS' and object_type='CLUSTER';
执行计划
----------------------------------------------------------
Plan hash value: 3811756094
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1417 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 13 | 1417 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TT_OO | 13 | | 1 (0)| 00:00:01 |--评估出来是13条，基本接近
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='CLUSTER')
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
2450 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

10 rows processed

总结：在Oracle优化SQL，很多时候是要让CBO评估准过滤数据的条数。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!