JobStream very slow delivery

[NuniPR]

Hi Guys!

I have a JobStream that has 4 dimension builds and 3 Fact Builds. Build E extracts data, Build T transforms it and Build L loads/creates the end fact table.

Build E handles about 1 million records, while build T and L handle about 2 million. Due to a time factor, some of the tables needed for the whole ETL process, are loaded into the database as snapshots of the production DB. These run perfectly fine on our Oracle 8i database. The JobStream updates/inserts data into a table.

Up until a week ago this and all ETL processes were running perfectly. Now, a simple JobStream that used to take 2 minutes now takes 25!! The one that I'm mentioning at the beginning used to take 3 hours, now takes 13 hours!

The logs don't show any errors nor the database or the database server (Sun). The Windows server where Cognos is installed did show some processor errors, which we corrected by changing both processors and the motherboard. We even changed the network card for the server, and did some testing by connecting the Windows server directly to the Sun.

I troubleshooted and tested after each change, and still took that long to process. I don't know what else to do! I have re-created indexes, tables, and everything. Still no improvement.

Any suggestions?

Thanks in advance for your help!




Nunina
Cognos BI Administrator
San Juan, PR

 

[KingCrab]

The best thing you can do is view the logs from DS. You should see a significant hike in time on one of the logs.

If you view the logs you can see each step the jobstream takes and can figure out how long it took to complete that part of the jobstream.

I would look for a giant variance in time from a log one week old and a present log.

Once you find that you should be able to see the problem. If it is spread out evenly then I would check your server memory.

 

[NuniPR]

Hello King,

I did that. That's how I know the variance in time, and what I am using to validate the time difference.

What I need to know is if anyone has had the same problem, and how/what they did to solve it. Like I mentioned before, the problem is not a memory problem since my Windows Server has 2 CPU's each 2.4 GHz, 2 GB of RAM which we just replaced for new ones.

Any other thoughts, would be greatly appreciated.



Nunina
Cognos BI Administrator
San Juan, PR

 

[KingCrab]

So is the variance spread across all jobs or is it one job? If it is one job what is that job doing?

 

[NuniPR]

Hi,

Actually it is across all jobs. The specific job that I mentioned, updates/inserts data into a table that already has data.

On most of the other jobs, it truncates the data before inserting it. On other jobs it appends the data.

Still, doesn't matter what it's doing, the time variance is consistent across all jobs.

Thanxs,



Nunina
Cognos BI Administrator
San Juan, PR

 

[KingCrab]

Are querys running slow as well? Honestly, 3 mill rows taking 3 hours seem extremely high. 25 hours is insane.

I can ETL my 3-4 million row dump from production in less then 30 minutes and that is 18 dimension and 5 facts with 12 of theose dimension being SCD 2.

 

[NuniPR]

Exactly!

It was working fine up until a few weeks ago. Some of these ETLs have been working fine for years!

Any suggestions as to how I can make it go as fast as it did before?

Nunina
Cognos BI Administrator
San Juan, PR

 

[KingCrab]

My only guess would be that something is running on your server or some server that is being used in the ETL process that was not running previously.

I would ask your DBA is ANYTHING new was added to the server or if any changes have been made at all to anything in the ETL process.

Things just can't slow down without something changing or failing somewhere in your ETL.

Personally I would get the time it takes to Extract from live to dev. then I would run that same extraction from dev to dev or from another server entirely. I would do this to see if there is an issue with Live or Dev first.

If Dev is still slow then there is a problem on this box. To proove it I would run the extract from Live to a Temp_Dev area on another server and get the time. This would exclude the "suspect DB server problem" and hopefully get you some answers. If not, then I would run DS from another box and give it a whirl.

 

[NuniPR]

Hi King,

Sorry to answer so late. Ended up changing jobs.

As far as I understand, the problem never got solved. What they did was to re-install Cognos in another server, and move everything to the new server. Now it's working fine.

I believe this was a temporary fix. Although I don't work there any longer, I think it should've been looked into a little deeper.

Anyway, thanks for your suggestions.

Nunina