此次案例来自西安某客户的一次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
