Effective way to delete from a large table in oracle

Today I want to discuss with you the effective way to get rid of most of data from a large table in oracle.

For example, if you need to delete 90 % of the data from a logging table or something like that.

A large table

•Black area contains unneeded data that should be deleted

•Green area contains data that is still needed

•White area unused space

Using Delete statement would most likely take an eternity and will generate a huge amount of REDO and UNDO (as we will see). Unnecessary REDO and UNDO represent only a stress for the entire database.

Usually Delete statement should be used only to remove a small amount of data.

Starting with the second version of Oracle 12 (12.2.0.1), oracle introduced an elegant way to achieve this, namely the DDL „alter table move“ was enhanced to move only a part of the data using where clause.

So, it is possible to move and reorg only the data that you need: for example, alter table large_table move including rows where id < 1000 (only data that meets this condition remains in the table)

Let’s create a test table

create table mytable as select id from dual connect by level < 101;

Using this new feature:

alter table mytable move including rows where id < 50;

This reorgs the table segment by keeping only data that meets the where clause.

It is even possible to delete all data (like truncate statement):

alter table mytable move including rows where 1=2;

no row meets this condition and consequently the table will be emptied.

This feature was only introduced in the version 12.2.

Does this mean that we must use a DML statement to delete data in versions older than 12.2 e.g., in 12.1 or 11.2.0.4?

Certainly not.

There are many ways to achieve delete data without using DML.

I will show you another method based on the partition exchange.

Using partition exchange, doesn’t mean that the target table should be partitioned but we create a worktable that is partitioned, and we create a partition with only the data that shouldn’t be deleted, and we perform a partition exchange

We will test the new method and compare it to the method that uses partition exchange and to the method that uses DELETE statement.

We create a big table: The data we want to delete is uniformly spread over all blocks – just to make the test more realistic

CREATE TABLE big_tbl AS
SELECT ROWNUM ID,2022+MOD(ROWNUM, 10) YEAR, rpad(rownum,10,'x') col1, rpad(rownum,20,'y') col2 , rpad(rownum,30,'z') col3,SYSDATE + ROWNUM/24/60 col4,
 rpad(rownum,50,'a') col5, rpad(rownum,100,'b') col6,rpad(rownum,100,'b') col7,rpad(rownum,100,'b') col8,rpad(rownum,100,'b') col9 FROM 
 (SELECT 1 FROM (SELECT 1 FROM dual CONNECT BY LEVEL < 11) CROSS JOIN (SELECT 1 FROM  dual CONNECT BY LEVEL < 100001))

Check the number of rows:

SELECT /*+ parallel(t 4) */ YEAR,COUNT(*) FROM big_tbl t GROUP BY ROLLUP(YEAR) ORDER BY 1

Year	count
2022	10000
2023	10000
2024	10000
2025	10000
2026	10000
2027	10000
2028	10000
2029	10000
2030	10000
2031	10000
Total	100000

Block to create nine tables as CTAS from big_tbl

DECLARE
  TYPE t_numbers IS TABLE OF NUMBER;
  v_years t_numbers := t_numbers(2022,2023,2024,2025,2026,2027,2028,2029,2030);
  PROCEDURE ex_ignor_err(cmd VARCHAR2) IS BEGIN EXECUTE IMMEDIATE cmd; EXCEPTION WHEN OTHERS THEN NULL; END;
  PROCEDURE LOG(msg VARCHAR2) IS BEGIN dbms_output.put_line(msg); END;
  
BEGIN
  FOR i IN 1..v_years.count LOOP
    LOG(v_years(i));
  
    ex_ignor_err( 'drop table tbl_' ||v_years(i) || ' purge') ; 
    EXECUTE IMMEDIATE( 'create table tbl_' || v_years(i) || ' as select /*+ parallel(t 4) */ * from  big_tbl t');
  END LOOP;
    
END;

Check the size of the tables:

SELECT segment_name,bytes/1024/1024 size_in_mb FROM user_segments WHERE segment_name LIKE 'TBL%'

Table	Size in MB
TBL_2029	608
TBL_2030	608
TBL_2031	608
TBL_2022	608
TBL_2023	607
TBL_2024	608
TBL_2025	608
TBL_2026	608
TBL_2027	608
TBL_2028	608

Deleting data using alter table move including rows

DECLARE
  v_redo NUMBER;
  v_undo NUMBER;
  v_ela NUMBER;
  TYPE t_numbers IS TABLE OF NUMBER;
  v_years t_numbers := t_numbers(2022,2023,2024,2025,2026,2027,2028,2029,2030);
  FUNCTION get_stat(p_name VARCHAR2) RETURN NUMBER IS
    ret NUMBER;
  BEGIN
    EXECUTE IMMEDIATE 'select b.value from v$statname a join v$mystat b using(statistic#) where a.name = :1' INTO ret USING p_name;
    RETURN ret;    
  END get_stat;
  PROCEDURE LOG(msg VARCHAR2) IS BEGIN dbms_output.put_line(msg); END;
BEGIN
FOR i IN 1..v_years.count  LOOP
    v_redo := get_stat('redo size'); 
    v_ela := dbms_utility.get_time;
    v_undo := get_stat('undo change vector size');
    EXECUTE IMMEDIATE 'alter table tbl_'||v_years(i) || ' move including rows where year >  ' ||v_years(i); 
    v_ela := dbms_utility.get_time - v_ela;
    v_redo := get_stat('redo size') - v_redo;
    v_undo := get_stat('undo change vector size') -v_undo;
    LOG(i*10 || '% ' || ' ' ||v_redo ||' ' || v_undo || ' ' || v_ela );
  END LOOP;  
  
END;

The above block iterates over the nine test tables and delete each time 10% more data than the previous iteration. The get_stat method is used to calculate the amount of generated REDO/UNDO.

Deleting data using DML

DECLARE
  v_redo NUMBER;
  v_undo NUMBER;
  v_ela NUMBER;
  TYPE t_numbers IS TABLE OF NUMBER;
  v_years t_numbers := t_numbers(2022,2023,2024,2025,2026,2027,2028,2029,2030);
  FUNCTION get_stat(p_name VARCHAR2) RETURN NUMBER IS
    ret NUMBER;
  BEGIN
    EXECUTE IMMEDIATE 'select b.value from v$statname a join v$mystat b using(statistic#) where a.name = :1' INTO ret USING p_name;
    RETURN ret;    
  END get_stat;
  PROCEDURE LOG(msg VARCHAR2) IS BEGIN dbms_output.put_line(msg); END;
BEGIN
FOR i IN 1..v_years.count -1 LOOP
    v_redo := get_stat('redo size'); 
    v_ela := dbms_utility.get_time;
    v_undo := get_stat('undo change vector size');
    EXECUTE IMMEDIATE ‘delete from table tbl_'||v_years(i) || ' where year >  ' ||v_years(i); 
    commit;
    v_ela := dbms_utility.get_time - v_ela;
    v_redo := get_stat('redo size') - v_redo;
    v_undo := get_stat('undo change vector size') -v_undo;
    LOG(i*10 || '% ' || ' ' ||v_redo ||' ' || v_undo || ' ' || v_ela );
  END LOOP;  
  
END;

Deleting data using partition exchange

DECLARE
  v_redo NUMBER;
  v_undo NUMBER;
  v_ela NUMBER;
  TYPE t_numbers IS TABLE OF NUMBER;
  v_years t_numbers := t_numbers(2022,2023,2024,2025,2026,2027,2028,2029,2030);
  v_cols sys.odcivarchar2list;
  v_get_cols VARCHAR2(4000);
  FUNCTION get_cols RETURN VARCHAR2 IS
    ret VARCHAR2(4000);
  BEGIN
    FOR i IN 1..v_cols.count LOOP ret := ret || v_cols(i) || ','; END LOOP;
    RETURN RTRIM(ret,',');
  END get_cols;
  PROCEDURE LOG(msg VARCHAR2) IS BEGIN dbms_output.put_line(msg); END;
PROCEDURE pc_del_data_using_prtexchg(p_trgt_tbl VARCHAR2, p_where_cond VARCHAR2, p_tblspace VARCHAR2, p_dop VARCHAR2 DEFAULT '4') IS  
  v_sql VARCHAR2(32767);
  v_tmp_tbl VARCHAR2(30) := 'tmp_tbl_to_purge_old_data';
  
BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'drop table ' || v_tmp_tbl || ' purge';
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
   END ;   
   v_sql := 'CREATE TABLE ' || v_tmp_tbl || ' ('||v_get_cols||') 
             partition by range ('||v_cols(1)||') (partition p0  values less than (maxvalue) tablespace '||p_tblspace||')
             as ( select /*+ parallel(t '||p_dop||') */ * from '||p_trgt_tbl||' t '||p_where_cond||')';
   EXECUTE IMMEDIATE v_sql;   
   v_sql := 'ALTER TABLE ' || v_tmp_tbl || ' EXCHANGE PARTITION p0 WITH TABLE ' || p_trgt_tbl ;         
   EXECUTE IMMEDIATE v_sql;   
EXCEPTION 
  WHEN OTHERS THEN
    LOG(SQLERRM);
    RAISE;
END pc_del_data_using_prtexchg;
FUNCTION get_stat(p_name VARCHAR2) RETURN NUMBER IS
    ret NUMBER;
  BEGIN
    EXECUTE IMMEDIATE 'select b.value from v$statname a join v$mystat b using(statistic#) where a.name = :1' INTO ret USING p_name;
    RETURN ret;    
  END get_stat;  
BEGIN
  SELECT column_name BULK COLLECT INTO v_cols FROM user_tab_cols WHERE table_name = 'BIG_TBL' ORDER BY column_id;
  v_get_cols := get_cols;
  FOR i IN 1..v_years.count  LOOP
    v_redo := get_stat('redo size'); 
    v_ela := dbms_utility.get_time;
    v_undo := get_stat('undo change vector size');
    pc_del_data_using_prtexchg('tbl_'||v_years(i), ' where year >  ' ||v_years(i), 'USERS');
    v_ela := dbms_utility.get_time - v_ela;
    v_redo := get_stat('redo size') - v_redo;
    v_undo := get_stat('undo change vector size') -v_undo;
    LOG(i*10 || '% ' || ' ' ||v_redo ||' ' || v_undo || ' ' || v_ela );
  END LOOP;  
END;

Elapsed time in ms to delete from a table with 1 Mio rows (600MB).
Alter table move vs partition exchange vs Delete

	MOVE	Partition Exchange
	MOVE_DOP 1	MOVE_DOP 4	PEXC_DOP 1	PEXC_DOP 4	Delete
10%	8070	4300	11740	9920	5420
20%	7240	3800	10910	8860	9350
30%	6460	3550	10180	8460	12610
40%	5930	3130	9440	7820	16540
50%	5130	2800	8230	6930	18580
60%	4580	2640	7720	6270	23570
70%	3910	2370	6920	5700	27950
80%	3340	1850	6510	4900	32270
90%	2890	1560	5770	4510	46880

Elapsed time to delete from a table with 1 Mio rows (600MB).
**Alter table move** vs **partition exchange** vs **Delete**

We see that using DML to delete a big amount of data is not suitable. The new feature is very performant, especially when parallel processing is used.

Let’s check the amount of generated REDO/UNDO

Amount of generated REDO/UNDO in MB – Deletion from a table with 1 Mio rows: 600 MB

	Redo_Delete	Undo_Delete	Redo_MOVE	Undo_Move	Redo_PEXC	Undo_PEXC
10%	81856	63481	481	61	486	67
20%	166274	127545	453	58	408	64
30%	246739	191644	426	54	379	60
40%	328479	255715	401	52	356	58
50%	410338	319785	374	49	326	54
60%	491963	383787	348	45	301	51
70%	574941	447902	320	42	275	48
80%	656880	511998	295	39	249	45
90%	738101	576195	265	35	222	42

The new feature and the method using partition exchange hardly generate REDO/UNDO in contrast to the method using DML.

Same test using a table with 10 Mio rows (6GB)

Elapsed time to delete from a table with 10 Mio rows (6GB).
Alter table move vs partition exchange

	MOVE	Partition Exchange
	MOVE_DOP 1	MOVE_DOP 4	PEXC_DOP 1	PEXC_DOP 4
