create table t_target ( id number(*, 0) not null, pkey number(*, 0) not null, filler varchar2(500) ) --segment creation immediate partition by range (pkey) --interval (1) ( partition pkey_0 values less than (1) , partition pkey_1 values less than (2) , partition pkey_2 values less than (3) , partition pkey_3 values less than (4) ); create table t_source compress as select 1 as id, rpad('x', 100) as filler from (select /*+ cardinality(1e3) */ null from dual connect by level <= 1e3), (select /*+ cardinality(1e0) */ null from dual connect by level <= 1e0) union all select 1 as id, rpad('y', 100) as filler from dual; -- Run this again once the DML statement below got started exec dbms_stats.gather_table_stats(null, 't_source', no_invalidate=>false) exec dbms_stats.gather_table_stats(null, 't_target', no_invalidate=>false) ---------------------------------------------------------------------------------------------------------------------------------- -- INSERT example -- -- Run above DBMS_STATS calls or any other command that invalidates the cursor during execution to force re-start of the cursor -- ---------------------------------------------------------------------------------------------------------------------------------- set echo on timing on time on -- alter session set tracefile_identifier = 'insert_restart'; -- alter session set events '10046 trace name context forever, level 12'; -- exec sys.dbms_monitor.session_trace_enable(waits => true, binds => true/*, plan_stat => 'all_executions'*/) insert /* append */ into t_target (id, pkey, filler) select * from ( select /*+ use_hash(a b) no_eliminate_oby */ a.id, 1 as pkey, a.filler from t_source a, t_source b where a.id = b.id and ( regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100)) ) order by a.id ) union all select * from ( select /*+ use_hash(a b) no_eliminate_oby */ a.id, 2 as pkey, a.filler from t_source a, t_source b where a.id = b.id and ( regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100)) ) order by a.id ) union all select * from ( select /*+ use_hash(a b) no_eliminate_oby */ a.id, 3 as pkey, a.filler from t_source a, t_source b where a.id = b.id and ( regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100)) ) order by a.id ) ; -- exec sys.dbms_monitor.session_trace_disable ---------------------------------------------------------------------------------------------------------------------------------- -- MERGE example -- -- Run above DBMS_STATS calls or any other command that invalidates the cursor during execution to force re-start of the cursor -- ---------------------------------------------------------------------------------------------------------------------------------- set echo on timing on time on merge /* append */ into t_target t using ( select * from ( select /*+ use_hash(a b) no_eliminate_oby */ a.id, 1 as pkey, a.filler from t_source a, t_source b where a.id = b.id and ( regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100)) ) order by a.id ) union all select * from ( select /*+ use_hash(a b) no_eliminate_oby */ a.id, 2 as pkey, a.filler from t_source a, t_source b where a.id = b.id and ( regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100)) ) order by a.id ) union all select * from ( select /*+ use_hash(a b) no_eliminate_oby */ a.id, 3 as pkey, a.filler from t_source a, t_source b where a.id = b.id and ( regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') --or regexp_replace(a.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') != regexp_replace(b.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') or (b.filler = rpad('y', 100) and a.filler = rpad('y', 100)) ) order by a.id ) ) s on (s.id = t.id) when not matched then insert (id, pkey, filler) values (s.id, s.pkey, s.filler) ;The idea of the test case is to maximise the time until each UNION ALL branch produces data to insert by performing an inefficient HASH JOIN (that in fact generates a Cartesian product and needs to apply a costly REGEXP filter on that huge intermediate result) and forcing a sort on the join result, so rows will only be handed over to the parent operations until all rows were processed in the join operation - and each branch generates data for a different partition of the target table. Typically it should take several seconds per branch to execute (if you need more time just un-comment the additional REGEXP_REPLACE filters), so you should have plenty of time to cause the invalidation from another session. This means during the execution of each branch invalidating the cursor (for example by executing either of the two DBMS_STATS calls on the source or target table using NO_INVALIDATE=>FALSE) will lead to a re-start of the statement at the next attempt to write into a new target partition, possibly rolling back rows already inserted into other partitions.
Rownum quiz
1 day ago
FYI...I can reproduce this behaviour on a 10.2.0.3 database
ReplyDeleteCheers
Fairlie
Hi Randolf,
ReplyDeletePresumably 12.2 fine-grained cursor invalidation might reduce some instances of this issue if the partition that was the target of the DDL was not relevant to the cursor in question?
https://blogs.oracle.com/optimizer/fine-grained-cursor-invalidation
Cheers,
Dominic
Hi Dominic,
ReplyDeletegood point. I don't have too much experience in that regard with 12.2 and later, so, yes, I agree, in theory the feature should reduce the impact - but can't say anything about real-life experience, so time will tell when more clients are on 12.2+.
Kind regards,
Randolf
Yes it still happens in 12.2 and 19.0, regardless of the CURSOR_INVALIDATION setting or equivalent "deferred invalidation" clause.
ReplyDeleteAlso, it didn't seem to need the condition "The DML statement hasn't touched yet one of the partitions". In our case, it was an INSERT with a 9-way join that normally takes around 15 minutes to load several million rows. Most of the tables are partitioned. Any partition/subpartition DDL on any partition of any of the 10 tables caused the v$sql.object_status to change immediately from VALID to INVALID_UNAUTH until the internal rollback completed (which could easily take an hour, depending on how far it had got before being invalidated), when it would go back to VALID and sql_exec_id and sql_exec_start would be reset.
ReplyDeleteI have experienced this with 11.2.0.4. The only way I found to fix it was to insert first into a Global Temporary table and then into the target table. My experiments and a discussion on Oracle-L lead me to believe that using a partioned table is the significant feature plus the database being "busy", although the example in this fine post shows that there is probably more than way to trip over this issue. When I turned on trace for event 14403 I found that it occurred for even the most simple "INSERT ... VALUES ..." statement, but in those cases the retry only happened once.
ReplyDeleteWilliam's comment about this re-producing in release 19 is interesting and a bit concerning that Oracle appear to see this issue as "just the way it works".