乐趣区

关于数据库:Oracle案例index-range-scan真的不会多块读吗

此次案例来自西安某客户的一次 SQL 优化,对于优化自身并不简单,然而发现了一个比拟乏味的问题,就是索引范畴扫描以及回表都有应用多块读的形式。上面来看看具体案例。

SQL 文本:

UPDATE A_INV_PRINT_DET P SET (P.P_POWER, P.N_POWER, P.V_POWER, P.P_KWH_PRC, P.N_KWH_PRC, P.V_KWH_PRC, P.P_KWH_AMT, P.N_KWH_AMT, P.V_KWH_AMT) = (SELECT KWH.P_POWER, KWH.N_POWER, KWH.V_POWER,
KWH.P_KWH_PRC, KWH.N_KWH_PRC, KWH.V_KWH_PRC, KWH.P_KWH_AMT, KWH.N_KWH_AMT, KWH.V_KWH_AMT FROM (SELECT B.PRC_AMT_ID, SUM(CASE WHEN B.PRC_TS_CODE= :B4 THEN B.SETTLE_APQ ELSE 0 END) P_POWER, SUM(CASE
WHEN B.PRC_TS_CODE= :B3 THEN B.SETTLE_APQ ELSE 0 END) N_POWER, SUM(CASE WHEN B.PRC_TS_CODE= :B2 THEN B.SETTLE_APQ ELSE 0 END) V_POWER, MAX(CASE WHEN B.PRC_TS_CODE= :B4 THEN B.KWH_PRC ELSE 0 END)
P_KWH_PRC, MAX(CASE WHEN B.PRC_TS_CODE= :B3 THEN B.KWH_PRC ELSE 0 END) N_KWH_PRC, MAX(CASE WHEN B.PRC_TS_CODE= :B2 THEN B.KWH_PRC ELSE 0 END) V_KWH_PRC, SUM(CASE WHEN B.PRC_TS_CODE= :B4 THEN
B.KWH_AMT ELSE 0 END) P_KWH_AMT, SUM(CASE WHEN B.PRC_TS_CODE= :B3 THEN B.KWH_AMT ELSE 0 END) N_KWH_AMT, SUM(CASE WHEN B.PRC_TS_CODE= :B2 THEN B.KWH_AMT ELSE 0 END) V_KWH_AMT FROM ARC_E_KWH_AMT B
WHERE B.ORG_NO LIKE :B1 AND EXISTS (SELECT 1 FROM A_NOTEPRC_TMP T WHERE B.PRC_AMT_ID = T.NOTE_ID) GROUP BY B.PRC_AMT_ID) KWH WHERE KWH.PRC_AMT_ID = P.PRC_AMT_ID) WHERE EXISTS (SELECT 1 FROM
A_INV_PRINT I WHERE I.INV_MAIN_ID = P.INV_MAIN_ID AND I.ORG_NO LIKE '61020%' AND EXISTS (SELECT 1 FROM A_AMT_CONS WHERE CONS_NO = I.CONS_NO)) AND EXISTS (SELECT 1 FROM ARC_E_KWH_AMT B WHERE B.ORG_NO
LIKE '61020%' AND B.PRC_AMT_ID = P.PRC_AMT_ID AND EXISTS (SELECT 1 FROM A_NOTEPRC_TMP T WHERE B.PRC_AMT_ID = T.NOTE_ID) )

执行打算:

Plan hash value: 4279392932
----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                          |                            |       |       | 78882 (100)|          |       |       |
|   1 |  UPDATE                                   | A_INV_PRINT_DET            |       |       |            |          |       |       |
|*  2 |   HASH JOIN SEMI                          |                            |     1 |    63 |  9915   (1)| 00:01:59 |       |       |
|   3 |    NESTED LOOPS                           |                            |   229 | 11450 |  2253   (1)| 00:00:28 |       |       |
|   4 |     NESTED LOOPS                          |                            |   229 | 11450 |  2253   (1)| 00:00:28 |       |       |
|   5 |      VIEW                                 | VW_SQ_1                    |   214 |  2782 |  1395   (1)| 00:00:17 |       |       |
|   6 |       SORT UNIQUE                         |                            |   214 | 22898 |            |          |       |       |
|   7 |        NESTED LOOPS                       |                            |   214 | 22898 |  1395   (1)| 00:00:17 |       |       |
|   8 |         NESTED LOOPS                      |                            |  1323 | 22898 |  1395   (1)| 00:00:17 |       |       |
|   9 |          SORT UNIQUE                      |                            |   126 |  9702 |     2   (0)| 00:00:01 |       |       |
|  10 |           INDEX FAST FULL SCAN            | DX_A_AMT_CONS              |   126 |  9702 |     2   (0)| 00:00:01 |       |       |
|* 11 |          INDEX RANGE SCAN                 | IDX_CONS_NO3               |    21 |       |     3   (0)| 00:00:01 |       |       |
|* 12 |         TABLE ACCESS BY GLOBAL INDEX ROWID| A_INV_PRINT                |     2 |    60 |    24   (0)| 00:00:01 | ROWID | ROWID |
|* 13 |      INDEX RANGE SCAN                     | IDX_AINVPRINTDET_INVMAINID |     1 |       |     3   (0)| 00:00:01 |       |       |
|  14 |     TABLE ACCESS BY INDEX ROWID           | A_INV_PRINT_DET            |     1 |    37 |     4   (0)| 00:00:01 |       |       |
|  15 |    VIEW                                   | VW_SQ_2                    |   295K|  3753K|  7662   (1)| 00:01:32 |       |       |
|* 16 |     HASH JOIN RIGHT SEMI                  |                            |   295K|    18M|  7662   (1)| 00:01:32 |       |       |
|  17 |      TABLE ACCESS FULL                    | A_NOTEPRC_TMP              |   127 |  6350 |     2   (0)| 00:00:01 |       |       |
|  18 |      PARTITION RANGE ITERATOR             |                            |   295K|  4908K|  7659   (1)| 00:01:32 |   KEY |   KEY |
|  19 |       PARTITION LIST ALL                  |                            |   295K|  4908K|  7659   (1)| 00:01:32 |     1 |    49 |
|  20 |        TABLE ACCESS BY LOCAL INDEX ROWID  | ARC_E_KWH_AMT              |   295K|  4908K|  7659   (1)| 00:01:32 |   KEY |   KEY |gc cr multi block request(5)(12.82%) |
|                                                                                                                                      |db file sequential read(6)(15.38%)   |
|                                                                                                                                      |gc current block 2-way(2)(5.13%)     |
|                                                                                                                                      |db file scattered read(9)(23.08%)    |
|                                                                                                                                      |CPU(2)(5.13%)                        |
|* 21 |         INDEX RANGE SCAN                  | IDX_ARC_E_KWH_AMT_OY_2X    |   295K|       |  1123   (1)| 00:00:14 |   KEY |   KEY |db file scattered read(6)(15.38%)    |
|                                                                                                                                      |db file sequential read(5)(12.82%)   |
|                                                                                                                                      |gc cr multi block request(1)(2.56%)  |
|                                                                                                                                      |CPU(2)(5.13%)                        |
|                                                                                                                                      |gc current block 2-way(1)(2.56%)     |
|  22 |   VIEW                                    |                            |     1 |   130 |  7662   (1)| 00:01:32 |       |       |
|  23 |    SORT GROUP BY                          |                            |     1 |    83 |  7662   (1)| 00:01:32 |       |       |
|  24 |     NESTED LOOPS                          |                            |     1 |    83 |  7662   (1)| 00:01:32 |       |       |
|  25 |      NESTED LOOPS                         |                            |   295K|    83 |  7662   (1)| 00:01:32 |       |       |
|  26 |       SORT UNIQUE                         |                            |     1 |    50 |     2   (0)| 00:00:01 |       |       |
|* 27 |        TABLE ACCESS FULL                  | A_NOTEPRC_TMP              |     1 |    50 |     2   (0)| 00:00:01 |       |       |
|  28 |       PARTITION RANGE ITERATOR            |                            |   295K|       |  1122   (1)| 00:00:14 |   KEY |   KEY |
|  29 |        PARTITION LIST ALL                 |                            |   295K|       |  1122   (1)| 00:00:14 |     1 |    49 |
|* 30 |         INDEX RANGE SCAN                  | IDX_ARC_E_KWH_AMT_OY_2X    |   295K|       |  1122   (1)| 00:00:14 |   KEY |   KEY |
|* 31 |      TABLE ACCESS BY LOCAL INDEX ROWID    | ARC_E_KWH_AMT              |     1 |    33 |  7659   (1)| 00:01:32 |     1 |     1 |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_2"="P"."PRC_AMT_ID")
11 - access("CONS_NO"="I"."CONS_NO")
12 - filter("I"."ORG_NO" LIKE :B1)
13 - access("ITEM_1"="P"."INV_MAIN_ID")
16 - access("B"."PRC_AMT_ID"=TO_NUMBER("T"."NOTE_ID"))
21 - access("B"."ORG_NO" LIKE :B1)
filter("B"."ORG_NO" LIKE :B1)
27 - filter(TO_NUMBER("T"."NOTE_ID")=:B1)
30 - access("B"."ORG_NO" LIKE :B1)
filter("B"."ORG_NO" LIKE :B1)
31 - filter(("B"."PRC_AMT_ID"=:B1 AND "B"."PRC_AMT_ID"=TO_NUMBER("T"."NOTE_ID")))
Note
-----
- dynamic sampling used for this statement (level=2)
 
PL/SQL procedure successfully completed.
 
+------------------------------------------------------------------------+
| infromation  from v$sqlstats               |
+------------------------------------------------------------------------+
 
 
            CPU(MS)  ELA(MS)     DISK          GET        ROWS      ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS)    PLSQL     JAVA
EXEC       PRE EXEC PRE EXEC PRE EXEC     PRE EXEC    PRE EXEC PRE FETCH  PER EXEC   PER EXEC    PER EXEC    PER EXEC PER EXEC PER EXEC SQL_PROFILE
---------- -------- -------- -------- ------------ ----------- --------- --------- ---------- ----------- ----------- -------- -------- ---------------
3             3,612   13,244   64,604      123,936           0         0         0          0       3,109       7,251        0        0

这个执行打算是关联了 ash 的 SQL_PLAN_LINE_ID 的后果,能清晰的指出 SQL 性能瓶颈在执行打算的 id=20 和 id=21。SQL 统计信息能够看到性能次要耗费在 IO 上,对于均匀每次 12w 的逻辑读,6w 的物理读的确有点高,查看相干对象统计信息能够十分疾速的给出解决方案,在 ARC_E_KWH_AMT 上创立组合全局索引 (PRC_AMT_ID,ORG_NO) 即可,local 也行然而没有全局好。SQL 优化不是本篇文章的重点,所以就不详细描述了。

****************************************************************************************
PARTITION TABLE
****************************************************************************************
 
TABLE           TABLE                PARTITION  SUBPART     PART SUBPART PARTITION PARTITION         COLUMN
OWNER           NAME                 TYPE       TYPE       COUNT   COUNT KEY COUNT COLUMN NAME     POSITION
--------------- -------------------- ---------- ---------- ----- ------- --------- --------------- --------
SGPM            ARC_E_KWH_AMT        RANGE      LIST          80       1         1 ORG_NO                 1
 
****************************************************************************************
TABLE COLUMNS
****************************************************************************************
 
                TABLE                               COLUMN                    Column                                   NUM      NUM                AVG                    LAST
OWNER           NAME                                NAME                      Date Type       NL      DENSITY        NULLS DISTINCT  BUCK      COL LEN  SAMPLE_SIZE HIST  ANALYZED
--------------- ----------------------------------- ------------------------- --------------- -- ------------ ------------ -------- ----- ------------ ------------ ----- --------
SGPM            ARC_E_KWH_AMT                       KWH_AMT_ID                NUMBER(22)      N             0            0 ########     1            7  248,746,093 NONE  20211110
                                                    PRC_AMT_ID                NUMBER(22)      N             0            0 ########     1            7  248,746,093 NONE  20211110
                                                    YM                        VARCHAR2(18)    Y             0            0       47     1            7  248,746,093 NONE  20211110
                                                    ORG_NO                    VARCHAR2(48)    Y             0            0      844     1           10  248,746,093 NONE  20211110
                                                    PRC_TS_CODE               VARCHAR2(24)    N             0            0        3     1            3  248,746,093 NONE  20211110
                                                    SETTLE_APQ                NUMBER(22)      N             0            0   241054     1            4  248,746,093 NONE  20211110
                                                    CAT_KWH_PRC               NUMBER(22)      N             0            0      439     1            5  248,746,093 NONE  20211110
                                                    CAT_KWH_AMT               NUMBER(22)      N             0            0  1365515     1            5  248,746,093 NONE  20211110
                                                    KWH_PRC                   NUMBER(22)      N             0            0      254     1            4  248,746,093 NONE  20211110
                                                    KWH_AMT                   NUMBER(22)      N             0            0  1192382     1            5  248,746,093 NONE  20211110
                                                    FLAT_BAL                  NUMBER(22)      N             0            0        5     1            3  248,746,093 NONE  20211110
 
 
 
 
****************************************************************************************
display every partition  info
****************************************************************************************
 
TABLE                               PARTITION                            HIGH_VALUE TABLESPACE                        PARTITION   EMPTY LAST TIME               AVG SUBPARTITION
NAME                                NAME                 HIGH_VALUE          LENGTH NAME            NUM_ROWS   BLOCKS SIZE_KB    BLOCKS ANALYZED              SPACE        COUNT COMPRESSION
----------------------------------- -------------------- --------------- ---------- --------------- -------- -------- ---------- ------ ------------------- ------- ------------ -----------
ARC_E_KWH_AMT                       P610101              '610101'                 8 DATA_ARC               0        0 0KB             0 2021-11-10                0           49 NONE
                                    P610102              '610102'                 8 DATA_ARC         2955515    30320 236.88KB        0 2021-11-10                0           49 NONE
                                    P610103              '610103'                 8 DATA_ARC         2637797    27158 212.17KB        0 2021-11-10                0           49 NONE
                                    P610104              '610104'                 8 DATA_ARC         8366792    84739 662.02KB        0 2021-11-10                0           49 NONE
                                    P610201              '610201'                 8 DATA_ARC         1853561    19190 149.92KB        0 2021-11-10                0           49 NONE
                                    P610322              '610322'                 8 DATA_ARC          613127     6420 50.16KB         0 2021-11-10                0           49 NONE
                                    P610323              '610323'                 8 DATA_ARC         6250465    62945 491.76KB        0 2021-11-10                0           49 NONE
                                    P610324              '610324'                 8 DATA_ARC         5619332    56615 442.3KB         0 2021-11-10                0           49 NONE
                                    P610326              '610326'                 8 DATA_ARC         5204001    52539 410.46KB        0 2021-11-10                0           49 NONE
                                    P610327              '610327'                 8 DATA_ARC         4299090    43575 340.43KB        0 2021-11-10                0           49 NONE
                                    P610328              '610328'                 8 DATA_ARC         3321117    33568 262.25KB        0 2021-11-10                0           49 NONE
                                    P610329              '610329'                 8 DATA_ARC         1698145    17385 135.82KB        0 2021-11-10                0           49 NONE
                                    P610331              '610331'                 8 DATA_ARC         1144643    11883 92.84KB         0 2021-11-10                0           49 NONE
                                    P610332              '610332'                 8 DATA_ARC          859963     9016 70.44KB         0 2021-11-10                0           49 NONE
                                    P610420              '610420'                 8 DATA_ARC            1988      184 1.44KB          0 2021-11-10                0           49 NONE
                                    P610422              '610422'                 8 DATA_ARC            5318      212 1.66KB          0 2021-11-10                0           49 NONE
                                    P610423              '610423'                 8 DATA_ARC         5702386    57544 449.56KB        0 2021-11-10                0           49 NONE
                                    P610424              '610424'                 8 DATA_ARC         5562344    56491 441.34KB        0 2021-11-10                0           49 NONE
                                    P610425              '610425'                 8 DATA_ARC         7106220    71766 560.67KB        0 2021-11-10                0           49 NONE
                                    P610426              '610426'                 8 DATA_ARC         5746855    58059 453.59KB        0 2021-11-10                0           49 NONE
                                    P610427              '610427'                 8 DATA_ARC         2503031    25485 199.1KB         0 2021-11-10                0           49 NONE
                                    P610428              '610428'                 8 DATA_ARC         2899486    29350 229.3KB         0 2021-11-10                0           49 NONE
                                    P610429              '610429'                 8 DATA_ARC         2420269    24620 192.34KB        0 2021-11-10                0           49 NONE
                                    P610430              '610430'                 8 DATA_ARC         3195884    32329 252.57KB        0 2021-11-10                0           49 NONE
                                    P610431              '610431'                 8 DATA_ARC         2373803    24178 188.89KB        0 2021-11-10                0           49 NONE
                                    P610521              '610521'                 8 DATA_ARC         5073435    51527 402.55KB        0 2021-11-10                0           49 NONE
                                    P610523              '610523'                 8 DATA_ARC         4471529    45274 353.7KB         0 2021-11-10                0           49 NONE
                                    P610524              '610524'                 8 DATA_ARC         9502101    95946 749.58KB        0 2021-11-10                0           49 NONE
                                    P610525              '610525'                 8 DATA_ARC         6428700    64719 505.62KB        0 2021-11-10                0           49 NONE
                                    P610527              '610527'                 8 DATA_ARC         4873164    49211 384.46KB        0 2021-11-10                0           49 NONE
                                    P610528              '610528'                 8 DATA_ARC         3275958    33151 258.99KB        0 2021-11-10                0           49 NONE
                                    P610529              '610529'                 8 DATA_ARC        ########   110670 864.61KB        0 2021-11-10                0           49 NONE
                                    P610621              '610621'                 8 DATA_ARC            3387      192 1.5KB           0 2021-11-10                0           49 NONE
                                    P610622              '610622'                 8 DATA_ARC         1611796    16524 129.09KB        0 2021-11-10                0           49 NONE
                                    P610623              '610623'                 8 DATA_ARC         1812815    18645 145.66KB        0 2021-11-10                0           49 NONE
                                    P610624              '610624'                 8 DATA_ARC         2633031    26846 209.73KB        0 2021-11-10                0           49 NONE
                                    P610625              '610625'                 8 DATA_ARC         1754829    17992 140.56KB        0 2021-11-10                0           49 NONE
                                    P610626              '610626'                 8 DATA_ARC         1435150    14834 115.89KB        0 2021-11-10                0           49 NONE
                                    P610627              '610627'                 8 DATA_ARC         1487353    15361 120.01KB        0 2021-11-10                0           49 NONE
                                    P610628              '610628'                 8 DATA_ARC         1105587    11537 90.13KB         0 2021-11-10                0           49 NONE
                                    P610630              '610630'                 8 DATA_ARC         1967378    20166 157.55KB        0 2021-11-10                0           49 NONE
                                    P610631              '610631'                 8 DATA_ARC         1793808    18382 143.61KB        0 2021-11-10                0           49 NONE
                                    P610632              '610632'                 8 DATA_ARC          855952     9004 70.34KB         0 2021-11-10                0           49 NONE
                                    P610721              '610721'                 8 DATA_ARC            9112      254 1.98KB          0 2021-11-10                0           49 NONE
                                    P610722              '610722'                 8 DATA_ARC         7195061    72587 567.09KB        0 2021-11-10                0           49 NONE
                                    P610723              '610723'                 8 DATA_ARC         7219216    72973 570.1KB         0 2021-11-10                0           49 NONE
                                    P610724              '610724'                 8 DATA_ARC         5662868    57109 446.16KB        0 2021-11-10                0           49 NONE
                                    P610726              '610726'                 8 DATA_ARC         5801644    58720 458.75KB        0 2021-11-10                0           49 NONE
                                    P610728              '610728'                 8 DATA_ARC         4471260    45133 352.6KB         0 2021-11-10                0           49 NONE
                                    P610729              '610729'                 8 DATA_ARC         3474328    35298 275.77KB        0 2021-11-10                0           49 NONE
                                    P610730              '610730'                 8 DATA_ARC          742407     7912 61.81KB         0 2021-11-10                0           49 NONE
                                    P610731              '610731'                 8 DATA_ARC          581609     6276 49.03KB         0 2021-11-10                0           49 NONE
                                    P610802              '610802'                 8 DATA_ARC_1          1464      184 1.44KB          0 2021-11-10                0           49 NONE
                                    P610822              '610822'                 8 DATA_ARC_1       7958250    77750 607.42KB        0 2021-11-10                0           49 NONE
                                    P610823              '610823'                 8 DATA_ARC_1       3173969    31214 243.86KB        0 2021-11-10                0           49 NONE
                                    P610824              '610824'                 8 DATA_ARC_1       3192427    31239 244.05KB        0 2021-11-10                0           49 NONE
                                    P610825              '610825'                 8 DATA_ARC_1       3644779    35998 281.23KB        0 2021-11-10                0           49 NONE
                                    P610826              '610826'                 8 DATA_ARC_1       4100854    40332 315.09KB        0 2021-11-10                0           49 NONE
                                    P610827              '610827'                 8 DATA_ARC_1       3607914    35289 275.7KB         0 2021-11-10                0           49 NONE
                                    P610828              '610828'                 8 DATA_ARC_1       1962899    19423 151.74KB        0 2021-11-10                0           49 NONE
                                    P610829              '610829'                 8 DATA_ARC_1       1910961    18793 146.82KB        0 2021-11-10                0           49 NONE
                                    P610830              '610830'                 8 DATA_ARC_1        836115     8538 66.7KB          0 2021-11-10                0           49 NONE
                                    P610831              '610831'                 8 DATA_ARC_1       1480360    14694 114.8KB         0 2021-11-10                0           49 NONE
                                    P610835              '610835'                 8 DATA_ARC_1       2239600    22101 172.66KB        0 2021-11-10                0           49 NONE
                                    P610836              '610836'                 8 DATA_ARC_1        358007     3850 30.08KB         0 2021-11-10                0           49 NONE
                                    P610837              '610837'                 8 DATA_ARC_1        205557     2280 17.81KB         0 2021-11-10                0           49 NONE
                                    P610921              '610921'                 8 DATA_ARC           47722      648 5.06KB          0 2021-11-10                0           49 NONE
                                    P610922              '610922'                 8 DATA_ARC         4458330    45278 353.73KB        0 2021-11-10                0           49 NONE
                                    P610923              '610923'                 8 DATA_ARC         3115029    31855 248.87KB        0 2021-11-10                0           49 NONE
                                    P610924              '610924'                 8 DATA_ARC         1273513    13222 103.3KB         0 2021-11-10                0           49 NONE
                                    P610925              '610925'                 8 DATA_ARC         4479663    45562 355.95KB        0 2021-11-10                0           49 NONE
                                    P610926              '610926'                 8 DATA_ARC         2554062    26074 203.7KB         0 2021-11-10                0           49 NONE
                                    P610927              '610927'                 8 DATA_ARC         3494954    35597 278.1KB         0 2021-11-10                0           49 NONE
                                    P610928              '610928'                 8 DATA_ARC         1047782    11009 86.01KB         0 2021-11-10                0           49 NONE
                                    P611023              '611023'                 8 DATA_ARC            2151      184 1.44KB          0 2021-11-10                0           49 NONE
                                    P611025              '611025'                 8 DATA_ARC         3156440    32074 250.58KB        0 2021-11-10                0           49 NONE
                                    P611026              '611026'                 8 DATA_ARC         4353954    43964 343.47KB        0 2021-11-10                0           49 NONE
                                    P611027              '611027'                 8 DATA_ARC         2660707    27047 211.3KB         0 2021-11-10                0           49 NONE
                                    P6140202             '6140202'                9 DATA_ARC_1          1483      185 1.45KB          0 2021-11-10                0           49 NONE
                                    PMAX                 MAXVALUE                 8 DATA_ARC         4834397    48355 377.77KB        0 2021-11-10                0           49 NONE

