How to tell what's going on with complex queries on linked oracle tables

[mat]

What's the most informative manner of getting a good picture of what's
going on when a complex query is run against linked oracle tables? I
have been using oracle traces but perhaps because I'm not a wiz at
setting them up, I'm not getting all the detail out of them that I'd
like.

With Microsoft odbc drivers there is a way to have the driver output to
a text file, which can be useful. But here I'm using an oracle driver
and I don't see any such trace outpu offered.

The query runs too long and I know why; but I'd like to 'see' what
exactly takes so long to get done.

 

[AccessVandal via AccessMonster.com]

In the ODBC Administrator - Tracing - click Start Tracing Now. The log file
should be in the users Temp folder, look at the Log File Path Field, that's
where the file is located.

 

[Stefan Hoffmann]

hi Mat,

What's the most informative manner of getting a good picture of what's
going on when a complex query is run against linked oracle tables?

I've always used the EXPLAIN PLAN which outputs the used query plan
after executing the query.

I've used it in conjunction with sqltools.net Oracle development
frontend (it's free and sufficent as long as you're not an Oracle DBA .


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I've always used the EXPLAIN PLAN which outputs the used query plan
after executing the query.

The EXPLAIN PLAN show what Oracle does, but I assume that using
JETSHOWPLAN is what you really want:

http://articles.techrepublic.com.com/5100-10878_11-5064388.html

btw, you should consider moving at least "long running" queries to
Oracle views and link these as tables.


mfG
--> stefan <--