Skip navigation.

Dominic Brooks

Syndicate content OraStory
Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)
Updated: 2 hours 48 min ago

The mess that is fast-refresh join-only Materialized Views

Thu, 2014-11-27 09:23

Every now and then you come across a feature or a combination of features which has turned into such a dog’s dinner that you wonder how many people can possibly be using it.

This week – fast fresh materialized views.
I would have thought that this was a very commonly used feature.

This is quite a long article so I will do a top-level TOC first

  1. Why am I looking at a fast-refresh, on-commit, join-only materialized view?
  2. Show me it working
  3. What’s it done to my commit time?
  4. How can this be avoided this with _mv_refresh_use_stats?
  5. Is it any different if I use MLOG stats?
  6. Why might I not want to mess with _mv_use_refresh_stats?


First, why am I looking at a fast refresh, on-commit join-only materialized view.

I have got two datasets which only produce a small resultset when they are joined.
But neither is, on their own, highly selective.

The model below might not be 100% representative of my real-world situation.
I’ve tried to sanitise and simplify but as a result I’ve then had to bump up the volumes a bit to make the point.

drop table t1;
drop table t2;

create table t1
as
select rownum oid
,      case when rownum <= 1000000 then 'Y' else 'N' end flag
,      rownum oid_t2
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t1_1 on t1 (oid);
create index i_t1_2 on t1(flag);
create index i_t1_3 on t1(oid_t2);

create table t2
as
select rownum oid
,      case when rownum >= 999901 then 'Y' else 'N' end flag
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t2_1 on t2 (oid);
create index i_t2_2 on t2(flag);

Currently, I can crunch my data in a few seconds:

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Gives

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:15.91 |     606K|    303K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:15.91 |     606K|    303K|   238M|  7667K|  292M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.54 |     303K|      0 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |      0 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:09.78 |     303K|    303K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
--------------------------------------------------- 

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But I want to query these tables quite frequently and preferably I want this driving logic to be subsecond.
And there will have reasonable inserts and updates incoming at various times.

So, one of the better options is a join-only materialized view.


So, let’s get going:

create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;

create materialized view mv_t1_t2
refresh fast on commit
enable query rewrite
as
select t1.rowid   t1_rowid
,      t1.oid     t1_oid
,      t1.flag    t1_flag
,      t1.oid_t2  t1_oid_t2
,      t1.padding t1_padding
,      t2.rowid   t2_rowid
,      t2.oid     t2_oid
,      t2.flag    t2_flag
,      t2.padding t2_padding
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

create index i_mv_t1_t2_1 on mv_t1_t2(t1_rowid);
create index i_mv_t1_t2_2 on mv_t1_t2(t2_rowid);

Tangent alert!
Now, if I wasn’t going to go off on this tangent because the article will be long enough anyway but when running up the article, a number of questions about
“hy was this not being rewritten?” came up so, we might as well cover more bases.

So, if we run our SELECT now, what happens?

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:08.22 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:08.22 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.69 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------  

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

Bother!

We could go digging around but let’s try the easy way out – DBMS_MVIEW.EXPLAIN_REWRITE

CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message
  measure_in_msg        VARCHAR2(30),   -- Measure in current message
  join_back_tbl         VARCHAR2(30),   -- Join back table in message
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used
  reerved2              VARCHAR2(10))   -- currently not used;

DECLARE
l_sql CLOB :=
q'{select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y'}';
BEGIN
 DBMS_MVIEW.EXPLAIN_REWRITE(l_sql,'MV_T1_T2','mysql');
END;
/

select message from rewrite_table;
MESSAGE
-------------------------------------
QSM-01150: query did not rewrite
QSM-01001: query rewrite not enabled

Yep – easy one.
Let’s set query_rewrite_enabled to true (would be system level if this was not just a test/demo) and repeat.

alter session set query_rewrite_enabled = true;
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |    101 |00:00:00.01 |      16 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |      1 |    101 |    101 |00:00:00.01 |      16 |
--------------------------------------------------------------------------------------------------