当咱们回过头去看此案例”神奇“的中央:

|  20 |        TABLE ACCESS BY LOCAL INDEX ROWID  | ARC_E_KWH_AMT              |   295K|  4908K|  7659   (1)| 00:01:32 |   KEY |   KEY |gc cr multi block request(5)(12.82%) |
|                                                                                                                                      |db file sequential read(6)(15.38%)   |
|                                                                                                                                      |gc current block 2-way(2)(5.13%)     |
|                                                                                                                                      |db file scattered read(9)(23.08%)    |
|                                                                                                                                      |CPU(2)(5.13%)                        |
|* 21 |         INDEX RANGE SCAN                  | IDX_ARC_E_KWH_AMT_OY_2X    |   295K|       |  1123   (1)| 00:00:14 |   KEY |   KEY |db file scattered read(6)(15.38%)    |
|                                                                                                                                      |db file sequential read(5)(12.82%)   |
|                                                                                                                                      |gc cr multi block request(1)(2.56%)  |
|                                                                                                                                      |CPU(2)(5.13%)                        |
|                                                                                                                                      |gc current block 2-way(1)(2.56%)     |

两行 db file scattered read 分外扎眼,为什么 INDEX RANGE SCAN 和 TABLE ACCESS BY LOCAL INDEX ROWID 会有 db file scattered read 期待事件?这是 physical reads prefetch warmup 的个性,意思是当实例重启或者 db cache 显著增大的状况下,Oracle 在读取一些块的时候,”顺便”把左近的块也读到 db cache 中,起到预热的作用,尽可能的应用 db cache,缩小后续的物理 IO,该个性并不会影响一个运行稳固的零碎。

