表扫描-多数据块读取

最新推荐文章于 2024-04-26 15:25:18 发布

转载最新推荐文章于 2024-04-26 15:25:18 发布 · 1k 阅读

文章标签：

本文探讨了Oracle数据库的成本模型（CBO），通过调整db_file_multiblock_read_count参数及设置表统计信息，展示了不同配置下全表扫描的成本变化，并分析了这些变化对查询计划的影响。

execute dbms_random.seed(0);

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

● pctfree 99 ：基本上每个数据块1行数据

ALTER SESSION SET db_file_multiblock_read_count = 8;

select MAX(val) from t1;

Description                        对象所有者     对象名称       耗费   基数   字节   IO耗费
------------------------------------------------------------------------------------------------------------
SELECT STATEMENT, GOAL = CHOOSE                       1227   1   3   1227
SORT AGGREGATE                                       1   3
TABLE ACCESS FULL           STUDENT       T1       1227   8073   24219   1227

ALTER SESSION SET db_file_multiblock_read_count = 16;

select MAX(val) from t1;
Description                        对象所有者     对象名称       耗费   基数   字节   IO耗费
------------------------------------------------------------------------------------------------------------
SELECT STATEMENT, GOAL = CHOOSE                       778   1   3   778
SORT AGGREGATE                                       1   3
TABLE ACCESS FULL           STUDENT       T1       778   8073   24219   778

ALTER SESSION SET db_file_multiblock_read_count = 32;

select MAX(val) from t1;
Description                        对象所有者     对象名称       耗费   基数   字节   IO耗费
------------------------------------------------------------------------------------------------------------
SELECT STATEMENT, GOAL = CHOOSE                       494   1   3   494
SORT AGGREGATE                                       1   3
TABLE ACCESS FULL           STUDENT       T1       494   8073   24219   494

设置表的块状态，欺骗优化器
exec dbms_stats.set_table_stats('STUDENT', 'T1', numblks => 128000000);

Description                        对象所有者     对象名称       耗费       基数   字节   IO耗费
------------------------------------------------------------------------------------------------------------
SELECT STATEMENT, GOAL = CHOOSE                       7800650   1   3   7800650
SORT AGGREGATE                                           1   3
TABLE ACCESS FULL           STUDENT       T1       7800650   8073   24219   7800650

SQL> analyze table sunwg compute statistics;

Table analyzed

SQL> select blocks from user_tables where table_name='SUNWG';

BLOCKS

----------

738

db_file_multiblock_read_count	COST	ADJUSTED DBF_MBRC
4	179	738/179 = 4.12
8	114	738/114 = 6.47
16	73	738/73 = 10.1

ADJUSTED DBF_MBRC是个很重要的参数，其实表扫描的成本也就是高水位标记下的块的数量除以ADJUSTED DBF_MBRC的结果。然后上面表中的ADJUSTED DBF_MBRC仅仅是个近似的值，因为并没有加上位图块的数量。因为本地管理的表空间是用一些位图来表示块的可用性的，所以如果算上位图块的话，ADJUSTED DBF_MBRC应该会大一些。

CBO中增加了对CPU的COST的统计，这个cpu costing在全表扫描的时候对COST值的影响会很大，所以在这里把cpu costing给去掉了。

/*+no_cpu_costing*/，这个提示的目的是去掉CPU的COST信息
在10G中CBO中增加了对CPU的COST的统计，这个cpu costing在全表扫描的时候对COST值的影响会很大，所以在这里把cpu costing给去掉了