Great – I have my subsecond response time.

But what about updates to the data?

First of all, for completeness, let’s observe what happens when we make an uncommitted change.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.27 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.27 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.63 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.65 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------   

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

I’ve lost my usage of the MV.
This is expected.
Why?
DBMS_MVIEW.EXPLAIN_REWRITE tells us:

MESSAGE
------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01279: query rewrite not possible because DML operation occurred on a table referenced by materialized view MV_T1_T2

We can’t use the materialized view because it’s stale.
To use it we have to consider whether we want to run with query_rewrite_integrity set to stale_tolerated.
In this case, not.

If I continue to leave the update uncommitted and check another session, the it continues to use the MV.

But, another tangent!!
In that other session, if I set statistics_level to all, then it won’t use the MV and DBMS_MVIEW.EXPLAIN_REWRITE can’t tell me why.
I’m going to conveniently leave that investigation for another time but just show the evidence:

alter session set statistics_level = all;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.10 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.10 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.49 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.72 |     303K|       |       |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------                                                                         

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But

alter session set statistics_level = typical;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------
| Id  | Operation                    | Name     | E-Rows |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |          |        |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |    101 |
----------------------------------------------------------   

Note
-----
   - Warning: basic plan statistics not available.....

So… anyway… we have our fast-refresh on commit materialized view.


What happens when we commit my previous change?

set timing on
update t1 set flag = 'Y' where oid = 1000001;

1 rows updated.
Elapsed: 00:00:00.029

commit

committed.
Elapsed: 00:00:02.098

Gadzooks! My COMMIT now takes two seconds!

At this point, if you read a lot of technical articles, you might get lucky and have that vague sense of nagging familiarity about having read about this at some point…

Alberto Dell’Era has an excellent set of articles which helps understand what is going on here and what we might do about it.

