Diff betn exec plan in appl and sqlplus
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!