[20140210]一条sql语句的优化(11g).txt

 

[20140210]一条sql语句的优化(11g).txt

今天下午看生产系统数据库,无意中发现一个错误,同时优化也有点小问题,写一个测试脚本。

1.建立测试环境:
SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t pctfree 99 as
select rownum id ,mod(rownum,200) idx,trunc(sysdate)-dbms_random.value(0,200) cr_date ,rpad(rownum,1000,'x') vc from dual connect by level

create unique index pk_t on t (id);
create index i_t_idx on t(idx);
create index i_t_cr_date on t(cr_date);

exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true) ;

2.有问题语句:

SCOTT@test> column vc noprint
SCOTT@test> select * from t where cr_date = ( select max(cr_date) from t where idx=42);
        ID        IDX CR_DATE
---------- ---------- -------------------
      7842         42 2014-02-06 09:57:35

--很明显这样查询结果目前是正确的,但是实际上如果我修改如下:
update t set cr_date='2014-02-06 09:57:35' where id=1e4;
commit ;
--注意我这里取了一个巧,直接使用cr_date='2014-02-06 09:57:35',只要nls*相关参数正确,一般没有问题。
--再次执行:
SCOTT@test> select * from t where cr_date = ( select max(cr_date) from t where idx=42);
        ID        IDX CR_DATE
---------- ---------- -------------------
      7842         42 2014-02-06 09:57:35
     10000          0 2014-02-06 09:57:35

--很明显,开发需要是IDX=42的记录,而不需要第2条。只不过日期相重的概率很少罢了。BTW,我已经不止一次发现这样类似的错误。
--至少这样写才正确:

SCOTT@test> alter session set statistics_level=all;
SCOTT@test> select * from t where cr_date = ( select max(cr_date) from t where idx=42) and idx=42;
        ID        IDX CR_DATE
---------- ---------- -------------------
      7842         42 2014-02-06 09:57:35

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  77hz50d71h2s9, child number 0
-------------------------------------
select * from t where cr_date = ( select max(cr_date) from t where
idx=42) and idx=42
Plan hash value: 2661465193
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |      1 |        |    32 (100)|      1 |00:00:00.06 |      39 |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T                |      1 |      1 |     2   (0)|      1 |00:00:00.06 |      39 |       |       |          |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                  |      1 |        |            |      1 |00:00:00.06 |      38 |       |       |          |
|   3 |    BITMAP AND                    |                  |      1 |        |            |      1 |00:00:00.06 |      38 |       |       |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                  |      1 |        |            |      1 |00:00:00.06 |      36 |       |       |          |
|*  5 |      INDEX RANGE SCAN            | I_T_CR_DATE      |      1 |      1 |     1   (0)|      2 |00:00:00.04 |      36 |       |       |          |
|   6 |       SORT AGGREGATE             |                  |      1 |      1 |            |      1 |00:00:00.04 |      34 |       |       |          |
|*  7 |        VIEW                      | index$_join$_002 |      1 |     50 |    30   (4)|     50 |00:00:00.04 |      34 |       |       |          |
|*  8 |         HASH JOIN                |                  |      1 |        |            |     50 |00:00:00.04 |      34 |  1096K|  1096K| 1580K (0)|
|*  9 |          INDEX RANGE SCAN        | I_T_IDX          |      1 |     50 |     1   (0)|     50 |00:00:00.01 |       2 |       |       |          |
|  10 |          INDEX FAST FULL SCAN    | I_T_CR_DATE      |      1 |     50 |    35   (0)|  10000 |00:00:00.01 |      32 |       |       |          |
|  11 |     BITMAP CONVERSION FROM ROWIDS|                  |      1 |        |            |      1 |00:00:00.01 |       2 |       |       |          |
|* 12 |      INDEX RANGE SCAN            | I_T_IDX          |      1 |      1 |     1   (0)|     50 |00:00:00.01 |       2 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CR_DATE"=)
   7 - filter("IDX"=42)
   8 - access(ROWID=ROWID)
   9 - access("IDX"=42)
  12 - access("IDX"=42)

--这样写,执行计划看上去很复杂。逻辑读39,实际上我的索引很小,生产系统不会选择INDEX FAST FULL SCAN I_T_CR_DATE 。

--生产系统实际上是这样
SCOTT@test> select /*+ index(t i_t_idx) */ * from t where cr_date = ( select /*+ index(t i_t_idx) */ max(cr_date) from t where idx=42) and idx=42;
        ID        IDX CR_DATE
---------- ---------- -------------------
      7842         42 2014-02-06 09:57:35

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  60p18ha8myc2j, child number 0
-------------------------------------
select /*+ index(t i_t_idx) */ * from t where cr_date = ( select /*+
index(t i_t_idx) */ max(cr_date) from t where idx=42) and idx=42

Plan hash value: 1551695814

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |   102 (100)|      1 |00:00:00.01 |     105 |     59 |
|*  1 |  TABLE ACCESS BY INDEX ROWID  | T       |      1 |      1 |    51   (0)|      1 |00:00:00.01 |     105 |     59 |
|*  2 |   INDEX RANGE SCAN            | I_T_IDX |      1 |     50 |     1   (0)|     50 |00:00:00.01 |       3 |      0 |
|   3 |   SORT AGGREGATE              |         |      1 |      1 |            |      1 |00:00:00.01 |      52 |     58 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T       |      1 |     50 |    51   (0)|     50 |00:00:00.01 |      52 |     58 |
|*  5 |     INDEX RANGE SCAN          | I_T_IDX |      1 |     50 |     1   (0)|     50 |00:00:00.01 |       2 |      0 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CR_DATE"=)
   2 - access("IDX"=42)
   5 - access("IDX"=42)
