1.optimizer_mode: -all_rows:优化器寻找在最短的时间内完成整个语句的执行计划(返回所有行:默认的) -first_rows_N:N为(1,10,100或者1000) -first_rows(n):n为任意正整数,优化器通过第一个join order来估算返回行数,目的在于最小的的资源返回整个数据片段的执行计划,从9i引入 -first_rows:在最短的时间内返回执行计划的第一行 2.CPU成本 CPU Cost= (SRds*sreadtim + MRds*mreadtim + CPUCycles/cpuspeed) /sreadtim 其中 -SRds: 单块读数量 -SRdstim: 单块读时间 -MRds: 多块读数量 -mreatim: 多块读时间 -CPUCycles: CPU Cycles数量 -cpuspeed: CPU Cycles每秒 (MHZ) 3.常见hint使用 3.1不带任何hint,使用INDEX RANGE SCAN select * from t1 where modded = 0 ; Execution Plan ---------------------------------------------------------- Plan hash value: 3052882459 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 33264 | 35 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 33 | 33264 | 35 | |* 2 | INDEX RANGE SCAN | T1_MOD | 33 | | 1 | ---------------------------------------------------------------------- 3.2 带order by id,使用INDEX FULL SCAN select * from t1 where modded = 0 order by id ; Execution Plan ---------------------------------------------------------- Plan hash value: 2004371487 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 33264 | 1450 | |* 1 | TABLE ACCESS BY INDEX ROWID | T1 | 33 | 33264 | 1450 | | 2 | INDEX FULL SCAN | T1_PK | 10000 | | 21 | --------------------------------------------------------------------- 3.3 no_merge hint (merge不仅仅是view,可以是table) select * from ( select /*+ no_merge */ * from t1 where modded = 0 ) order by id ; Execution Plan ---------------------------------------------------------- Plan hash value: 1453079715 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 33924 | 36 (3)| 00:00:01 | | 1 | SORT ORDER BY | | 33 | 33924 | 36 (3)| 00:00:01 | | 2 | VIEW | | 33 | 33924 | 35 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T1 | 33 | 33264 | 35 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | T1_MOD | 33 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- 3.3.1不使用no_merge hint,少了VIEW聚合 Execution Plan ---------------------------------------------------------- Plan hash value: 2068790959 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 33264 | 36 (3)| 00:00:01 | | 1 | SORT ORDER BY | | 33 | 33264 | 36 (3)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 33 | 33264 | 35 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_MOD | 33 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- 3.4.带有no_index(table_name,index_name) hint select /*+ no_index(t1,t1_pk) */ * from t1 where modded = 0 order by id ; Execution Plan ---------------------------------------------------------- Plan hash value: 2068790959 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 33264 | 59 | | 1 | SORT ORDER BY | | 33 | 33264 | 59 | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 33 | 33264 | 35 | |* 3 | INDEX RANGE SCAN | T1_MOD | 33 | | 1 | ----------------------------------------------------------------------- 4. full hint全表 select /*+ full(t1) */ * from t1 where modded=0 order by id; Execution Plan ---------------------------------------------------------- Plan hash value: 2148421099 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33 | 33264 | 391 (1)| 00:00:05 | | 1 | SORT ORDER BY | | 33 | 33264 | 391 (1)| 00:00:05 | |* 2 | TABLE ACCESS FULL| T1 | 33 | 33264 | 390 (1)| 00:00:05 | --------------------------------------------------------------------------- 5._complex_view_merging _complex_view_merging = true(先join[merge]再group by) _complex_view_merging = false(先group by 再join[merge]) rem The manual (9.2 Perf Guide p.2-37) points out that rem when a view contains one of the following structures, rem it can be merged into a referencing query block only if rem complex view merging is enabled: rem A GROUP BY clause rem A DISTINCT operator in the select list rem With _complex_view_merging = true, we join then group by rem With _complex_view_merging = false, we group by then join 5.1.没有加hint select t1.vc1, avg_val_t1 from t1, avg_val_view where t1.vc2 = lpad(18,32) and avg_val_view.id_par = t1.id_par ; Execution Plan ---------------------------------------------------------- Plan hash value: 1541385322 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 23 | 2024 | 26 | | 1 | HASH GROUP BY | | 23 | 2024 | 26 | | 2 | NESTED LOOPS | | 32 | 2816 | 6 | | 3 | TABLE ACCESS FULL | T2 | 1024 | 7168 | 5 | |* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 81 | 1 | |* 5 | INDEX UNIQUE SCAN | T1_PK | 1 | | | 5.2 "_complex_view_merging"=true alter session set "_complex_view_merging"=true; Execution Plan ---------------------------------------------------------- Plan hash value: 1541385322 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 23 | 2024 | 26 | | 1 | HASH GROUP BY | | 23 | 2024 | 26 | | 2 | NESTED LOOPS | | 32 | 2816 | 6 | | 3 | TABLE ACCESS FULL | T2 | 1024 | 7168 | 5 | |* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 81 | 1 | |* 5 | INDEX UNIQUE SCAN | T1_PK | 1 | | | ----------------------------------------------------------------------- (id=3,4先merge join,之后id=1 最后group by) 5.3 no_merge hint select /*+ no_merge (avg_val_view) */ t1.vc1, avg_val_t1 from t1, avg_val_view where t1.vc2 = lpad(18,32) and avg_val_view.id_par = t1.id_par ; Execution Plan ---------------------------------------------------------- Plan hash value: 661622897 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 95 | 27 | | 1 | NESTED LOOPS | | 1 | 95 | 27 | | 2 | VIEW | AVG_VAL_VIEW | 32 | 832 | 26 | | 3 | HASH GROUP BY | | 32 | 224 | 26 | | 4 | TABLE ACCESS FULL | T2 | 1024 | 7168 | 5 | |* 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 69 | 1 | |* 6 | INDEX UNIQUE SCAN | T1_PK | 1 | | | ----------------------------------------------------------------------------- id=3,5先group然后view,没有进行merge 5.4 "_complex_view_merging"=false alter session set "_complex_view_merging"=false; select t1.vc1, avg_val_t1 from t1, avg_val_view where t1.vc2 = lpad(18,32) and avg_val_view.id_par = t1.id_par ; ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 95 | 27 | | 1 | NESTED LOOPS | | 1 | 95 | 27 | | 2 | VIEW | AVG_VAL_VIEW | 32 | 832 | 26 | | 3 | HASH GROUP BY | | 32 | 224 | 26 | | 4 | TABLE ACCESS FULL | T2 | 1024 | 7168 | 5 | |* 5 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 69 | 1 | |* 6 | INDEX UNIQUE SCAN | T1_PK | 1 | | | ----------------------------------------------------------------------------- id=3,5先group by然后 merge
