关于Optimizer_index_cost_adj参数的设置
Thomas建议:
对于许多系统,应到考虑设置这两个参数为非默认值,至少测试一下两种极端情形:
1. optimizer_index_caching=0 和 optimizer_index_cost_adj=100的默认值. 他们一般适用于许多数据仓库/报表系统
2. otpimizer_index_caching=90和optimizer_index_cost_adj=25的设置,他们一般适用于许多事物处理系统/oltp系统.
对于数据仓库和DSS系统要反复调整来取一个合理值。
Oracle在选择不同的访问路径时,会对全表扫描和索引扫描进行比较评估,在比较的时候,
Oracle会把索引扫描的成本转换为全表扫描的成本,和全表扫描的COST进行比较。这个转换需要一个转换因子,就是Optimizer_index_cost_adj;
Optimizer_index_cost_adj*(index scan cost)=等价的Full Scan cost
所以 optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost
在缺省情况下:Optimizer_index_cost_adj=100
SQL> show parameter optimizer_index_cost_adj
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL> create table t3 as select * from dba_objects;
Table created
SQL>
SQL> create index ind_owner on t3(owner);
Index created
SQL> analyze table t3 compute statistics;
SQL> set autotrace on
SQL> set timing on
SQL> set autotrace traceonly
SQL> alter session set optimizer_index_cost_adj=43;
Session altered.
select /*+ FULL(t3)*/* from bia_stg.t3 t3 where owner='BIA_STG';
322 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2574254479
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3044 | 255K| 130 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T3 | 3044 | 255K| 130 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='BIA_STG')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
739 consistent gets
0 physical reads
0 redo size
23185 bytes sent via SQL*Net to client
700 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
322 rows processed
SQL> select /*+ index(t3 ind_owner)*/* from bia_stg.t3 t3 where owner='BIA_STG';
322 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2790462862
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 3044 | 255K| 89 (0)| 0
0:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 3044 | 255K| 89 (0)| 0
0:00:02 |
|* 2 | INDEX RANGE SCAN | IND_OWNER | 3044 | | 8 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='BIA_STG')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
65 consistent gets
0 physical reads
0 redo size
23185 bytes sent via SQL*Net to client
700 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
322 rows processed
optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost* 100=130/89 * 100=1.460 * 100
=146
及optimizer_index_cost_adj调整为147
相关推荐
赠送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带破解,很好用的图片压缩工具,占用空间小,压缩质量很好,速度也很快
赠送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-...
ZendOptimizer_linux是linux下对zend加密后的php文件进行解密的工具
matlab_Gray Wolf Optimizer_GWO_优化器_灰狼优化器 GWO算法模仿自然界中灰狼的领导层级和狩猎机制。采用 alpha、beta、delta 和 omega 四种类型的灰狼来模拟领导层级。此外,还实现了狩猎、寻找猎物、包围猎物和...
windows7下Apache PHP MySQL phpMyAdmin Zend_Optimizer_安装配置教程
mysql查询优化,索引原理,各种查询优化教程。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。...
文档涉及到Oracle数据库内存参数调优。
Mu Online Game Server Optimizer. A very useful tools.
Internet Download Manager Optimizer
Auto Optimizer是安卓手机上的一款全自动型性能优化软件,用户只需要设定好相关参数,app就会在合适的时机去对手机进行清理优化,让手机时刻保持最优性能状态。 Auto Optimizer是一款安卓平台自动优化器应用。它可以...