Flashback cursors

Flashback query allows to get data as of required point in time. It’s a nice feature. It’s strange in the implementation though. If you try to use AS OF SCN query with bind variables, it won’t help you to keep number of child cursors low: each execution (independently of the incoming SCN value) will cause a new child cursor to appear with FLASHBACK_CURSOR as the cause. I don’t understand why Oracle is doing it this way.

In the OakTable mailing list there was an idea that it could be due to some additional data stored alongside with the child cursor which may be used by the query, such as index validness or object statistics as of different time. This is just an idea, which doesn’t seem correct to me since with normal queries it’s very easy to get a re-parse on execute if something changes between the parse and execute calls. Like this:

drop table t cascade constraints purge;
create table t as select * from all_objects;
create index t_indx on t(object_id);
exec dbms_stats.gather_table_stats(user, 't', cascade=>true, no_invalidate=>false)

exec dbms_session.session_trace_enable

var x number
var n varchar2(100)
    😡 := dbms_sql.open_cursor;
    dbms_sql.parse(:x, 'select object_name from t where object_id = 42', dbms_sql.native);
    dbms_sql.define_column(:x, 1, :n, 100);

drop index t_indx;

    l_tmp number := dbms_sql.execute(:x);
    if dbms_sql.fetch_rows(:x) > 0 then
        dbms_sql.column_value(:x, 1, :n);
    end if;

exec dbms_session.session_trace_disable

I parse a cursor at one point in time, then drop an index on the accessed table and execute the query. And necessary part the trace file is:

PARSING IN CURSOR #47420142723192 len=177 dep=0 uid=62 oct=47 lid=62 tim=1338964506334918 hv=1969760827 ad='70b47a5d8' sqlid='3mmfzntuqhajv'
    😡 := dbms_sql.open_cursor;
    dbms_sql.parse(:x, 'select object_name from t where object_id = 42', dbms_sql.native);
    dbms_sql.define_column(:x, 1, :n, 100);
PARSE #47420142723192:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1338964506334917
PARSING IN CURSOR #47420144475512 len=46 dep=1 uid=62 oct=3 lid=62 tim=1338964506335822 hv=3206175636 ad='6a7f9ef50' sqlid='a75z50azjnqwn'
select object_name from t where object_id = 42
PARSE #47420144475512:c=1000,e=709,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=4155885868,tim=1338964506335821
WAIT #47420142723192: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=7035526 tim=1338964506335882
EXEC #47420142723192:c=1000,e=922,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1338964506335899
WAIT #47420142723192: nam='SQL*Net message from client' ela= 8405 driver id=1413697536 #bytes=1 p3=0 obj#=7035526 tim=1338964506344329
EXEC #47420144475512:c=13998,e=13591,p=0,cr=79,cu=0,mis=1,r=0,dep=1,og=1,plh=1601196873,tim=1338964506406680
FETCH #47420144475512:c=4999,e=4864,p=0,cr=1042,cu=0,mis=0,r=0,dep=1,og=1,plh=1601196873,tim=1338964506411587
STAT #47420144475512 id=1 cnt=0 pid=0 pos=1 obj=7035525 op='TABLE ACCESS FULL T (cr=1042 pr=0 pw=0 time=4864 us cost=235 size=32 card=1)'
CLOSE #47420144475512:c=0,e=4,dep=1,type=0,tim=1338964506411685

Notice that the cursor was parsed twice – first on the PARSE and then on the EXEC call. And the plan at those two calls are different: they have different PLAN_HASH_VALUE (plh).
So this doesn’t look like a the cause to me. Oracle can do all the associated work on the execute if it needs to.
What else could it be? I’ve checked how normal and flashback cursors are different in the shared memory chunks using a simple case:

col curr_date new_value curr_date
select to_char(sysdate, 'HH24MISS') curr_date from dual;
set define on serveroutput off

var x number
exec 😡 := dbms_flashback.get_system_change_number
select /* &curr_date */ * from t as of scn 😡 where object_id = 42;

col prev_sql_id new_value sql_id_1
select prev_sql_id from v$session where sid = userenv('sid');

exec 😡 := 42
select /* &curr_date */ * from t where object_id = :x;

col prev_sql_id  new_value sql_id_2
select prev_sql_id from v$session where sid = userenv('sid');

select function, chunk_com from v$sql_shared_memory where sql_id = '&sql_id_1'
select function, chunk_com from v$sql_shared_memory where sql_id = '&sql_id_2';

With the help of V$SQL_SHARED_MEMORY it’s easy to check what’s inside shared cursors. And it looks like a simple flashback cursor has only 3 more chunks allocated:

FUNCTION                                                         CHUNK_COM
---------------------------------------------------------------- ----------------
kkfdParal                                                        pqctx:kkfdParal
ktfxfsnp                                                         qeSnp: ktfxfsnp
qkexrXfo                                                         ub1[]: qkexrXfo

On a the final query produced no output at all, which makes me think that there’s nothing significant inside a flashback cursor. So why it works this way? I guess it’s to make things easier in implementation. With a new child for each flashback query execution there’s no need to check that executions plan is valid. With a DDL (drop index in my example) it’s a different story – it invalidates dependent cursors and on the execution attempt Oracle knows that the plan is no longer valid. Kind of hard making the same for flashback query. How to implement objects dependency for any given point in time? The simplest approach in my opinion is “just don’t” and try using an existent flashback child cursor and in case of an error – like missing index at this point in time – build a new cursor. I have no idea if it’s doable or not, but I’d like to see it implemented. We use flashback query; it causes hundreds of child cursors to appear in Oracle versions before (this version limits number of child cursors to 100 by default). Of course they should be purged from shared pool relatively quickly, but still this could be done better.

One Trackback

  1. […] to submit ER and if I’m allowed I’ll definitely submit it. The second question, about flashback cursors, had a mistake. Tom has built a test case to show that flashback query builds new child cursor on […]

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: