Oracle SQL
  • LICENSE

Monthly Archives: July 2012

When oracle invalidates result_cache function results without any changes in objects on which depends

Posted on July 30, 2012 by Sayan Malakshinov Posted in documentation, oracle, result_cache 2,109 Page views Leave a comment

On our production servers we have simple function with result_cache, like this:

create or replace function f_rc(p_id number) return number result_cache
is
  ret number;
begin
  select t.val into ret from rc_table t where t.id=p_id;
  return ret;
exception 
  when no_data_found then 
     return null;
end;
/

And oracle invalidates its results very frequently without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:
1. “select for update” from this table with commit; (strictly speaking, select-for-update is DML and it changes blocks, so it’s ok)
2. deleting rows from parent table that has no child records if there is unindexed foreign key with “on delete cascade”.
I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases for this i will show below.
But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with “select for update”, it still does not stop invalidating.
In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and “commits”?
Continue reading→

invalidation oracle undocumented behaviour result_cache

An interesting question from Valentin Nikotin

Posted on July 21, 2012 by Sayan Malakshinov Posted in Uncategorized 1,692 Page views Leave a comment

Test table:

create table tclob(c clob);

What is this code going to put out from the following blocks with and without “rollback”:

Task 1

[sourcecode language=”sql”]
declare
cl1 clob;
cl2 clob;
cl3 clob;
cl4 clob;
begin
cl1:=’1′;
insert into tclob values(cl1) returning c into cl2;
cl3:=cl2;
dbms_lob.append(cl3,’2′);
select c into cl4 from tclob;
— rollback;

dbms_output.put_line(cl1);
dbms_output.put_line(cl2);
dbms_output.put_line(cl3);
dbms_output.put_line(cl4);
end;
/
[/sourcecode]

[collapse]

Task 2

[sourcecode language=”sql”]
declare
cl1 clob;
cl2 clob;
cl3 clob;
cl4 clob;
begin
cl1 := ‘1’;
insert into tclob values (cl1) returning c into cl2;
cl3 := cl2;
dbms_lob.append(cl2, ‘2’);
select c into cl4 from tclob;
— rollback;

dbms_output.put_line(cl1);
dbms_output.put_line(cl2);
dbms_output.put_line(cl3);
dbms_output.put_line(cl4);
end;
/
[/sourcecode]

[collapse]
Task 3

[sourcecode language=”sql”]
declare
cl1 clob;
cl2 clob;
cl3 clob;
cl4 clob;
begin
cl1 := ‘1’;
insert into tclob values (cl1) returning c into cl2;
cl3 := cl2;
dbms_lob.append(cl2, ‘2’);
dbms_lob.append(cl3, ‘3’);
select c into cl4 from tclob;
— rollback;

dbms_output.put_line(cl1);
dbms_output.put_line(cl2);
dbms_output.put_line(cl3);
dbms_output.put_line(cl4);
end;
/
[/sourcecode]

[collapse]
Task 4

[sourcecode language=”sql”]
declare
cl1 clob;
cl2 clob;
cl3 clob;
cl4 clob;
begin
cl1 := ‘1’;
insert into tclob values (cl1) returning c into cl2;
cl3 := cl2;
dbms_lob.append(cl2, ’22’);
dbms_lob.append(cl3, ‘3’);
dbms_lob.append(cl2, ’44’);
select c into cl4 from tclob;
— rollback;
dbms_output.put_line(cl1);
dbms_output.put_line(cl2);
dbms_output.put_line(cl3);
dbms_output.put_line(cl4);
end;
/
[/sourcecode]

[collapse]

Check it both in Windows and Solaris/Linux 🙂

Explanation
It is clear that this bug is platform-depending and that the matter is in specifics of working with memory. The answer lies in the fact that cl3 and cl2 have unsynchronized lengths, which means that Oracle “forgets” to change the lengths of all remaining variables, that point to this “clob”, and as every operation of changing cl2/cl3 in fact changes the same thing, it turns out that “superfluous” becomes overwritten.

photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro Alumni

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • Oracle Telegram Bot
  • Partition Pruning and Global Indexes
  • Interval Search: Part 4. Dynamic Range Segmentation – interval quantization
  • Interval Search Series: Simplified, Advanced, and Custom Solutions
  • Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Popular posts

Recent Comments

  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Optimizing Date Range Queries – Part 1
  • Oracle SQL | Interval Search Series: Simplified, Advanced, and Custom Solutions on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 2. Dynamic Range Segmentation – Simplified on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin
©Sayan Malakshinov. Oracle SQL