Unanswered: Server go crazy by running a basic procedure !
I have this procedure on the server :
delete from FAI_StockAchat a
select 1 from FAI_StockAchat_temp b
(b.D_Pays) like '%'||(a.D_Pays)||'%' And
(b.D_ZonGeo) like '%'||(a.D_ZonGeo)||'%' And
(b.D_RefSoc) like '%'||(a.D_RefSoc)||'%' And
(b.D_RefUsine) like '%'||(a.D_RefUsine)||'%' And
(b.D_GenRefTypeArt) like '%'||(a.D_GenRefTypeArt)||'%' And
(b.D_GenRefArt) like '%'||(a.D_GenRefArt)||'%' And
(b.D_AppFam) like '%'||(a.D_AppFam)||'%' And
(b.D_AppSFa) like '%'||(a.D_AppSFa)||'%' And
(b.D_AppSsFa) like '%'||(a.D_AppSsFa)||'%' And
b.F_DateSTEvt like '%'||(a.F_DateSTEvt)||'%' And
b.F_HeureMvtStock like '%'||(a.F_HeureMvtStock)||'%'
insert into FAI_StockAchat
(select * from FAI_StockAchat_temp);
I call it from a batch. The table _temp has generally 50000 lines and the original table 10000.
The problem is that when the procedure is running, the CPU of the server is at 100% !!!
The table gets locked, the session is actived but after one hour, nothing is done.
I suppose that the architecture of the database is not well defined
(I didn't build the db but it's got the classic tablespaces definitions user,index,rds etc...)
What should I do to optimize this cause I think that even if the SQL code has an exist statement
and a where clause with plenty of like %, it shouldn't bring down the server (it's not a hardware
performance problem, I have a pretty good CPU with plenty of RAM) !!!!