通过 v$sysstat 能够查看实例启动以来 physical reads prefetch 的相干统计:

SQL> select name,value from v$sysstat where name like '%prefetch%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
prefetch clients - keep                                                   0
prefetch clients - recycle                                                0
prefetch clients - default                                                0
prefetch clients - 2k                                                     0
prefetch clients - 4k                                                     0
prefetch clients - 8k                                                     0
prefetch clients - 16k                                                    0
prefetch clients - 32k                                                    0
physical reads cache prefetch                                          7817
physical reads prefetch warmup                                         1466
prefetched blocks aged out before use                                     0
prefetch warmup blocks aged out before use                                0
prefetch warmup blocks flushed out before use                             0
index crx upgrade (prefetch)                                              0

该个性由参数_db_cache_pre_warm 管制,不晓得是什么版本的个性, 最多只能占据 db cache 的 10%,由_db_block_prefetch_quota 管制。

SQL> @sp warm
 
-- show parameter by sp
 
-- show hidden parameter by sp
old   3: where x.indx=y.indx and ksppinm like '_%&p%'
new   3: where x.indx=y.indx and ksppinm like '_%warm%'
 
NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_db_cache_pre_warm                       TRUE       Buffer Cache Pre-Warm Enabled : hidden parameter
 
SQL> @sp prefetch_quota
 
