由会话执行的 Oracle 查询 [英] Oracle queries executed by a session

问题描述

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

I am trying to trace the SQL statements executed against a particular database user. I don't have AUDITING enabled and I am using 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 视图以获取用户详细信息.

But if multiple users running the 'APP.EXE' are connected to the same db user, I am not able to understand which OS user executed which query. So I tried to join with V$SESSION view to get the user details.

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

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

But this doesn't seems to be working(In my case it didn't return any rows) So, I have the following questions

1) 如何让每个会话执行的查询?

1) How do I get the queries executed by each session?

2) V_$SQL 的 EXECUTIONS 列似乎是所有会话的执行.我如何知道某个会话执行特定查询的次数?

2) The EXECUTIONS column of V_$SQL seems to the executions from all the sessions. How do I know the number of times a particular query is executed by a session?

3) 查询的记录将在 V_$SQL 中存储多长时间?Oracle 什么时候从视图中删除它?

3) How long a record about a query will be stored in V_$SQL? When do Oracle delete it from the view?

提前谢谢大家，

普拉迪普

推荐答案

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

You're probably not going to get the data that you're looking for without doing more configuration (such as enabling auditing) or making some compromises. What is the business problem you're trying to solve? Depending on the problem, we may be able to help you identify the easiest approach to configuring the database to be able to record the information you're after.

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

Oracle does not attempt to store anywhere how many times a particular user (and particularly not how many times a particular operating system user) executed a particular query. The SQL_ID in V$SESSION only indicates the SQL_ID that the session is currently executing. If, as I'm guessing, this is a client-server application, it is quite likely that this is NULL 99% of the time because the vast majority of the time, the session is not executing any SQL, it's waiting on the user to do something. The PREV_SQL_ID in V$SESSION is the prior SQL statement that was executed-- that at least won't generally be NULL. But it's only going to have one value, it's not going to have a history of the SQL statements executed by that session.

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

The V$SQL view is a representation of what is in the SQL shared pool. When a SQL statement ages out of the shared pool, it will no longer be in the V$SQL view. How quickly that happens depends on a multitude of factors-- how frequently someone is executing the statement, how frequently new statements are parsed (which generally depends heavily on whether your applications are using bind variables correctly), how big your shared pool is, etc. Generally, that's going to be somewhere between a few minutes and until the database shuts down.

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

If you are licensed to use the AWR tables and you are interested in approximations rather than perfectly correct answers, you might be able to get the information you're after by looking at some of the AWR tables. For example, V$ACTIVE_SESSION_HISTORY will capture the SQL statement that each session was actively executing each second. Since this is a client-server application, however, that means that the vast majority of the time, the session is going to be inactive, so nothing will be captured. The SQL statements that do happen to get captured for a session, though, will give you some idea about the relative frequency of different SQL statements. Of course, longer-running SQL statements are more likely to be captured as well since they are more likely to be active on a given instant. If query A and B both execute in exactly the same amount of time and a session was captured executing A 5 times and B 10 times in the last hour, you can conclude that B is executed roughly twice as often as A. And if you know the average execution time of a query, the average probability that the query was captured is going to be the number of seconds that the query executes (a query that executes in 0.5 seconds has a 50% chance of getting captured, one that executes in 0.25 seconds has a 25% chance of getting captured) so you can estimate how often a particular session executed a particular query. That is far from an exact number particularly over shorter time-frames and for queries whose actual execution times are more variable.

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

The data in V$ACTIVE_SESSION_HISTORY view is generally available for a few hours. It then gets sampled down into the DBA_HIST_ACTIVE_SESS_HISTORY table which cuts the amount of data available by an order of magnitude making any estimates much less accurate. But that data is kept for whatever your AWR retention interval is (by default, that's one week though many sites increase it to 30 or 60 days).

这篇关于由会话执行的 Oracle 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！