ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120626]11G的Extended Stats问题.txt

[20120626]11G的Extended Stats问题.txt

原创 Linux操作系统 作者:lfree 时间:2012-06-27 10:00:50 0 删除 编辑
[20120626]11G的Extended Stats问题.txt

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 Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


1.建立测试:
create table t1
as
with generator as (
    select    --+ materialize
        rownum id 
    from dual 
    connect by 
        level <= 10000
)
select
    mod(rownum,100)        col1,
    mod(rownum,10)        col2
from
    generator    v1,
    generator    v2
where
    rownum <= 50000
;

begin
    dbms_stats.gather_table_stats(
        ownname         => user,
        tabname         =>'T1',
        method_opt     => 'for all columns size 1'
    );
end;
/

2.开始测试:

SQL> select * from t1 where col1 = 50 and col2 = 5 ;

no rows selected

SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2w1wpwhgwnpuy, child number 0
-------------------------------------
select * from t1 where col1 = 50 and col2 = 5

Plan hash value: 3617692013

--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    25 (100)|
|*  1 |  TABLE ACCESS FULL| T1   |     50 |    25   (0)|
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("COL1"=50 AND "COL2"=5))

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

24 rows selected.
--可以发现估计是50rows,而实际是0行.

50000/100/50=50行.

很明显col1与col2存在相关性.

3.建立Extended Stats分析:
exec DBMS_STATS.GATHER_TABLE_STATS (OwnName=>null,TabName => 'T1',Estimate_Percent=> NULL,Method_Opt => 'FOR COLUMNS (col1,col2) SIZE 254 ',Cascade=> TRUE);

SQL> select * from t1 where col1 = 50 and col2 = 5 ;

no rows selected

SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2w1wpwhgwnpuy, child number 0
-------------------------------------
select * from t1 where col1 = 50 and col2 = 5

Plan hash value: 3617692013

--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    25 (100)|
|*  1 |  TABLE ACCESS FULL| T1   |    250 |    25   (0)|
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("COL1"=50 AND "COL2"=5))

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

24 rows selected.
--更加糟糕!估计250行.

select count(*),col1,col2 from t1 group by col1 ,col2
返回100行.oracle对于不在范围的行,返回的是

50000/100*.5 =250行.

SQL> select * from t1 where col1 = 29 and col2 = 9 ;
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  7zxz2cswcz432, child number 0
-------------------------------------
select * from t1 where col1 = 29 and col2 = 9

Plan hash value: 3617692013

--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    25 (100)|
|*  1 |  TABLE ACCESS FULL| T1   |    500 |    25   (0)|
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("COL1"=29 AND "COL2"=9))

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

24 rows selected.

--这样才正确.

SQL> select * from t1 where col1 = 29 and col2 = 9.0001 ;

no rows selected

SQL> @dpc
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  34rumajymcx1g, child number 0
-------------------------------------
select * from t1 where col1 = 29 and col2 = 9.0001

Plan hash value: 3617692013

--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    25 (100)|
|*  1 |  TABLE ACCESS FULL| T1   |     50 |    25   (0)|
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("COL1"=29 AND "COL2"=9.0001))

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

24 rows selected.

--看来oracle优化器存在许多改进的地方.

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2285
  • 访问量
    6025297