sql - session 执行的 Oracle 查询

Question

我正在尝试跟踪针对特定数据库用户执行的 SQL 语句。我没有启用 AUDITING，我使用的是 Oracle 11g。

我有以下查询:

SELECT  
  S.MODULE, 
  SQL_TEXT , 
  S.EXECUTIONS    
FROM
  SYS.V_$SQL S, 
  SYS.ALL_USERS U
WHERE
  S.PARSING_USER_ID=U.USER_ID 
  AND UPPER(U.USERNAME) IN ('USERNAME')
  AND (UPPER(s.MODULE)='APP.EXE')
ORDER BY S.LAST_LOAD_TIME

但是如果运行“APP.EXE”的多个用户连接到同一个数据库用户，我无法理解哪个操作系统用户执行了哪个查询。所以我尝试加入 V$SESSION View 以获取用户详细信息。

SELECT  
  S.MODULE,SQL_TEXT ,SN.OSUSER, SN.MACHINE, S.EXECUTIONS
FROM
  SYS.V_$SQL S, 
  SYS.ALL_USERS U,
  V$SESSION SN 
WHERE
  S.PARSING_USER_ID=U.USER_ID 
  AND UPPER(U.USERNAME) IN ('USERNAME')
  AND (UPPER(S.MODULE)='APP.EXE')
  AND S.SQL_ID=SN.SQL_ID
ORDER BY S.LAST_LOAD_TIME

但这似乎不起作用(在我的例子中它没有返回任何行)所以，我有以下问题

1) 如何获取每个 session 执行的查询？

2) V_$SQL 的 EXECUTIONS 列似乎是所有 session 的执行。我如何知道 session 执行特定查询的次数？

3) V_$SQL 中有关查询的记录将存储多长时间？ Oracle 什么时候从 View 中删除它？

先谢谢大家

普拉迪普

Answer 1

如果不进行更多配置(例如启用审计)或做出一些妥协，您可能无法获得所需的数据。您要解决的业务问题是什么？根据问题的不同，我们或许能够帮助您确定配置数据库的最简单方法，以便能够记录您需要的信息。

Oracle 不会尝试在任何地方存储特定用户(尤其是特定操作系统用户)执行特定查询的次数。 V$SESSION中的SQL_ID只表示 session 当前正在执行的SQL_ID。如果，正如我猜测的那样，这是一个客户端-服务器应用程序，那么 99% 的时间很可能这是 NULL，因为绝大多数时间， session 不执行任何 SQL，它在等待用户做某事。 V$SESSION 中的 PREV_SQL_ID 是先前执行的 SQL 语句——至少通常不会是 NULL。但它只会有一个值，不会有该 session 执行的 SQL 语句的历史记录。

V$SQL View 表示 SQL 共享池中的内容。当 SQL 语句从共享池中老化时，它将不再出现在 V$SQL View 中。发生的速度取决于多种因素——某人执行语句的频率、解析新语句的频率(通常在很大程度上取决于您的应用程序是否正确使用绑定(bind)变量)、您的共享池有多大等. 通常，这将持续几分钟，直到数据库关闭。

如果您获得了使用 AWR 表的许可，并且对近似值而不是完全正确的答案感兴趣，则可以通过查看一些 AWR 表来获得所需的信息。例如，V$ACTIVE_SESSION_HISTORY 将捕获每个 session 每秒主动执行的 SQL 语句。然而，由于这是一个客户端-服务器应用程序，这意味着绝大多数时间 session 将处于非事件状态，因此不会捕获任何内容。不过，恰好为 session 捕获的 SQL 语句将使您对不同 SQL 语句的相对频率有所了解。当然，运行时间较长的 SQL 语句也更有可能被捕获，因为它们更有可能在给定的瞬间处于事件状态。如果查询 A 和 B 的执行时间完全相同，并且捕获到 session 在过去一小时内执行 A 5 次和 B 10 次，那么您可以得出结论，B 的执行频率大约是 A 的两倍。如果您知道查询的平均执行时间，查询被捕获的平均概率将是查询执行的秒数(在 0.5 秒内执行的查询有 50% 的机会被捕获，在 0.25 秒内执行的查询秒有 25% 的机会被捕获)因此您可以估计特定 session 执行特定查询的频率。这远不是一个确切的数字，尤其是在较短的时间范围内以及对于实际执行时间变化更大的查询而言。

V$ACTIVE_SESSION_HISTORY View 中的数据通常可用几个小时。然后它被采样到 DBA_HIST_ACTIVE_SESS_HISTORY 表中，该表将可用数据量减少一个数量级，从而使任何估计的准确性大大降低。但是无论您的 AWR 保留间隔是多少，该数据都会保留(默认情况下，这是一周，尽管许多网站将其增加到 30 或 60 天)。