--很明显这样逻辑读有点高,到达105.主要的问题要使用索引范围扫描2次(I_T_IDX).

==更正如下:实际这样执行:
SCOTT@test> select /*+ index(t i_t_cr_date) */ * from t where cr_date = ( select /*+ index(t i_t_idx) */ max(cr_date) from t where idx=42) and idx=42;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a8kx1xu0c7cmh, child number 1
-------------------------------------
select /*+ index(t i_t_cr_date) */ * from t where cr_date = ( select
/*+ index(t i_t_idx) */ max(cr_date) from t where idx=42) and idx=42
Plan hash value: 2838288168
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |    54 (100)|      1 |00:00:00.01 |      57 |    181 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | T           |      1 |      1 |     3   (0)|      1 |00:00:00.01 |      57 |    181 |
|*  2 |   INDEX RANGE SCAN             | I_T_CR_DATE |      1 |      1 |     1   (0)|      2 |00:00:00.01 |      55 |    180 |
|   3 |    SORT AGGREGATE              |             |      1 |      1 |            |      1 |00:00:00.01 |      52 |    164 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T           |      1 |     50 |    51   (0)|     50 |00:00:00.01 |      52 |    164 |
|*  5 |      INDEX RANGE SCAN          | I_T_IDX     |      1 |     50 |     1   (0)|     50 |00:00:00.01 |       2 |     16 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("IDX"=42)
   2 - access("CR_DATE"=)
   5 - access("IDX"=42)
===========================

select * from t where (idx,cr_date ) in ( select idx, max(cr_date) from t where idx=42 group by idx ) ;
--也不是很好。
select * from (select * from t where idx=42 order by cr_date desc) where rownum

--建立索引
create index i_t_idx_cr_date on t(idx,cr_date);


SCOTT@test> select * from t where cr_date = ( select max(cr_date) from t where idx=42) and idx=42;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  77hz50d71h2s9, child number 0
-------------------------------------
select * from t where cr_date = ( select max(cr_date) from t where
idx=42) and idx=42
Plan hash value: 3430031104
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |     4 (100)|      1 |00:00:00.01 |       6 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T               |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       6 |      1 |
|*  2 |   INDEX RANGE SCAN             | I_T_IDX_CR_DATE |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       5 |      1 |
|   3 |    SORT AGGREGATE              |                 |      1 |      1 |            |      1 |00:00:00.01 |       2 |      1 |
|   4 |     FIRST ROW                  |                 |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       2 |      1 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| I_T_IDX_CR_DATE |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       2 |      1 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("IDX"=42 AND "CR_DATE"=)
   5 - access("IDX"=42)

SCOTT@test> select * from (select * from t where idx=42 order by cr_date desc) where rownumSCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  19pf4scday9pv, child number 0
-------------------------------------
select * from (select * from t where idx=42 order by cr_date desc)
where rownumPlan hash value: 2332835607
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |     4 (100)|      1 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY                 |                 |      1 |        |            |      1 |00:00:00.01 |       3 |
|   2 |   VIEW                         |                 |      1 |      2 |     4   (0)|      1 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T               |      1 |     50 |     4   (0)|      1 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN DESCENDING| I_T_IDX_CR_DATE |      1 |      2 |     2   (0)|      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM   4 - access("IDX"=42)

--这样写最好,当然前提是不会返回多行,好像实际不会出现这种情况。
--这样逻辑读确实下降不少,但是不是我需要,因为建立的索引有些冗余了。
drop index i_t_idx_cr_date ;

SCOTT@test> select * from (select * from t where idx=42 order by cr_date desc) where rownumSCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  19pf4scday9pv, child number 0
-------------------------------------
select * from (select * from t where idx=42 order by cr_date desc)
where rownumPlan hash value: 587900075
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |    52 (100)|      1 |00:00:00.02 |      52 |       |       |          |
|*  1 |  COUNT STOPKEY                 |         |      1 |        |            |      1 |00:00:00.02 |      52 |       |       |          |
|   2 |   VIEW                         |         |      1 |     50 |    52   (2)|      1 |00:00:00.02 |      52 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY       |         |      1 |     50 |    52   (2)|      1 |00:00:00.02 |      52 | 73728 | 73728 |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T       |      1 |     50 |    51   (0)|     50 |00:00:00.02 |      52 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | I_T_IDX |      1 |     50 |     1   (0)|     50 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM   3 - filter(ROWNUM   5 - access("IDX"=42)

--这样仅仅扫描i_t_idx一次。比原来逻辑读少一半。

总结:
1.修改语句如下:
select * from (select * from t where idx=42 order by cr_date desc) where rownum2.比较好的解决方式是建立idx,cr_date的复合索引。删除idx的索引。

上一篇:.Net常用的工具小结


下一篇:Linux学习笔记(压缩工具gzip,bzip2,xz)