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) begin :x := 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); end; / drop index t_indx; declare l_tmp number := dbms_sql.execute(:x); begin if dbms_sql.fetch_rows(:x) > 0 then dbms_sql.column_value(:x, 1, :n); end if; dbms_sql.close_cursor(:x); end; / 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' begin :x := 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); end; END OF STMT 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 END OF STMT 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 :x := dbms_flashback.get_system_change_number select /* &curr_date */ * from t as of scn :x 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 :x := 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' minus 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 10.2.0.4 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 220.127.116.11 (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.