For the parameter Optimizer_index_cost_adj I find it is the most important parameter of all the other parameters, and the default setting of 100 is incorrect for most Oracle systems.
From the Internet I got to know:for some OLTP systems, re-setting this parameter to a smaller value (between 10- to 30) may result in huge performance gains!
And
The parameter can be set from 1 to 10000,
The default is 100.
There is a formula to express Optimizer_index_cost_adj;
Optimizer_index_cost_adj*(index scan cost)==Full Scan cost.
I have tested it in my way .
I got the way to find best value should be set for Optimizer_index_cost_adj.
col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format 999.999
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format 999.999
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
select
sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
(
sum(a.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c3,
(
sum(b.total_waits) /
sum(a.total_waits + b.total_waits)
) * 100 c4,
(
sum(b.time_waited_micro) /
sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)
) * 100 c5
from
dba_hist_system_event a,
dba_hist_system_event b
where
a.snap_id = b.snap_id
and
a.event_name = 'db file scattered read'
and
b.event_name = 'db file sequential read';
Starting
Value
for
Percent of Percent of optimizer
I/O Waits I/O Waits index
Average Waits for Average Waits for for scattered for sequential cost
Full Scan Read I/O Index Read I/O Full Scans Index Scans adj
------------------ ----------------- ------------- -------------- ---------
.011 .005 56.452 43.548 41
Luckily ,The parameter can be modified without restart database. select isses_modifiable,issys_modifiable
from v$parameter
where name='optimizer_index_cost_adj';
ISSES ISSYS_MOD
----- ---------
TRUE IMMEDIATE
I wish ORACLE could provide more decent default value for it:)))
分享到:
相关推荐
赠送jar包:flink-optimizer_2.11-1.7.1.jar 赠送原API文档:flink-optimizer_2.11-1.7.1-javadoc.jar 赠送源代码:flink-optimizer_2.11-1.7.1-sources.jar 包含翻译后的API文档:flink-optimizer_2.11-1.7.1-...
赠送jar包:flink-optimizer_2.11-1.13.2.jar; 赠送原API文档:flink-optimizer_2.11-1.13.2-javadoc.jar; 赠送源代码:flink-optimizer_2.11-1.13.2-sources.jar; 赠送Maven依赖信息文件:flink-optimizer_2.11-...
赠送jar包:flink-optimizer_2.11-1.7.1.jar 赠送原API文档:flink-optimizer_2.11-1.7.1-javadoc.jar 赠送源代码:flink-optimizer_2.11-1.7.1-sources.jar 包含翻译后的API文档:flink-optimizer_2.11-1.7.1-...
Dell-Precision-Optimizer_82GT9_WIN_4.0.10_A00.exe (一下内容复制粘贴仅供参考) 文件格式: Update Package for Microsoft® Windows® 文件名: Dell-Precision-Optimizer_82GT9_WIN_4.0.10_A00.EXE 下载类型: ...
Zend_Optimizer_User_Guide
(狼群算法)Grey_wolf_optimizer_a_review_of_recent_variants_and_applications
Grey_Wolf_Optimizer_灰狼_灰狼算法_源码.zip
Grey_Wolf_Optimizer_灰狼_灰狼算法.zip
WinXP_IIS_MySql_PHP_ZendOptimizer_phpMyAdmin环境配置安装指南
Image_Optimizer_v4带破解,很好用的图片压缩工具,占用空间小,压缩质量很好,速度也很快
ZendOptimizer_linux是linux下对zend加密后的php文件进行解密的工具
赠送jar包:flink-optimizer_2.11-1.10.0.jar; 赠送原API文档:flink-optimizer_2.11-1.10.0-javadoc.jar; 赠送源代码:flink-optimizer_2.11-1.10.0-sources.jar; 赠送Maven依赖信息文件:flink-optimizer_2.11-...
matlab_Gray Wolf Optimizer_GWO_优化器_灰狼优化器 GWO算法模仿自然界中灰狼的领导层级和狩猎机制。采用 alpha、beta、delta 和 omega 四种类型的灰狼来模拟领导层级。此外,还实现了狩猎、寻找猎物、包围猎物和...
windows7下Apache PHP MySQL phpMyAdmin Zend_Optimizer_安装配置教程
mysql查询优化,索引原理,各种查询优化教程。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。...
Mu Online Game Server Optimizer. A very useful tools.
赠送jar包:flink-optimizer_2.11-1.13.2.jar; 赠送原API文档:flink-optimizer_2.11-1.13.2-javadoc.jar; 赠送源代码:flink-optimizer_2.11-1.13.2-sources.jar; 赠送Maven依赖信息文件:flink-optimizer_2.11-...
Internet Download Manager Optimizer
Particle swarm optimizer, in matlab