why are you doing this row by row by stinking slow row??????

why would you merge a row at a time?

merge is designed to merge two SETS, not single rows.

your row by row by slow slow slow row processing is the killer here. You have tons of procedural code that is just slowing you way way way down. You are dyanmically allocating storage, casting, using object types -- all of which is wholly unneccesary.

The invoker of this routine shouldn't be -- the invoker should just be running MERGE on sets, period.

Your insert/update routine is many many times slower then it needs to be as well - forget about MERGE for a moment, you are using many orders of magnitude more resources then you ever need to!


To answer the very last question "what can you do to make this system more efficient" i have only one word for you:

SETS


it is all about sets, SQL is about sets, the best way to make SQL perform is to think SETS, not procedurally, SETS. MERGE is all about SETS. MERGE is not about row by row (cobol is maybe)...


I found this so slow -- I could not even bear to run a 1,000,000 row test! Here is a couple of thousand rows. We can make it faster -- and I'll iteratively show you how -- but really a single sole MERGE statement is all you want. I'll do 10000 rows to show you really slow (your way) and just plain slow (a simple modification). I use a script "big_table.sql" from my new book Effective Oracle By Design to create my test table:


---------------- big_table.sql ----------------------------------

create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;

declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= &1;

l_cnt := sql%rowcount;

commit;

while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

alter table big_table add constraint
big_table_pk primary key(id)
/

begin
dbms_stats.gather_table_stats
( ownname => user,
tabname => 'BIG_TABLE',
method_opt => 'for all indexed columns',
cascade => TRUE );
end;
/
select count(*) from big_table;
------------------------------------------------------------------


I use that to create a 10000 row table and then ran this script "slow.sql"

---------------------- slow.sql ---------------------------------------

create or replace type myScalarType
as object
(ID NUMBER,
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_TYPE VARCHAR2(18),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7)
)
/
create or replace type myArrayType
as table of myScalarType
/


drop table big_table;
@big_table &1
alter table big_table modify object_id null;
exec dbms_stats.set_table_stats( user, 'BIG_TABLE', numrows => 1000000, numblks => 15000 );

drop table updates;

create table updates
as
select id+(select trunc(count(*)/2) from big_table) id,
owner, object_name, object_type, created,
last_ddl_time, timestamp, status
from big_table
/
alter table updates add constraint updates_pk primary key(id);


create or replace
procedure do_single_row
( p_id in big_table.id%type,
p_owner in big_table.owner%type,
p_object_name in big_table.object_name%type,
p_object_type in big_table.object_type%type,
p_created in big_table.created%type,
p_last_ddl_time in big_table.last_ddl_time%type,
p_timestamp in big_table.timestamp%type,
p_status in big_table.status%type )
as
l_data myArrayType := myArrayType();
begin
l_data.extend;
l_data(1) :=
myScalarType( p_id, p_owner, p_object_name,
p_object_type, p_created,
p_last_ddl_time, p_timestamp,
p_status );

merge into big_table t1
using ( select * from table( cast(l_data as myArrayType) ) ) t2
on ( t1.id = t2.id )
when matched then
update set t1.owner = t2.owner,
t1.object_name = t2.object_name,
t1.object_type = t2.object_type,
t1.created = t2.created,
t1.last_ddl_time = t2.last_ddl_time,
t1.timestamp = t2.timestamp,
t1.status = t2.status
when not matched then
insert ( id, owner, object_name, object_type,
created, last_ddl_time, timestamp,
status)
values ( t2.id, t2.owner, t2.object_name,
t2.object_type, t2.created,
t2.last_ddl_time, t2.timestamp, t2.status );
end;
/

create or replace procedure slow_way
as
begin
for x in ( select * from updates )
loop
do_single_row( x.id, x.owner, x.object_name,
x.object_type, x.created,
x.last_ddl_time, x.timestamp, x.status );
end loop;
end;
/

@trace
exec slow_way
---------------------------------------------------------------------

I set the stats on big_table so as to get a plan for the merge that would be used for millions of rows in big_table (else, the merge used a hash anti join on a small table, that would skew our findings for this scaled down test). I wanted an index lookup as that is what would really happen in real life. Given we are doing a row at a time, this is a valid use of setting stats to "get a plan" -- so we can extrapolate what would happen if we did this process 1000000 times instead of just the 1,000 i did here:


the above script will update 1/2 the rows in big_table and insert 1/2 as many as exist by design.

Now, TKPROF shows us from this run:


MERGE into big_table t1
using ( select * from table( cast(:b1 as myArrayType) ) ) t2
on ( t1.id = t2.id )
when matched then
update set t1.owner = t2.owner,
t1.object_name = t2.object_name,
t1.object_type = t2.object_type,
t1.created = t2.created,
t1.last_ddl_time = t2.last_ddl_time,
t1.timestamp = t2.timestamp,
t1.status = t2.status
when not matched then
insert ( id, owner, object_name, object_type,
created, last_ddl_time, timestamp,
status)
values ( t2.id, t2.owner, t2.object_name,
t2.object_type, t2.created,
t2.last_ddl_time, t2.timestamp, t2.status )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 12.65 15.36 0 25600 23216 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 12.66 15.36 0 25600 23216 10000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 542 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
20000 MERGE (cr=25646 r=0 w=0 time=6067730 us)
10000 VIEW (cr=25019 r=0 w=0 time=2636444 us)
10000 NESTED LOOPS OUTER (cr=25019 r=0 w=0 time=2329853 us)
10000 COLLECTION ITERATOR PICKLER FETCH (cr=0 r=0 w=0 time=291352 us)
5000 TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=25019 r=0 w=0 time=1275712 us)
5000 INDEX RANGE SCAN BIG_TABLE_PK (cr=20019 r=0 w=0 time=953202 us)(object id 56866)



Now, if we just extrapolate up to 1,000,000 rows:


ops$tkyte@ORA920> select 1000000/10000*12.66 from dual;

1000000/10000*12.66
-------------------
1266

1* select 1266/60 from dual
ops$tkyte@ORA920> /

1266/60
----------
21.1

it would take 21 CPU minutes to do this (trust me, elapsed wall clock time even longer)!!! So, lets modify the routine a little bit. You are using object types, nested tables, cast -- very cpu intensive -- that and the optimizer is guessing that there will be 8k rows in that little subquery as well (no stats for that "table()"). Lets create a small 1 row table:




drop table iot;
create table iot ( x int primary key ) organization index;
insert into iot values ( 1 );
analyze table iot compute statistics;



and rewrite out do_single_row routine "better"

create or replace
procedure do_single_row_better
( p_id in big_table.id%type,
p_owner in big_table.owner%type,
p_object_name in big_table.object_name%type,
p_object_type in big_table.object_type%type,
p_created in big_table.created%type,
p_last_ddl_time in big_table.last_ddl_time%type,
p_timestamp in big_table.timestamp%type,
p_status in big_table.status%type )
as
begin
merge into big_table t1
using ( select p_id id,
p_owner owner,
p_object_name object_name,
p_object_type object_type,
p_created created,
p_last_ddl_time last_ddl_time,
p_timestamp timestamp,
p_status status
from iot
where x = 1 ) t2
on ( t1.id = t2.id )
when matched then
update set t1.owner = t2.owner,
t1.object_name = t2.object_name,
t1.object_type = t2.object_type,
t1.created = t2.created,
t1.last_ddl_time = t2.last_ddl_time,
t1.timestamp = t2.timestamp,
t1.status = t2.status
when not matched then
insert ( id, owner, object_name, object_type,
created, last_ddl_time, timestamp, status)
values ( t2.id, t2.owner, t2.object_name, t2.object_type,
t2.created, t2.last_ddl_time,
t2.timestamp, t2.status );
end;
/


No temporary variables, less procedural code, uses all real tables (analyzed). The tkprof is now:

MERGE into big_table t1
using ( select :b8 id,
:b7 owner,
:b6 object_name,
:b5 object_type,
:b4 created,
:b3 last_ddl_time,
:b2 timestamp,
:b1 status
from iot
where x = 1 ) t2
on ( t1.id = t2.id )
when matched then
update set t1.owner = t2.owner,
t1.object_name = t2.object_name,
t1.object_type = t2.object_type,
t1.created = t2.created,
t1.last_ddl_time = t2.last_ddl_time,
t1.timestamp = t2.timestamp,
t1.status = t2.status
when not matched then
insert ( id, owner, object_name, object_type,
created, last_ddl_time, timestamp, status)
values ( t2.id, t2.owner, t2.object_name, t2.object_type,
t2.created, t2.last_ddl_time,
t2.timestamp, t2.status )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 11.94 12.60 0 35587 23194 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 11.95 12.61 0 35587 23194 10000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 542 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
20000 MERGE (cr=35627 r=0 w=0 time=3548112 us)
10000 VIEW (cr=35009 r=0 w=0 time=1010034 us)
10000 NESTED LOOPS OUTER (cr=35009 r=0 w=0 time=933976 us)
10000 INDEX UNIQUE SCAN SYS_IOT_TOP_56881 (cr=10000 r=0 w=0 time=401922 us)(object id 56882)
5000 TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=25009 r=0 w=0 time=353633 us)
5000 INDEX UNIQUE SCAN BIG_TABLE_PK (cr=20009 r=0 w=0 time=150502 us)(object id 56878)


So, it is a little better, but not good enough. Lets do it "right". forget the code, lets just go for it, single SQL statement:



merge into big_table t1
using ( select * from updates ) t2
on ( t1.id = t2.id )
when matched then
update set t1.owner = t2.owner,
t1.object_name = t2.object_name,
t1.object_type = t2.object_type,
t1.created = t2.created,
t1.last_ddl_time = t2.last_ddl_time,
t1.timestamp = t2.timestamp,
t1.status = t2.status
when not matched then
insert ( id, owner, object_name, object_type,
created, last_ddl_time, timestamp, status)
values ( t2.id, t2.owner, t2.object_name, t2.object_type,
t2.created, t2.last_ddl_time,
t2.timestamp, t2.status )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 1.03 1.03 0 15783 8319 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.05 1.05 0 15783 8319 10000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 542

Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE (cr=15832 r=0 w=0 time=1042526 us)
10000 VIEW (cr=15141 r=0 w=0 time=474658 us)
10000 NESTED LOOPS OUTER (cr=15141 r=0 w=0 time=440052 us)
10000 TABLE ACCESS FULL OBJ#(56889) (cr=127 r=0 w=0 time=49104 us)
5000 TABLE ACCESS BY INDEX ROWID OBJ#(56887) (cr=15014 r=0 w=0 time=276923 us)
5000 INDEX RANGE SCAN OBJ#(56888) (cr=10014 r=0 w=0 time=166679 us)(object id 56888)


now, that -- that is significant. what about 1,000,000 rows?

merge into big_table t1
using ( select * from updates ) t2
on ( t1.id = t2.id )
when matched then
update set t1.owner = t2.owner,
t1.object_name = t2.object_name,
t1.object_type = t2.object_type,
t1.created = t2.created,
t1.last_ddl_time = t2.last_ddl_time,
t1.timestamp = t2.timestamp,
t1.status = t2.status
when not matched then
insert ( id, owner, object_name, object_type,
created, last_ddl_time, timestamp, status)
values ( t2.id, t2.owner, t2.object_name, t2.object_type,
t2.created, t2.last_ddl_time,
t2.timestamp, t2.status )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.31 0 0 0 0
Execute 1 67.60 277.51 14310 55205 2159380 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 67.61 277.82 14310 55205 2159380 1000000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 542

Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE (cr=55544 r=14310 w=0 time=277757501 us)
1000000 VIEW (cr=26882 r=14304 w=0 time=54631955 us)
1000000 HASH JOIN OUTER (cr=26882 r=14304 w=0 time=51744510 us)
1000000 TABLE ACCESS FULL OBJ#(56903) (cr=12561 r=0 w=0 time=1592967 us)
1000000 TABLE ACCESS FULL OBJ#(56901) (cr=14321 r=14304 w=0 time=31082443 us)


there we go -- a couple of minutes, not a couple of hours! 1 cpu minute (lots less then 21) Hugely different.


So, the best way to make this have "fast=true"? Use set based operations, a single merge. lose the slow by slow (oops -- meant row by row) processing.