10%	84040	60370	133170	86090
20%	70120	48910	107490	85520
30%	59560	45040	90850	78610
40%	53760	42210	83130	69570
50%	52450	36210	77590	66380
60%	43260	32750	73830	60630
70%	37120	27480	67570	55770
80%	33120	21520	61460	49840
90%	27320	14750	54860	44020

Elapsed time (ms) to delete from a table with 10 Mio rows (6GB).
Alter table move vs partition exchange

	Redo_MOVE	Undo_Move	Redo_PEXC	Undo_PEXC
10%	1289	115	1242	121
20%	1213	111	1188	117
30%	1139	107	1109	113
40%	1066	103	1016	109
50%	989	99	943	105
60%	914	95	868	101
70%	841	91	792	97
80%	782	95	720	93
90%	564	65	513	70

The two methods hardly generate REDO/UNDO and are very fast.

Oracle Performance issues by using sequence in parallel high insert DML

Sequences are good choice to generate unique integers for populating primary key and foreign key columns. By using them it is guaranteed that two sessions do not pull the same value. Ensuring this could have a big price in terms of performance. Indeed a sequence is just a row in the seq$ table and every time a session tries to get the next value of the sequence, it should increment the highwater value. This means updating highwater column for the row of the sequence in the seq$ table. For that, the session needs to request a row lock. In parallel processing, this leads to poor performance.
But also in serial processing (Even if the session may only request the lock once for the whole work), the act to update the seq$ table for each used value of the sequence is very expensive. Therefore you should cache the sequence.

Cache specifies how many sequence numbers to preallocate and keep in memory.
Only when all values from the cache are used, the table seq$ is updated. This means the larger is the cache the fewer updates on the seq$ table take place. So if you want to insert millions of rows the cache should be at least 1000.
In my point of view, there is no problem with caching of 5000 values or more. Because only in event of an instance failure (power failure, shutdown abort), any unused values still in memory are lost.

Caching has also a positive impact on parallel processing. Even significantly better than serial processing.

By using the technique of directly calling the sequence in the DML statements, do not forget to increase the cache explicitly and reset it to the default value after the DML is finished.

Let see some examples:

--we create a sequence with no cache
create sequence my_seq increment by 1 nocache;
--we create two table a and b. Both with 1Mio records
create table a as select rownum id, decode(mod(rownum,2),0,null,rownum) id2 from (
(select * from dual connect by level < 1001 ) cross join (select * from dual connect by level < 1001));

create table b as select rownum id from (
(select * from dual connect by level < 1001 ) cross join (select * from dual connect by level < 1001));

-- we create an empty table
create table tbl_test_seq (id number,  text varchar2(300));
-- we insert 1 Mio records in serial processing
-- and we enable event 10046
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
insert /*+ append gather_plan_statistics */ into tbl_test_seq t
select  nvl(a.id2,my_seq.nextval) ,lpad('xx',200,'y') text from a  join b
on a.id = b.id;
commit;
ALTER SESSION SET EVENTS '10046 trace name context off';

Insert of 1Mio records in serial processing

Insert 1Mio records in dop = 8. Trace for one process slave:

After caching the sequence, the insert works very efficiently.

A remark about the consistency check when deleting from a table in Oracle.

When deleting data from a table, Oracle checks for each deleted row whether there are other rows that depend on the deleted one. So oracle will scan (or use indexes if they exist) all tables that have a foreign key pointing on the target table, to see whether
the deleted row has child rows. So Deleting must not cause orphans.
Oracle will do a full scan on the child tables if no indexes are defined on the foreign keys columns. This is of course very inefficient if the child tables are big.

But what if you just want to delete data from the parent table that does not exist in the child tables? Like this „delete from tbl_parent where not exists (select * from tbl_child)„.
You would expect Oracle to not recheck that the deleted records does not have any dependent records in the child tables.

Let’s illustrate this with an example

CREATE TABLE tbl_parent (ID NUMBER PRIMARY KEY) ;

CREATE TABLE tbl_child (ID NUMBER , id_parent NUMBER, CONSTRAINT fk_tbl_child_parent FOREIGN KEY (id_parent) REFERENCES tbl_parent(ID)) ;

INSERT INTO tbl_parent SELECT ROWNUM FROM dual CONNECT BY LEVEL < 101;
INSERT   INTO tbl_child SELECT ROWNUM, ROWNUM + 50 FROM dual CONNECT BY LEVEL < 51;
COMMIT;

And we try to delete all rows from the parent table that does not exist in the child table. We enable the event 10046 and analyze the trace file.

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

delete from tbl_parent where id not in (select id_parent from tbl_child);
commit;
ALTER SESSION SET EVENTS '10046 trace name context off';

In the trace file:

SQL ID: 8uucqtmzuqnhz Plan Hash: 2719173255
delete from tbl_parent
where
id not in (select id_parent from tbl_child)
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 4 2 0
Execute 1 0.00 0.00 0 11 256 50
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 2 0.01 0.00 0 15 258 50
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
0 0 0 DELETE TBL_PARENT (cr=361 pr=0 pw=0 time=9025 us starts=1)
50 50 50 HASH JOIN ANTI NA (cr=11 pr=0 pw=0 time=285 us starts=1 cost=5 size=2600 card=100)
100 100 100 INDEX FAST FULL SCAN SYS_C0021393 (cr=4 pr=0 pw=0 time=24 us starts=1 cost=2 size=1300 card=100)(object id 100084)
50 50 50 TABLE ACCESS FULL TBL_CHILD (cr=7 pr=0 pw=0 time=17 us starts=1 cost=3 size=650 card=50)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
Disk file operations I/O 1 0.00 0.00
log file sync 1 0.00 0.00
PGA memory operation 1 0.00 0.00
SQLNet message to client 1 0.00 0.00 SQLNet message from client 1 0.00 0.00

SQL ID: g59tya9raw21s Plan Hash: 3669815686
select /*+ all_rows */ count(1)
from
„TESTER“.“TBL_CHILD“ where „ID_PARENT“ = :1
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 50 0.00 0.00 0 0 0 0
Fetch 50 0.00 0.00 0 350 0 50
——- —— ——– ———- ———- ———- ———- ———-
total 101 0.00 0.00 0 350 0 50
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
1 1 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=27 us starts=1)
0 0 0 TABLE ACCESS FULL TBL_CHILD (cr=7 pr=0 pw=0 time=23 us starts=1 cost=2 size=13 card=1)

SQL ID: 9pgdf32vu0h3c Plan Hash: 0
commit

So we see that for each deleted row oracle do the following check:

select /*+ all_rows */ count(1)
from
"TESTER"."TBL_CHILD" where "ID_PARENT" = :1

So oracle did not realize that this check is superfluous.

with clause performance issue in oracle

Here’s my experience with a performance problem in the Oracle database. After several months of work on a product and after successful tests, it was time to carry out the integration tests at the customer. Now the customer has noticed that the GUI (JAVA) is quite slow in some situations.

What happened then?! Everything works for us perfect. What is special about the customer? Now I was assigned to analyze the problem.

The first thing I did was to look in our application log and to compare it with the logs in our UAT environment. Surprisingly, the suspicious SQL was easy to find. It was a simple SQL. It should return at most dozens of records and is finished in our UAT environmentin in one second. But by the customer takes more than a minute. The Statement should be called several times, each time it takes more than a minute. This of course makes the GUI very slow.

The SQL ist:

WITH CFG_TBL_AS AS
   (SELECT 
     OWNER, TABLE_NAME, COLUMN_NAME, FUNCTION_NAME, TABLE_NAME_NEW
      FROM TMP_$_ANONY$CONFIG$
     WHERE TABLE_NAME_NEW IS NOT NULL)
  SELECT A.TABLE_NAME, A.COLUMN_NAME, B.TABLE_NAME_NEW, B.FUNCTION_NAME
    FROM ALL_TAB_COLS A
    LEFT JOIN CFG_TBL_AS B
      ON A.OWNER = B.OWNER
     AND A.TABLE_NAME = B.TABLE_NAME
     AND A.COLUMN_NAME = B.COLUMN_NAME
   WHERE (A.OWNER, A.TABLE_NAME) IN
         (SELECT OWNER, TABLE_NAME FROM CFG_TBL_AS)
   ORDER BY A.TABLE_NAME, A.COLUMN_ID

looks easy. I get the Execute Plan from the Customer. I will only show the important here. (which is actually the problem)

It looks like oracle is trying to materialize a subquery. (A powrful feature of With Clause is materializing subqueries) In our environment the execute plan looks like this

Then I tried the SQL with the materialize hint and as expected I get the same execute paln and it takes also more than one minutes.

WITH CFG_TBL_AS AS
   (SELECT --+materialize
     OWNER, TABLE_NAME, COLUMN_NAME, FUNCTION_NAME, TABLE_NAME_NEW
      FROM TMP_$_ANONY$CONFIG$
     WHERE TABLE_NAME_NEW IS NOT NULL)
  SELECT A.TABLE_NAME, A.COLUMN_NAME, B.TABLE_NAME_NEW, B.FUNCTION_NAME
    FROM ALL_TAB_COLS A
    LEFT JOIN CFG_TBL_AS B
      ON A.OWNER = B.OWNER
     AND A.TABLE_NAME = B.TABLE_NAME
     AND A.COLUMN_NAME = B.COLUMN_NAME
   WHERE (A.OWNER, A.TABLE_NAME) IN
         (SELECT OWNER, TABLE_NAME FROM CFG_TBL_AS)
   ORDER BY A.TABLE_NAME, A.COLUMN_ID

Why is it even trying to materialize a few records (which fit into a single block)? And why should something take forever? But the important question for me was why does the statement behave as if the hint is inline?

This is because of the hidden system parameter: _with_subquery. It turns out that this is set to inline on our UAT environment!

ALTER SYSTEM SET "_with_subquery" = INLINE;

Someone forgot to remove this parameter, so we could not spot this issue before delivery. After adding the hint everythings work fine.

For more information about „With Clause“ I found this very interessant blog: http://dbaora.com/with-clause-and-hints-materialize-and-inline/

Diff two tables

In this post I would like to briefly introduce how I dynamically generate a SQL to find diff of two tables.
When optimizing a SQL Statement to get better performance, in most cases is not only enough to add (or remove) a few hints, but you have to rewrite the whole SQL statement. This is too risky and can cause the new statement to produce a very different result. That’s why you have to compare the results of both statements.
One saves the result of the SQL before the optimization in a table e.g. table_a. And the result of SQL after optimization in the table Table_b. The difference is the result of the following SQL:

Select ‘table_a’ src,t.* from (Select * from table_a minus select * from table_b)
Union all
Select ‘table_b’ src,t.* from (Select * from table_b minus select * from table_a)

This will not work if the tables contain LOB objects. You can exclude the LOB objects from the comparison. But if they were quite relevant, you could use a hash function.
The following PL / SQL block dynamically generates the Diff SQL and use the dbms_crypto.hash function:

DECLARE
tbl_a VARCHAR2(30):= 'TABLE_a';
tbl_b VARCHAR2(30):= 'TABLE_b';
cols VARCHAR2(32767);
stmt VARCHAR2(32767);

BEGIN
  FOR rec IN (SELECT * FROM user_tab_columns WHERE table_name = 'JURISTISCHE_PERSON' ORDER BY column_id) LOOP
    cols := cols || CASE WHEN rec.data_type IN ('CLOB','BLOB') THEN 'dbms_crypto.hash( utl_raw.cast_to_raw('||rec.column_name||'), 2) as ' || rec.column_name
    ELSE rec.column_name END||','||CHR(10);
  END LOOP;
  cols := rtrim(cols,','||CHR(10));
  stmt := 'select '''||tbl_a||''' src,t.* from (select '||cols|| ' from ' || tbl_a || ' minus select '||cols|| ' from ' || tbl_b||') t 
           union all 
           select '''||tbl_b||''',t.* from (select '||cols|| ' from ' || tbl_b || ' minus select '||cols|| ' from ' || tbl_a||') t';
  dbms_output.put_line(stmt);
END;