11G result set cache limit, 11G cache limit

11G result set cache limit, 11G cache limit
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 index primary key and table of this table are all written into memory by KEEP.
The query speed is basically within 50-30 milliseconds.
The data of this table needs to be queried more than 5 million times a day, and the number of times it is repeatedly queried is quite large.

For this reason, I suggest you add the prompt/* + result_cache */to the developer */

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