Re: Oracle performance with Microsoft Project

tomthayer2_at_yahoo.com (tom thayer) wrote

> We are running Microsoft Project 2000 against tables in a database via
> ODBC. We have large projects and the performance is terrible when
> saving changes.

<snipped>
> We have done
> performance tuning and looked at Oracle Performance Manager and TOP
> SQL and see that the sql is optimized. Why is the SQL Server path so
> much faster than the Oracle path here?

Before you can performance tune, you need to identify the problem. And that in my book means running sql trace and using TKPROF to see just what the heck is happening between the application and Oracle.

My suggestion is to implement a database session trigger that will check the schema logon name and if that is the MS Project Schema, enabling sql trace in that session (using the dbms_system.set_sql_trace_in_session(sid,serial#,true) call). Easiest and most seamless way to enable sql tracing for 3rd party apps IMO.

Run the trace files via TKPROF after the MS Project update has been completed and then work with what is *really* happening under the hood between Oracle and MS Project (instead of trying to guess via OEM or other tools).

Question. Just how was that schema generated? Who created the tables and indexes and stuff in Oracle? MS Project via ODBC? If so.. then *OUCH*! No wonder you are having performance issues.

--
Billy