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

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

Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ

瀏覽:161日期: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 數據庫
主站蜘蛛池模板: 深圳激光打标机_激光打标机_激光焊接机_激光切割机_同体激光打标机-深圳市创想激光科技有限公司 深圳快餐店设计-餐饮设计公司-餐饮空间品牌全案设计-深圳市勤蜂装饰工程 | 钢格板_钢格栅_格栅板_钢格栅板 - 安平县鑫拓钢格栅板厂家 | 石英砂矿石色选机_履带辣椒色选机_X光异物检测机-合肥幼狮光电科技 | 贴片电容-贴片电阻-二三极管-国巨|三星|风华贴片电容代理商-深圳伟哲电子 | 刘秘书_你身边专业的工作范文写作小秘书 | 液晶拼接屏厂家_拼接屏品牌_拼接屏价格_监控大屏—北京维康 | 100_150_200_250_300_350_400公斤压力空气压缩机-舰艇航天配套厂家 | 杭州代理记账费用-公司注销需要多久-公司变更监事_杭州福道财务管理咨询有限公司 | 厚壁钢管-厚壁无缝钢管-小口径厚壁钢管-大口径厚壁钢管 - 聊城宽达钢管有限公司 | 南方珠江-南方一线电缆-南方珠江科技电缆-南方珠江科技有限公司 南汇8424西瓜_南汇玉菇甜瓜-南汇水蜜桃价格 | 沙盘模型公司_沙盘模型制作公司_建筑模型公司_工业机械模型制作厂家 | 假肢-假肢价格-假肢厂家-河南假肢-郑州市力康假肢矫形器有限公司 | 桂林腻子粉_内墙外墙抗裂砂浆腻子粉推荐广西鑫达涂料厂家供应 | 深圳工程师职称评定条件及流程_深圳职称评审_职称评审-职称网 | 对照品_中药对照品_标准品_对照药材_「格利普」高纯中药标准品厂家-成都格利普生物科技有限公司 澳门精准正版免费大全,2025新澳门全年免费,新澳天天开奖免费资料大全最新,新澳2025今晚开奖资料,新澳马今天最快最新图库 | 27PR跨境电商导航 | 专注外贸跨境电商 | 无锡门窗-系统门窗-阳光房-封阳台-断桥铝门窗厂[窗致美] | 中式装修设计_室内中式装修_【云臻轩】中式设计机构 | 冷却塔改造厂家_不锈钢冷却塔_玻璃钢冷却塔改造维修-广东特菱节能空调设备有限公司 | 深圳公司注册-工商注册公司-千百顺代理记账公司 | 集装箱展厅-住人集装箱住宿|建筑|房屋|集装箱售楼处-山东锐嘉科技工程有限公司 | ★济南领跃标识制作公司★济南标识制作,标牌制作,山东标识制作,济南标牌厂 | 低气压试验箱_高低温低气压试验箱_低气压实验箱 |林频试验设备品牌 | 日本SMC气缸接头-速度控制阀-日本三菱伺服电机-苏州禾力自动化科技有限公司 | 袋式过滤器,自清洗过滤器,保安过滤器,篮式过滤器,气体过滤器,全自动过滤器,反冲洗过滤器,管道过滤器,无锡驰业环保科技有限公司 | 等离子表面处理机-等离子表面活化机-真空等离子清洗机-深圳市东信高科自动化设备有限公司 | 氟氨基酮、氯硝柳胺、2-氟苯甲酸、异香兰素-新晨化工 | 低气压试验箱_高低温低气压试验箱_低气压实验箱 |林频试验设备品牌 | 护腰带生产厂家_磁石_医用_热压护腰_登山护膝_背姿矫正带_保健护具_医疗护具-衡水港盛 | 聚天冬氨酸,亚氨基二琥珀酸四钠,PASP,IDS - 远联化工 | 楼承板-钢筋楼承板-闭口楼承板-无锡优贝斯楼承板厂 | 影像测量仪_三坐标测量机_一键式二次元_全自动影像测量仪-广东妙机精密科技股份有限公司 | 美的商用净水器_美的直饮机_一级代理经销商_Midea租赁价格-厂家反渗透滤芯-直饮水批发品牌售后 | 水稻烘干机,小麦烘干机,大豆烘干机,玉米烘干机,粮食烘干机_巩义市锦华粮食烘干机械制造有限公司 水环真空泵厂家,2bv真空泵,2be真空泵-淄博真空设备厂 | 渣土车电机,太阳能跟踪器电机,蜗轮蜗杆减速电机厂家-淄博传强电机 | 老城街小面官网_正宗重庆小面加盟技术培训_特色面馆加盟|牛肉拉面|招商加盟代理费用多少钱 | 沈阳网站建设_沈阳网站制作_沈阳网页设计-做网站就找示剑新零售 沈阳缠绕膜价格_沈阳拉伸膜厂家_沈阳缠绕膜厂家直销 | 卸料器-卸灰阀-卸料阀-瑞安市天蓝环保设备有限公司 | 全自动包衣机-无菌分装隔离器-浙江迦南科技股份有限公司 | 洁净化验室净化工程_成都实验室装修设计施工_四川华锐净化公司 | 塑胶跑道施工-硅pu篮球场施工-塑胶网球场建造-丙烯酸球场材料厂家-奥茵 |