Databuilts are very slowly

[DWHEntwickler]

Hello,

i hope anybody can help me, because the execution of databuilts are very slowly.

We have one virtual Server with :

Windows 2003 x64 Enterprise Edition
Cognos 8.4.1 Data Manager with SAP R3 Connector
McAfee VirusScan Enterprise
4 CPU (Intel Xenon X5560)
8 GB RAM
SAN Networkstorage
Oracle 11R Client (32 Bit Libery)
Oracle 11R 64-Bit DataBase

The SQL is very simple and has 2.000.000 rows as result.
The Delivery are 10.000 Rows per 12 seconds.

CPU-Utilization under 10%
Memory Utilization under 10%
Network Utilization under 5%
Disk-Utilization under 10%
DB-Utilization under 3%

Not Even the Process Explorer shows information where the bottleneck could be.  ???

best regards

PS: Sorry for my bad english

[MFGF]

What source(s) are you extracting from?  If you turn on detail logging and look at the results in the log file after the build completes, are the source rows being read at a constant rate, or is there a long pause before the first row is read, or do they start reading in quickly then get slower, or...?

Are the builds merging or aggregating rows?  If so, they will be reading all rows into memory then delivering them all at the end of the build.  If this is the case, how long is the input phase taking before delivery begins?

What target(s) are you delivering to?  Similar questions to those for input - are the rows being delivered at a constant rate, or is there a long pause before the first row gets delivered, or do rows start being delivered quickly, then get steadily slower?  What refresh type are you using on delivery?  If you are using UPDATE or UPDATE INSERT, delivery may be slow as a result of the update process - are there any indexes to speed up the UPDATE statements on the target table(s)?

Lots of questions - but with a little luck, one of them may point you in the right direction.

Regards,

MF.

[DWHEntwickler]

Thanks for your ideas.

We read only one Oracle Table, without merging or aggregations.
The target is also an Oracle Table.
TRUNCATE -Mode without any Index, constraint or triggers etc..

The ETL Server and the Oracle DB has multinetwordadapter in an GigBit VLAN.

Built logging is disabled. The rows being read at an constant rate.

The DataManager Systemvariable are standart, no specifics are set.

Fetching rows in the Databuilt-Datasource needs 8 seconds for 10.000 rows.
The same SQL  needs 10 sec for 200.000 rows at Quest SQL Navigator (SQL-Editor) on the same hardware  ???


We think the problem could be the liberys or the interface, which are used by the DataManager.

[MFGF]

How have you configured the connection to Oracle from within Data Manager? Are you using ODBC or the native client libraries?

If you run the build in check-only mode (ie without delivering anything), do you see the same retrieval performance or does it imptrove?

MF.

[DWHEntwickler]

I use the native Client Liberays by Oracle 11R2 Client (32-Bit).

At Check-Only Mode, it´s the same bad performance.

If i set 
DM_UDA_FETCH_ROWS = 50000
DM_UDA_BULKWRITE_ROWS = 2500
then it will be a little bit faster and the Built needs 45min for success.

if i use SQL Plus to execute the SQL, then the  Query has fetched all rows after 5min and the database use parallel execution (up to 32 degrees). But the databuiltprocess doesn´t use paralell execution (The Database is configured to manage parallel execution automatically ).

Regards.