【环境配置】
【建立表空间】
SELECT tablespace_name, file_name FROM dba_data_files ORDER BY 1;
SELECT dbms_metadata.get_ddl('TABLESPACE', 'USERS') tbs_ddl FROM dual;
ALTER SYSTEM SET db_2k_cache_size=50M;
CREATE TABLESPACE dict_tabs_2k BLOCKSIZE 2k DATAFILE
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\dict_tabs_2k01.DBF' SIZE 50M
AUTOEXTEND ON UNIFORM SIZE 1M
LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL;
ALTER SYSTEM SET db_4k_cache_size=50M;
CREATE TABLESPACE dict_tabs_4k BLOCKSIZE 4k DATAFILE
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\dict_tabs_4k01.DBF' SIZE 50M
AUTOEXTEND ON UNIFORM SIZE 1M
LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL;
CREATE TABLESPACE dict_tabs DATAFILE
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\dict_tabs01.DBF' SIZE 100M
AUTOEXTEND ON UNIFORM SIZE 1M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT MANUAL;
【建表】
CREATE TABLE onew_tab TABLESPACE dict_tabs
PCTFREE 99
PCTUSED 1
AS
SELECT CAST(LEVEL AS CHAR(20)) AS id1,
CAST(MOD(LEVEL, 20) AS CHAR(20)) AS id2,
CAST(MOD(LEVEL, 500) AS CHAR(20)) AS id3
FROM dual
CONNECT BY LEVEL <= 10000;
CREATE TABLE dict_tabs_2k TABLESPACE dict_tabs_2k
PCTFREE 99
PCTUSED 1
AS
SELECT CAST(LEVEL AS CHAR(20)) AS id1,
CAST(MOD(LEVEL, 20) AS CHAR(20)) AS id2,
CAST(MOD(LEVEL, 500) AS CHAR(20)) AS id3
FROM dual
CONNECT BY LEVEL <= 10000;
CREATE TABLE dict_tabs_4k TABLESPACE dict_tabs_4k
PCTFREE 99
PCTUSED 1
AS
SELECT CAST(LEVEL AS CHAR(20)) AS id1,
CAST(MOD(LEVEL, 20) AS CHAR(20)) AS id2,
CAST(MOD(LEVEL, 500) AS CHAR(20)) AS id3
FROM dual
CONNECT BY LEVEL <= 10000;
【收集统计信息】
BEGIN
dbms_stats.gather_table_stats(ownname => USER,
tabname => 'onew_tab',
estimate_percent => 100,
method_opt => 'for all columns size 1',
cascade => TRUE);
dbms_stats.gather_table_stats(ownname => USER,
tabname => 'dict_tabs_2k',
estimate_percent => 100,
method_opt => 'for all columns size 1',
cascade => TRUE);
dbms_stats.gather_table_stats(ownname => USER,
tabname => 'dict_tabs_4k',
estimate_percent => 100,
method_opt => 'for all columns size 1',
cascade => TRUE);
END;
/
【浏览统计信息】
SELECT de.file_id, de.block_id, de.blocks
FROM dba_extents de
WHERE de.segment_name = upper('ONEW_TAB');
SELECT utl_raw.cast_to_varchar2(dtcs.low_value) AS lval,
utl_raw.cast_to_varchar2(dtcs.high_value) AS hval,
dtcs.histogram,
dtcs.num_distinct,
dtcs.density,
dtcs.num_nulls,
dtcs.avg_col_len
FROM dba_tab_col_statistics dtcs
WHERE dtcs.table_name = 'ONEW_TAB';
SELECT dts.num_rows, dts.blocks, dts.avg_row_len
FROM dba_tab_statistics dts
WHERE dts.table_name = 'ONEW_TAB';
【查看cbo成本估算】
DELETE FROM plan_table;
EXPLAIN PLAN SET STATEMENT_ID='8k' FOR SELECT COUNT(*) FROM ONEW_TAB;
EXPLAIN PLAN SET STATEMENT_ID='2k' FOR SELECT COUNT(*) FROM dict_tabs_2k;
EXPLAIN PLAN SET STATEMENT_ID='4k' FOR SELECT COUNT(*) FROM dict_tabs_4k;
SELECT STATEMENT_ID,cpu_cost,io_cost,COST FROM plan_table WHERE ID=0;
/*
STATEMENT_ID CPU_COST IO_COST COST
2k 611 611
4k 963 963
8k 1519 1519
*/
io_cost成本公式为：
blocks / ( POWER(db_file_multiblock_read_count,0.6581)*1.6765 )
round(10000 / (power(db_file_multiblock_read_count, 0.6581) * 1.6765) + 1) = 611 --推算出db_file_multiblock_read_count=32
round(10000 / (power(db_file_multiblock_read_count, 0.6581) * 1.6765) + 1) = 963 --推算出db_file_multiblock_read_count=16
【结论】
从上面的观测中我们得知：对于非标准块大小的表，cbo会以标准块为基准来评估成本，通俗解释如下：
对于本来可以一次性读取8（db_file_multiblock_read_count）个8192（db_block_size）字节个块的系统；
如果表块为2k，则它一次性可以读取32块；
如果表块为4k，则它一次性可以读取16块。
8*8 = 2*32 = 16*4
所以在计算非标准块的io成本时，cbo会偷偷的将db_file_multiblock_read_count修改。
