12条语句学会oracle cbo计算(一)
工作中,你可能会遇到统计数据正确,但就是不走你想要的执行计划的情况,最后一般通过hint或sql_profile解决.一条sql语句的性能主要依赖于好的物理结构,准确的系统统计数据,准确的对象统计数据,合理的查询优化器参数,合理的系统参数.这些因素也就是cost计算基本参数.因此产生的sql优化技术有很多,大概包含:改变访问结构,修改sql语句,加hint,改变执行环境,sql profile,stored outlines,sql plan baseline.其实无外乎就是通过调整sql适应环境或者通过调整环境适应sql.如果清楚Cost值的算法,就可以通过算法的对比,找到问题的真正原因,更有针对性的去解决问题.
全文主要参考Jonathan Lewis的<<基于成本的Oracle优化法则>>和黄玮(fuyuncat)的<>,特别黄玮(fuyuncat)的这本,是非常值得去学习的.
准备用14篇来描述完,前2篇是统计数据,算法公式说明,后12篇用12条语句分别去套用说明.
本篇是cost计算中要用到的统计数据说明:
1.系统统计数据
a.查询方法:
select rpad(pname, '20', ' ') pname,
rpad(pval1, '20', ' ') pval1,
rpad(pval2, '20', ' ') pval2
from SYS.AUX_STATS$
where sname = 'SYSSTATS_MAIN';
/*
PNAME PVAL1 PVAL2
CPUSPEED
CPUSPEEDNW 3074.07407407407
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM
*/
b.说明:
当CPUSPEED或MREADTIM或SREADTIM为空时,为无负载模式(NOWORKLOAD),否则为负载模式(WORKLOAD).
CPUSPEEDNW:CPU在无负栽模式下的处理速度,即每秒钟可以完成的机器指令数(或者说转数,Cycles),单位为百万次,10g中默认值为1,llg中默认值为100
IOSEEKTIM:IO寻址时间,即10寻址銪要的时间,单位为毫秒,默认值为10
IOTFRSPEED:IO传输速度,即每亳秒传输的字节数,默认值为4096
负载模式(WORKLOAD)用的到参数:
CPUSPEED:CPU负载模式下的处理速度,即每秒钟可以完成的机器指令数,单位为百万次
MBRC:系统设置多数据块读的数据块数
SREADTIM:单数据块读的平均读取时间,单位为毫秒
MREADTIM:多数据块读的平均读取时间,单位为毫秒
MAXTHR:系统的最大吞吐量,单位为每秒字节数
SLAVETHR:承个并行服务进程的锒大吞吐a,单位为毎秒字节数
2.优化器系统参数
a.查询方法:
select rpad(name,40,' ') name,rpad(value,20,' ') value,isdefault
from (select nam.ksppinm name,
val.KSPPSTVL value,
--nam.ksppdesc description,
val.ksppstdf isdefault
from sys.x$ksppi nam, sys.x$ksppcv val
where nam.inst_id = val.inst_id
and nam.indx = val.indx)
where name in
('_db_file_optimizer_read_count', 'db_file_multiblock_read_count',
'_optimizer_block_size', '_table_scan_cost_plus_one',
'_optimizer_ceil_cost', '_optimizer_cost_model',
'_optimizer_cache_stats', '_smm_auto_min_io_size',
'_smm_auto_max_io_size', '_smm_min_size', '_smm_max_size',
'_smm_px_max_size', 'sort_area_retained_size', 'sort_area_size',
'workarea_size_policy','_optimizer_percent_parallel');
/*
NAME VALUE ISDEFAULT
db_file_multiblock_read_count 116 TRUE
_db_file_optimizer_read_count 8 TRUE
sort_area_size 65536 TRUE
sort_area_retained_size 0 TRUE
_optimizer_cost_model CHOOSE TRUE
_optimizer_cache_stats FALSE TRUE
_table_scan_cost_plus_one TRUE TRUE
workarea_size_policy AUTO TRUE
_smm_auto_min_io_size 56 TRUE
_smm_auto_max_io_size 248 TRUE
_smm_min_size 286 TRUE
_smm_max_size 57344 TRUE
_smm_px_max_size 143360 TRUE
_optimizer_percent_parallel 101 TRUE
_optimizer_block_size 8192 TRUE
_optimizer_ceil_cost TRUE TRUE
*/
b.说明:
db_file_multiblock_read_count:决定了优化器进行代价估算时和语句实际执行过程中采用多数据块读的最大数据块数量
_db_file_optimizer_read_count:是优化器做代价估算时的参考参数
sort_area_size:手动管理策略下,排序区的大小及最大大小
sort_area_retained_size:手动管理策略下,排序区的保留大小
_optimizer_cost_model:当其值为FIXED时,CPUSPEED或CPUSPEEDNW不会采用的系统统计数据,而是采用固定值100000
_optimizer_cache_stats:优化器做代价估算时将缓存统计数据考虑进去
_table_scan_cost_plus_one:对计算进行微调,其含义是在全表扫描代价估算结果上再加一,默认值为TRUE
workarea_size_policy:工作区(包括排序区和哈希区)大小的管理方式,分auto和mannul,默认auto自动管理
_smm_auto_min_io_size:工作区一次直接读写到临时磁盘的最小大小,单位为KB
_smm_auto_max_io_size:工作区一次直接读写到临时磁盘的最大大小,单位为KB
_smm_min_size:自动管理策略下,排序区的大小,单位为KB
_smm_max_size:自动管理策略下,排序区的最大大小,单位为KB
_smm_px_max_size:并行模式下总的工作区大小,并行服务进程使用的总的工作区大小不能超过该值
_optimizer_percent_parallel:控制优化器在估算时，采用并行度代价估算的比例
_optimizer_block_size:优化器参考的数据块大小
_optimizer_ceil_cost:对计算进行微调,其含义是在全表扫描代价估算结果ceil
建一张表为下面说明与对象相关的统计数据:
create table scott.t_test1 as select * from dba_objects;
create index scott.idx_t_test1_01 on scott.t_test1(owner);
begin
dbms_stats.gather_table_stats('scott','t_test1',method_opt => 'for columns owner size 100');
end;
3.表统计数据
a.查询方法:
select rpad(table_name, 10, ' ') table_name,
rpad(num_rows, 10, ' ') num_rows,
rpad(blocks, 10, ' ') blocks,
avg_row_len
from dba_tables
where owner = 'SCOTT'
and table_name = 'T_TEST1' ;
/*
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
T_TEST1 690648 10125 98
*/
b.说明:
TABLE_NAME:表名
NUM_ROWS:表(或者分区、子分区)中的数据记录数
EMPTY_BLOCKS:表(或者分区、子分区)中平均空闲数据块数
AVG_ROW_LEN:表(或者分区、子分区)的数据记录平均长度,单位为字节
4.字段统计数据
a.查询方法:
select rpad(column_name, 12, ' ') column_name,
rpad(num_distinct, 8, ' ') num_distinct,
rpad(utl_raw.cast_to_varchar2(low_value), 15, ' ') low_value,
rpad(utl_raw.cast_to_varchar2(high_value), 10, ' ') high_value,
rpad(nullable, 8, ' ') nullable,
rpad(num_nulls, 8, ' ') num_nulls,
rpad(avg_col_len, 6, ' ') avg_col_len,
rpad(density, 15, ' ') density,
histogram
from dba_tab_columns
where owner = 'SCOTT'
and table_name = 'T_TEST1'
and column_name = 'OWNER';
--low_value,high_value是根据不同的字段类型通过utl_raw.cast_to_xxx转换后的.
/*
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NULLABLE NUM_NULLS AVG_COL_LEN DENSITY HISTOGRAM
OWNER 23 APEX_030200 XDB Y 0 6 .00000566390003 FREQUENCY
*/
b.说明:
COLUMN_NAME:字段名
NUM_DISTINCT:字段中的唯一值
LOW_VALUE:字段的最小数值
HIGH_VALUE:字段的最大数值
NULLABLE:可否为空
NUM_NULLS:字段中的空值数
DENSITY:字段的密度(即平均每个唯一值在该字段中的重复数据数)
HISTOGRAM:直方图类型
5.索引统计数据
a.查询方法:
select index_name,
rpad(blevel, 8, ' ') blevel,
rpad(leaf_blocks, 8, ' ') leaf_blocks,
rpad(distinct_keys, 8, ' ') distinct_keys,
rpad(clustering_factor, 15, ' ') clustering_factor,
num_rows
from dba_indexes
where owner = 'SCOTT'
and table_name = 'T_TEST1';
/*
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
IDX_T_TEST1_01 1 203 23 2302 86332
*/
b.说明:
index_name:索引名
BLEVEL:索引(或者本地分区索引)树的支节点层数
LEAF_BLOCKS:索引(或者本地分区索引)的叶子数据块数
DISTINCT_EYS:索引(或者本地分区索引)的唯一键值数
CLUSTERING_FACTOR:索引(或者本地分区索引)的族集因子(簇集因子反映了每个键值所指向的表的数据块数直接的连续性,数值越低,这些数据块之间越连续;反之,数值越低,说明这些数据块分布越分散)
NUM_ROWS:被索引的数据记录数
6.直方图统计数据
a.查询方法:
select column_name,
endpoint_number,
utl_raw.cast_to_varchar2(substr(lpad(to_char(endpoint_value,
'fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),
30,
'0'),
1,
12)) char_value,
endpoint_value
from dba_tab_histograms
where owner = 'SCOTT' and table_name = 'T_TEST1' and column_name = 'OWNER'
order by endpoint_number;
--endpoint_value是根据不同的字段类型通过utl_raw.cast_to_xxx转换后的.
/*
COLUMN_NAME ENDPOINT_NUMBER CHAR_VALUE ENDPOINT_VALUE
OWNER 165 APEX_0 3.39127382666026E35
OWNER 192 CTXSYS 3.49594609802084E35
OWNER 197 DBSNMP 3.54421425584994E35
OWNER 220 EXFSYS 3.60058907050643E35
OWNER 221 FLOWS_ 3.65008529278141E35
OWNER 347 MDSYR? 4.01192665542225E35
OWNER 389 OLAPSY 4.11738089644256E35
OWNER 390 ORACLE 4.11859780070016E35
OWNER 408 ORDDAT 4.1186001805076E35
OWNER 410 ORDPLU 4.11860021777884E35
OWNER 575 ORDSYS 4.11860022722044E35
OWNER 576 OWBSYR 4.11961276313737E35
OWNER 577 OWBSYS 4.11961276313739E35
OWNER 2806 PUBLIB 4.17113006167251E35
OWNER 2807 SCOTT 4.32325845824965E35
OWNER 5268 SYR??? 4.32772349650604E35
OWNER 5499 SYSMAN 4.32772373559899E35
OWNER 5530 SYSTEM 4.32772375731125E35
OWNER 5545 WMSYS 4.53298175814007E35
OWNER 5624 XDA??? 4.58306556462839E35
*/
b.说明:
COLUMN_NAME:字段名
ENDPOINT_NUMBER:结束点数
CHAR_VALUE:转换后的结束点值
ENDPOINT_VALUE:结束点值
全文主要参考Jonathan Lewis的<<基于成本的Oracle优化法则>>和黄玮(fuyuncat)的<
准备用14篇来描述完,前2篇是统计数据,算法公式说明,后12篇用12条语句分别去套用说明.
本篇是cost计算中要用到的统计数据说明:
1.系统统计数据
a.查询方法:
select rpad(pname, '20', ' ') pname,
rpad(pval1, '20', ' ') pval1,
rpad(pval2, '20', ' ') pval2
from SYS.AUX_STATS$
where sname = 'SYSSTATS_MAIN';
/*
PNAME PVAL1 PVAL2
CPUSPEED
CPUSPEEDNW 3074.07407407407
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM
*/
b.说明:
当CPUSPEED或MREADTIM或SREADTIM为空时,为无负载模式(NOWORKLOAD),否则为负载模式(WORKLOAD).
CPUSPEEDNW:CPU在无负栽模式下的处理速度,即每秒钟可以完成的机器指令数(或者说转数,Cycles),单位为百万次,10g中默认值为1,llg中默认值为100
IOSEEKTIM:IO寻址时间,即10寻址銪要的时间,单位为毫秒,默认值为10
IOTFRSPEED:IO传输速度,即每亳秒传输的字节数,默认值为4096
负载模式(WORKLOAD)用的到参数:
CPUSPEED:CPU负载模式下的处理速度,即每秒钟可以完成的机器指令数,单位为百万次
MBRC:系统设置多数据块读的数据块数
SREADTIM:单数据块读的平均读取时间,单位为毫秒
MREADTIM:多数据块读的平均读取时间,单位为毫秒
MAXTHR:系统的最大吞吐量,单位为每秒字节数
SLAVETHR:承个并行服务进程的锒大吞吐a,单位为毎秒字节数
2.优化器系统参数
a.查询方法:
select rpad(name,40,' ') name,rpad(value,20,' ') value,isdefault
from (select nam.ksppinm name,
val.KSPPSTVL value,
--nam.ksppdesc description,
val.ksppstdf isdefault
from sys.x$ksppi nam, sys.x$ksppcv val
where nam.inst_id = val.inst_id
and nam.indx = val.indx)
where name in
('_db_file_optimizer_read_count', 'db_file_multiblock_read_count',
'_optimizer_block_size', '_table_scan_cost_plus_one',
'_optimizer_ceil_cost', '_optimizer_cost_model',
'_optimizer_cache_stats', '_smm_auto_min_io_size',
'_smm_auto_max_io_size', '_smm_min_size', '_smm_max_size',
'_smm_px_max_size', 'sort_area_retained_size', 'sort_area_size',
'workarea_size_policy','_optimizer_percent_parallel');
/*
NAME VALUE ISDEFAULT
db_file_multiblock_read_count 116 TRUE
_db_file_optimizer_read_count 8 TRUE
sort_area_size 65536 TRUE
sort_area_retained_size 0 TRUE
_optimizer_cost_model CHOOSE TRUE
_optimizer_cache_stats FALSE TRUE
_table_scan_cost_plus_one TRUE TRUE
workarea_size_policy AUTO TRUE
_smm_auto_min_io_size 56 TRUE
_smm_auto_max_io_size 248 TRUE
_smm_min_size 286 TRUE
_smm_max_size 57344 TRUE
_smm_px_max_size 143360 TRUE
_optimizer_percent_parallel 101 TRUE
_optimizer_block_size 8192 TRUE
_optimizer_ceil_cost TRUE TRUE
*/
b.说明:
db_file_multiblock_read_count:决定了优化器进行代价估算时和语句实际执行过程中采用多数据块读的最大数据块数量
_db_file_optimizer_read_count:是优化器做代价估算时的参考参数
sort_area_size:手动管理策略下,排序区的大小及最大大小
sort_area_retained_size:手动管理策略下,排序区的保留大小
_optimizer_cost_model:当其值为FIXED时,CPUSPEED或CPUSPEEDNW不会采用的系统统计数据,而是采用固定值100000
_optimizer_cache_stats:优化器做代价估算时将缓存统计数据考虑进去
_table_scan_cost_plus_one:对计算进行微调,其含义是在全表扫描代价估算结果上再加一,默认值为TRUE
workarea_size_policy:工作区(包括排序区和哈希区)大小的管理方式,分auto和mannul,默认auto自动管理
_smm_auto_min_io_size:工作区一次直接读写到临时磁盘的最小大小,单位为KB
_smm_auto_max_io_size:工作区一次直接读写到临时磁盘的最大大小,单位为KB
_smm_min_size:自动管理策略下,排序区的大小,单位为KB
_smm_max_size:自动管理策略下,排序区的最大大小,单位为KB
_smm_px_max_size:并行模式下总的工作区大小,并行服务进程使用的总的工作区大小不能超过该值
_optimizer_percent_parallel:控制优化器在估算时，采用并行度代价估算的比例
_optimizer_block_size:优化器参考的数据块大小
_optimizer_ceil_cost:对计算进行微调,其含义是在全表扫描代价估算结果ceil
建一张表为下面说明与对象相关的统计数据:
create table scott.t_test1 as select * from dba_objects;
create index scott.idx_t_test1_01 on scott.t_test1(owner);
begin
dbms_stats.gather_table_stats('scott','t_test1',method_opt => 'for columns owner size 100');
end;
3.表统计数据
a.查询方法:
select rpad(table_name, 10, ' ') table_name,
rpad(num_rows, 10, ' ') num_rows,
rpad(blocks, 10, ' ') blocks,
avg_row_len
from dba_tables
where owner = 'SCOTT'
and table_name = 'T_TEST1' ;
/*
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
T_TEST1 690648 10125 98
*/
b.说明:
TABLE_NAME:表名
NUM_ROWS:表(或者分区、子分区)中的数据记录数
EMPTY_BLOCKS:表(或者分区、子分区)中平均空闲数据块数
AVG_ROW_LEN:表(或者分区、子分区)的数据记录平均长度,单位为字节
4.字段统计数据
a.查询方法:
select rpad(column_name, 12, ' ') column_name,
rpad(num_distinct, 8, ' ') num_distinct,
rpad(utl_raw.cast_to_varchar2(low_value), 15, ' ') low_value,
rpad(utl_raw.cast_to_varchar2(high_value), 10, ' ') high_value,
rpad(nullable, 8, ' ') nullable,
rpad(num_nulls, 8, ' ') num_nulls,
rpad(avg_col_len, 6, ' ') avg_col_len,
rpad(density, 15, ' ') density,
histogram
from dba_tab_columns
where owner = 'SCOTT'
and table_name = 'T_TEST1'
and column_name = 'OWNER';
--low_value,high_value是根据不同的字段类型通过utl_raw.cast_to_xxx转换后的.
/*
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NULLABLE NUM_NULLS AVG_COL_LEN DENSITY HISTOGRAM
OWNER 23 APEX_030200 XDB Y 0 6 .00000566390003 FREQUENCY
*/
b.说明:
COLUMN_NAME:字段名
NUM_DISTINCT:字段中的唯一值
LOW_VALUE:字段的最小数值
HIGH_VALUE:字段的最大数值
NULLABLE:可否为空
NUM_NULLS:字段中的空值数
DENSITY:字段的密度(即平均每个唯一值在该字段中的重复数据数)
HISTOGRAM:直方图类型
5.索引统计数据
a.查询方法:
select index_name,
rpad(blevel, 8, ' ') blevel,
rpad(leaf_blocks, 8, ' ') leaf_blocks,
rpad(distinct_keys, 8, ' ') distinct_keys,
rpad(clustering_factor, 15, ' ') clustering_factor,
num_rows
from dba_indexes
where owner = 'SCOTT'
and table_name = 'T_TEST1';
/*
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
IDX_T_TEST1_01 1 203 23 2302 86332
*/
b.说明:
index_name:索引名
BLEVEL:索引(或者本地分区索引)树的支节点层数
LEAF_BLOCKS:索引(或者本地分区索引)的叶子数据块数
DISTINCT_EYS:索引(或者本地分区索引)的唯一键值数
CLUSTERING_FACTOR:索引(或者本地分区索引)的族集因子(簇集因子反映了每个键值所指向的表的数据块数直接的连续性,数值越低,这些数据块之间越连续;反之,数值越低,说明这些数据块分布越分散)
NUM_ROWS:被索引的数据记录数
6.直方图统计数据
a.查询方法:
select column_name,
endpoint_number,
utl_raw.cast_to_varchar2(substr(lpad(to_char(endpoint_value,
'fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),
30,
'0'),
1,
12)) char_value,
endpoint_value
from dba_tab_histograms
where owner = 'SCOTT' and table_name = 'T_TEST1' and column_name = 'OWNER'
order by endpoint_number;
--endpoint_value是根据不同的字段类型通过utl_raw.cast_to_xxx转换后的.
/*
COLUMN_NAME ENDPOINT_NUMBER CHAR_VALUE ENDPOINT_VALUE
OWNER 165 APEX_0 3.39127382666026E35
OWNER 192 CTXSYS 3.49594609802084E35
OWNER 197 DBSNMP 3.54421425584994E35
OWNER 220 EXFSYS 3.60058907050643E35
OWNER 221 FLOWS_ 3.65008529278141E35
OWNER 347 MDSYR? 4.01192665542225E35
OWNER 389 OLAPSY 4.11738089644256E35
OWNER 390 ORACLE 4.11859780070016E35
OWNER 408 ORDDAT 4.1186001805076E35
OWNER 410 ORDPLU 4.11860021777884E35
OWNER 575 ORDSYS 4.11860022722044E35
OWNER 576 OWBSYR 4.11961276313737E35
OWNER 577 OWBSYS 4.11961276313739E35
OWNER 2806 PUBLIB 4.17113006167251E35
OWNER 2807 SCOTT 4.32325845824965E35
OWNER 5268 SYR??? 4.32772349650604E35
OWNER 5499 SYSMAN 4.32772373559899E35
OWNER 5530 SYSTEM 4.32772375731125E35
OWNER 5545 WMSYS 4.53298175814007E35
OWNER 5624 XDA??? 4.58306556462839E35
*/
b.说明:
COLUMN_NAME:字段名
ENDPOINT_NUMBER:结束点数
CHAR_VALUE:转换后的结束点值
ENDPOINT_VALUE:结束点值
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28539951/viewspace-1654660/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
mysql 常用参数整理
请登录后发表评论
登录
全部评论
