Diff betn exec plan in appl and sqlplus

[chikkodi]

Please explain how this can be possible..

One of the queries in our application was running very slow. I ran statspack during that time and obtained the query which consumed most resources (cpu and tons of buffer gets). This is the same query that is seen when I "Edit" the session details on OEM. ie this query is marked as a long running query on OEM.

Now that I know the problem query, to debug further, I turned on tracing (10046) for that session and obtained the bind variable values for the query. Plugged these values into the query and ran it from sqlplus. It runs extremely fast in sqlplus. How is that possible?

Then I turned on tracing in sqlplus and obtained the execution plan and compared it with the execution plan of the query from the application (this was available in Session Details of OEM as well as the trace file). One from the application (both oem and trace file) shows cartesian joins and one from the sqlplus session shows hash joins.

Why the difference? I am lost now.. at the end of my debugging adventure! please help!

[davey23uk]

how does the app connect to the database?

are your stats up to date - it can make a different when using binds instead of literals

[chikkodi]

apps connect through JDBC. Stats were generated on Sunday afternoon and are current as not much activity on the db since then.

[jmodic]

I turned on tracing (10046) for that session and obtained the bind variable values for the query. Plugged these values into the query and ran it from sqlplus.

What do you mean by that exactly? Did you actually replaced the occurancess of bind variables in the query with their litteral values?

If you turned the original query that was something like that:

select * from emp where ename = :1;

into something like that (by replacing a bind with a litteral value):

select * from emp where ename = 'SCOTT';

then those two queries are totaly different things for the optimizer.

Or did you define a variable in SQL*Plus, assign it a value and run your query?

[chikkodi]

I replaced the values literally in the bind variable. Let me retry it the correct way by defining bind variables.

[chikkodi]

You guys are way too good!!
Using bind variables, the query is slow on sqlplus also!!
Unfortunately that also means I have more work to do :(
Need to fix it now. Thanks a bunch!

[chikkodi]

An update on this -
Noticed that the query was running very fast on the test system which is a clone of the production. The only difference between the test system and the production system is, the tables in the production system were analyzed last weekend. So, as a first try, I deleted the statistics on the tables referred to in the query and boom! the sql ran very fast in production too! The report which was running for the past 2 days finished in a matter of minutes! Now.. how can this be explained ?? I then tried it several times on test too. Everytime I analyze the tables, the query runs slow (uses cartesian joins). I delete statistics and the query runs fast. Do you think we might be having some unnecessary indexes on the tables?

[hrishy]

Hi

I suspect cardinality issue gather stats on that column with histograms and let us know what happens.

regards
Hrishy

[tamilselvan]

Use 10053 event to konw about why CBO chooses the wrong path, and fix them. There may be several reasons that the CBO uses wrong execution plan.

If nothing works, then go for plan stability.

Tamil

[chikkodi]

Just wanted to update you all on this issue as it has been resolved (yeah!!!)

The problem got solved when I changed the statistics generation procedure to compute stats instead of estimate. All queries are superfast now.