[20140116]视图?隐式转换?sql优化问题.txt
最近一直在优化单位的垃圾数据库,这个数据库可以讲是一个垃圾工程.在有优化的过程遇到视图中存在隐式转化问题,在我的测试环境模
拟出来,提出解决方案:
1.建立测试环境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
--说明:我们生产系统10.2.0.4. 这个版本在使用PUSH_PRED会遇到问题.先在11.2.0.3上测试.
--参考链接:http://blog.itpub.net/267265/viewspace-1065675/
create table t11 pctfree 99 pctused 1 as select cast(rownum+1e4 as varchar2(5)) id,rownum idx,rpad('t11',80,'a') name from dual connect by level<=1e4;
create table t21 pctfree 99 pctused 1 as select rownum+1e4 id,rpad('t21',80,'c') name from dual connect by level<=1e4;
create table t22 pctfree 99 pctused 1 as select cast(rownum+1e4 as varchar2(5)) id,rpad('t22',80,'d') name from dual connect by level<=1e4;
create index i_t11_id on t11(id);
create index i_t11_idx on t11(idx);
create index i_t21_id on t21(id);
create index i_t22_id on t22(id);
exec dbms_stats.gather_table_stats(user, 'T11', method_opt=>'for all columns size 1 ',no_invalidate => false);
exec dbms_stats.gather_table_stats(user, 'T21', method_opt=>'for all columns size 1 ',no_invalidate => false);
exec dbms_stats.gather_table_stats(user, 'T22', method_opt=>'for all columns size 1 ',no_invalidate => false);
create view v_t2 as
select to_char(t21.id) id,t21.name from t21
union all
select t22.id id,t22.name from t22;
create view v_t2x as
select t22.id id,t22.name from t22
union all
select to_char(t21.id) id,t21.name from t21;
--说明:T11的字段ID是字符型的.T21的ID字段是number.T22的ID字段是字符型的.
2.测试例子:
SCOTT@test> alter session set statistics_level=all;
Session altered.
SCOTT@test> Select t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2 where t11.id=v_t2.id and t11.idx=42;
ID IDX C10
----- ---------- ----------
10042 42 t21ccccccc
10042 42 t22ddddddd
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 260jb3mu5a1nh, child number 0
-------------------------------------
Select t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2 where
t11.id=v_t2.id and t11.idx=42
Plan hash value: 3705598605
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5509 (100)| 2 |00:00:00.28 | 20023 | | | |
|* 1 | HASH JOIN | | 1 | 20000 | 5509 (1)| 2 |00:00:00.28 | 20023 | 1206K| 1206K| 678K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| T11 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 | | | |
|* 3 | INDEX RANGE SCAN | I_T11_IDX | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 | | | |
| 4 | VIEW | V_T2 | 1 | 20000 | 5506 (1)| 20000 |00:00:00.21 | 20020 | | | |
| 5 | UNION-ALL | | 1 | | | 20000 |00:00:00.18 | 20020 | | | |
| 6 | TABLE ACCESS FULL | T21 | 1 | 10000 | 2753 (1)| 10000 |00:00:00.05 | 10010 | | | |
| 7 | TABLE ACCESS FULL | T22 | 1 | 10000 | 2753 (1)| 10000 |00:00:00.04 | 10010 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("T11"."IDX"))
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_HASH(@"SEL$1" "V_T2"@"SEL$1")
FULL(@"SEL$3" "T22"@"SEL$3")
FULL(@"SEL$2" "T21"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T11"."ID"="V_T2"."ID")
3 - access("T11"."IDX"=42)
--可以发现执行计划全表扫描T21,T22.至少T11不行(存在隐式转换),T22应该可以使用索引.
--加入提示PUSH_PRED:
Select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" 1)
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("T11"."IDX"))
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_HASH(@"SEL$1" "V_T2"@"SEL$1")
FULL(@"SEL$3" "T22"@"SEL$3")
FULL(@"SEL$2" "T21"@"SEL$2")
END_OUTLINE_DATA
*/
t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2 where t11.id=v_t2.id and t11.idx=42;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dva7nmmkuwqn4, child number 0
-------------------------------------
Select /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3')
ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1") OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" 1) INDEX_RS_ASC(@"SEL$1"
"T11"@"SEL$1" ("T11"."IDX")) NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_HASH(@"SEL$1" "V_T2"@"SEL$1") FULL(@"SEL$3" "T22"@"SEL$3")
FULL(@"SEL$2" "T21"@"SEL$2") END_OUTLINE_DATA */
t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2 where
t11.id=v_t2.id and t11.idx=42
Plan hash value: 3875113017
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5508 (100)| 2 |00:00:00.07 | 20024 |
| 1 | NESTED LOOPS | | 1 | 2 | 5508 (1)| 2 |00:00:00.07 | 20024 |
| 2 | TABLE ACCESS BY INDEX ROWID | T11 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | I_T11_IDX | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 |
| 4 | VIEW | V_T2 | 1 | 1 | 5506 (1)| 2 |00:00:00.07 | 20020 |
| 5 | UNION ALL PUSHED PREDICATE | | 1 | | | 2 |00:00:00.07 | 20020 |
|* 6 | TABLE ACCESS FULL | T21 | 1 | 1 | 2753 (1)| 1 |00:00:00.04 | 10010 |
|* 7 | TABLE ACCESS FULL | T22 | 1 | 1 | 2753 (1)| 1 |00:00:00.03 | 10010 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T11"."IDX"=42)
6 - filter(TO_CHAR("T21"."ID")="T11"."ID")
7 - filter("T22"."ID"="T11"."ID")
--可以发现第6,7步,存在过滤条件,这样修改提示应该可以走索引,至少第7步是可以的.修改如下:
Select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" 1)
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("T11"."IDX"))
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_HASH(@"SEL$1" "V_T2"@"SEL$1")
INDEX_RS_ASC(@"SEL$3" "T22"@"SEL$3" ("T22"."ID"))
FULL(@"SEL$2" "T21"@"SEL$2")
END_OUTLINE_DATA
*/
t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2 where t11.id=v_t2.id and t11.idx=42;
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8zk5jf55xbuyy, child number 0
-------------------------------------
Select /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3')
ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1") OUTLINE_LEAF(@"SEL$1")
PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" 1) INDEX_RS_ASC(@"SEL$1"
"T11"@"SEL$1" ("T11"."IDX")) NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_HASH(@"SEL$1" "V_T2"@"SEL$1") INDEX_RS_ASC(@"SEL$3"
"T22"@"SEL$3" ("T22"."ID")) FULL(@"SEL$2" "T21"@"SEL$2")
END_OUTLINE_DATA */ t11.id,t11.idx,substr(v_t2.name,1,10) c10 from
t11,v_t2 where t11.id=v_t2.id and t11.idx=42
Plan hash value: 1459454479
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2757 (100)| 2 |00:00:00.04 | 10017 |
| 1 | NESTED LOOPS | | 1 | 2 | 2757 (1)| 2 |00:00:00.04 | 10017 |
| 2 | TABLE ACCESS BY INDEX ROWID | T11 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | I_T11_IDX | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 |
| 4 | VIEW | V_T2 | 1 | 1 | 2755 (1)| 2 |00:00:00.04 | 10013 |
| 5 | UNION ALL PUSHED PREDICATE | | 1 | | | 2 |00:00:00.04 | 10013 |
|* 6 | TABLE ACCESS FULL | T21 | 1 | 1 | 2753 (1)| 1 |00:00:00.04 | 10010 |
| 7 | TABLE ACCESS BY INDEX ROWID| T22 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
|* 8 | INDEX RANGE SCAN | I_T22_ID | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SET$5715CE2E")
PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("T11"."IDX"))
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_NL(@"SEL$1" "V_T2"@"SEL$1")
INDEX_RS_ASC(@"SEL$B01C6807" "T22"@"SEL$3" ("T22"."ID"))
FULL(@"SEL$639F1A6F" "T21"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T11"."IDX"=42)
6 - filter(TO_CHAR("T21"."ID")="T11"."ID")
8 - access("T22"."ID"="T11"."ID")
--可以使用索引,注意取出outline的变化.第8步限制实际上是access,而不是原来的filter,nested loop变成了hash连接,而且取出的outline发生了许多变化.
--仔细看如果建立T21的TO_CHAR("T21"."ID")函数索引,问题会迎刃而解.
--建立函数索引.
SCOTT@test> create index if_t21_id on t21(to_char(id));
Index created.
--先确定如何加入函数索引的提示:
select * from T21 where to_char(id)='10042' ;
SCOTT@test> @dpc '' outline
...
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T21"@"SEL$1" "IF_T21_ID")
END_OUTLINE_DATA
*/
---
--修改提示使用索引:
Select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SET$5715CE2E")
PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("T11"."IDX"))
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_NL(@"SEL$1" "V_T2"@"SEL$1")
INDEX_RS_ASC(@"SEL$B01C6807" "T22"@"SEL$3" ("T22"."ID"))
INDEX_RS_ASC(@"SEL$639F1A6F" "T21"@"SEL$2" "IF_T21_ID")
END_OUTLINE_DATA
*/
t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2 where t11.id=v_t2.id and t11.idx=42;
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2aa2k0h3c30m3, child number 0
-------------------------------------
Select /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3') ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F") OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SET$5715CE2E") PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1"
1) OUTLINE_LEAF(@"SEL$1") OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3") OUTLINE(@"SET$1") OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("T11"."IDX"))
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1") LEADING(@"SEL$1" "T11"@"SEL$1"
"V_T2"@"SEL$1") USE_NL(@"SEL$1" "V_T2"@"SEL$1")
INDEX_RS_ASC(@"SEL$B01C6807" "T22"@"SEL$3" ("T22"."ID"))
INDEX_RS_ASC(@"SEL$639F1A6F" "T21"@"SEL$2" "IF_T21_ID")
END_OUTLINE_DATA */ t11.id,t11.idx,substr(v_t2.name,1,10) c10 from
t11,v_t2 where t11.id=v_t2.id and t11.idx=42
Plan hash value: 1604650597
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5508 (100)| 2 |00:00:00.19 | 20024 |
| 1 | NESTED LOOPS | | 1 | 20000 | 5508 (1)| 2 |00:00:00.19 | 20024 |
| 2 | TABLE ACCESS BY INDEX ROWID| T11 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | I_T11_IDX | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 |
|* 4 | VIEW | V_T2 | 1 | 20000 | 5506 (1)| 2 |00:00:00.19 | 20020 |
| 5 | UNION-ALL | | 1 | | | 20000 |00:00:00.17 | 20020 |
| 6 | TABLE ACCESS FULL | T21 | 1 | 10000 | 2753 (1)| 10000 |00:00:00.04 | 10010 |
| 7 | TABLE ACCESS FULL | T22 | 1 | 10000 | 2753 (1)| 10000 |00:00:00.04 | 10010 |
-----------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("T11"."IDX"))
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_NL(@"SEL$1" "V_T2"@"SEL$1")
FULL(@"SEL$3" "T22"@"SEL$3")
FULL(@"SEL$2" "T21"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T11"."IDX"=42)
4 - filter("T11"."ID"="V_T2"."ID")
--昏,回头了.不能使用索引.
--使用OLD_PUSH_PRED看看.这个提示最后参数是视图里最后一个表第2个表的字段.
Select /*+ OPT_PARAM('_optimizer_push_pred_cost_based' 'false') */
t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2 where t11.id=v_t2.id and t11.idx=42;
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b847gs40drdr2, child number 0
-------------------------------------
Select /*+ OPT_PARAM('_optimizer_push_pred_cost_based' 'false') */
t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2 where
t11.id=v_t2.id and t11.idx=42
Plan hash value: 4289706985
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| 2 |00:00:00.01 | 11 | 4 |
| 1 | NESTED LOOPS | | 1 | 2 | 5 (0)| 2 |00:00:00.01 | 11 | 4 |
| 2 | TABLE ACCESS BY INDEX ROWID | T11 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 4 | 0 |
|* 3 | INDEX RANGE SCAN | I_T11_IDX | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 | 0 |
| 4 | VIEW | V_T2 | 1 | 2 | 3 (0)| 2 |00:00:00.01 | 7 | 4 |
| 5 | UNION-ALL PARTITION | | 1 | | | 2 |00:00:00.01 | 7 | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID| T21 | 1 | 100 | 41 (0)| 1 |00:00:00.01 | 4 | 4 |
|* 7 | INDEX RANGE SCAN | IF_T21_ID | 1 | 40 | 1 (0)| 1 |00:00:00.01 | 3 | 4 |
| 8 | TABLE ACCESS BY INDEX ROWID| T22 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 | 0 |
|* 9 | INDEX RANGE SCAN | I_T22_ID | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 | 0 |
----------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$661FCD0D")
OUTLINE_LEAF(@"SEL$A8E2213E")
OUTLINE_LEAF(@"SET$AD7CC163")
OLD_PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" ("T22"."ID"))
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("T11"."IDX"))
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_NL(@"SEL$1" "V_T2"@"SEL$1")
INDEX_RS_ASC(@"SEL$A8E2213E" "T22"@"SEL$3" ("T22"."ID"))
INDEX_RS_ASC(@"SEL$661FCD0D" "T21"@"SEL$2" "IF_T21_ID")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T11"."IDX"=42)
7 - access("T21"."SYS_NC00003$"="T11"."ID")
9 - access("T22"."ID"="T11"."ID")
--说明: OUTLINE_LEAF(@"SEL$661FCD0D") 里面的东西与前面的执行计划不同,我不懂这些东西.我原来直接改上面的提示行不通.
--第7步执行计划的E_ROWS估计不正确,这个是因为没有分析函数索引列.
--使用v_t2x(两个表对调的视图)视图看看执行计划:
exec DBMS_STATS.GATHER_TABLE_STATS (user,'T21',Method_Opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1 ',No_Invalidate=> FALSE);
Select /*+ OPT_PARAM('_optimizer_push_pred_cost_based' 'false') */
t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2x v_t2 where t11.id=v_t2.id and t11.idx=42
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c9jjy1futnf7a, child number 0
-------------------------------------
Select /*+ OPT_PARAM('_optimizer_push_pred_cost_based' 'false') */
t11.id,t11.idx,substr(v_t2.name,1,10) c10 from t11,v_t2x v_t2 where
t11.id=v_t2.id and t11.idx=42
Plan hash value: 3799939397
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| 2 |00:00:00.01 | 11 |
| 1 | NESTED LOOPS | | 1 | 2 | 5 (0)| 2 |00:00:00.01 | 11 |
| 2 | TABLE ACCESS BY INDEX ROWID | T11 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | I_T11_IDX | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 |
| 4 | VIEW | V_T2X | 1 | 2 | 3 (0)| 2 |00:00:00.01 | 7 |
| 5 | UNION-ALL PARTITION | | 1 | | | 2 |00:00:00.01 | 7 |
| 6 | TABLE ACCESS BY INDEX ROWID| T22 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | I_T22_ID | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 3 |
| 8 | TABLE ACCESS BY INDEX ROWID| T21 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
|* 9 | INDEX RANGE SCAN | IF_T21_ID | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$661FCD0D")
OUTLINE_LEAF(@"SEL$A8E2213E")
OUTLINE_LEAF(@"SET$AD7CC163")
OLD_PUSH_PRED(@"SEL$1" "V_T2"@"SEL$1" ("T21"."SYS_NC00003$"))
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("T11"."IDX"))
NO_ACCESS(@"SEL$1" "V_T2"@"SEL$1")
LEADING(@"SEL$1" "T11"@"SEL$1" "V_T2"@"SEL$1")
USE_NL(@"SEL$1" "V_T2"@"SEL$1")
INDEX_RS_ASC(@"SEL$A8E2213E" "T21"@"SEL$3" "IF_T21_ID")
INDEX_RS_ASC(@"SEL$661FCD0D" "T22"@"SEL$2" ("T22"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T11"."IDX"=42)
7 - access("T22"."ID"="T11"."ID")
9 - access("T21"."SYS_NC00003$"="T11"."ID")
--可以发现提示OLD_PUSH_PRED使用的最后参数是("T21"."SYS_NC00003$").也就是视图里面的最后一个表的字段.
--而这个对应的就是T21的隐含字段to_char(id).
SCOTT@test> column data_default format a30
SCOTT@test> select column_name,data_type,data_length,data_default from dba_tab_cols where owner=user and table_name='T21';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT
-------------------- ---------- ----------- ------------------------------
ID NUMBER 22
NAME VARCHAR2 80
SYS_NC00003$ VARCHAR2 40 TO_CHAR("ID")
总结:
1.累!还是累!
2.数据结构的问题,准确的讲最好不要选择这样的方式来解决.
3.看来数据结构的设计很重要,我们现在的系统存在大量隐式转换,程序代码number与varchar2混用.DBA早期介入开发,能够及早的发现问
题,现在要修改这个数据库异常困难.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1072028/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1072028/