Is there a way in TOAD or some other tool to monitor queries being executed by your web app?
I'd like to examine the explain/execution plans for the web app queries.
I'm debugging why the webapp queries are slower than when run from sqlplus.
+2
A:
Generally you can track and anlyse from three points. Firstly SQL, mostly through the v$sql view. Secondly through session (starting with v$session). Finally through time (measuring, normally at either a system or session level, for a period of time).
If a particular SQL statement, such as SELECT * FROM table WHERE type = :val, is executed then the database will make a quick hash of it and see if there is a matching statement in the cache. The statement not only has to match on the text, but on certain environmental settings too (such as Parsing user, Optimizer Goal, bind variable types, NLS settings...).
If there is no matching statement, then the database will feed it to the optimizer to come up with a query plan. If there is a match, then the plan already determined for that statement will be used.
So I would suggest your first step is to take an SQL which has been executed by both the web-app and from sqlplus and see if it is using the same plan. You should be able to look in v$sql for the statement of interest and see how many occurrences it has).
If you have multiple occurrences, especially with different MODULE/ACTION/SERVICE values, then you can look at the plans to see if they differ (DBMS_XPLAN.DISPLAY_CURSOR). If you have only one occurrence then the SQL is being shared and you need to take a different approach to isolating the web-app executions from the sqlplus executions.
One way to do that would be to trace the execution of the SQL through both a web-app session and sqlplus session (DBMS_MONITOR). Then tkprof or similar on the trace files and look for differences.
can't help you with doing it through TOAD, but you can't go wrong in getting an understanding of the underlying tools and techniques.
Gary
2010-07-21 22:58:30
Thanks, great response. Given a row in v$sql, how can you see the display plan?
Marcus
2010-07-22 14:00:27
Assuming 10g or later, v$sql has a column sql_id (with a value like fdxrh8tzyw0yw). You can see the explan plan with a select * from table(dbms_xplan.display_cursor('fdxrh8tzyw0yw'));
Gary
2010-07-22 23:05:39