Re: Oracle ODBC 2.5 slams my 7.3.4!

I suggest looking at what the ODBC client applications are doing. "Friendly" applications like Access and Excel can sometimes do things (like fetching a list of all the tables, columns, and indexes in the database) that can kill you. They may also generate incredibly un-optimized SQL.

Turn on ODBC Logging on the client, and see what queries the offending applications are sending to the database. Access and Excel can be submitting queries that are not optimized. ODBC tracing can be enabled via the ODBC control panel. Start it, close the control panel, run the app, then turn it off. Look for calls to SQLExec and SQLExecDirect.

I'm 99% certain that the ODBC driver is only connecting to the instance specified in the connect string. The Instance name is one of the required values. Can you check this with V$SESSION? We have multiple Oracle instances on one of our Unix boxes, and have not seen the connection behavior you describe.

--
Christopher Payne, MCSE

"Raindrop Support" <support_at_Raindrop.co.uk> wrote in message news:942753431.3823.0.nnrp-10.c2de8940_at_news.demon.co.uk...
> We have a multi-cpu NT server running 7.3.4. It has a single instance with
> >50 tablespaces (client data copies). Any application that connects via
ODBC
> (Access, Excel) seems to really hammer the Oracle. Performance really
dies.
>
> Our normal applications are based on Uniface and do not use ODBC but
SQL-NET
> directly. This does not impact our Oracle.
>
> We believe that when the ODBC connects it accesses all the instance and
then
> restricts itself to the logon's rights. If this is true, it is accessing
the
> central data dictionaries which based on 50+ tablespaces with 1400+ tables
> in each one is presumably quite large. Is there anything we can do to
reduce
> the ODBC impact?
>
> TIA.
>
>
Received on Tue Nov 16 1999 - 21:47:11 CST