oracle Find Query 运行超过 5 秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4202382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find Query running more than 5 second
提问by Egalitarian
I was asked by my friend to find long running queries (more than 5 second) on his oracle database. He wanted to do some sort of polling after a periodic interval and wanted to send himself an alert so that he knows which query are taking so long to execute and send query and corresponding session to him.
我的朋友让我在他的 oracle 数据库上找到长时间运行的查询（超过 5 秒）。他想在定期间隔后进行某种轮询，并想向自己发送警报，以便他知道哪个查询需要很长时间来执行并向他发送查询和相应的会话。
I wrote this Oracle query :
我写了这个 Oracle 查询：
select sess.sid,
sess.username,
sess.paddr,
sess.machine,
optimizer_mode,
sess.schemaname,
hash_value,
address,
sess.sql_address,
cpu_time,
elapsed_time,
sql_text
from v$sql sql, v$session sess
where
sess.sql_hash_value = sql.hash_value
and sess.sql_address = sql.address
and sess.username is not null
and elapsed_time > 1000000 * 5
order by
cpu_time desc
But he says that when he runs a query manually and calculates the time, the time it spends in executing it is a fraction of the result he is getting from the table generated by this particular query.
但他说，当他手动运行查询并计算时间时，执行它所花费的时间只是他从这个特定查询生成的表中获得的结果的一小部分。
I wonder if my query is wrong , I have done some search but it still seems the query is fine.
我想知道我的查询是否有误，我已经进行了一些搜索，但似乎查询仍然正常。
Database is Oracle 10g
数据库是 Oracle 10g
Suggestions???
建议？？？
回答by APC
ELAPSED_TIME is the accumulated time for all the times that SQL statement has been run. So it will be high for frequently executed queries.
ELAPSED_TIME 是该 SQL 语句所有运行次数的累计时间。因此，对于频繁执行的查询，它会很高。
Consider this a snappy query (the HINT-style comment is for the purposes of getting the SQL_ID in V$SQLAREA):
考虑一下这是一个快速查询（HINT 样式的注释是为了在 V$SQLAREA 中获取 SQL_ID）：
select /*+ fast_running_query */ id
from big_table
where id = 1
/
How long does it take to run? This long:
运行需要多长时间？这么长：
SQL> select elapsed_time
2 , executions
3 , elapsed_time / executions as avg_ela_time
4 from v$sqlarea
5 where sql_id = '73c1zqkpp23f0'
6 /
ELAPSED_TIME EXECUTIONS AVG_ELA_TIME
------------ ---------- ------------
235774 1 235774
SQL>
That's a relatively large chunk o' microsecs, because of the parse time. We can see that running it a couple more times doesn't add much to the elapsed time, and the average is much lower:
由于解析时间的原因，这是一个相对较大的微秒块。我们可以看到，再运行几次并不会增加多少经过的时间，而且平均值要低得多：
SQL> r
1 select elapsed_time,
2 executions,
3 elapsed_time / executions as avg_ela_time
4 from v$sqlarea
5* where sql_id = '5v4nm7jtq3p2n'
ELAPSED_TIME EXECUTIONS AVG_ELA_TIME
------------ ---------- ------------
237570 3 79190
SQL>
And after running it another 100000 times ...
再运行 100000 次后......
SQL> r
1 select elapsed_time,
2 executions,
3 elapsed_time / executions as avg_ela_time
4 from v$sqlarea
5* where sql_id = '5v4nm7jtq3p2n'
ELAPSED_TIME EXECUTIONS AVG_ELA_TIME
------------ ---------- ------------
1673900 100003 14.3809724
SQL>
Now, what you wantt is to find active sessions which have been doing something continuously for more than five seconds. So you need session-level timings, and in particular the LAST_CALL_ET on V$SESSION, which is the number of seconds the session has been doing something (if its status is ACTIVE) or the total elapsed time since its last action (if its status is INACTIVE).
现在，您想要的是找到连续做某事超过五秒的活动会话。因此，您需要会话级计时，尤其是 V$SESSION 上的 LAST_CALL_ET，它是会话执行某事的秒数（如果其状态为 ACTIVE）或自上次操作以来经过的总时间（如果其状态为处于非活动状态）。
select sid
, serial#
, sql_address
, last_call_et
from v$session
where status = 'ACTIVE'
and last_call_et > sysdate - (sysdate-(5/86400))
/
So, consider this query. It's slow:
所以，考虑这个查询。它很慢：
SQL> select /*+ slow_running_query */ *
2 from big_table
3 where col2 like '%whatever%'
4 /
no rows selected
Elapsed: 00:00:07.56
SQL>
That's long enough to monitor using a query on V$SESSION. This one searches for statements which have been running for more than 3 seconds...
这段时间足以使用 V$SESSION 上的查询进行监控。这一项搜索已运行超过 3 秒的语句...
SQL> select sid
2 , serial#
3 , sql_id
4 , last_call_et
5 from v$session
6 where status = 'ACTIVE'
7 and last_call_et > sysdate - (sysdate - (3/86400))
8 and username is not null
9 /
SID SERIAL# SQL_ID LAST_CALL_ET
---------- ---------- ------------- ------------
137 7 096rr4hppg636 4
170 5 ap3xdndsa05tg 7
SQL>
and lo!
瞧！
SQL> select sql_text from v$sqlarea where sql_id = 'ap3xdndsa05tg'
2 /
SQL_TEXT
--------------------------------------------------------------------------------
select /*+ slow_running_query */ * from big_table where col2 like '%whatever%'
SQL>
"it should give me the list of queries which are currently executing or have recently finished executing and I can find the total time the query took to complete"
“它应该给我当前正在执行或最近完成执行的查询列表，我可以找到完成查询所需的总时间”
The LAST_ACTIVE_TIME on the V$SQLAREA view records the most recent time the statement was executed. However, there is no view which exposes the metrics for each individual execution of the statement. If you want that sort of detail you will need to start tracing. And that is a separate question.
V$SQLAREA 视图上的 LAST_ACTIVE_TIME 记录了最近一次执行语句的时间。但是，没有视图可以公开语句的每个单独执行的度量标准。如果你想要那种细节，你需要开始追踪。这是一个单独的问题。
回答by demas
Use the tracing:
使用跟踪：
# alter session set timed_statistics = true;
# alter session set sql_trace = true;
.....
# show parameter user_dump_dest
$ tkprof <trc-файл> <txt-файл>
