Re: Select statement tuning - help required

How did you generate this plan? Was it from a 10046 trace? Are the statistics up to date?

The cardinality in the plan indicates that Oracle believes that only one row will be returned at many of the steps. Oracle will typically try to join the tables with the smallest predicted cardinality near the start of the join process, if possible, yet it is first joining a table with a (predicted) cardinality of 1 with another table with a (predicted) cardinality of 281,092.

If possible, take a look at the plan when the SQL statement is run _from_ the remote site. Does the SQL statement have the same plan, with the same join order. If not, and the SQL statement runs much faster on the remote size, consider listing the tables in the FROM clause in the order in which the tables were joined on the remote site, and then add an ORDERED hint to the SQL statement to see if it makes a difference.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Charles,
You are both IT Manager and DBA ? I want your job :-)

Matthias Received on Thu Nov 01 2007 - 15:29:27 CDT