[20140116]檢視?隱式轉換?sql優化問題.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- scala隱式轉換優先順序問題
- mysql隱式轉換問題MySql
- sql隱式轉換SQL
- [20220811]奇怪的隱式轉換問題.txt
- 隱式轉換影響物化檢視查詢重寫
- SQL Server資料庫中的資料型別隱式轉換問題SQLServer資料庫資料型別
- “no_merge”hints優化檢視訪問低效問題優化
- [20150611]優化sql遇到問題.txt優化SQL
- js顯式轉換和隱式轉換JS
- [20201214]查詢隱式轉換的sql語句.txtSQL
- 20201214]查詢隱式轉換的sql語句.txtSQL
- javascript 隱式轉換JavaScript
- Oracle 隱式轉換Oracle
- java隱式轉換Java
- [20181119]使用sql profile優化問題.txtSQL優化
- 檢視自動sql調優作業，最佳化sql訪問路徑SQL
- [20191106]隱式轉換.txt
- 【原創】由隱式轉換引起的資料庫效能問題資料庫
- Scala - 隱式轉換和隱式引數
- Scala隱式轉換與隱式引數
- SQL優化一則：取消檢視合併SQL優化
- 高效的SQL（隱式轉換導致不走索引）SQL索引
- 【隱式轉換】注意隱式轉換將導致索引無法使用索引
- [20220815]奇怪的隱式轉換問題(11g測試補充).txt
- [20220811]奇怪的隱式轉換問題(12c補充測試).txt
- 【SQL優化器查詢變換器】檢視合併(View Merging)SQL優化View
- Scala Essentials: 隱式轉換
- 【SQL】Oracle查詢轉換之檢視合併SQLOracle
- SQL優化--not in和or出的問題SQL優化
- SQL優化引出的問題（二）SQL優化
- SQL優化引出的問題（一）SQL優化
- 隱式型別轉換（SYS_OP_C2C）-記一次SQL調優型別SQL
- 快速定位隱蔽的sql效能問題及調優SQL
- 記錄一次SQL函式和優化的問題SQL函式優化
- 從一條問題SQL優化看SQL TransformationSQL優化ORM
- JavaScript隱式型別轉換JavaScript型別
- MySQL 隱式型別轉換MySql型別
- 【C++】禁止隱式轉換C++