此次案例来自西安某客户的一次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(CASEWHEN 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 THENB.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 BWHERE 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 FROMA_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_NOLIKE '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 JAVAEXEC 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 COLUMNOWNER 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 LASTOWNER 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 SUBPARTITIONNAME 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 0prefetch clients - recycle 0prefetch clients - default 0prefetch clients - 2k 0prefetch clients - 4k 0prefetch clients - 8k 0prefetch clients - 16k 0prefetch clients - 32k 0physical reads cache prefetch 7817physical reads prefetch warmup 1466prefetched blocks aged out before use 0prefetch warmup blocks aged out before use 0prefetch warmup blocks flushed out before use 0index 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 spold 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 spold 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 spold 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 spold 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 spold 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性能优化，故障诊断，非凡复原。