If we trace my session, we can find out what is contributing to this unpleasant two seconds.
I’m going to cheat, flush the shared pool, do the update and commit and check v$sql
Here is a summary of the significant contributing sql snippets.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
dt1mmbxbp6uk7            1                          1                 1.9356 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE(
5qah9xjyhapg1            0       828077931          1               0.010237 /* MV_REFRESH (DEL) */ DELETE FROM "E668983_DBA"."MV_T1_T2" SNA$ WHERE "T1_ROWID
4agmycb29dd1p            0                          1               0.001747 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000589 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000518 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmlty

A couple of interesting observations.
First is that we can see the XID$$ mechanism which Alberto mentions in his articles linked to above.

Second is that the INSERT is the major contributor to our commit time, followed by the DELETE.
We shall focus on these.

These are the prettified statements:

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM   ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
         FROM "DOM"."T1" "MAS$"
         WHERE ROWID IN (SELECT  /*+ HASH_SJ */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                         FROM "DOM"."MLOG$_T1" "MAS$"
                         WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE  "JV$"."FLAG"='Y'
AND    "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y';

With plan:

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL         |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   2 |   NESTED LOOPS                   |            |      1 |        |      1 |00:00:01.92 |     303K|       |       |          |
|   3 |    NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|   4 |     VIEW                         |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|*  5 |      HASH JOIN RIGHT SEMI        |            |      1 |      1 |      1 |00:00:01.92 |     303K|   832K|   832K|  387K (0)|
|   6 |       TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| T1         |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  9 |        INDEX RANGE SCAN          | I_T1_2     |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN            | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID   | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------ 

Predicate Information (identified by operation id):
--------------------------------------------------- 

   5 - access(ROWID=CHARTOROWID("MAS$"."M_ROW$$"))
   7 - access("MAS$"."XID$$"=:1)
   9 - access("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE
FROM   "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ *
                      FROM   (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"
                              WHERE "MAS$".XID$$ = :1 )MAS$)
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|*  2 |   HASH JOIN SEMI              |              |      1 |    101 |      1 |00:00:00.01 |       3 |   963K|   963K| 1252K (0)|
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |       |       |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------  

Predicate Information (identified by operation id):
---------------------------------------------------  

   2 - access("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

If you have read Alberto’s blogs then you will notice the problematic HASH JOIN RIGHT SEMI in the INSERT
And you will also be familiar with a couple of solutions which now present themselves.

It’s great that there are known workarounds to this problem but, as I hope to demonstrate, the fact that there are at least three separate codepaths through the fast refresh mechanism worries me particularly when we see that
at least one of these alternative code paths causes bugs with other variations on the MV refresh mechanism. What a mess!!


First solution to the problem is paramter _mv_refresh_use_stats.
Repeat update & commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

set timing on
update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.035

commit;
committed.
Elapsed: 00:00:00.174

That’s better.
What do our INSERT and DELETE statements look like now?
Note the different SQL IDs – these are completely different statements.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
av53npjd112vx            1       374625119          1               0.013106 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
5a6ugwh5v1j5x            0       466548951          1               0.009088 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
gpk46p11kbp3d            0      1735585849          1               0.001665 SELECT OLD_NEW$$, COUNT(*)  FROM "DOM"."MLOG$_T1"   WHERE SNAPTIME$$ > :1 AND SN
4agmycb29dd1p            0                          1               0.001615 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000538 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000515 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$     

For both the INSERT and the DELETE, we’ve lost the HASH_SJ hint and we’ve gained a CARDINALITY hint and a NO_SEMIJOIN.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
       FROM   "DOM"."T1" "MAS$"
       WHERE  ROWID IN (SELECT /*+ CARDINALITY(MAS$ 0)  NO_SEMIJOIN */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                        FROM   "DOM"."MLOG$_T1" "MAS$"
                        WHERE  "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y'
AND   "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y'

Giving plan:

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL           |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                     |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                    |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                           |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       VIEW                         | VW_NSO_1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   7 |        SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   8 |         TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  9 |          INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |       TABLE ACCESS BY USER ROWID   | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 11 |     INDEX UNIQUE SCAN              | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 12 |    TABLE ACCESS BY INDEX ROWID     | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("MAS$"."XID$$"=:1)
  10 - filter("MAS$"."FLAG"='Y')
  11 - access("JV$"."OID_T2"="MAS$0"."OID")
  12 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  NO_SEMIJOIN  */ *
                      FROM   (SELECT  /*+ CARDINALITY(MAS$ 0) */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)

with plan:

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                         | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   2 |   NESTED LOOPS                  |              |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   3 |    VIEW                         | VW_NSO_1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   4 |     SORT UNIQUE                 |              |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|*  7 |    INDEX RANGE SCAN             | I_MV_T1_T2_1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("MAS$"."XID$$"=:1)
   7 - access("T1_ROWID"="RID$")

There’s no doubt that that is faster for our specific circumstances.

What about the second workaround available?

Let’s reset “_mv_refresh_use_stats” and look at locking stats on the MV logs.

alter session set "_mv_refresh_use_stats" = false;

session SET altered.

begin
  dbms_stats.gather_table_stats(USER,'MLOG$_T1');
  dbms_stats.gather_table_stats(USER,'MLOG$_T2');
  dbms_stats.lock_table_stats(USER,'MLOG$_T1');
  dbms_stats.lock_table_stats(USER,'MLOG$_T2');
end;
/

anonymous block completed

select table_name, num_rows from user_tables where table_name in ('MLOG$_T1','MLOG$_T2');

TABLE_NAME                     NUM_ROWS
------------------------------ --------
MLOG$_T1                              0
MLOG$_T2                              0

What happens then?

update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.026

commit;

committed.
Elapsed: 00:00:00.169

Very similar to the effects of “mv_refresh_use_stats”, but exactly the same? No.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
69xq38c4ts0j2            1       190791364          1               0.013053 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
dzzb43k6h3vhs            0                          1               0.009172 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
4agmycb29dd1p            0                          1               0.000924 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1                0.00064 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000632 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$

We’ve got completely different SQL IDs again.

In both INSERT and DELETE we’ve lost any hint to do or avoid a semi-join and there’s no CARDINALITY hint.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM "DOM"."T1" "MAS$"
       WHERE ROWID IN (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                       FROM "DOM"."MLOG$_T1" "MAS$"
                       WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y' AND "JV$"."OID_T2"="MAS$0"."OID" AND "MAS$0"."FLAG"='Y'
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL          |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                    |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                   |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                          |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                 |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|*  9 |       TABLE ACCESS BY USER ROWID  | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN             | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID    | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("MAS$"."XID$$"=:1)
   9 - filter("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  */ *
                      FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                            FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |      13 |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |      13 |
|   2 |   NESTED LOOPS SEMI           |              |      1 |    101 |      1 |00:00:00.01 |       5 |
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |    101 |      1 |      1 |00:00:00.01 |       4 |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |    101 |      1 |    101 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

So, to briefly summarise the above, we have at least three clear codepaths through the fast refresh mechanism which result in completely different internal SQL being generated.
Multiple code paths are added complexity when it comes to combining other features, upgrades, testing, etc

It’s then no great surprise when such multiple codepaths lead to other bugs.

So which approach are we meant to do?

Should we just take the default longer commit on the chin?

I’m not sure.

But I can come up with an illustration of why we should think twice about doing down the “_mv_refresh_use_stats” approach.
Firstly it’s an underscore parameter so we should really get Oracle Support approval before setting it (which means a fair bit of back and forth until you get someone who knows what you’re going on about)


Secondly, we can quickly find some bugs around the usage of this parameter with the newer COMMIT SCN (also covered in Alberto’s blogs above)

drop materialized view log on t1;
drop materialized view log on t2;
create materialized view log on t1 with rowid, commit scn;
create materialized view log on t2 with rowid, commit scn;

drop materialized view mv_t1_t2;
create materialized view mv_t1_t2...

Then go back to our update and commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated

commit;

SQL Error: ORA-12008: error in materialized view refresh path
ORA-00904: "SNAPTIME$$": invalid identifier
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.

We don’t actually need COMMIT SCN in our ON-COMMIT MV because we’re using the XID.
It’s a newer mechanism for FAST REFRESH ON DEMAND

I know that I don’t need COMMIT SCN in my case above but the fact that it’s so easy to find an issue means that I’m reluctant to do down the parameter route.

Note that there are quite a few bug notes around this in Oracle Support.
A lot of the advice is conflicting, not least because the behaviour of “_mv_refresh_use_stats” changes mid 10.1 something.

At the moment, I think I’m going to go with the locked stats on the MLOGs but it’s worrying.

What a mess!

References:
Lots of Oracle doc ids…


OLTP Compression, Drop Column, Partition Exchange

Tue, 2014-11-11 06:02

With Basic Compression, you cannot drop a column.

create table t1
(col1 number
,col2 number)
compress;
 
table T1 created.

alter table t1 drop column col2;

SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 -  "unsupported add/drop column operation on compressed tables"
*Cause:    An unsupported add/drop column operation for compressed table
           was attemped.
*Action:   When adding a column, do not specify a default value.
           DROP column is only supported in the form of SET UNUSED column
           (meta-data drop column).

But with Basic Compression, you can set as unused.

alter table t1a set unused column col2;  

table T1A altered

With OLTP Compression, on the face of it, you can drop a column.

drop table t1;

create table t1
(col1 number
,col2 number)
compress for oltp;

select table_name, column_name, hidden_column 
from   user_tab_cols 
where  table_name = 'T1' order by column_id;

TABLE_NAME COLUMN_NAME HIDDEN_COLUMN
---------- ----------- -------------
T1         COL1        NO
T1         COL2        NO

alter table t1 drop column col2;

table T1 altered

But this is a lie/misdirection.
In reality, the dropped column is just renamed.
We see the column is not dropped but hidden:

select table_name, column_name, hidden_column 
from   user_tab_cols 
where  table_name = 'T1' order by column_id;

TABLE_NAME COLUMN_NAME                HIDDEN_COLUMN
---------- -------------------------- -------------
T1         COL1                       NO
T1         SYS_C00002_14111103:30:14$ YES

Note
1. The hidden column name is system generated and suffixed with YYMMDDHH24:MI:SS$
2. Hidden columns are not exposed via USER_TAB_COLUMNS hence usage of USER_TAB_COLS.

I’m not a fan of this behaviour.
It is intended and documented.

Not in OLTP Compression master note 1223705.1.

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1223705.1

But in doc id 1068820.1

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1068820.1

Only advanced compression, which requires Advanced Compression license, supports drop on compressed tables but even in this situation there is no real drop but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations.

Why might this matter?

I can think of at least one time when this might matter – partition exchange.
Ok, the circumstances are probably niche and more relevant for a DEV environment than PROD.
But imagine you add a column to one side of the partition exchange operation and then think better of it and immediately drop it.

drop table t1;
drop table t2;

create table t1
(col1 number
,col2 number)
compress for oltp;
 
create table t2
(col1 number
,col2 number)
partition by range (col1) interval(1)
(partition p0 values less than (0))
compress for oltp;

Show that partition exchange works initially:

lock table t2 partition for (1) in exclusive mode;

lock succeeded.

alter table t2 exchange partition for (1) with table t1;

table T2 altered.

But if I add and drop a column

alter table t1 add col3 number;

table T1 altered.

alter table t1 drop column col3;

table T1 altered.

Then my partition exchange no longer works:

alter table t2 exchange partition for (1) with table t1;

SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 -  "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause:    The corresponding columns in the tables specified in the
           ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action:   Ensure that the two tables have the same number of columns
           with the same type and size.

Until I recreate the table or use DBMS_REDEFINITION.
Or until I do the same to the other side of the exchange operation:

alter table t2 add col345 number;

table T2 altered.

alter table t2 drop column col345;

table T2 altered.

Then

alter table t2 exchange partition for (1) with table t1;

table T2 altered.

Oracle 12c Multitenant – Inmemory admin basics

Tue, 2014-10-21 11:15

A couple of very, very basic observations on getting going with 12c Inmemory in a multitenant database.

1. When trying to set inmemory_size within a PDB when inmemory_size is 0 in the CDB
ORA-02096: specified initialization parameter is not modifiable with this option

SQL> alter session set container = cdb$root;

Session altered.

SQL> select value from v$parameter where name = 'inmemory_size';

VALUE
--------------------------------------------------------------------------------
0

SQL> alter session set container = orcl;

Session altered.

SQL> alter session set inmemory_size=100M;

alter session set inmemory_size=100M
                  *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

2. You have to use scope=spfile when setting inmemory on CDB and it requires restart to take effect

SQL> alter session set container = cdb$root;

Session altered.

SQL> alter system set inmemory_size = 500M;

alter system set inmemory_size = 500M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set inmemory_size = 500M scope=spfile;

System altered.

SQL> select * from v$sga;

NAME                 VALUE      CON_ID
-------------------- ---------- ----------
Fixed Size           2926472    0
Variable Size        1224738936 0
Database Buffers     905969664  0
Redo Buffers         13848576   0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size               2926472 bytes
Variable Size            1275070584 bytes
Database Buffers         318767104 bytes
Redo Buffers             13848576 bytes
In-Memory Area           536870912 bytes
Database mounted.
Database opened.

3. You don’t use scope when setting in a PDB otherwise
ORA-02096: specified initialization parameter is not modifiable with this option

SQL> alter session set container = orcl;

Session altered.

SQL> alter system set inmemory_size=400M scope=spfile;

alter system set inmemory_size=400M scope=spfile
                                               *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

SQL> alter system set inmemory_size=400M;

System altered.

4. If you try to set the PDB inmemory_size larger than the CDB then you get
ORA-02097: parameter cannot be modified because specified value is invalid

SQL> alter session set container = orcl;

Session altered.

SQL> alter system set inmemory_size=600M;

alter system set inmemory_size=600M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified

Added:

5. Similar to point (2), if we change the inmemory size of the CDB, we have to restart for it to take effect

SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL> alter system set inmemory_size=500M scope=spfile;

System altered.

SQL> select value from v$parameter where name = 'inmemory_size';

VALUE
--------------------------------------------------------------------------------
218103808

SQL> shutdown immediate  
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size               2926472 bytes
Variable Size            1157630072 bytes
Database Buffers         436207616 bytes
Redo Buffers             13848576 bytes
In-Memory Area           536870912 bytes
Database mounted.
Database opened.
SQL> 

6. If we modify inmemory_size for PDB, it takes effect immediately (presumably because all it really is a maximum quota on the actual memory structure established in the CDB?):

SQL> alter session set container = orcl;

Session altered.

SQL> alter system set inmemory_size=200M;

System altered.

SQL> select value from v$parameter where name = 'inmemory_size';

VALUE
--------------------------------------------------------------------------------
209715200

SQL> alter system set inmemory_size=400M;

System altered.

SQL> select value from v$parameter where name = 'inmemory_size';

VALUE
--------------------------------------------------------------------------------
419430400

7. While we showed in point (4) that a single PDB inmemory_size cannot exceed the CDB size, we can overallocate such “maximum quotas” across all PDBs – i.e. the sum of inmemory_size for all PDBs can exceed CDB size (prompts future investigations):

SQL> alter session set container=cdb$root;

Session altered.

SQL> select value from v$parameter where name = 'inmemory_size';

VALUE
--------------------------------------------------------------------------------
536870912

SQL> alter session set container=orcl;

Session altered.

SQL> select value from v$parameter where name = 'inmemory_size';

VALUE
--------------------------------------------------------------------------------
419430400

SQL> alter session set container=cdb$root;

Session altered.

SQL> create pluggable database dom from orcl file_name_convert=
 2  ('/home/oracle/app/oracle/oradata/cdb1/orcl',
 3  '/home/oracle/app/oracle/oradata/cdb1/dom');

Pluggable database created.

SQL> alter pluggable database dom open;

Pluggable database altered.

SQL> alter session set container = dom;

Session altered.

SQL> alter system set inmemory_size = 400M;

System altered.

SQL> select value from v$parameter where name = 'inmemory_size';

VALUE
--------------------------------------------------------------------------------
419430400

Remote DML with DBMS_PARALLEL_EXECUTE

Fri, 2014-08-22 10:20

An example of sucking data into a table over a db link using DBMS_PARALLEL_EXECUTE.

This particular example is based on something I needed to do in the real world, copying data from one database into another over a db link. Datapump is not available to me. Tables in question happen to be partitioned by a date-like number (boo!) hence some of the specific actions in the detail.

I think it’s a good example of how to use dbms_parallel_execute but also it might be interesting to see how we might combine that functionality with parallel sessions each operating on a single numeric partition.

For setup, let’s create a suitable source table on a remote db.
In this example, I’m recreating the entries in dba_objects for every day for a couple of years.

CREATE TABLE remote_px_test
(dt,owner,object_name,subobject_name,object_id,data_object_id,object_type,created,last_ddl_time,timestamp,status,temporary,generated,secondary,namespace,edition_name)
PARTITION BY RANGE(dt) INTERVAL(1)
(PARTITION p_default VALUES LESS THAN (20120101))
AS
WITH days AS
(SELECT TO_NUMBER(TO_CHAR(TO_DATE(20120101,'YYYYMMDD') + ROWNUM - 1,'YYYYMMDD')) dt
 FROM   dual
 CONNECT BY ROWNUM <= (TRUNC(SYSDATE) - TO_DATE(20120101,'YYYYMMDD')))
SELECT d.dt, o.*
FROM   dba_objects o
CROSS JOIN days d;
SELECT /*+ parallel(16) */ COUNT(*) FROM remote_px_test;
209957272

SELECT round(sum(bytes)/power(1024,3)) FROM user_segments WHERE segment_name = 'REMOTE_PX_TEST';
31

First step is to see how long it takes to do a parallel INSERT SELECT over a db link.

The benefits of parallelisation in such an operation is severely limited because we have a single session over the db link.

Back to the target database.

First create an empty destination table, same as remote.

CREATE TABLE remote_px_test
(dt,owner,object_name,subobject_name,object_id,data_object_id,object_type,created,last_ddl_time,timestamp,status,temporary,generated,secondary,namespace,edition_name)
PARTITION BY RANGE(dt) INTERVAL(1)
(PARTITION p_default VALUES LESS THAN (20100101))
AS
WITH days AS
(SELECT TO_NUMBER(TO_CHAR(TO_DATE(20120101,'YYYYMMDD') + ROWNUM - 1,'YYYYMMDD')) dt
 FROM   dual
 WHERE 1=0)
SELECT d.dt, o.*
FROM   dba_objects o
CROSS JOIN days d;

Now, let’s see how long it takes to do an INSERT SELECT over a db link.
Time is often not a good measure but in this case I’m primarily interested in how long it takes to copy a whole bunch of tables from A to B over a db link.

insert /*+ append */ into remote_px_test l
select * 
from   remote_px_test@d1 r;

209,957,272 rows inserted.

commit;

This executed in 20 minutes.

As mentioned, you could parallelise bits of it either side but the benefit is limited, it might even make things worse thanks to BUFFER SORT operation.

Next let’s compare to method with DBMS_PARALLEL_EXECUTE.

We want some parallel threads to work on independent partitions, doing direct path inserts, concurrently.

First I’m just going to create a view on the SOURCE DB to make my chunking on daily partition interval simpler.

I could create this on the TARGET DB with references to the dictionary tables over db link but it could be significantly slower depending on the number of partitioned tables and whether predicates are being pushed.

CREATE OR REPLACE VIEW vw_interval_partitions
AS
SELECT table_name, partition_name, partition_position, hi
FROM   (SELECT table_name, partition_name, partition_position
        ,      to_char(
                 extractvalue(
                   dbms_xmlgen.getxmltype
                  ('select high_value from user_tab_partitions x'
                 ||' where x.table_name   = '''||t.table_name||''''
                 ||' and   x.partition_name = '''|| t.partition_name|| ''''),'//text()')) hi
        FROM   user_tab_partitions t);

Secondly, I’m going to create a little helper package which will generate the dynamic SQL for our inserts into specific partitions (PARTITION FOR clause not able to use binds).

		
CREATE OR REPLACE PACKAGE sid_data_pkg
AS
  --
  PROCEDURE sid_ipt (
    i_table_name                 IN     VARCHAR2,
    i_table_owner                IN     VARCHAR2,
    i_column_name                IN     VARCHAR2,
    i_dblink                     IN     VARCHAR2,
    i_start_id                   IN     NUMBER,
    i_end_id                     IN     NUMBER
  );
  --
END sid_data_pkg;
/

CREATE OR REPLACE PACKAGE BODY sid_data_pkg
AS
  PROCEDURE sid_ipt (
    i_table_name                 IN     VARCHAR2,
    i_table_owner                IN     VARCHAR2,
    i_column_name                IN     VARCHAR2,
    i_dblink                     IN     VARCHAR2,
    i_start_id                   IN     NUMBER,
    i_end_id                     IN     NUMBER
  )
  AS
    --
    l_cmd CLOB;
    --
  BEGIN
     --
     l_cmd :=
     q'{INSERT /*+ APPEND */}'||chr(10)||
     q'{INTO   }'||i_table_name||chr(10)||
     q'{PARTITION FOR (}'||i_start_id||')'||chr(10)||
     q'{SELECT *}'||chr(10)||
     q'{FROM   }'||CASE WHEN i_table_owner IS NOT NULL THEN i_table_owner||'.' END
                 ||i_table_name
                 ||CASE WHEN i_dblink IS NOT NULL THEN '@'||i_dblink END
                 ||chr(10)||
     q'{WHERE  }'||i_column_name||' < '||i_end_id||chr(10)||
     CASE WHEN i_start_id IS NOT NULL THEN q'{AND   }'||i_column_name||' >= '||i_start_id END;
     --
     --DBMS_OUTPUT.PUT_LINE(l_cmd);
     --
     EXECUTE IMMEDIATE l_cmd;
     --
     COMMIT;
     --
  END sid_ipt;
  --
END sid_data_pkg;
/

Next, truncate our target table again.

Then create our parallel execute task:

begin
  DBMS_PARALLEL_EXECUTE.CREATE_TASK(task_name => 'PX_TEST_TASK');
end;
/

Create the chunks of work to be executed concurrently:

declare
 l_chunk_sql varchar2(1000);
begin
  l_chunk_sql := q'{select (hi - 1) AS partval, hi }'||chr(10)||
                 q'{from   vw_interval_partitions@d1 v }'||chr(10)||
                 q'{where  table_name = 'REMOTE_PX_TEST' }'||chr(10)||
                 q'{order  by partition_position }';
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(task_name => 'PX_TEST_TASK',sql_stmt => l_chunk_sql, by_rowid => false);
end;
/

Check our task and our chunks:

select * from dba_parallel_execute_tasks;

TASK_OWNER TASK_NAME    CHUNK_TYPE   STATUS  TABLE_OWNER TABLE_NAME NUMBER_COLUMN TASK_COMMENT JOB_PREFIX SQL_STMT LANGUAGE_FLAG EDITION APPLY_CROSSEDITION_TRIGGER FIRE_APPLY_TRIGGER PARALLEL_LEVEL JOB_CLASS
---------- ------------ ------------ ------- ----------- ---------- ------------- ------------ ---------- -------- ------------- ------- -------------------------- ------------------ -------------- ---------
ME_DBA     PX_TEST_TASK NUMBER_RANGE CHUNKED 
select * from dba_parallel_execute_chunks order by chunk_id;

  CHUNK_ID TASK_OWNER TASK_NAME    STATUS     START_ROWID END_ROWID START_ID END_ID   JOB_NAME START_TS END_TS ERROR_CODE ERROR_MESSAGE
---------- ---------- ------------ ---------- ----------- --------- -------- -------- -------- -------- ------ ---------- -------------
      3053 ME_DBA     PX_TEST_TASK UNASSIGNED                       20120100 20120101 
      3054 ME_DBA     PX_TEST_TASK UNASSIGNED                       20120101 20120102 
        ...
      4017 ME_DBA     PX_TEST_TASK UNASSIGNED                       20140821 20140822 

 965 rows selected 

Then we run our parallel tasks thus, each executing the helper package and working on individual partitions:

set serveroutput on
DECLARE
  l_task     VARCHAR2(24) := 'PX_TEST_TASK';
  l_sql_stmt VARCHAR2(1000);
BEGIN
  --
  l_sql_stmt := q'{begin sid_data_pkg.sid_ipt ('REMOTE_PX_TEST','ME_DBA','DT','D1',:start_id,:end_id); end;}';
  --
  DBMS_PARALLEL_EXECUTE.RUN_TASK(l_task, l_sql_stmt, DBMS_SQL.NATIVE,parallel_level => 16);
  --
  dbms_output.put_line(DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task));
  --
end;
/

This executed in 2 minutes and returned code 6 which is FINISHED (without error).

Status of individual chunks can be checked via DBA_PARALLEL_EXECUTE_CHUNKS.