9.4 export to Oracle speed

[AnthonyT]

Morning all

I am exporting data from a 7 dimension cube (pulling in ruled data from around 15 other 6/7/8 dimension cubes) to an Oracle 10g database. (TM1 version 9.4 MR1 64bit, 40Gb memory, 24 Core 2.8Ghz Six-Core AMD Opteron 8439 SE processor)

Depending on the time of day I get anywhere between 30 - 130 rows of data exported per second. Sounds ok, until you have to export 3 million rows

TM1 is being used as a calculation engine in a long line of Cognos ETL, with a handshake between Cognos. So the ETL processes sit and wait until TM1 has done all it calculations on its imported data and exported them out to a table.

If I export the data to a flat file instead of straight into an Oracle table, the export speed has been seen to be anywhere between 3 - 10 times faster.

I treid to export the cube data into Oracle from a cube that had the same structure, but the values had been copied across so that there were no feeders / rules. This did nothing for the export speed. I also exported have as much information per row of data, but this only increased the speed by around 10% (and that may not have been a true reflection, as the results also vary on time of day as well)

My questions are -
* Is this normal?
* What factors increase / decrease export speed?


Thanking you

Anthony

[David Usherwood]

Hi Anthony.
My guess it is the SQL inserts which are slowing the TI down. Suggest you push out to a file and use SQL*Loader to pump the numbers into Oracle.

[afshin]

Hi Anthony,

I would ask the resident Oracle DBA to have a look at the issue before looking at flat files etc.
This could largely be due to size of redo logs, rollback contention, indexes etc. I suggest you ask the DBA to execute dbms_system.set_sql_trace_in_session(170, 590, true); to understand the nature of the problem before using SQL Loader.

Alternatively consider using External tables in Oracle is a nice feature that you could explore.

Cheers

[AnthonyT]

Thanks for the replies

David - that was the way I was thinking I was going to go unfortunately, though there is talk of trying to use TM1 as a datasource and suck the data back into Oracle instead of push it out

Afshin - I will pass on your message to the people who have more Oracle knowledge than me (not hard) and see if this helps them

Thanks again