For the parameter Optimizer_index_cost_adj
来源:互联网 发布:高仿qq源码 编辑:程序博客网 时间:2024/04/30 04:41
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:)))
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:)))
- For the parameter Optimizer_index_cost_adj
- optimizer_index_cost_adj
- Show the implicit parameter for each instance.
- SQLException: Parameter metadata not available for the given statement
- Parameter metadata not available for the given statement
- No value was provided for the parameter ‘appIdName’ when provisioning
- No value was provided for the parameter ‘appIdName’ when provisioning
- About Modifying the SERVICE_NAMES Parameter for Oracle RAC
- What is the read parameter in @ViewChild for
- SQLException:Parameter metadata not available for the given statement
- Maven: Could not get the value for parameter encoding for plugin execution default-resources Plugin
- CoreException: Could not get the value for parameter compilerId for plugin execution default-compile
- Could not get the value for parameter encoding for plugin execution default-resources Plugin org.apa
- CoreException: Could not get the value for parameter compilerId for plugin execution default-compile
- Could Not Get the Value for Parameter compilerId for Plugin Execution
- Maven: Could not get the value for parameter encoding for plugin execution default-resources Plugin
- Could not get the value for parameter compilerId for plugin execution default-compile
- CoreException: Could not get the value for parameter compilerId for plugin execution default-compile
- 宣言
- 一个用来验证身份证号码的JS
- 收藏
- 树上剩几只鸟--老问题,新答案
- [转]英文表白
- For the parameter Optimizer_index_cost_adj
- 分页控件之分页算法 —— for SQL Server 版。百万级的数据只需要15毫秒到900毫秒
- Web3D技术
- 中美教育的对比与反思
- 在 ASP.NET 中执行 URL 重写
- 校长开会心得!
- JSP:自定义标签——开发实例[4]
- WINDOWS XP安装SQL2000方法
- GDAL_DATA环境变量的设置