ETL load and Linux server performance

Hi Guys,

One of the things that comes into picture is the speed at which a ETL server is creating data file which has to be loaded. If the data file is about 20 GB in size because you are pulling from one database like oracle and loading the data into Teradata. Then the speed at which ETL server is able to pull the data from source becomes very important.

We were noticing that our Server was taking 1 hour to build 1 GB data file which was very slow. So we decided to look into the bandwidth of server. Now considering the IT setup you need to contact Linux server teams to get the information. But knowing few basics System Activity commands helps you to know first hand what is happening on linux server

SAR ( System activity monitor )

Note - we tried using SAR but it was not giving proper information on what amount of data is coming in from network Try using NMON instead 

Long running Loads and one Inactive session

Hi Guys,

Usually by looking at V$session, active session history we are able to find the issue with the load, what is happening. What are the event on which load is waiting like reading from temp space, updating index. However this was one long running load where i was not able to find any information on what is happening for almost half a day as if load is not running. 

Below are 3 of my favourite links from Oracle which will help you understand your ETL loads better than ever. Read it as generic examples. It can be applied to Sql server, Teradata anything

What is Session and How to find what is happening with ETL load

Different database events on which a session can be waiting and how to analyse them

How to find why a data load took long time. Analysis done after load has finished

 

Below is my analysis on session which Active session history did not capture 

Note - ACTIVE SESSION HISTORY will capture only if the session is active in case of long loads the session becomes inactive and active session history will not capture it DBA_HIST_ACTIVE_SESS_HISTORY

select * from DBA_HIST_ACTIVE_SESS_HISTORY--- look into this to see which session are running

select event,p1text,p2text,wait_class,blocking_session_status,current_obj#

,pga_allocated,temp_space_allocated from DBA_HIST_ACTIVE_SESS_HISTORY

MASTER QUERY

select count(event),event,current_obj#,object_name

 from DBA_HIST_ACTIVE_SESS_HISTORY,dba_objects  where sql_id = 'ABCCFDFDDD'

 and current_obj# = object_id

and sample_time >= '27-MAR-14 05.00.02.829000000 AM'

 group by event,current_obj#,object_name

 order by 1 desc

 

SQL loader does not perform Bulk load when using Pentaho

Hi Guys,

We are trying to bulk load 200 million rows into staging table from source by using Sql Loader direct path load (Bulk load). When we tried using explain plan on the sql fired by SQL loader it uses a hint called SYS_DL_CURSOR which does a conventional load.

Speeds worth to be mentioned  ---SQL loader loads 180k pers seconds which is 11 million per minute.

Db links directly can load -- 3.5 millions per minute . Loaded 180 million in 60 minutes. Some time spent on sql read

Reason -- We were using a option in Pentaho which says load on the fly. So it reads the data from sql and starts loading into oracle as rows come in. The problem is you never have enough rows available for sql loader to go for Bulk load. so it goes for conventional load.

Solution -- Use Automatic load at the end. Any option which creates a Data file on the ETL server and then SQL loader will load from this data file. This will allow sql loader to go for direct load

Note -- The hint  SYS_DL_CURSOR does not matter . Even if the explain plan shows that it is doing a conventional load. You should look into log file of Sql loader you will come to know whether it is doing direct load or conventional load. I have lot of confusion because the explain plan was showing conventional load.

Analysis which was flawed and you might even do this so just noting down. Even this did not work because it never had enough data to do direct load. 

Note - I have tried to manually create SQL loader control file and triggering it from the Linux server. Still it adds the hint

SQL loader control file (.ctl)

load data

 infile '/archive/Load_trial.txt'

 into table ABC

 fields terminated by "," optionally enclosed by '"'

( SRC,

CD ,

SRC_VAL_ID,

GG_CD ,

NMT_IND,

DENOM_IND,

NMT_XCLS_IND,

DENOM_XCLS_IND,

DENOM_XCPT_IND,

RSLT_ANS,

SR_EPSD_ID,

XCLS_RSN_CD,

VAL_TYP_CD,

CREAT_USER_ID,

CREAT_DTM DATE 'mm/dd/yy')

Command to load using SQL loader

sqlldr userid=username/password@servicename control=archive/Trial_load1.ctl log=archive/Trial_logg.log DIRECT=TRUE

 Alternate solution 

Create a procedure using db links which loads data from source schema to target schema. Procedure will perform good because you are eliminating ETL server in the middle. But you will be limited by the speed of the db link connection. DBA will not agree because DB links are a security issue as they expose the data over the network. But keep the procedure ready as contigency measure if load fails and you want urgent load 

Alternate Solution 2 

Data pump.  Pull the tables required from source schema to target schema using data pump and write procedure in target schema to load the data from these pulled source tables. Increadible speed can be achieved.  200  million records can be inserted in less than 1 hour in oracle. 

Data pump is fast and can easily give you speed of 20 million per minute when creating a dump file for import/Export