ITPub博客

首页 > Linux操作系统 > Linux操作系统 > in_list 11g优化器改进

in_list 11g优化器改进

原创 Linux操作系统 作者:yangzhangyue 时间:2013-07-24 16:28:13 0 删除 编辑
测试脚本:
create table audience as
select
        trunc(dbms_random.value(1,13))  month_no
from
        all_objects
where
        rownum <= 1200
;

begin
        dbms_stats.gather_table_stats(
                user,
                'audience',
                cascade => true,
                estimate_percent => null,
                method_opt => 'for all columns size 1'
        );
end;
/
set autotrace traceonly explain

select count(*) from audience
where month_no = 25
;

select count(*) from audience
where month_no in (4, 4)
;

select count(*) from audience
where month_no in (3, 25)
;

select count(*) from audience
where month_no in (3, 25, 26)
;

select count(*) from audience
where month_no in (3, 25, 25, 26)
;

select count(*) from audience
where month_no in (3, 25, null)
;

select count(*) from audience
where month_no in (:b1, :b2, :b3)
;

set autotrace off

现象:
10g
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |     1 |     3 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=4)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   101 |   303 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   101 |   303 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   200 |   600 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR
              "MONTH_NO"=TO_NUMBER(NULL))

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   300 |   900 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=TO_NUMBER(:B1) OR "MONTH_NO"=TO_NUMBER(:B2) OR
              "MONTH_NO"=TO_NUMBER(:B3))

11g:
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |     1 |     3 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=4)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   101 |   303 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   101 |   303 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)


Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   300 |   900 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=TO_NUMBER(:B1) OR "MONTH_NO"=TO_NUMBER(:B2) OR
              "MONTH_NO"=TO_NUMBER(:B3))

   观测红色字体部分的内容。我们会发现11g中能更精确的计算基数card的值。在刘易斯《基于成本的oracle优化法则中》,他所测试的9i/10g还不能正确计算超出最大最小值界限,只可以很好的处理重复值
我所测试的10g版本已经可以很好的处理最大最小值的问题。11.2.0.3.6中进一步完善了算法,可以更好的处理异常值,但帮定变量还不行

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

请登录后发表评论 登录
全部评论

注册时间:2013-07-09

  • 博文量
    36
  • 访问量
    219775