电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ

瀏覽:162日期:2023-11-15 14:52:01
Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ Tom Kyte的新書Effective Oracle by Design的第6章 Getting the Most Out of the Cost-Based Optimizer中介紹了參數OPTIMIZER_INDEX_COST_ADJ,并認為可以理解為Oracle執行多塊(MultiBlock)I/O(比如全表掃描)的代價與執行單塊(Single-block)I/O代價的相對比例。Tom指出,9i下該參數缺省值為100,意即二者擁有相同的代價,若減小,則CBO傾向于使用索引(即單塊I/O),反之則傾向于全表掃描(多塊I/O)。Tom同樣給出了一個不錯的例子。 在自己的手提上試驗了一下,硬盤大小原因,只裝了一個Oracle 10g(10.1.0.2),誰知就發現該參數在10g下取值發生了改變。我們先來看一下參照Tom的實驗: A. 創建數據表。 SQL> drop table t1; 表已刪除。 SQL> drop table t2; 表已刪除。 SQL> create table t1 2; as 3; select mod(rownum,1000) id,rpad('x',300,'x') data 4; from all_objects 5; where rownum<=5000; 表已創建。 SQL> ed 已寫入 file afiedt.buf 1; create table t2 2; as 3; select rownum id,rpad('x',300,'x') data 4; from all_objects 5* where rownum<=1000 SQL> / 表已創建。 B.創建索引并分析。 SQL> create index idx_t1 on t1(id); 索引已創建。 SQL> create index idx_t2 on t2(id); 索引已創建。 SQL> ed 已寫入 file afiedt.buf 1; begin 2;dbms_stats.gather_table_stats 3;(user,'T1',method_opt=>'for all indexed columns',cascade=>true); 4;dbms_stats.gather_table_stats 5;(user,'T2',method_opt=>'for all indexed columns',cascade=>true); 6* end; SQL> / PL/SQL 過程已成功完成。 C.查詢缺省值,并設置好環境。 SQL> set autot off SQL> show parameters optimizer_index_cost_adj; NAME TYPE;;;;;VALUE; ------------------------------------ ----------- ------------------------------ optimizer_index_cost_adj;;integer;;100; SQL> set autot traceonly eXP stat; D.在缺省值下查詢的結果。 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000; );; 1;0TABLE Access (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Card; =1 Bytes=100) 2;1;;NESTED LOOPS (Cost=13 Card=5 Bytes=1000); 3;2;;;;TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3 Card=5 Bytes=500);; 4;3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=; 5); 5;2;;;;INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1); 統計信息 ----------------------------------------------------------;;; 367; recursive calls; 0; db block gets;;; 101; consistent gets; 0; physical reads;; 0; redo size; 1507; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 12; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;;; E.修改參數值,注重,引用Tom的結論,9i中這個值在0-100之間,而10g呢? SQL> alter session set optimizer_index_cost_adj=0; ERROR: ORA-00068: 值 0 對參數 optimizer_index_cost_adj 無效, 必須在 1 和 10000 之間 SQL> alter session set optimizer_index_cost_adj=1; 會話已更改。 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000); 1;0TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card; =1 Bytes=100) 2;1;;NESTED LOOPS (Cost=2 Card=5 Bytes=1000) 3;2;;;;TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1 Card=5 Bytes=500);; 4;3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=; 5); 5;2;;;;INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1); 統計信息 ----------------------------------------------------------;;; 1; recursive calls; 0; db block gets;;; 48; consistent gets; 0; physical reads;; 0; redo size; 1507; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 0; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;; SQL> alter session set optimizer_index_cost_adj=50; 會話已更改。 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=5 Bytes=1000); 1;0TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card; =1 Bytes=100) 2;1;;NESTED LOOPS (Cost=7 Card=5 Bytes=1000) 3;2;;;;TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=2 Card=5 Bytes=500);; 4;3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=; 5); 5;2;;;;INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1); 統計信息 ----------------------------------------------------------;;; 1; recursive calls; 0; db block gets;;; 48; consistent gets; 0; physical reads;; 0; redo size; 1507; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 0; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;; F.繼續修改參數值,改大。 SQL> alter session set optimizer_index_cost_adj=200; 會話已更改。 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=5 Bytes=1000; );; 1;0TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card; =1 Bytes=100) 2;1;;NESTED LOOPS (Cost=26 Card=5 Bytes=1000); 3;2;;;;TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=6 Card=5 Bytes=500);; 4;3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=; 5); 5;2;;;;INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1); 統計信息 ----------------------------------------------------------;;; 1; recursive calls; 0; db block gets;;; 48; consistent gets; 0; physical reads;; 0; redo size; 1507; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 0; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;; SQL> alter session set optimizer_index_cost_adj=500; 會話已更改。 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=5 Bytes=1000; );; 1;0TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=10 Car; d=1 Bytes=100);;;;; 2;1;;NESTED LOOPS (Cost=63 Card=5 Bytes=1000); 3;2;;;;TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 By; tes=500);;; 4;2;;;;INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1); 統計信息 ----------------------------------------------------------;;; 1; recursive calls; 0; db block gets;;; 90; consistent gets; 0; physical reads;; 0; redo size; 1507; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 0; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;; SQL> alter session set optimizer_index_cost_adj=1000; 會話已更改。 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000; );; 1;0HASH JOIN (Cost=66 Card=5 Bytes=1000);;;; 2;1;;TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte; s=500);;;;; 3;1;;TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt; es=2600);;; 統計信息 ----------------------------------------------------------;;; 1; recursive calls; 0; db block gets;;; 271; consistent gets; 213; physical reads;; 0; redo size; 1651; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 0; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;; SQL> alter session set optimizer_index_cost_adj=10000; 會話已更改。 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000; );; 1;0HASH JOIN (Cost=66 Card=5 Bytes=1000);;;; 2;1;;TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte; s=500);;;;; 3;1;;TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt; es=2600);;; 統計信息 ----------------------------------------------------------;;; 1; recursive calls; 0; db block gets;;; 271; consistent gets; 0; physical reads;; 0; redo size; 1651; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 0; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;; G.現在看看對性能的影響。 SQL> set timing on SQL> alter session set optimizer_index_cost_adj=100; 會話已更改。 已用時間:; 00: 00: 00.00 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 已用時間:; 00: 00: 00.02 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000; );; 1;0TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Card; =1 Bytes=100) 2;1;;NESTED LOOPS (Cost=13 Card=5 Bytes=1000); 3;2;;;;TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3 Card=5 Bytes=500);; 4;3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=; 5); 5;2;;;;INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1); 統計信息 ----------------------------------------------------------;;; 0; recursive calls; 0; db block gets;;; 48; consistent gets; 0; physical reads;; 0; redo size; 1507; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 0; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;; SQL> alter session set optimizer_index_cost_adj=1; 會話已更改。 已用時間:; 00: 00: 00.00 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 已用時間:; 00: 00: 00.02 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000); 1;0TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card; =1 Bytes=100) 2;1;;NESTED LOOPS (Cost=2 Card=5 Bytes=1000) 3;2;;;;TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1 Card=5 Bytes=500);; 4;3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=; 5); 5;2;;;;INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1); 統計信息 ----------------------------------------------------------;;; 0; recursive calls; 0; db block gets;;; 48; consistent gets; 0; physical reads;; 0; redo size; 1507; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 0; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;; SQL> alter session set optimizer_index_cost_adj=10000; 會話已更改。 已用時間:; 00: 00: 00.00 SQL> ed 已寫入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 已用時間:; 00: 00: 00.03 執行計劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000; );; 1;0HASH JOIN (Cost=66 Card=5 Bytes=1000);;;; 2;1;;TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte; s=500);;;;; 3;1;;TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt; es=2600);;; 統計信息 ----------------------------------------------------------;;; 0; recursive calls; 0; db block gets;;; 271; consistent gets; 0; physical reads;; 0; redo size; 1651; bytes sent via SQL*Net to client 523; bytes received via SQL*Net from client;; 3; SQL*Net roundtrips to/from client; 0; sorts (memory);; 0; sorts (disk);;;; 30; rows processed;; 來具體分析一下。 首先,10g中OPTIMIZER_INDEX_COST_ADJ的取值范圍發生了改變。9i中為0-100,而10g中為1-10000。 其次,由于測試環境的不同,Tom的測試結果是在缺省值(100)的環境下,就已經和上面取值500時一樣了,即對T2全表掃描而T1使用索引。Tom試驗中,減小取值直至0,訪問路徑就變成使用兩個索引,而并不會出現均不使用索引的情況。除去系統的不同(可能導致取缺省值時訪問路徑是否一致),只看變化趨勢,顯然10g中靈活性更高,1-10000的取值使得CBO可以覆蓋所有的訪問路徑。另一方面,正如Tom的結論所說,OPTIMIZER_INDEX_COST_ADJ的取值越大,優化器越傾向于使用全表掃描,取值越小,優化器越傾向于使用索引。 再次,我們對比相同訪問路徑下的不同點。在取值從1變化到200(1-50-100-200)的過程中,優化器計算出的代價是持續增長的,而從1000到10000則是不變的。這說明這個參數與索引I/O的代價有關,而和全表掃描并無關系,這與Tom所說的并不矛盾,不過顯然更精確一點。 最后我們其實應該看到,雖然有如上所說的代價變化問題,同一訪問路徑下實際的運行性能并無區別,由于數據量比較小,上面的例子也許不能很好的說明這一點,不過想想Oracle用相同的路徑去執行,也沒有理由不同性能吧。 好,來看看官方文檔吧。10G的官方文檔(Reference)中對這個參數描述如下: OPTIMIZER_INDEX_COST_ADJ Parameter type Integer;參數類型為整數 Default value 100 缺省值為100 Modifiable ALTER SESSION, ALTER SYSTEM; 可通過ALTER SESSION, ALTER SYSTEM來修改 Range of values 1 to 10000取值范圍是1-10000 OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, TO MAKE THE OPTIMIZER MORE OR LESS PRONE TO SELECTING AN INDEX ACCESS PATH OVER A FULL TABLE SCAN. The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal. Note: The adjustment does not apply to user-defined cost functions for domain indexes. 注重:調整對用戶為域索引自定義的代價函數無效。 描述的第一段正證實了參數的作用。第二段值得注重,正如Tom所說(我覺得假如他把原文versus前后顛倒一下會更好,即單塊I/O代價比之多塊I/O),參數表達了索引訪問代價對比普通(表掃描)代價的比值。不過還有一個疑問我暫時還沒辦法想通,什么條件下索引I/O居然比掃描慢100倍(取值10000)? 結論: OPTIMIZER_INDEX_COST_ADJ通過指明索引I/O代價與掃描全表I/O代價的相對比值來影響CBO的行為,取值越小,CBO越傾向于使用索引,取值越大,越傾向于全表掃描。而缺省值100,指明缺省下,二者的代價是相等。與9i不同的是,10g中OPTIMIZER_INDEX_COST_ADJ的取值范圍從0-100改為1-10000,相信是更合理的取值(至少按照文檔的說明,作為代價的比值,取0是不合適的,除非說索引I/O的代價相對于全表掃描I/O代價是無窮小的)。
標簽: Oracle 數據庫
主站蜘蛛池模板: 济南网站建设_济南网站制作_济南网站设计_济南网站建设公司_富库网络旗下模易宝_模板建站 | 氟塑料磁力泵-不锈钢离心泵-耐腐蚀化工泵厂家「皖金泵阀」 | 东莞市超赞电子科技有限公司 全系列直插/贴片铝电解电容,电解电容,电容器 | 印刷人才网 印刷、包装、造纸,中国80%的印刷企业人才招聘选印刷人才网! | 工业PH计|工业ph酸度计|在线PH计价格-合肥卓尔仪器仪表有限公司 济南画室培训-美术高考培训-山东艺霖艺术培训画室 | 郑州外墙清洗_郑州玻璃幕墙清洗_郑州开荒保洁-河南三恒清洗服务有限公司 | 山东螺杆空压机,烟台空压机,烟台开山空压机-烟台开山机电设备有限公司 | 山东钢衬塑罐_管道_反应釜厂家-淄博富邦滚塑防腐设备科技有限公司 | 青岛成人高考_山东成考报名网 | 微信聊天记录恢复_手机短信删除怎么恢复_通讯录恢复软件下载-快易数据恢复 | 臭氧老化试验箱,高低温试验箱,恒温恒湿试验箱,防水试验设备-苏州亚诺天下仪器有限公司 | BAUER减速机|ROSSI-MERSEN熔断器-APTECH调压阀-上海爱泽工业设备有限公司 | 伸缩器_伸缩接头_传力接头-巩义市润达管道设备制造有限公司 | 交通气象站_能见度检测仪_路面状况监测站- 天合环境科技 | 通风天窗,通风气楼,屋顶通风天窗,屋顶通风天窗公司 | 桨叶搅拌机_螺旋挤压/方盒旋切造粒机厂家-无锡市鸿诚输送机械有限公司 | 干式变压器厂_干式变压器厂家_scb11/scb13/scb10/scb14/scb18干式变压器生产厂家-山东科锐变压器有限公司 | 水冷式工业冷水机组_风冷式工业冷水机_水冷螺杆冷冻机组-深圳市普威机械设备有限公司 | 低压载波电能表-单相导轨式电能表-华邦电力科技股份有限公司-智能物联网综合管理平台 | 设定时间记录电子秤-自动累计储存电子秤-昆山巨天仪器设备有限公司 | 螺旋绞龙叶片,螺旋输送机厂家,山东螺旋输送机-淄博长江机械制造有限公司 | 手表腕表维修保养鉴定售后服务中心网点 - 名表维修保养 | 健康管理师报名入口,2025年健康管理师考试时间信息网-网站首页 塑料造粒机「厂家直销」-莱州鑫瑞迪机械有限公司 | 驾驶人在线_专业学车门户网站 | 仿古建筑设计-仿古建筑施工-仿古建筑公司-汉匠古建筑设计院 | 【ph计】|在线ph计|工业ph计|ph计厂家|ph计价格|酸度计生产厂家_武汉吉尔德科技有限公司 | 玻璃钢格栅盖板|玻璃钢盖板|玻璃钢格栅板|树篦子-长沙川皖玻璃钢制品有限公司 | 双舌接地线-PC68数字式高阻计-ZC36|苏海百科| 优秀的临床医学知识库,临床知识库,医疗知识库,满足电子病历四级要求,免费试用 | 拉力测试机|材料拉伸试验机|电子拉力机价格|万能试验机厂家|苏州皖仪实验仪器有限公司 | 注塑模具_塑料模具_塑胶模具_范仕达【官网】_东莞模具设计与制造加工厂家 | 便携式XPDM露点仪-在线式防爆露点仪-增强型烟气分析仪-约克仪器 冰雕-冰雪世界-大型冰雕展制作公司-赛北冰雕官网 | 精密钢管,冷拔精密无缝钢管,精密钢管厂,精密钢管制造厂家,精密钢管生产厂家,山东精密钢管厂家 | 稳尚教育加盟-打造高考志愿填报平台_新高考志愿填报加盟_学业生涯规划加盟 | 【灵硕展览集团】展台展会设计_展览会展台搭建_展览展示设计一站式服务公司 | 塑料异型材_PVC异型材_封边条生产厂家_PC灯罩_防撞扶手_医院扶手价格_东莞市怡美塑胶制品有限公司 | 冷却塔厂家_冷却塔维修_冷却塔改造_凉水塔配件填料公司- 广东康明节能空调有限公司 | 深圳3D打印服务-3D打印加工-手板模型加工厂-悟空打印坊 | ★店家乐|服装销售管理软件|服装店收银系统|内衣店鞋店进销存软件|连锁店管理软件|收银软件手机版|会员管理系统-手机版,云版,App | 圆形振动筛_圆筛_旋振筛_三次元振动筛-河南新乡德诚生产厂家 | 成都亚克力制品,PVC板,双色板雕刻加工,亚克力门牌,亚克力标牌,水晶字雕刻制作-零贰捌广告 |