Oracle11G多列收集统计信息实验

﹏ヽ暗。殇╰゛Y 2022-07-13 12:19 179阅读 0赞

在上一篇文章中解决了[Oracle11g 多列收集统计信息][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,'test','(owner,object\_type)');  
    end;  
    /  
SQL> set autotrace traceonly  
SQL> **exec dbms\_stats.gather\_table\_stats(user,'test',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评估准过滤数据的条数。**

[Oracle11g]: http://blog.csdn.net/stevendbaguo/article/details/54134677