Performance problem even after tuning...URGENT

deepak_b73 · Post by **deepak_b73** » Fri Apr 17, 2009 2:44 am

Ray,

As suggested by you I used stage tracing on transformer. I'm not bale to interpret data collecetd.It is as below.This is from different job.

Project:WIPHYP (10.201.51.161)
Job name:GeoVertLkp_Files
Event #:72
Timestamp:4/17/2009 2:03:40 PM
Event type:Info
User:vintipa
Message:
GeoVertLkp_Files..Transformer_36: DSD.StageRun Performance statistics
Name Percent Count Minimum Average
GeoVertLkp_Files..GeoCC.Geo 38 306747 36 46
GeoVertLkp_Files..Geo_HSH_AR.DSLink40 49 306747 38 62
Status.Update 0 43 61 101
DSLink40.Derivation 2 306747 2 3

Can you please tell me what these statistics mean and how to use it to tune transformer.

I don't have access to premium content. Please give details without premium content.

Regards,
Deepak

deepak_b73 · Post by **deepak_b73** » Fri Apr 17, 2009 2:48 am

kryt0n,

Three lookups are not loading data into memory. Preload to memory is disabled while loading data into hash files.

every row from source has to gothrough all three lookups to fetch data.

Regards,
Deepak

deepak_b73 · Post by **deepak_b73** » Fri Apr 17, 2009 3:00 am

kryt0n,

Small correction to my earlier note. Pre load to memeory has been enabled.This we tried. After this job took 20 minutes less to complete.That means 17 minutes of performance improvement.
But still it takes 43 minutes. IS there any seeting needs to be done at oracle bulk load stage to improve performance.

Regards,
Deepak

ray.wurlod · Post by **ray.wurlod** » Fri Apr 17, 2009 3:01 am

It doesn't matter what you do when loading the hashed files. This is the write cache. When you're using the hashed file stage to source rows for a reference link, you can enable the read cache, which is independent of the write cache.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 17, 2009 3:04 am

deepak_b73 wrote:I don't have access to premium content. Please give details without premium content.

Premium membership is one of the ways that the hosting and bandwidth costs of DSXchange are defrayed, and I do not intend to establish a precedent for anything that would prejudice that, and the continuing existence of DSXchange.
Good information about intepreting the gathered statistics is to be had both in on-line help and in the manual.