[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
create table t21 pctfree 99 pctused 1 as select rownum+1e4 id,rpad('t21',80,'c') name from dual connect by level
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

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早期介入开发,能够及早的发现问
题,现在要修改这个数据库异常困难.

时间： 2024-09-23 00:58:54

[20140116]视图?隐式转换?sql优化问题.txt的相关文章

Oracle的隐式转换

都说Oracle存在NUMBER和VARCHAR2类型的隐式转换,严格意义上需要避免,但为何需要避免,从下面的实验进行验证. 1. 创建测试表和索引 create table tn (id number, name varchar2(1)); create index idx_tn on tn (id); create index idx_tn on tn (name); 分别对NUMBER类型的id字段,VARCHAR2类型的name字段创建索引. 2. 查看VARCHAR2->NUMBER的

Oracle隐式转换会影响物化视图查询重写

今天有人问我一个物化视图查询重写的问题,最后发现问题其实和物化视图的功能没有多大的关系,而是隐式转换导致的问题. 还是通过例子来说明这个问题: SQL> create table t ( 2  id number, 3  time date, 4  other varchar2(4000)) 5  partition by range (time) 6  (partition p1 values less than (to_date('2008-1-1', 'yyyy-mm-dd')), 7  

RDS SQL Server - 专题分享 - 巧用执行计划缓存之数据类型隐式转换

摘要 SQL Server数据库基表数据类型隐式转换,会导致Index Scan或者Clustered Index Scan的问题,这篇文章分享如何巧用执行计划缓存来发现数据类型隐式转换的查询语句,从而可以有针对性的优化查询,解决高CPU使用率的问题. 问题引入 测试环境 为了更好的展示从执行计划缓存缓存中找出导致数据类型转化的查询语句,我们先建立测试环境. -- Create testing database IF DB_ID('TestDb') IS NULL CREATE DATABASE

SQL Server 隐式转换引发的躺枪死锁-程序员需知

原文:SQL Server 隐式转换引发的躺枪死锁-程序员需知 在SQL Server的应用开发过程(尤其是二次开发)中可能由于开发人员对表的结构不够了解,造成开发过程中使用了不合理的方式造成数据库引擎未按预定执行,以致影响业务.这是非常值得注意的.这次为大家介绍由于隐式数据类型转换而造成的死锁及相应解决方案. 现实中有些程序员/数据库开发者会根据数据库的处理机制实现一些应用,如抢座应用,可能会对事务中的查询加一些列的Hint以细化粒度,实现应用的同时使得影响最低,但也有可能因为一些小细节的欠缺

SQL Server中提前找到隐式转换提升性能的办法

原文:SQL Server中提前找到隐式转换提升性能的办法     http://www.cnblogs.com/shanksgao/p/4254942.html 高兄这篇文章很好的谈论了由于数据隐式转换造成执行计划不准确,从而造成了死锁.那如果在事情出现之前发现了这类潜在的风险岂不是更好?     那么我们来看一个简单的例子,如代码清单1所示.   1: SELECT * 2: FROM HumanResources.Employee 3: WHERE NationalIDNumber = 2

ORACLE绑定变量隐式转换导致性能问题

   年后一次系统升级后,监控数据库的工具DPA发现数据库的Total Wait时间突然飙增,如下截图所示,数据库的总体等待时间对比升级前飙增了非常多 另 外就是发现出现了较多的等待事件,主要有latch: cache buffers chains. latch: shared pool .db file scattered read.根据这边的监控发现TOP SQL里面从升级前的0次变为了一天的一万多次(有些甚至更多),分析过后我们就找开发人员了解一下系统升级变跟的内容和改动 开 发人员坚定的

从Java的类型转换看MySQL和Oracle中的隐式转换(二)

说起数据类型转换,在开发中如此,在数据库中也是如此,之前简单对比过MySQL和Oracle的数据类型转换情况,可以参见MySQL和Oracle中的隐式转换 http://blog.itpub.net/23718752/viewspace-1787973/ 不过当时写完之后,有个读者随口问了一句为什么,为什么呢?似乎自己还是一知半解,说是规则,无规矩不成方圆,倒也无可非议,不过我觉得还是要再看看,看看还能有哪些收获,接下来的内容我就不能保证正确性了,希望大家明辨,也希望提出意见,毕竟就是希望把问题

Scala入门到精通——第十九节 隐式转换与隐式参数（二）

作者:摇摆少年梦 配套视频地址:http://www.xuetuwuyou.com/course/12 本节主要内容 隐式参数中的隐式转换 函数中隐式参数使用概要 隐式转换问题梳理 1. 隐式参数中的隐式转换 前一讲中,我们提到函数中如果存在隐式参数,在使用该函数的时候如果不给定对应的参数,则编译器会自动帮我们搜索相应的隐式值,并将该隐式值作为函数的参数,这里面其实没有涉及到隐式转换,本节将演示如何利用隐式参数进行隐式转换,下面的代码给定的是一个普通的比较函数: object ImplicitP

Scala入门到精通——第十八节 隐式转换与隐式参数（一）

本节主要内容 隐式转换简介 隐式转换函数 隐式转换规则 隐式参数 1. 隐式转换简介 在scala语言当中,隐式转换是一项强大的程序语言功能,它不仅能够简化程序设计,也能够使程序具有很强的灵活性.要想更进一步地掌握scala语言,了解其隐式转换的作用与原理是很有必要的,否则很难得以应手地处理日常开发中的问题. 在scala语言中,隐式转换是无处不在的,只不过scala语言为我们隐藏了相应的细节,例如scala中的类继承层次结构中: 它们存在固有的隐式转换,不需要人工进行干预,例如Float在必要