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 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 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

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;
/

[collapse]

Task 2

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;
/

[collapse]
Task 3

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;
/

[collapse]
Task 4

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;
/

[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

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

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

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL