Home » RDBMS Server » Performance Tuning » Improving data loading into tables
| Improving data loading into tables [message #301005] |
Tue, 19 February 2008 00:01  |
irremediable
Messages: 38
Registered: December 2007
|
Member |
|
|
Hello!
I'm using an application that loads data into 20-23 tables
from txt files each night. Stats gathered every day after data loaded into the tables.
It used to take utmost 3 hr for the entire loading process to complete.
This the third day it takes more than 5 hr to do the same.
I've noticed that three tables have started to take more time to load.
I tried ALTER TABLE.....MOVE and rebuilt indexes but it didnt improve anything.
Temp is half full and undo is one-third full.
I've searhed google but couldnt find anything to start with
to find solutions to a problem like this one.
Smth like a......some space is allocated for the table but
it uses only a little part of it, what can cause performance degradation because Oracle scans more space to read the neccesary data.
What could be the possible reasons?
What docs could you forward me to in order to solve this?
Thank's!
|
|
|
|
|
|
| Re: Improving data loading into tables [message #301024 is a reply to message #301005] |
Tue, 19 February 2008 00:35  |
irremediable
Messages: 38
Registered: December 2007
|
Member |
|
|
It's not loaded with the aid of sqldlr.
The whole process of loading consists of two steps.
First txt files loaded into Gate-Tables under a certain
schema. Then by means of certain scripts
data from these tables are loaded into base tables under the schema DWH.
Scripts are smth like this
UPDATE TABLE1 WHERE....update a row in the tables if a row was updated in the source
INSERT INTO TABLE1 VALEUS if a new row to be inserted.
How external tables can help me in this case if all the work is done by the application?
|
|
|
|
|
|
|
|
|
|
| Re: Improving data loading into tables [message #301041 is a reply to message #301005] |
Tue, 19 February 2008 00:59 |
irremediable
Messages: 38
Registered: December 2007
|
Member |
|
|
Thank's. Really I cant 'hack' the application.
And users viewing reports every morning and migrating to external tables might take some time what can stop report generation for some period in my case.
Do you mean that if the tables will get large enough then the application might come needless, which I've been using
for almost a half and a year and everything was ok!?
Although I've benn using the appl for such along time, I wasnt
paying much attention to administration.
And now I'm stuck.
Seems the matter is in those three tables that slow down the loading process because the other tables now loading as they used to.
So is there anything you can advice to do with the tables in order to improve UPDATE and INSERT operations?
What possible reasons could be?
There must be at least some general rules to start with, arent there?
|
|
|
|
| Re: Improving data loading into tables [message #301044 is a reply to message #301041] |
Tue, 19 February 2008 01:05 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
| Quote: | UPDATE TABLE1 WHERE....update a row in the tables if a row was updated in the source
INSERT INTO TABLE1 VALEUS if a new row to be inserted.
|
These are expensive statements if you are doing it row by row.
It is what it is. No other workaround. Time to rewrite the application loading logic  .
The said workarounds are "like" bulk processing where you do not do things in a row level. MERGE statement is time saver.
Instead of doing update if found, insert if not found, MERGE will do it much efficiently.
You can use these methods to load data into staging tables, validate the data, load into your production tables.
Seems you cannot afford it anyhow.
[Updated on: Tue, 19 February 2008 01:07] Report message to a moderator |
|
|
|
| Re: Improving data loading into tables [message #301053 is a reply to message #301005] |
Tue, 19 February 2008 01:30 |
irremediable
Messages: 38
Registered: December 2007
|
Member |
|
|
Here is a sample script for one table
DECLARE
l_ID_FILE ud.id;
l_DT_OPEN ud.name;
BEGIN
l_ID_FILE := :P1;
l_DT_OPEN := :P2;
-- 1.1 ÎÁÍÎÂËßÅÌ ÏÐÈ ÑÎÂÏÀÄÅÍÈÈ DT_OPEN È WH.SYSMOMENT < BF.SYSMOMENT ÑÒÀÒÓÑ = 0
UPDATE DWH.DET_BALANCE WH
SET (WH.SYSMOMENT,WH.ID_FILE,WH.ID_R050,WH.ID_TREST,WH.COM_BALANCE,WH.NS_BALANCE,WH.NL_BALANCE,WH.WHMOMENT) =
(SELECT BF.SYSMOMENT,BF.ID_FILE,BF.ID_R050,BF.ID_TREST,BF.COM_BALANCE,BF.NS_BALANCE,BF.NL_BALANCE,sysdate FROM LDR_BF.DET_BALANCE BF
WHERE WH.CODE_BALANCE= BF.CODE_BALANCE and WH.DT_OPEN= BF.DT_OPEN and WH.ID_PLAN_ACC= BF.ID_PLAN_ACC
AND BF.REC_STATUS in(0,2)
AND WH.SYSMOMENT < BF.SYSMOMENT)
WHERE WH.ROWID IN (
SELECT WH.ROWID FROM LDR_BF.DET_BALANCE BF,DWH.DET_BALANCE WH
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.REC_STATUS in(0,2)
AND WH.DT_OPEN = BF.DT_OPEN
AND WH.SYSMOMENT < BF.SYSMOMENT) ;
-- 1.2.2 ÎÁÍÎÂËßÅÌ ÏÐÈ ÏÎÏÀÄÅÍÈÈ DT_OPEN  ÄÅÉÑÒÂÓÞÙÈÉ ÏÅÐÈÎÄ (ÑÒÀÒÓÑ 0)
UPDATE DWH.DET_BALANCE WH
SET (WH.SYSMOMENT,WH.ID_FILE,WH.DT_CHANGE,WH.ID_R050,WH.ID_TREST,WH.COM_BALANCE,WH.NS_BALANCE,WH.NL_BALANCE,WH.WHMOMENT) =
(SELECT BF.SYSMOMENT,BF.ID_FILE,BF.DT_OPEN,BF.ID_R050,BF.ID_TREST,BF.COM_BALANCE,BF.NS_BALANCE,BF.NL_BALANCE,sysdate FROM LDR_BF.DET_BALANCE BF
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.DT_OPEN > WH.DT_OPEN
AND BF.DT_OPEN <= WH.DT_CLOSE
AND ((BF.DT_OPEN > WH.DT_CHANGE) OR ((BF.DT_OPEN = WH.DT_CHANGE) AND (BF.SYSMOMENT > WH.SYSMOMENT)))
AND BF.REC_STATUS in(0,2) )
WHERE WH.ROWID IN (
SELECT WH.ROWID FROM LDR_BF.DET_BALANCE BF,DWH.DET_BALANCE WH
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.REC_STATUS in(0,2)
AND ((BF.DT_OPEN > WH.DT_CHANGE) OR ((BF.DT_OPEN = WH.DT_CHANGE) AND (BF.SYSMOMENT > WH.SYSMOMENT)))
AND BF.DT_OPEN > WH.DT_OPEN
AND BF.DT_OPEN <= WH.DT_CLOSE);
-- ÅÑËÈ ÅÑÒÜ ÇÀÏÈÑÈ WH.DT_OPEN > DT
UPDATE DWH.DET_BALANCE WH
SET (WH.DT_OPEN ,WH.WHMOMENT) =
(SELECT BF.DT_OPEN ,sysdate FROM LDR_BF.DET_BALANCE BF
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.REC_STATUS in(0,2)
AND BF.DT_OPEN <
(SELECT NVL(MIN(WH.DT_OPEN) ,TO_DATE('01.01.1901','DD.MM.YYYY'))
FROM LDR_BF.DET_BALANCE BF
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE))
WHERE WH.ROWID IN
(SELECT WH.ROWID FROM LDR_BF.DET_BALANCE BF,DWH.DET_BALANCE WH
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.REC_STATUS in(0,2)
AND BF.DT_OPEN <
(SELECT NVL(MIN(WH.DT_OPEN) ,TO_DATE('01.01.1901','DD.MM.YYYY'))
FROM LDR_BF.DET_BALANCE BF
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE));
-- ÅÑËÈ ÅÑÒÜ ÇÀÏÈÑÈ WH.DT_CLOSE < DT
UPDATE DWH.DET_BALANCE WH
SET (WH.DT_CLOSE,WH.SYSMOMENT,WH.DT_CHANGE,WH.WHMOMENT) =
(SELECT TO_DATE('01.01.3001','DD.MM.YYYY'),BF.SYSMOMENT,BF.DT_OPEN,sysdate
FROM LDR_BF.DET_BALANCE BF
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.REC_STATUS in(0,2)
AND ((BF.DT_OPEN > WH.DT_CHANGE) OR ((BF.DT_OPEN = WH.DT_CHANGE) AND (BF.SYSMOMENT > WH.SYSMOMENT)))
AND BF.DT_OPEN >
(SELECT NVL(WH.DT_CLOSE,TO_DATE('01.01.3001','DD.MM.YYYY'))
FROM LDR_BF.DET_BALANCE BF
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE))
WHERE WH.ROWID IN
(SELECT WH.ROWID FROM LDR_BF.DET_BALANCE BF,DWH.DET_BALANCE WH
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.REC_STATUS in(0,2)
AND ((BF.DT_OPEN > WH.DT_CHANGE) OR ((BF.DT_OPEN = WH.DT_CHANGE) AND (BF.SYSMOMENT > WH.SYSMOMENT)))
AND BF.DT_OPEN >=
(SELECT NVL(WH.DT_CLOSE,TO_DATE('01.01.3001','DD.MM.YYYY'))
FROM LDR_BF.DET_BALANCE BF
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE));
-- 1.3.1 ÑÎÇÄÀÅÌ ÍÎÂÛÉ ÏÅÐÈÎÄ - ÍÎÂÛÅ ÇÀÏÈÑÈ
INSERT INTO DWH.DET_BALANCE
(ID_BALANCE,IP_BALANCE,ID_PLAN_ACC,CODE_BALANCE,NL_BALANCE,NS_BALANCE,COM_BALANCE,ID_TREST,ID_R050,DT_OPEN,DT_CHANGE,DT_CLOSE,SYSMOMENT,ID_FILE,WHMOMENT)
SELECT DWH.RSDHObjSeq.Nextval,DWH.RSDHObjSeq.CURRVAL,BF.ID_PLAN_ACC,BF.CODE_BALANCE,BF.NL_BALANCE,BF.NS_BALANCE,BF.COM_BALANCE,BF.ID_TREST,BF.ID_R050,BF.DT_OPEN,BF.DT_OPEN,TO_DATE('01.01.3001','DD.MM.YYYY'),BF.SYSMOMENT,BF.ID_FILE,sysdate
FROM LDR_BF.DET_BALANCE BF
WHERE (BF.CODE_BALANCE,BF.ID_PLAN_ACC) NOT IN (
SELECT WH.CODE_BALANCE,WH.ID_PLAN_ACC FROM DWH.DET_BALANCE WH
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.REC_STATUS in(0,2) )
AND BF.REC_STATUS in(0,2);
:l_Out := SQL%Rowcount;
-- 2. ÎÒÁÐÀÊÎÂÛÂÀÅÌ Â ÊÎÐÇÈÍÓ ÇÀÏÈÑÈ ÑÎ ÑÒÀÒÓÑ = 1, ÍÅ ÏÎÏÀÄÀÞÙÈÅ ÍÈ Â ÊÀÊÎÉ ÏÅÐÈÎÄ
INSERT INTO LDR_BD.DET_BALANCE
(ID_BALANCE,ID_PLAN_ACC,CODE_BALANCE,NL_BALANCE,NS_BALANCE,COM_BALANCE,ID_TREST,ID_R050,IP_BALANCE,DT_OPEN,DT_CLOSE,SYSMOMENT,ID_FILE,PLAN_ACC_CODE,TREST_CODE,R050_CODE,REC_STATUS,BD_REASON,BD_CHANGED)
SELECT BF.ID_BALANCE,BF.ID_PLAN_ACC,BF.CODE_BALANCE,BF.NL_BALANCE,BF.NS_BALANCE,BF.COM_BALANCE,BF.ID_TREST,BF.ID_R050,BF.IP_BALANCE,BF.DT_OPEN,BF.DT_CLOSE,BF.SYSMOMENT,BF.ID_FILE,BF.PLAN_ACC_CODE,BF.TREST_CODE,BF.R050_CODE,BF.REC_STATUS,NULL,0
FROM LDR_BF.DET_BALANCE BF
WHERE (BF.CODE_BALANCE,BF.ID_PLAN_ACC) NOT IN (
SELECT WH.CODE_BALANCE,WH.ID_PLAN_ACC FROM DWH.DET_BALANCE WH
WHERE (WH.CODE_BALANCE,WH.ID_PLAN_ACC) IN (
SELECT BF.CODE_BALANCE,BF.ID_PLAN_ACC FROM LDR_BF.DET_BALANCE BF
WHERE BF.REC_STATUS in(1,3)
AND BF.DT_OPEN > WH.DT_OPEN
AND BF.DT_OPEN <= WH.DT_CLOSE))
AND BF.REC_STATUS in(1,3) ;
-- 2.1 ÇÀÊÐÛÂÀÅÌ DT_CLOSE ÏÐÈ ÏÎÏÀÄÅÍÈÈ DT_OPEN Â ÄÅÉÑÒÂÓÞÙÈÉ ÏÅÐÈÎÄ (ÑÒÀÒÓÑ =1 BF.SYSMOMENT > WH.SYSMOMENT)
UPDATE DWH.DET_BALANCE WH
SET (WH.DT_CLOSE,WH.WHMOMENT) =
(SELECT BF.DT_OPEN-1,sysdate FROM LDR_BF.DET_BALANCE BF
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.DT_OPEN > WH.DT_OPEN
AND BF.DT_OPEN <= WH.DT_CLOSE
AND BF.REC_STATUS in(1,3)
AND BF.SYSMOMENT > WH.SYSMOMENT)
WHERE WH.ROWID IN
(SELECT WH.ROWID FROM LDR_BF.DET_BALANCE BF,DWH.DET_BALANCE WH
WHERE WH.ID_PLAN_ACC = BF.ID_PLAN_ACC and WH.CODE_BALANCE = BF.CODE_BALANCE
AND BF.REC_STATUS in(1,3)
AND BF.DT_OPEN > WH.DT_OPEN
AND BF.DT_OPEN <= WH.DT_CLOSE
AND BF.SYSMOMENT > WH.SYSMOMENT);
-- 3 ÎÒÁÐÀÊÎÂÛÂÀÅÌ Â ÊÎÐÇÈÍÓ ÇÀÏÈÑÈ ÑÎ ÑÒÀÒÓÑ <> 0,1
INSERT INTO LDR_BD.DET_BALANCE
(ID_BALANCE,ID_PLAN_ACC,CODE_BALANCE,NL_BALANCE,NS_BALANCE,COM_BALANCE,ID_TREST,ID_R050,IP_BALANCE,DT_OPEN,DT_CLOSE,SYSMOMENT,ID_FILE,PLAN_ACC_CODE,TREST_CODE,R050_CODE,REC_STATUS,BD_REASON,BD_CHANGED)
SELECT BF.ID_BALANCE,BF.ID_PLAN_ACC,BF.CODE_BALANCE,BF.NL_BALANCE,BF.NS_BALANCE,BF.COM_BALANCE,BF.ID_TREST,BF.ID_R050,BF.IP_BALANCE,BF.DT_OPEN,BF.DT_CLOSE,BF.SYSMOMENT,BF.ID_FILE,BF.PLAN_ACC_CODE,BF.TREST_CODE,BF.R050_CODE,BF.REC_STATUS,NULL,0
FROM LDR_BF.DET_BALANCE BF
WHERE BF.REC_STATUS NOT IN(0,2,1,3);
END;
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:42:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
