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

最新推荐文章于 2022-08-25 10:36:18 发布
最新推荐文章于 2022-08-25 10:36:18 发布
阅读量106 收藏
点赞数

[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/

cuixun7780
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Bug 10190002: POOR PERFORMANCE WITH /*+ OPT_PARAM('_OPTIMIZER_PUSH_PRED_COST_BASED','TRUE')*/ _optim...
ruilinruirui
04-18 1930
Recently I’ve met many long running issues that due to hidden paraleter _optimizer_extend_jppd_view_types set to true.I have open an SR (3-3225436011) to oracle, and oracle said that it’s a bug Bug ...
sql隐式转换_SQL Server中的隐式转换
culuo4781的博客
07-28 1925
sql隐式转换This article will provide an overview of SQL Server implicit conversion including data type precedence and conversion tables, implicit conversion examples and means to detect occurrences of imp...
SQL之视图的使用
博客已搬家，请访问新博客https://kswapd.cn，提供更好的阅读体验。
05-17 358
视图 视图是虚拟的表，只包含使用动态检索数据的查询。 利用视图可以一次性的编写基础SQL，然后根据需要多此调用。 Access不支持视图，MYSQL 5 以后版本支持视图，SQLite只支持只读视图。视图的作用 简化复杂的SQL操作，重用SQL语句。 使用表的一部分，而不是使用整个表。 保护数据，可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。 更改显示的数据格式和表示，视图可
MySQL
qq_59942266的博客
08-25 3165
MySQL的学习
分析MySQL中隐式转换导致查询结果错误及索引不可用
zdp787的博客
06-20 431
原文作者： SEian.G 原文链接：http://www.seiang.com/?p=387 在日常的工作中经常会遇到客户反馈，针对一个等值查询，为什么查询出来的结果是错误的呢？而此刻我的内存独白是：一定是sql语句写的有问题呗，找我干啥？当然了，这也就是开玩笑，客户是上帝啊，客户虐我千万遍，我待客户如初恋！接下来肯定就是收集相关的信息，比如建表语句，SQL语句，查询结果等； 下面针对客户所反馈...
[20150705]使用use_concat提示.txt
07-06 89
[20150703]使用use_concat提示.txt --生产系统有一条sql语句遇到性能问题,由于生产系统语句非常复杂,我拿测试用户scott的表作为例子来说明: 1.问题提出: SCOTT@test01p> ...
mysql性能优化.SQL语句性能优化
12-04
MySQL性能优化是数据库管理中的重要环节，特别是在处理大量数据时，高效的SQL语句能显著提升系统性能。以下是一些关于SQL语句性能优化的关键知识点： 1. **LIMIT使用**： LIMIT子句常用于限制查询返回的结果数量，...
SqlServer之性能优化-索引与隐式转化
cheng_samll_baby的博客
06-05 625
一.索引基础知识汇总 聚集索引：也叫做聚簇索引，索引中的叶子节点就是数据节点。 非聚集索引：索引的逻辑顺序与磁盘物理存储的顺序不同，一个数据表中可以有多个非聚集索引。 聚集索引实际存放示意图 从上图中可以看出聚集索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据。 非聚集索引实际存放示意图 从上图可以看到，非聚集索引的叶子节点仍旧为索引列，并通过对应的指针指向对应数据块。如果在实际的查询过程中，如果执行计划命中了该索引，但如果查询列中包含了该索引没有覆盖到的列，那么
8种最坑的SQL错误用法.pdf
09-03
当SQL语句中的查询变量与字段定义类型不匹配时，数据库会进行隐式转换，可能导致索引失效。确保字段类型与查询值匹配，避免框架自动填充时产生性能问题。 3. **关联更新和删除操作**： MySQL的物化特性主要针对...
SQL编写规范.doc
07-15
- **数据类型一致性**：避免数据类型的隐式转换，尤其是当列数据类型为varchar类型时，确保条件内容也符合相应类型。 - **表达式优化**：避免在WHERE子句中使用包含表中列的表达式进行比较，而应尽量将计算放到...
HINT避免MERGE JOIN/*+ OPT_PARAM(\'_optimizer_mjc_enabled\',\'false/true\')
Daniel's Blog
12-17 2941
--alter session set “_optimizer_ignore_hints”=TRUE;  -- select /*+ opt_param(‘parallel_execution_enabled’, ‘false’) */ .. --swap_join_input(tab) hint to swap the order of the tables going through t
【OUTLINE】使用Oracle Outline技术暂时锁定SQL的执行计划
cuanchuwei1207的博客
09-08 230
Oracle的Outline技术可以在特殊情况下保证执行计划的稳定性。在极端情况下可以使用此项技术实现暂时锁定执行计划的目的。 主要使用场景如下： ①短时间内无法完成SQL的优化任务，此时可以使用outline暂时锁定S...
1014OPT_PARAM OPTIMIZER_FEATURES_ENABLE
10-14 382
[20141014]OPT_PARAM与OPTIMIZER_FEATURES_ENABLE.txt http://www.itpub.net/thread-1495845-1-1.html http://space.it...
浅析oracle执行计划
renyanjie123的博客
03-26 617
Oracle 执行计划 一、什么是执行计划 Oracle执行计划是Oracle为了执行某些SQL而生成的执行步骤的组合。这些执行计划是由Oracle优化器通过具体参数配合和被访问对象统计信息等内容来生成的一系列具体的执行步骤，然后优化器选择一个最优的执行步骤作为这一条语句的执行计划。正如之前讨论所述执行计划是优化器通过划分CPU和内存在Oracle中生成的一个执行步骤，这种执行计划通常是以pl...
记一次sql查询慢的坑（存在查询条件隐式转换）
dzyweer的博客
11-08 980
今天在做功能的时候。很慢 sql语句 也正常啊，没毛病 原sql  我贴一下 SELECT * FROM `goods` WHERE `sku` = 12454207 LIMIT 1; 查看执行时间是30多秒  起初猜测是 索引问题 后面发现建立啦，   最后我找到原因了，我先查看这个sku字段的类型 发现是varchar 上图中的值 是int类型   于是 我就想到 会不会是隐式...
oracle各模块是什么意思,执行计划中各字段各模块描述-Oracle
weixin_39918145的博客
04-04 300
在SQL语句的执行计划中，包含很多字段项和很多模块，其不同字段代表了不同的含义且在不同的情形下某些字段、模块显示或不显示，下面的描述给出了执行计划中各字段的含义以及各模块的描述。有关执行计划中各字段模块的描述请参考： 执行计划中各字段各模块描述有关由SQL语句来获取执行计划请参考：使用EXPLAIN PLAN获取SQL语句执行计划有关使用autotrace来获取执行计划请参考：启用AUTOTRAC...
push_pred用法
cotchte0421的博客
08-01 969
create table hao1 as select * from dba_objects;create table hao2 as select * from dba_objects;create table hao3 as...
2024影视泛系统,CMS苹果V10二开影视泛程序,苹果影视泛目录.txt
09-02
后台地址：http://域名/shensss.php/admin/index/login.html 账号和密码：admin 数据库：lao 密码pFSGtemHShMTszhB 数据库文件:根目录/application/database.php 伪静态： if (!-e $request_filename) { rewrite ^/index.php(.*)$ /index.php?s=$1 last; rewrite ^/api.php(.*)$ /api.php?s=$1 last; rewrite ^/shensss.php(.*)$ /shensss.php?s=$1 last; rewrite ^(.*)$ /index.php?s=$1 last; break; } rewrite ^/vod-(.*)$ /index.php?m=vod-$1 break; rewrite ^/art-(.*)$ /index.php?m=art-$1 break; rewrite ^/gbook-(.*)$ /index.php?m=gbook-$1 bre
JSP001网络购物系统毕业课程设计源码
最新发布
09-02
编号:34 实现技术： Java + Sqlserver 包括网站前端用户和后台管理员。用户注册登录后可以根据商品类别和关键字查询商品，可以加入商品到购物车，下单购买，查询订单，修改个人信息，给管理员留言，查询销售排行榜和友情链接等；管理员登录后台后可以添加商品和管理商品，查询管理用户订单信息，查询管理员用户信息，添加友情链接，公告信息管理等。
Oracle SQL优化：避免索引列类型转换的影响
SQL语句会经过一系列的处理，包括语法和语义分析，视图和表达式的转换，以及选择合适的优化器（如基于规则的RULE、基于成本的COST或选择性的CHOOSE）。优化器会根据查询的特性决定是全表扫描还是通过索引访问数据，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值