如何监控Web应用程序DB查询执行计划？
TOAD 或其他工具是否有办法监控您的网络应用程序正在执行的查询？
我想检查网络应用程序查询的解释/执行计划。
我正在调试为什么 webapp 查询比从 sqlplus 运行时慢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一般可以从三点进行跟踪和分析。
首先是 SQL，主要是通过 v$sql 视图。
其次通过会话（以 v$session 开头）。
最后通过时间（通常在系统或会话级别测量一段时间）。
如果执行特定的 SQL 语句，例如 SELECT * FROM table WHERE type = :val，那么数据库将对其进行快速哈希，并查看缓存中是否有匹配的语句。该语句不仅必须匹配文本，还必须匹配某些环境设置（例如解析用户、优化器目标、绑定变量类型、NLS 设置...）。
如果没有匹配的语句，那么数据库会将其提供给优化器以制定查询计划。如果存在匹配，则将使用已为该语句确定的计划。
因此，我建议您的第一步是获取已由 Web 应用程序和 sqlplus 执行的 SQL，并查看它是否使用相同的计划。您应该能够在 v$sql 中查找感兴趣的语句并查看它出现了多少次）。
如果出现多次，特别是具有不同的 MODULE/ACTION/SERVICE 值，那么您可以查看计划以查看它们是否不同 (DBMS_XPLAN.DISPLAY_CURSOR)。如果只出现一次，则 SQL 正在被共享，您需要采取不同的方法来将 Web 应用程序执行与 sqlplus 执行隔离。
一种方法是通过 Web 应用程序会话和 sqlplus 会话 (DBMS_MONITOR) 跟踪 SQL 的执行。然后 tkprof 或类似的跟踪文件并查找差异。
无法帮助您通过 TOAD 完成此操作，但您在了解底层工具和技术方面不会出错。
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.
是的。有一种方法可以监控 Web 应用程序对 Oracle TOAD 中数据库的查询调用。
使用此工具，您可以选择进程（
[TOAD，Web_dev（我不记得调试的名称）]在本例中为“运行”，也是“调试”）。此工具显示正在调用应用程序的存储过程或函数。Yes. There is a way to monitor a web app callings to queries to DB in Oracle TOAD.
With this tool you select the process (
[TOAD, Web_dev (I dont remember the name of debug)]"running" in this case, "debug" too). This tool shows what stored procedure or function is calling the app.