Here’s a little problem I’ve recently stumbled across when was writing a piece of ETL functionality. The problem is that Oracle restarts an Insert statement over and over when certain criteria is met. Although the full conditions list is known only to Oracle engineers, here are those I was able to deterministically reproduce the issue on Oracle 11.2.0.3 (Linux x64):
- The destination table (one to be inserting into) is partitioned
- The source is a join of at least 2 tables one of which is partitioned (but I believe it doesn’t have to be)
- The cursor gets invalidated while join is still running
Now the last bit requires a bit of elaboration. In my testbed I managed to reproduce it on a 2 tables join and the invalidation had had to be invalidated when Oracle was reading the first table. This is the reason why the tables are largish.
If the conditions above are met and while Oracle is reading the first table I issued a DDL leading to the cursor invalidation, my Insert statement restarted. If I invalidated the cursor again, it would restart again, and that could be continued till the cows come home.
Here’s a test script. First let’s prepare the tables:
1: create table t1 partition by range (dt) interval (interval'1'day) subpartition by list (slot)
2: subpartition template (subpartition sp1 values (1)) (partition p1 values less than (date '2014-01-01'))
3: as
4: with gen as (select null from dual connect by level <= 1e3)
5: select rownum as id, date '2014-01-10' as dt, 1 as slot, rpad(trunc(rownum / 10), 500) as key, rpad('x', 1000) padding from gen, gen where rownum <= 1e6;
6:
7: create table t2 as
8: with gen as (select null from dual connect by level <= 1e3)
9: select rownum as id, rpad(rownum, 500) as key, rpad('x', 1000) padding from gen, gen where rownum <= 5e5;
10:
11: create table t3 partition by range(dt) interval(interval'1'day) (partition values less than (date '2013-12-14'))
12: as select trunc(sysdate) as dt, t2.* from t2 where 1=0;
13:
14: create table t4 as select * from t3 where 1 = 0;
The first and the second tables would be our source of data in the insert statement. The T3 table would be the destination. The T4 table would be an alternative destination which is not partitioned.
Now open two sessions. Write down the SID of the first one. In the first session (the SID of which you’ve written down) prepare but don’t execute the insert statement as follows:
1: insert all into t3 (dt, id, key, padding) values (dt, id, key, padding)
2: select /*+ monitor*/t1.dt, t1.id, t1.key, t1.padding from t1,t2
3: where dt = date '2014-01-10' and slot = 1 and t2.key = t1.key;
In the second session prepare but again don’t execute the following block:
1: declare
2: pname varchar2(30);
3: begin
4: select partition_name into pname from user_tab_partitions where table_name = 'T1' and partition_name != 'P1';
5: for j in 2..1000 loop
6: execute immediate utl_lms.format_message('alter table t1 modify partition %s add subpartition %s values (%s)'
7: ,pname, pname||'_SP'||j, to_char(j));
8: dbms_lock.sleep(5);
9: end loop;
10: end;
11: /
That block adds a new empty sub-partition to the current partition, which in turn invalidates all the cursors referencing the table in any way. It does it over and over again with a 5 seconds interval so the cursor gets invalidated many times before it can finish reading T2 (first row source in the join).
Run the insert statement in the first session and within 5 sec interval run the pl/sql block in the second session.
Finally, you can sit and monitor v$sql_monitor using the following query having replaced SID with the one you’ve wrote down:
1: select key, status, sid, sql_id from v$sql_monitor where (sid, sql_id) in (select sid, sql_id from v$session where sid=<YOURSID>);
Depending on how much resources you've got to run the query, you should see over time something like this:
1: SQL> select invalidations from v$sql where sql_id = (select sql_id from v$session where sid = 328);
2:
3: INVALIDATIONS
4: -------------
5: 2
6:
7: SQL> select key, status, sid, sql_id from v$sql_monitor where sql_id = (select sql_id from v$session where sid
8: = 328);
9:
10: KEY STATUS SID SQL_ID
11: ---------- ------------------- ---------- -------------
12: 4.7245E+10 EXECUTING 328 6245n79k73xf7
13: 1.5720E+12 DONE 328 6245n79k73xf7
14: 3.2169E+12 DONE 328 6245n79k73xf7
15:
16: SQL>
If you replace, however, the destination table to T4, no restarts will happen (even though the cursor is still being invalidated).
Oracle says the behaviour is expected if the insert is being done on partitioned table (Doc ID 1462003.1).
