ITPub博客

首页 > 数据库 > Oracle > [20201224]sql优化困惑.txt

[20201224]sql优化困惑.txt

原创 Oracle 作者:lfree 时间:2020-12-24 09:23:27 0 删除 编辑

[20201224]sql优化困惑.txt

--//昨天优化生产系统一条语句,不小心折腾一个下午,回家的路上才想起如何优化,自己一下子没有转过来,浪费大量的时间。

1.环境:
> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

> @ bind_cap 1n7yhk3p9cd66 ''
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from ( select huanzhexin0_.id as col_0_0_, huanzhexin0_.binglihao as col_1_0_, huanzhexin0_.xingming as col_2_0_, huanzhexin0_.xingbie as col_3_0_, huanzhexin0_.jtdz as col_4_0_, huanzhexin0_
.shengri as col_5_0_, jcd2_.id as col_6_0_, jiuzhen1_.id as col_7_0_, jcd2_.jcdh as col_8_0_, jcd2_.kd_time as col_9_0_, jcd2_.kdys as col_10_0_, (select yuangong3_.xingming from yuangong yuangong3_ w
here yuangong3_.gonghao=jcd2_.kdys) as col_11_0_, (select bumen4_.bmmc from bumen bumen4_ where bumen4_.id=jcd2_.kdks_id) as col_12_0_, jcd2_.kdks_id as col_13_0_, jcd2_.biaoti as col_14_0_, (select y
uangong5_.xingming from yuangong yuangong5_ where yuangong5_.gonghao=jcd2_.jcys) as col_15_0_, jcd2_.jcjs_time as col_16_0_ from huanzhexinxi huanzhexin0_, jiuzhen jiuzhen1_, jcd jcd2_ where huanzhexi
n0_.id=jiuzhen1_.huanzhe_id and jiuzhen1_.id=jcd2_.jiuzhen_id and jcd2_.biaoshi=:"SYS_B_0" and (jcd2_.state is null) order by jcd2_.jcjs_time ) where rownum <= :1

SQL_ID        CHILD_NUMBER WAS NAME                                         POSITION   MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- ---------------------------------------- ------------ ------------ ------------------- --------------- ------------
1n7yhk3p9cd66            0 YES :SYS_B_0                                            1           22 2020-07-21 11:26:31 NUMBER          56
                           YES :1                                                  2           22 2020-07-21 11:26:31 NUMBER          100
--//理论讲这样语句不会输出许多行,逻辑读也不会很高。猜测操作完成后修改state非NULL。

> @ d_buffer 1n7yhk3p9cd66 60
    EXECUTIONS1    BUFFER_GETS1   ELAPSED_TIME1 ROWS_PROCESSED1 每次buffer_gets    每次执行时间  平均处理记录数
--------------- --------------- --------------- --------------- --------------- --------------- ---------------
        2098552       576708129    360817107935           32134 274.81240827008  171936.2245658 .01531246306977

... sleep 60 , waiting ....

    EXECUTIONS2    BUFFER_GETS2   ELAPSED_TIME2 ROWS_PROCESSED2 每次buffer_gets    每次执行时间  平均处理记录数
--------------- --------------- --------------- --------------- --------------- --------------- ---------------
        2098562       576797449    360819016026           32134 274.85366122135 171936.31449821 .01531239010332

  总buffer_gets 每次buffer_gets        执行次数      总执行时间    每次执行时间    总处理记录数  平均处理记录数
--------------- --------------- --------------- --------------- --------------- --------------- ---------------
          89320            8932              10         1908091        190809.1               0               0

--//每分钟执行10次。又是一个经典的刷新语句。每次接近0.19秒。而且经常是输出记录为0.

--//执行计划如下:
Plan hash value: 1469246793
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name               | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                    |        |       |       |  4736 (100)|          |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID              | YUANGONG           |      1 |    13 |       |     2   (0)| 00:00:01 |       |       |          |
|*  2 |   INDEX RANGE SCAN                        | I_YUANGONG_GONGHAO |      1 |       |       |     1   (0)| 00:00:01 |  1025K|  1025K|          |
|   3 |  TABLE ACCESS BY INDEX ROWID              | BUMEN              |      1 |    14 |       |     1   (0)| 00:00:01 |       |       |          |
|*  4 |   INDEX UNIQUE SCAN                       | PK_BUMEN           |      1 |       |       |     0   (0)|          |  1025K|  1025K|          |
|   5 |  TABLE ACCESS BY INDEX ROWID              | YUANGONG           |      1 |    13 |       |     2   (0)| 00:00:01 |       |       |          |
|*  6 |   INDEX RANGE SCAN                        | I_YUANGONG_GONGHAO |      1 |       |       |     1   (0)| 00:00:01 |  1025K|  1025K|          |
|*  7 |  COUNT STOPKEY                            |                    |        |       |       |            |          |       |       |          |
|   8 |   VIEW                                    |                    |  46745 |   107M|       |  4736   (1)| 00:00:57 |       |       |          |
|*  9 |    SORT ORDER BY STOPKEY                  |                    |  46745 |  6893K|  7800K|  4736   (1)| 00:00:57 |  1024 |  1024 |          |
|* 10 |     HASH JOIN                             |                    |  46745 |  6893K|  4848K|  3276   (1)| 00:00:40 |  8577K|  2802K| 9729K (0)|
|  11 |      TABLE ACCESS STORAGE FULL FIRST ROWS | HUANZHEXINXI       |  75182 |  3964K|       |   370   (1)| 00:00:05 |  1025K|  1025K|          |
|* 12 |      HASH JOIN                            |                    |  46745 |  4427K|  3120K|  2430   (1)| 00:00:30 |  9672K|  4837K| 7815K (0)|
|  13 |       TABLE ACCESS STORAGE FULL FIRST ROWS| JIUZHEN            |    145K|  1416K|       |   372   (1)| 00:00:05 |  1025K|  1025K|          |
|* 14 |       TABLE ACCESS STORAGE FULL FIRST ROWS| JCD                |  46745 |  3971K|       |  1687   (1)| 00:00:21 |  1025K|  1025K|          |
----------------------------------------------------------------------------------------------------------------------------------------------------

2.优化过程略:
--//我加入的提示如下:
/*+ gather_plan_statistics
  leading(jcd2_ jiuzhen1_ huanzhexin0_)
  index(jcd2_ I_JCD_BIAOSHI_STATE_JCJS_TIME)
  index(jiuzhen1_ PK_JIUZHEN)
  index(huanzhexin0_ PK_HUANZHEXINXI)
  use_nl( jiuzhen1_)
  use_nl( huanzhexin0_ )
  cardinality(jcd2_ 200)
*/
--// I_JCD_BIAOSHI_STATE_JCJS_TIME 索引包含3个字段 BIAOSHI,STATE,JCJS_TIME。
--//如果我带入jcd2_.biaoshi=:"SYS_B_0"的值53,biaoshi=53,state is null很多,执行统计信息如下:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                               |      1 |        |       |  4214 (100)|          |    100 |00:00:00.44 |     164K|       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID      | YUANGONG                      |   6370 |      1 |    13 |     2   (0)| 00:00:01 |   6354 |00:00:00.02 |    1927 |       |       |          |
|*  2 |   INDEX RANGE SCAN                | I_YUANGONG_GONGHAO            |   6370 |      1 |       |     1   (0)| 00:00:01 |   6354 |00:00:00.01 |       3 |  1025K|  1025K|          |
|   3 |  TABLE ACCESS BY INDEX ROWID      | BUMEN                         |      6 |      1 |    14 |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       9 |       |       |          |
|*  4 |   INDEX UNIQUE SCAN               | PK_BUMEN                      |      6 |      1 |       |     0   (0)|          |      6 |00:00:00.01 |       3 |  1025K|  1025K|          |
|   5 |  TABLE ACCESS BY INDEX ROWID      | YUANGONG                      |     15 |      1 |    13 |     2   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |       |       |          |
|*  6 |   INDEX RANGE SCAN                | I_YUANGONG_GONGHAO            |     15 |      1 |       |     1   (0)| 00:00:01 |     14 |00:00:00.01 |       3 |  1025K|  1025K|          |
|*  7 |  COUNT STOPKEY                    |                               |      1 |        |       |            |          |    100 |00:00:00.44 |     164K|       |       |          |
|   8 |   VIEW                            |                               |      1 |    200 |   472K|  4214   (1)| 00:00:51 |    100 |00:00:00.44 |     164K|       |       |          |
|*  9 |    SORT ORDER BY STOPKEY          |                               |      1 |    200 | 30400 |  4214   (1)| 00:00:51 |    100 |00:00:00.44 |     164K| 22528 | 22528 |20480  (0)|
|  10 |     NESTED LOOPS                  |                               |      1 |    200 | 30400 |  4213   (1)| 00:00:51 |  48454 |00:00:00.39 |     162K|       |       |          |
|  11 |      NESTED LOOPS                 |                               |      1 |    200 | 30400 |  4213   (1)| 00:00:51 |  48454 |00:00:00.31 |     114K|       |       |          |
|  12 |       NESTED LOOPS                |                               |      1 |    200 | 19600 |  4013   (1)| 00:00:49 |  48454 |00:00:00.22 |   70013 |       |       |          |
|  13 |        TABLE ACCESS BY INDEX ROWID| JCD                           |      1 |    200 | 17600 |  3813   (1)| 00:00:46 |  48454 |00:00:00.07 |    5407 |       |       |          |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|* 14 |         INDEX RANGE SCAN          | I_JCD_BIAOSHI_STATE_JCJS_TIME |      1 |   9315 |       |    45   (0)| 00:00:01 |  48454 |00:00:00.02 |      97 |  1025K|  1025K|          |
|  15 |        TABLE ACCESS BY INDEX ROWID| JIUZHEN                       |  48454 |      1 |    10 |     1   (0)| 00:00:01 |  48454 |00:00:00.12 |   64606 |       |       |          |
|* 16 |         INDEX UNIQUE SCAN         | PK_JIUZHEN                    |  48454 |      1 |       |     0   (0)|          |  48454 |00:00:00.06 |   16152 |  1025K|  1025K|          |
|* 17 |       INDEX UNIQUE SCAN           | PK_HUANZHEXINXI               |  48454 |      1 |       |     0   (0)|          |  48454 |00:00:00.07 |   44430 |  1025K|  1025K|          |
|  18 |      TABLE ACCESS BY INDEX ROWID  | HUANZHEXINXI                  |  48454 |      1 |    54 |     1   (0)| 00:00:01 |  48454 |00:00:00.06 |   48454 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//实际上扫描jcd获得100条记录就可以停止,而实际读取48454行,如果控制呢?感觉oracle的优化器那里出了问题。

3.分析:
--//我在这里折腾很长时间,一直没有调整出来。后来我单独把表jcd拿出来执行,才知道问题在哪里。
alter session set statistics_level = all;
select * from (select id from jcd where biaoshi=53 and state is null order by jcjs_time) where rownum<=10;

Plan hash value: 1740280897
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                               |      1 |        |       |  1188 (100)|          |     10 |00:00:00.24 |    1070 |       |       |          |
|*  1 |  COUNT STOPKEY                    |                               |      1 |        |       |            |          |     10 |00:00:00.24 |    1070 |       |       |          |
|   2 |   VIEW                            |                               |      1 |   8191 |   103K|  1188   (1)| 00:00:15 |     10 |00:00:00.24 |    1070 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY          |                               |      1 |   8191 |   175K|  1188   (1)| 00:00:15 |     10 |00:00:00.24 |    1070 |  2048 |  2048 | 2048  (0)|
|*  4 |     VIEW                          | index$_join$_002              |      1 |   8191 |   175K|  1186   (1)| 00:00:15 |  48496 |00:00:00.22 |    1070 |       |       |          |
|*  5 |      HASH JOIN                    |                               |      1 |        |       |            |          |  48644 |00:00:00.21 |    1070 |  4967K|  3974K| 4037K (0)|
|*  6 |       INDEX RANGE SCAN            | I_JCD_BIAOSHI_STATE_JCJS_TIME |      1 |   8191 |   175K|   225   (0)| 00:00:03 |  48644 |00:00:00.01 |      98 |  1025K|  1025K|          |
|   7 |       INDEX STORAGE FAST FULL SCAN| PK_JCD                        |      1 |   8191 |   175K|  1200   (1)| 00:00:15 |    331K|00:00:00.06 |     972 |  1025K|  1025K|          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//实际读取48644行。id=6.我希望就是id=6,仅仅读取100行就ok了。怎么控制不住呢。浪费N多时间。
--//回家的路上才想起来,实际上问题出在order by的选择上。加入biaoshi,state就可以控制仅仅扫描很少的行。

select * from (select id from jcd where biaoshi=53 and state is null order by biaoshi,state,jcjs_time) where rownum<=10;
--//补充一点,只要order by 中jcjs_time不在第一的位置,执行计划都是一样的。

Plan hash value: 2856453538
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                               |      1 |        |       |     8 (100)|          |     10 |00:00:00.01 |      12 |       |       |          |
|*  1 |  COUNT STOPKEY                |                               |      1 |        |       |            |          |     10 |00:00:00.01 |      12 |       |       |          |
|   2 |   VIEW                        |                               |      1 |     10 |   130 |     8   (0)| 00:00:01 |     10 |00:00:00.01 |      12 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| JCD                           |      1 |     10 |   220 |     8   (0)| 00:00:01 |     10 |00:00:00.01 |      12 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I_JCD_BIAOSHI_STATE_JCJS_TIME |      1 |        |       |     3   (0)| 00:00:01 |     41 |00:00:00.01 |       4 |  1025K|  1025K|          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//这样就可以很好的控制逻辑读,扫描行数。
--//这样上面的语句修改如下:

SELECT *
  FROM (  SELECT /*+ gather_plan_statistics */
                huanzhexin0_.id AS col_0_0_
                ,huanzhexin0_.binglihao AS col_1_0_
                ,huanzhexin0_.xingming AS col_2_0_
                ,huanzhexin0_.xingbie AS col_3_0_
                ,huanzhexin0_.jtdz AS col_4_0_
                ,huanzhexin0_.shengri AS col_5_0_
                ,jcd2_.id AS col_6_0_
                ,jiuzhen1_.id AS col_7_0_
                ,jcd2_.jcdh AS col_8_0_
                ,jcd2_.kd_time AS col_9_0_
                ,jcd2_.kdys AS col_10_0_
                , (SELECT yuangong3_.xingming
                     FROM yuangong yuangong3_
                    WHERE yuangong3_.gonghao = jcd2_.kdys)
                    AS col_11_0_
                , (SELECT bumen4_.bmmc
                     FROM bumen bumen4_
                    WHERE bumen4_.id = jcd2_.kdks_id)
                    AS col_12_0_
                ,jcd2_.kdks_id AS col_13_0_
                ,jcd2_.biaoti AS col_14_0_
                , (SELECT yuangong5_.xingming
                     FROM yuangong yuangong5_
                    WHERE yuangong5_.gonghao = jcd2_.jcys)
                    AS col_15_0_
                ,jcd2_.jcjs_time AS col_16_0_
            FROM huanzhexinxi huanzhexin0_, jiuzhen jiuzhen1_, jcd jcd2_
           WHERE     huanzhexin0_.id = jiuzhen1_.huanzhe_id
                 AND jiuzhen1_.id = jcd2_.jiuzhen_id
                 AND jcd2_.biaoshi = :"SYS_B_0"
                 AND (jcd2_.state IS NULL)
        ORDER BY jcd2_.biaoshi,jcd2_.state,jcd2_.jcjs_time)
 WHERE ROWNUM <= :1;

--//执行计划如下: :"SYS_B_0" = 53.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |      1 |        |       |    32 (100)|          |    100 |00:00:00.01 |     353 |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID     | YUANGONG                      |     29 |      1 |    13 |     2   (0)| 00:00:01 |     17 |00:00:00.01 |       4 |       |       |          |
|*  2 |   INDEX RANGE SCAN               | I_YUANGONG_GONGHAO            |     29 |      1 |       |     1   (0)| 00:00:01 |     17 |00:00:00.01 |       3 |  1025K|  1025K|          |
|   3 |  TABLE ACCESS BY INDEX ROWID     | BUMEN                         |      3 |      1 |    14 |     1   (0)| 00:00:01 |      3 |00:00:00.01 |       5 |       |       |          |
|*  4 |   INDEX UNIQUE SCAN              | PK_BUMEN                      |      3 |      1 |       |     0   (0)|          |      3 |00:00:00.01 |       2 |  1025K|  1025K|          |
|   5 |  TABLE ACCESS BY INDEX ROWID     | YUANGONG                      |     15 |      1 |    13 |     2   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |       |       |          |
|*  6 |   INDEX RANGE SCAN               | I_YUANGONG_GONGHAO            |     15 |      1 |       |     1   (0)| 00:00:01 |     14 |00:00:00.01 |       3 |  1025K|  1025K|          |
|*  7 |  COUNT STOPKEY                   |                               |      1 |        |       |            |          |    100 |00:00:00.01 |     353 |       |       |          |
|   8 |   VIEW                           |                               |      1 |     11 | 26587 |    32   (0)| 00:00:01 |    100 |00:00:00.01 |     353 |       |       |          |
|   9 |    NESTED LOOPS                  |                               |      1 |     11 |  1672 |    32   (0)| 00:00:01 |    100 |00:00:00.01 |     337 |       |       |          |
|  10 |     NESTED LOOPS                 |                               |      1 |     12 |  1672 |    32   (0)| 00:00:01 |    100 |00:00:00.01 |     237 |       |       |          |
|  11 |      NESTED LOOPS                |                               |      1 |     12 |  1176 |    20   (0)| 00:00:01 |    100 |00:00:00.01 |     196 |       |       |          |
|  12 |       TABLE ACCESS BY INDEX ROWID| JCD                           |      1 |   9315 |   800K|     8   (0)| 00:00:01 |    100 |00:00:00.01 |      46 |       |       |          |
|* 13 |        INDEX RANGE SCAN          | I_JCD_BIAOSHI_STATE_JCJS_TIME |      1 |     12 |       |     3   (0)| 00:00:01 |    100 |00:00:00.01 |       3 |  1025K|  1025K|          |
|  14 |       TABLE ACCESS BY INDEX ROWID| JIUZHEN                       |    100 |      1 |    10 |     1   (0)| 00:00:01 |    100 |00:00:00.01 |     150 |       |       |          |
|* 15 |        INDEX UNIQUE SCAN         | PK_JIUZHEN                    |    100 |      1 |       |     0   (0)|          |    100 |00:00:00.01 |      50 |  1025K|  1025K|          |
|* 16 |      INDEX UNIQUE SCAN           | PK_HUANZHEXINXI               |    100 |      1 |       |     0   (0)|          |    100 |00:00:00.01 |      41 |  1025K|  1025K|          |
|  17 |     TABLE ACCESS BY INDEX ROWID  | HUANZHEXINXI                  |    100 |      1 |    54 |     1   (0)| 00:00:01 |    100 |00:00:00.01 |     100 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--//执行计划如下: :"SYS_B_0" = 56.这个逻辑读更少。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |      1 |        |       |    32 (100)|          |      0 |00:00:00.01 |       3 |       |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID     | YUANGONG                      |      0 |      1 |    13 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  2 |   INDEX RANGE SCAN               | I_YUANGONG_GONGHAO            |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |  1025K|  1025K|          |
|   3 |  TABLE ACCESS BY INDEX ROWID     | BUMEN                         |      0 |      1 |    14 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  4 |   INDEX UNIQUE SCAN              | PK_BUMEN                      |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |  1025K|  1025K|          |
|   5 |  TABLE ACCESS BY INDEX ROWID     | YUANGONG                      |      0 |      1 |    13 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |   INDEX RANGE SCAN               | I_YUANGONG_GONGHAO            |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |  1025K|  1025K|          |
|*  7 |  COUNT STOPKEY                   |                               |      1 |        |       |            |          |      0 |00:00:00.01 |       3 |       |       |          |
|   8 |   VIEW                           |                               |      1 |     11 | 26587 |    32   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |       |       |          |
|   9 |    NESTED LOOPS                  |                               |      1 |     11 |  1672 |    32   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |       |       |          |
|  10 |     NESTED LOOPS                 |                               |      1 |     12 |  1672 |    32   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |       |       |          |
|  11 |      NESTED LOOPS                |                               |      1 |     12 |  1176 |    20   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |       |       |          |
|  12 |       TABLE ACCESS BY INDEX ROWID| JCD                           |      1 |   9315 |   800K|     8   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |       |       |          |
|* 13 |        INDEX RANGE SCAN          | I_JCD_BIAOSHI_STATE_JCJS_TIME |      1 |     12 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |  1025K|  1025K|          |
|  14 |       TABLE ACCESS BY INDEX ROWID| JIUZHEN                       |      0 |      1 |    10 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|* 15 |        INDEX UNIQUE SCAN         | PK_JIUZHEN                    |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |  1025K|  1025K|          |
|* 16 |      INDEX UNIQUE SCAN           | PK_HUANZHEXINXI               |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |  1025K|  1025K|          |
|  17 |     TABLE ACCESS BY INDEX ROWID  | HUANZHEXINXI                  |      0 |      1 |    54 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3.总结:
--//实际上还是细节很重要,如果当时我加入提示实际上优化已经完成,这个语句带入就是56,根本不会变,记录很少,逻辑读不会很高
--//,但是仔细再思考一步,就可以发现开发没有写好sql语句。
--//另外再次说明交流很重要,根本没必要在上面浪费这么多时间,估计问一下周围的人许多很快得到结果。可惜在我周围连一个问问题
--//的人都没有..........

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2744838/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2008-01-03

  • 博文量
    2823
  • 访问量
    6620846