ITPub博客

首页 > 数据库 > Oracle > [20140116]视图?隐式转换?sql优化问题.txt

[20140116]视图?隐式转换?sql优化问题.txt

原创 Oracle 作者:lfree 时间:2014-01-17 11:04:50 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6290115