Home » RDBMS Server » Performance Tuning » Improving data loading into tables
Improving data loading into tables [message #301005] Tue, 19 February 2008 00:01 Go to next message
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 #301015 is a reply to message #301005] Tue, 19 February 2008 00:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> that loads data into 20-23 tables from txt files each night
How exactly?
Sqlldr? If so post the exact command line.
did you try direct load?
Or is the application using other methods (read the file line by line and load it?)
Did you try external tables?
Re: Improving data loading into tables [message #301024 is a reply to message #301005] Tue, 19 February 2008 00:35 Go to previous messageGo to next message
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 #301028 is a reply to message #301024] Tue, 19 February 2008 00:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Then no wonder it runs slow.
Use sqlldr direct loads to load into staging tables and make use of MERGE statements.
Or
Use external tables where the external text files are treated like oracle tables and you can move data with sql means with some restrictions.
Just search this forum/google/ documentation for external tables.
Re: Improving data loading into tables [message #301029 is a reply to message #301024] Tue, 19 February 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external tables and MERGE statement and don't use the application.

Regards
Michel
Re: Improving data loading into tables [message #301030 is a reply to message #301024] Tue, 19 February 2008 00:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> How external tables can help me in this case if all the work is done by the application?
Sorry i missed this.
If you cannot 'hack' the application (whatever it is) to change its
behavior, there is nothing much you can do.
See if you can employ these methods directly.
Re: Improving data loading into tables [message #301041 is a reply to message #301005] Tue, 19 February 2008 00:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Smile.
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 Go to previous messageGo to next message
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;
Re: Improving data loading into tables [message #301074 is a reply to message #301005] Tue, 19 February 2008 02:34 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member

Even if I use external tables I 'll have to write
a script that again should update or insert new row into the base tables reading data from these external tables what will take me to the initial problem, will not it?
Re: Improving data loading into tables [message #301092 is a reply to message #301074] Tue, 19 February 2008 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
should update or insert new row into the base tables reading data from these external tables what will take me to the initial problem, will not it?

This is the purpose of MERGE.

Regards
Michel
Re: Improving data loading into tables [message #301094 is a reply to message #301092] Tue, 19 February 2008 03:25 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
This is the purpose of MERGE

Precisely.
Previous Topic: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g.
Next Topic: After Collecting Statistics database is very very slow
Goto Forum:
  


Current Time: Sat Dec 10 22:30:04 CST 2016

Total time taken to generate the page: 0.13721 seconds