set timing off
define m_blocks=128000 -- 定义表的块大小为128000(欺骗优化器)
rem define m_blocks = &1
alter session set optimizer_mode = all_rows;
drop table t1;
create table t1 -- 创建表
nologging
pctfree 90 -- pctfree高表明，一个块上存放的数据行比较少
pctused 10
storage (initial 40M)
as
select
rownum id,
to_char(rownum) vc_small,
rpad('x',1000) padding
from
all_objects
where
rownum <= 1000
;
-- 计算统计信息
analyze table t1 compute statistics;
-- 设置表的block块数
-- 欺骗优化器
begin
dbms_stats.set_table_stats(
ownname => null,
tabname =>'T1',
numrows => &m_blocks,
numblks => &m_blocks,
avgrlen => 3500,
flags => 0
);
end;
/
delete from plan_table;
commit;
begin
for r in 1..128 loop
-- 设置读取块数
execute immediate
'alter session set db_file_multiblock_read_count = ' || r;
-- 无CPU成本
execute immediate
'explain plan set statement_id = ''' ||
to_char(r,'fm000') || 'N'' for ' ||
' select /*+ nocpu_costing */ count(*) from t1';
-- 有CPU成本
execute immediate
'explain plan set statement_id = ''' ||
to_char(r,'fm000') || 'C'' for ' ||
' select /*+ cpu_costing */ count(*) from t1';
end loop;
end;
.
/
-- 输出计算的dbf_mbrc
spool calc_mbrc
set linesize 100
set pagesize 90
rem
rem We use the 'cost - 1' for Oracle 9.2 because it has
rem _tablescan_cost_plus_one = true;
rem whereas Oracle 8.1.7 has
rem _tablescan_cost_plus_one = false;
rem
select
to_number(substr(statement_id,1,3)) id,
cost act_cost,
round(&m_blocks/to_number(substr(statement_id,1,3)),0) old_cost,
round(&m_blocks/cost,3) eff_mbrc
-- round(&m_blocks/(cost-1),3) eff_mbrc
from
plan_table
where
id = 0
and statement_id like '%N%'
order by
statement_id
;
spool off
附：计算结果：
SQL> set linesize 100
SQL> set pagesize 90
SQL>
SQL> rem
SQL> rem We use the 'cost - 1' for Oracle 9.2 because it has
SQL> rem _tablescan_cost_plus_one = true;
SQL> rem whereas Oracle 8.1.7 has
SQL> rem _tablescan_cost_plus_one = false;
SQL> rem
SQL>
SQL> select
2 to_number(substr(statement_id,1,3)) id,
3 cost act_cost,
4 round(&m_blocks/to_number(substr(statement_id,1,3)),0) old_cost,
5 round(&m_blocks/cost,3) eff_mbrc
6 -- round(&m_blocks/(cost-1),3) eff_mbrc
7 from
8 plan_table
9 where
10 id = 0
11 and statement_id like '%N%'
12 order by
13 statement_id*
14 ;
原值 4: round(&m_blocks/to_number(substr(statement_id,1,3)),0) old_cost,
新值 4: round(128000/to_number(substr(statement_id,1,3)),0) old_cost,
原值 5: round(&m_blocks/cost,3) eff_mbrc
新值 5: round(128000/cost,3) eff_mbrc
原值 6: -- round(&m_blocks/(cost-1),3) eff_mbrc
新值 6: -- round(128000/(cost-1),3) eff_mbrc
ID ACT_COST OLD_COST EFF_MBRC
---------- ---------- ---------- ----------
1 76353 128000 1.676
2 48383 64000 2.646
3 37051 42667 3.455
4 30660 32000 4.175
5 26472 25600 4.835
6 23479 21333 5.452
7 21213 18286 6.034
8 19429 16000 6.588
9 17980 14222 7.119
10 16775 12800 7.63
11 15755 11636 8.124
12 14878 10667 8.603
13 14115 9846 9.068
14 13443 9143 9.522
15 12846 8533 9.964
16 12312 8000 10.396
17 11830 7529 10.82
18 11394 7111 11.234
19 10995 6737 11.642
20 10630 6400 12.041
21 10294 6095 12.434
22 9984 5818 12.821
23 9696 5565 13.201
24 9428 5333 13.577
25 9178 5120 13.946
26 8945 4923 14.31
27 8725 4741 14.67
28 8519 4571 15.025
29 8324 4414 15.377
30 8141 4267 15.723
31 7967 4129 16.066
32 7802 4000 16.406
33 7646 3879 16.741
34 7497 3765 17.073
35 7355 3657 17.403
36 7220 3556 17.729
37 7091 3459 18.051
38 6968 3368 18.37
39 6850 3282 18.686
40 6737 3200 19
41 6628 3122 19.312
42 6524 3048 19.62
43 6424 2977 19.925
44 6327 2909 20.231
45 6234 2844 20.533
46 6145 2783 20.83
47 6058 2723 21.129
48 5975 2667 21.423
49 5895 2612 21.713
50 5817 2560 22.004
51 5741 2510 22.296
52 5668 2462 22.583
53 5598 2415 22.865
54 5529 2370 23.151
55 5463 2327 23.43
56 5399 2286 23.708
57 5336 2246 23.988
58 5275 2207 24.265
59 5216 2169 24.54
60 5159 2133 24.811
61 5103 2098 25.083
62 5049 2065 25.352
63 4996 2032 25.62
64 4945 2000 25.885
65 4894 1969 26.154
66 4845 1939 26.419
67 4798 1910 26.678
68 4751 1882 26.942
69 4706 1855 27.199
70 4661 1829 27.462
71 4618 1803 27.718
72 4576 1778 27.972
73 4534 1753 28.231
74 4494 1730 28.482
75 4455 1707 28.732
76 4416 1684 28.986
77 4378 1662 29.237
78 4341 1641 29.486
79 4305 1620 29.733
80 4269 1600 29.984
81 4235 1580 30.224
82 4201 1561 30.469
83 4167 1542 30.718
84 4134 1524 30.963
85 4102 1506 31.204
86 4071 1488 31.442
87 4040 1471 31.683
ID ACT_COST OLD_COST EFF_MBRC
---------- ---------- ---------- ----------
88 4010 1455 31.92
89 3980 1438 32.161
90 3951 1422 32.397
91 3922 1407 32.636
92 3894 1391 32.871
93 3867 1376 33.101
94 3840 1362 33.333
95 3813 1347 33.569
96 3787 1333 33.8
97 3761 1320 34.034
98 3736 1306 34.261
99 3711 1293 34.492
100 3686 1280 34.726
101 3662 1267 34.954
102 3639 1255 35.174
103 3615 1243 35.408
104 3592 1231 35.635
105 3570 1219 35.854
106 3548 1208 36.077
107 3526 1196 36.302
108 3504 1185 36.53
109 3483 1174 36.75
110 3462 1164 36.973
111 3442 1153 37.188
112 3422 1143 37.405
113 3402 1133 37.625
114 3382 1123 37.847
115 3363 1113 38.061
116 3343 1103 38.289
117 3325 1094 38.496
118 3306 1085 38.717
119 3288 1076 38.929
120 3270 1067 39.144
121 3252 1058 39.36
122 3234 1049 39.579
123 3217 1041 39.789
124 3200 1032 40
125 3183 1024 40.214
126 3166 1016 40.43
127 3150 1008 40.635
128 3134 1000 40.842
已选择128行。
SQL>
SQL> spool off
结论：在不改变任何情况下，全表扫描一张大表，
使用db_file_multiblock_read_count的值设得越大，成本越低!
阅读(987) | 评论(0) | 转发(0) |
