CBO学习笔记系列2(转载)

最新推荐文章于 2024-10-18 13:02:57 发布

coo3678

最新推荐文章于 2024-10-18 13:02:57 发布

阅读量115

点赞数

文章标签：数据库操作系统

关于tablescan 之自调整的db_file_multiblock_read_count

今天打算超额完成任务。

闲言碎语不要讲，来做个实验先！--这个实验是CBO fundamentals里面的。

首先我的实验环境是
Oracle 9.2.0.4，
block size 8k，
LMT，
默认的db_file_multiblock_read_count = 8
MSSM，也就是使用free list而不是使用ASSM，
system statistics没有收集，也就是说cost里面不会考虑CPU的cost，也不会分辨single block read和multi-block read的差别。
这是一个最简单的环境了:)

建表t1:
execute dbms_random.seed(0)

create table t1
pctfree 99
pctused 1
as
select
rownum id,
trunc(100 * dbms_random.normal) val,
rpad('x',100) padding
from
all_objects
where
rownum <= 10000
;

--这个表里我们指定了pctfree=99，这就导致我们的每一个Block里面只有一个行，这就使我们可以用较少的数据生成一个较大的表来。

收集统计信息：
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

运行:
SQL> select max(val)
2 from t1
3 ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1519 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=1519 Card=10000 Bytes=40000)

这里是9i的计划，CBO fundamentals里面提供的8i的计划是：
Execution Plan (8.1.7.4)
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1518 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=1518 Card=10000 Bytes=40000)

9i的cost要比8i的多1，这其实是因为9i比8i聪明了，因为9i意识到进行表扫描的时候也要访问segment header的那个block，所以要加上1，这个加1的动作是由一个叫_tablescan_cost_plus_one的隐含参数控制的，在8i里这个隐含参数被设置成flase，而在9i这个隐含参数被设置为true。（我现在9i用的MSSM，大家可以测试一下ASSM，加的还要多）Ok，关于这一点，我不打算多说，我们还是继续我们的实验。

刚才的实验是在db_file_multiblock_read_count = 8的条件下做的，那么如果我们调整db_file_multiblock_read_count = 16会怎么样呢？

SQL> set autotrace trace exp
SQL> alter session set db_file_multiblock_read_count=16;

Session altered.

SQL>
SQL> select max(val) from t1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=963 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=963 Card=10000 Bytes=40000)

总体的cost现在降下来了，但似乎不是我们预计的降低一半。这是因为当我们设置db_file_multiblock_read_count = 16的时候，Oracle并不打算按照我们的设置去每次IO读取16个block，它自有自己的想法。通过用执行计划里的card除以cost （注意我们的表是每行占一个block的，所以如果card告诉我们执行这次table full scan需要访问10000行，也就意味着需要访问10000个block），我们可以算出，当设置db_file_multiblock_read_count=8的时候，每次IO打算读取的block数是10000/1519= 6.58，当设置db_file_multiblock_read_count=16的时候每次IO打算读取的block数是10000/963=10.38。这时候这个10.38不同于我们设置的db_file_multiblock_read_count，而是一个Oracle，或CBO自己调整过了的:
adjusted db_file_multiblock_read_count。这个调整的依据是Oracle在启动的时候了解到的操作系统方面允许的最大physical read size。

引用原文就是：
When Oracle starts up, it negotiates with the operating system to find the largest physical read size your operating system will allow, and silently uses that to limit whatever value you set for the db_file_multiblock_read_count.

通过多次调整db_file_multiblock_read_count，我发现在我的系统上，当db_file_multiblock_read_count设置大于128之后，总体的cost就不会再变化了，这说明达到了操作系统的阀值。

SQL> set autotrace trace exp

SQL> alter session set db_file_multiblock_read_count= 4;

Session altered.

SQL> select max(val) from t1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2397 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2397 Card=10000 Bytes=40000)

SQL> alter session set db_file_multiblock_read_count=16;

Session altered.

SQL>
SQL> select max(val) from t1;

SQL> alter session set db_file_multiblock_read_count= 32;

Session altered.

SQL> select max(val) from t1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=611 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=611 Card=10000 Bytes=40000)

SQL> alter session set db_file_multiblock_read_count= 64;

Session altered.

SQL> select max(val) from t1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=388 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=388 Card=10000 Bytes=40000)

SQL> alter session set db_file_multiblock_read_count= 128;

Session altered.

SQL> select max(val) from t1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=246 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=246 Card=10000 Bytes=40000)

SQL> alter session set db_file_multiblock_read_count= 130
2 ;

Session altered.

SQL> select max(val) from t1;

SQL> alter session set db_file_multiblock_read_count= 200;

Session altered.

SQL> select max(val) from t1;

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

转载于:http://blog.itpub.net/8410760/viewspace-732348/