ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ora9ir2直方图测试

ora9ir2直方图测试

原创 Linux操作系统 作者:osdba 时间:2008-09-26 10:41:45 0 删除 编辑

--环境:windows下oracle9i  9.2.0.1.0

--建一张表有一个state状态字段,同时给这个字段上建一个索引,状态为1的记录有100000条,为2的只有1条。
--select * from t where state=1,最好的执行计划是走索引;
--select * from t where state=2, 最好的执行计划是走全表扫描

--当没有统计信息时,全部走索引
--当用analyze table t compute statistics;分析表后,全部走全表扫描
--当只有正确生成了直方图信息后:
BEGIN
dbms_stats.gather_table_stats(
ownname => 'scott',
tabname => 't',
estimate_percent => 100,
method_opt => 'FOR COLUMNS size 10 state'
);
END;
/
--执行计划才正确。

--查看直方图
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
  FROM user_HISTOGRAMS
 WHERE TABLE_NAME ='T' AND COLUMN_NAME='STATE'
 ORDER BY ENDPOINT_NUMBER;
 

create table t(id number primary key,name varchar2(256),state number);

insert into t values(100001,'aaaaa',1);
commit;

begin
for i in 1..100000 loop
 insert into t values

(i,'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345

678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234

56789012345678901234567890',2);
end loop;
end;
/
commit;
/

create index idx_t_state on t(state);

--没有统计信息和直方图信息时,都会走到索引
SQL> select count(*) from user_HISTOGRAMS where table_name='T';

  COUNT(*)
----------
         0

SQL> select * from t where state=2;

已选择100000行。


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'IDX_T_STATE' (NON-UNIQUE)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16541  consistent gets
       1647  physical reads
          0  redo size
    1796196  bytes sent via SQL*Net to client
      73713  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> select * from t where state=1;


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'IDX_T_STATE' (NON-UNIQUE)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        486  bytes sent via SQL*Net to client
        387  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>


--分析表:analyze table t compute statistics;
--可以看出直方图信息并不正确,执行SQL时,总是走全表扫描,所以是错误的。

col endpoint_actual_value format a20
select endpoint_number,endpoint_value,endpoint_actual_value from user_HISTOGRAMS where

table_name='T' and column_name='STATE';

SQL> set autotrace off
SQL> col endpoint_actual_value format a20
SQL> select endpoint_number,endpoint_value,endpoint_actual_value from user_HISTOGRAMS where ta
me='T' and column_name='STATE';

ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
--------------- -------------- --------------------
              0              1
              1              2
SQL> set autotrace trace
SQL> select * from t where state=2;

已选择100000行。


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=312 Card=50001 Bytes
          =11300226)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=312 Card=50001 Bytes=1130
          0226)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       9688  consistent gets
       1293  physical reads
          0  redo size
    1796196  bytes sent via SQL*Net to client
      73713  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> select * from t where state=1;


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=312 Card=50001 Bytes
          =11300226)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=312 Card=50001 Bytes=1130
          0226)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3236  consistent gets
       1171  physical reads
          0  redo size
        486  bytes sent via SQL*Net to client
        387  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>


--生成正确的直方图信息后,执行计划就能正确执行了,
BEGIN
dbms_stats.gather_table_stats(
ownname => 'scott',
tabname => 't',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR COLUMNS size 10 state '
);
END;
/

SQL> BEGIN
  2  dbms_stats.gather_table_stats(
  3  wnname => 'scott',
  4  tabname => 't',
  5  estimate_percent => dbms_stats.auto_sample_size,
  6  method_opt => 'FOR COLUMNS size 10 state '
  7  );
  8  END;
  9  /
PL/SQL 过程已成功完成。
SQL> select * from t where state=1;

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=227)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
          227)

   2    1     INDEX (RANGE SCAN) OF 'IDX_T_STATE' (NON-UNIQUE) (Cost=1
           Card=1)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        486  bytes sent via SQL*Net to client
        387  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 state=2;
已选择100100行。

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=312 Card=99972 Bytes
          =22693644)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=312 Card=99972 Bytes=2269
          3644)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       9695  consistent gets
       1055  physical reads
          0  redo size
    1798049  bytes sent via SQL*Net to client
      73790  bytes received via SQL*Net from client
       6675  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100100  rows processed

SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15145533/viewspace-464430/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 卖猪的故事
全部评论

注册时间:2008-08-11

  • 博文量
    12
  • 访问量
    25825