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!
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?