SQL Server Profiler can reveal only what's executing on SQL Server. But linked-server queries also execute on the Oracle server, so to fully analyze an Oracle linked-server query, you must use Oracle's SQL Trace facility. You can enable SQL Trace at either the session level or the instance level. You enable SQL Trace for the instance by adding the following lines to the Oracle parameter file, init.ora:
timed_statistics = true
sql_trace = true
Instance-level tracing slows the server but ensures that you won't miss anything. You can identify Oracle trace files, which reside in the UDUMP folder specified in the init.ora file, by their .trc extension.
When you issue a linked-server query, the OLE DB provider causes the server to execute an extra query, which obtains metadata from the Oracle data dictionary. SQL Server needs the Oracle metadata to complete the query resolution on the SQL Server side. For example, let's analyze the following query
SELECT * FROM oradb..SCOTT.BIGTAB WHERE orderid = 10248
where BIGTAB is an unindexed multimillion-row table. Two queries were executed, as Figure A's excerpt from the trace file shows.
You can use the Oracle utility TKPROF to reformat a trace file into a more readable format. However, finding the relevant data in the trace file is easy. In the trace file excerpt, look for tim, which represents the elapsed time, measured in hundredths of a second. Note that although only one query was submitted from the Query Analyzer, two queries were executed on the Oracle server. Although the first query's execution plan shows that the server executed a full table scan, this fact is misleading. The query doesn't have a FETCH statement and doesn't return data. Subtracting the first query's starting tim value from the second query's starting tim value, you can see that the elapsed time for the first query is only 0.01 second. A true table scan against a table this size would take much longer.
Although Profiler offers an Oracle trace template, it can only provide limited details such as when a query starts and finishes. Use Oracle's trace facility if you need to analyze Oracle linked-server query performance problems.