Result Cache “wrong result”

I have a post showing that we suffered from Result Cache: RC Latch.

In relation to that we noticed another more severe issue I would say.
The result from the result cache was wrong.

The server result cache is a memory pool within the shared pool. This memory pool consists of the SQL query result cache—which stores results of SQL queries.
If you want to read more about the result cache look here.

It started with some Siebel users complaining that when they updated a column showed the old value and not the new value. When they changed screen the new value was seen. The problem was seen occasionally.

Working with the developers we could reproduce this issue:

-- Start by selecting the current value
SIEBEL > select num_of_beds from s_org_ext where row_id = '1-G9YGL9';

NUM_OF_BEDS
-----------
 201506
-- Update the column 
SIEBEL > update s_org_ext set num_of_beds = '201503' where row_id = '1-G9YGL9';

1 row updated.
-- Commit the transaction
SIEBEL > commit;

Commit complete.
-- Select the row again  
SIEBEL > select num_of_beds from s_org_ext where row_id = '1-G9YGL9';
-- The row should have the new value 201503
NUM_OF_BEDS
-----------
 201506
-- In my example it is even worse, when i run a different 
   sql with the updated column I get a totally bogus value. 
   Our developers saw the old value.
SIEBEL > select row_id,num_of_beds from s_org_ext where row_id = '1-G9YGL9';
ROW_ID          NUM_OF_BEDS
--------------- -----------
1-G9YGL9           15062400

Now if I run the same again but add the no_result_cache hint, it works as expected.

SIEBEL > select /*+ NO_RESULT_CACHE */ num_of_beds from s_org_ext 
where row_id = '1-G9YGL9';
NUM_OF_BEDS
-----------
 201503
SIEBEL > update s_org_ext set num_of_beds = '201501' 
where row_id = '1-G9YGL9';
1 row updated.

SIEBEL > commit;

Commit complete.
SIEBEL > select /*+ NO_RESULT_CACHE */ num_of_beds from s_org_ext 
where row_id = '1-G9YGL9';
NUM_OF_BEDS
-----------
 201501
SIEBEL > select /*+ NO_RESULT_CACHE */ row_id,num_of_beds 
from s_org_ext where row_id = '1-G9YGL9';
ROW_ID          NUM_OF_BEDS
--------------- -----------
1-G9YGL9             201501

At the time when this is seen we do see a lot of waits on Result Cache: RC Latch.
I do believe this is related to:

Bug 14095982 – “latch free” waits when result cache is used (Doc ID 14095982.8)

Workaround is to disable the result cache by setting:

SQL> alter system set result_cache_max_size=0 scope=both;
System altered.

After I disabled the result cache the waits are gone.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s