-- show parameter by sp
 
-- show hidden parameter by sp
old   3: where x.indx=y.indx and ksppinm like '_%&p%'
new   3: where x.indx=y.indx and ksppinm like '_%prefetch_quota%'
 
NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_db_block_prefetch_quota                 10         Prefetch quota as a percent of cache size

每次预读取的 block 下限由参数_db_file_noncontig_mblock_read_count 管制:

SQL> @sp noncontig 
 
-- show parameter by sp
 
-- show hidden parameter by sp
old   3: where x.indx=y.indx and ksppinm like '_%&p%'
new   3: where x.indx=y.indx and ksppinm like '_%noncontig%'
 
NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_db_file_noncontig_mblock_read_count     11         number of noncontiguous db blocks to be prefetched

禁用该性能有三种形式,不过该性能是一个十分好的性能,不倡议敞开:

  • _db_file_noncontig_mblock_read_count 批改为 0 或 1;
  • _db_cache_pre_warm 改为 false;
  • _db_block_prefetch_quota 改为 0。

对于索引还有更粗疏的参数管制,默认为开启,_index_prefetch_factor 为索引预取因子,默认为 100,如果变小则更偏向于 index prefetching。

SQL> @sp index_block_pre
 
-- show parameter by sp
 
-- show hidden parameter by sp
old   3: where x.indx=y.indx and ksppinm like '_%&p%'
new   3: where x.indx=y.indx and ksppinm like '_%index_block_pre%'
 
NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_disable_index_block_prefetching         FALSE      disable index block prefetching
 
SQL> @sp index_pre
 
-- show parameter by sp
 
-- show hidden parameter by sp
old   3: where x.indx=y.indx and ksppinm like '_%&p%'
new   3: where x.indx=y.indx and ksppinm like '_%index_pre%'
 
NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_index_prefetch_factor                   100        index prefetching factor

墨天轮原文链接:https://www.modb.pro/db/17496…(复制链接至浏览器或点击文末浏览原文查看)

对于作者
李翔宇,云和恩墨西区交付技术顾问,长期服务挪动运营商行业客户,相熟 Oracle 性能优化,故障诊断,非凡复原。

退出移动版