Re: Oracle performance with Microsoft Project

Rob Schneider <rmschne_at_stripthisoutbeebstripthisout.net> wrote i

> > 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.
> >
> What's the alternative to using MS Project via ODBC
> to create the tables and indexes for Oracle?

You still let MS Project do it via ODBC - then you dump the schema definition to a SQL script (using something like TOAD) and fix what is broken. Am thinking specifically of the physical definition of the schema. Using IOTs. Sequences and triggers. Storage clauses. Using partitioning to reduces i/o overheads. Making sure that everything is analyzed so that the CBO can work on more than a thumbsuck.

I'm sure that the TKPROF trace will point to one or more SQLs that are the real performance culprits. Then it should not be that difficult to determine what physical changes can be made in the schema to make these less of a performance problem. Maybe that will just require using OEM and not even re-building the schema from a dumped-and-fixed SQL script.

The problem with ODBC is that when a db independant product is used, the product does not use native and optimal SQL for the chosen db. You are relying on the ODBC driver to translate what the application says, into good-and-optimal-native-SQL for that db. The ODBC driver will not realise that a specific CREATE TABLE and CREATE INDEX are better served by an IOT.. or that partitioning suits a specific table.

Logical db design is good. However, one must never forget that in order to make that logical design work and work well, requires a (proper) physical design for the chosen db. And this is here when many ISV products using databases fall down. In fact, IMO, this is where many db developments also fail. Developers tend not to think about the physical aspects of the database - just slam the logical db design onto it and fire up the code generators and report writers.

--
Billy