This case comes from an SQL optimization of a customer in Xi'an. The optimization itself is not complex, but an interesting problem is found, that is, the index range scanning and back table all use multi block reading. Let's take a look at specific cases.
SQL text:
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) )
Execution plan:
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 0This execution plan is SQL associated with ash_ PLAN_ LINE_ The result of ID can clearly point out that the bottleneck of SQL performance is id=20 and id=21 of the execution plan. It can be seen from the SQL statistics that the performance is mainly consumed by IO. For the average logical read of 12w each time, the physical read of 6w is indeed a little high. Viewing the relevant object statistics can give a solution very quickly. In arc_ E_ KWH_ Create a combined global index (PRC_AMT_ID, ORG_NO) on AMT. local is also OK, but it is not as good as global. SQL optimization is not the focus of this article, so it will not be described in detail.
****************************************************************************************
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 NONEWhen we look back at the "magical" place of this case:
| 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%) |
The two lines of DB file scaled read are particularly dazzling. Why do INDEX RANGE SCAN and TABLE ACCESS BY LOCAL INDEX ROWID have DB file scaled read waiting events? This is the feature of physical reads prefetch warm, which means that when the instance restarts or the db cache increases significantly, Oracle reads some blocks to the db cache "by the way" to warm up, use the db cache as much as possible and reduce the subsequent physical IO. This feature will not affect a stable system.
Through v$sysstat, you can view the relevant statistics of physical reads prefetch since the instance was started:
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
This property is determined by parameters_ db_ cache_ pre_ It is controlled by warm. I don't know what version of the feature is. It can only occupy 10% of the db cache at most_ db_block_prefetch_quota control.
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
The upper limit of each pre read block is determined by the parameter_ db_file_noncontig_mblock_read_count control:
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
There are three ways to disable this function, but it is a very good function and it is not recommended to turn it off:
- _ db_ file_ noncontig_ mblock_ read_ Modify count to 0 or 1;
- _ db_ cache_ pre_ Change warm to false;
- _ db_ block_ prefetch_ Change quota to 0.
There are more detailed parameter controls for the index. It is on by default_ index_prefetch_factor is the index prefetch factor, which defaults to 100. If it becomes smaller, it is more inclined to 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
Original link of ink Sky Wheel: https://www.modb.pro/db/17496... (copy the link to the browser or click the end of the text to read the original text)
About the author
Li Xiangyu, delivery technical consultant of Yunhe enmo West District, has long served customers in the mobile operator industry, and is familiar with Oracle Performance Optimization, fault diagnosis and special recovery.