11g result set cache restrictions
There is a small table with a data volume of more than one million. This is similar to the IP address range lookup. The Table query volume is large. The data is generated in real time and sometimes updated.
The conclusion is that the result cache is invalid because the table dependency occurs. That is to say, if the data of the corresponding table changes, it is retrieved from the table again. the time is much higher than the ordinary 45 ms to 945 Ms. doesn't it know how to get it from memory? I still think that the table data has changed and reading from the hard disk is true!
Declare
B1 VARCHAR2 (20 );
B2 NUMBER;
Org number (10 );
S timestamp;
E timestamp;
R timestamp;
Begin
B2: = 12;
B1: = 668239581895;
For I IN 1 .. 100 LOOP
S: = policimestamp;
SELECT/* + result_cache */USE_IP into IP
FROM (select a. USE_IP
A. IP_TYPE
FROM BACK_IP
WHEREA. IP_TYPE IN (3, 5, 9)
ANDIP_LEGN = B2
ANDA. START_IP <= B1
ANDA. END_IP> = B1
Order bya. DISP_DATE DESC,
A. CREATE_TIME DESC)
Where rownum <= 1;
E: = policimestamp;
DBMS_OUTPUT.PUT_LINE (TO_CHAR (E-S ));
DBMS_LOCK.SLEEP (10 );
End loop;
END;
The test results are not ideal and the volatility is too high.
+ 00000000000:00:00. 090374000
+ 00000000000:00:00. 102860000
+ 00000000000:00:00. 000237000
+ 00000000000:00:00. 000139000
+ 00000000000:00:00. 000135000
+ 00000000000:00:00. 000163000
+ 00000000000:00:00. 000170000
+ 00000000000:00:00. 000206000
+ 00000000000:00:00. 000173000
+ 000000000 00:00:00. 000171000
+ 00000000000:00:00. 000170000
+ 00000000000:00:00. 000139000
+ 00000000000:00:00. 000267000
+ 00000000000:00:00. 000171000
+ 00000000000:00:00. 000160000
+ 00000000000:00:00. 000180000
+ 00000000000:00:00. 000161000
+ 00000000000:00:00. 000183000
+ 00000000000:00:00. 000182000
+ 00000000000:00:00. 000139000
+ 00000000000:00:00. 000147000
+ 00000000000:00:00. 000160000
+ 00000000000:00:00. 000212000
+ 00000000000:00:00. 000353000
+ 00000000000:00:00. 094454000