QV Extract Performance drop with upgrade to Oracle 11g vs. 10g

[jbeierschmitt]

We just upgraded the Oracle Business Intelliegence (OBI) environment from 10g to 11g and now have much longer reload durations for the exact same tables.  All other variables are identical - QlikView 11.0 x64, Oracle 11.2.0 Client (OraOLEDB), running from QMC, same server, same data tables.

Anyone have experience with similar change in performance after they upgrade from 10g to 11g?  There are several avenues to examine, but wanted to check in with QlikCommunity to see if this is a familiar issue for someone.

Example:

W_SALES_INVOICE_LINE (113 fields, 3,908,751 rows)

- 10G: 1h 9m

- 11G: 4h 28m

I will follow up to this post as we learn more.

Thanks

Jim

[#2]

Jim,

I understand you have upgraded the oracle bi and database from from 10g to 11g, is it correct?

A)

If you have upgraded the db you can have performances issues related to the database or to the connection OraOLEBD.

Try to rum the same queries from SQLPlus to determine if the performance problem is related to the db or to the conection. If you have a new db version, you need a new Oracle client; have you upgraded the Oracle Client?

B)

If haven't upgraded the DB. Oracle BI 11g has a much bigger hw requeriments than 10g; mainly because 11g uses WebLogic as apps server.If you have OBI 11g and the database in the same machine you will have a performance drop from Qv and from any tool. Try to run the same queries from SQL Plus.

Hope this help, Ferran

[jbeierschmitt]

Hi Ferran,

Yes, the Oracle DB is a brand new environment located off site (whereas the previous environment was in our corporate network).  The Oracle client is 11.2.1, while the Oracle DB is 11gR2.  We installed TOAD on the same environment and it does not see any difference in the extract performance between the 10g and 11g environments.

The current theory is to determine whether the OLEDB settings need to be tuned for 11g database.  I'm not sure what would have such a dramatic performance hit to the QV extract performance?

Any thoughts on this approach?

Jim

[Ferran_Garcia_Pagans]

Jim,

I don't usually use Toad, but I think Toad uses an ODBC or OLEDB connection. SQLPlus uses an orcl native connection. If I were you I would try to run the same queries from the sql installed on the db server. In this way I'll be able to know if it is an DB or connectio problem.

Do you have OBI and DB in the same machine?

Ferran.

[jbeierschmitt]

I have a colleague who ran the SQL queries directly against the OBI database and they did not see any difference in extract duration between the two environments.  We started down the path of this being a connection problem, but the additional time is only 1ms to 4 ms (additional as the new environment is hosted off site) greater than the previous environment.

I will see if we can test using the SQLPlus