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

问题描述

我正在尝试跟踪针对特定数据库用户执行的SQL语句.我没有启用审计，而我正在使用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"的用户连接到同一数据库用户，则我将无法理解哪个OS用户执行了哪个查询.因此，我尝试加入V $ SESSION视图以获取用户详细信息.

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)如何获取每个会话执行的查询?

2)V_ $ SQL的EXECUTIONS列似乎来自所有会话的执行.我怎么知道会话执行特定查询的次数?

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

谢谢大家

Pradeep

解决方案

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

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

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

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

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

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.

I have the following query :

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

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) How do I get the queries executed by each session?

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) How long a record about a query will be stored in V_$SQL? When do Oracle delete it from the view?

Thanking you all in advance,

Pradeep

解决方案

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 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.

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.

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.

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屋！