Performance tuning in server job

[rajvarathan]

Hi ,
I need to tune a server job which loads data from a remote DB using DBlink. The data is retreived from 2 DBlinks,Unioned and loaded to oracle table.
The query is something like this:
Select * from tableA@DBlink1
where cond1 or cond2 or cond3 or cond4
Union
Select * from TableB@DBlink1.
The job takes more time to run .And in addition to the above query,Upper& NVL conditions are also given in the select sql for some columns.

Pls., suggest an approach to tune this job.Thanks in advance.

Regards,
Varadha.

[ray.wurlod]

Start by defining "performance" in an ETL context.

Hint: rows/sec is definitely NOT a meaningful measure of anything.

[rajvarathan]

Hi Ray,
In ETL context there is one OCA stage , a transformer and an output OCA stage used in the job. Since the remote DB is having millions of records, it takes 1-2 hours for completion...

[ray.wurlod]

And your key performance indicators are... ?

[rajvarathan]

The KPI here to be taken care of is load time of records from remote DB(which needs to be reduced).... and throughput.

[ray.wurlod]

Load the tables into local text files. Use cat to put them together for UNION ALL. If you need to remove duplicates, there are several strategies available, all of which need the file to be sorted.

And, of course, tune your database queries. Make sure that the tables have been analyzed so that the query optimizer can come up with the best possible plan.

[chulett]

Break the job into two pieces as noted, so each aspect of it can be 'tuned' - first the extract and then the load itself. Where is the actual bottleneck in your process?

[rajvarathan]

I have loaded the data to separate temp tables(For every query). Do you suggest text files to Oracle table load in reducing the load time?

[rajvarathan]

The actual bottleneck is with the load only(As it is from a remote DB).

[ray.wurlod]

With a text file you have the option of sqlldr.