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

[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 

)

select

    mod(rownum,100)        col1,

    mod(rownum,10)        col2

from

    generator    v1,

    generator    v2

where

    rownum 

;

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优化器存在许多改进的地方.