oracle 如何检查执行查询的次数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20787787/
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
how to check no of times query executed?
提问by user2824874
I am using Oracle 11gr2 Database. Is there a way to check how many times one particular query executed and what is total run time of that execution for a current date or with in last 24 hrs? Its like history of sql run time. Please provide your suggestions.
我正在使用 Oracle 11gr2 数据库。有没有办法检查一个特定查询执行了多少次，以及该执行在当前日期或过去 24 小时内的总运行时间是多少？它就像 sql 运行时的历史。请提供您的建议。
回答by Jon Heller
select sql_text, sql_fulltext, executions, elapsed_time/1000000 seconds
from gv$sql
order by executions desc;
This will only return queries that have not aged out of the shared pool, and only give you cumulative run times. But if you're only interested in the most common queries, chances are they have been used recently and will not age out.
这将只返回尚未超出共享池的查询，并且只会为您提供累积运行时间。但是，如果您只对最常见的查询感兴趣，那么它们很可能最近已被使用过并且不会过时。
An AWR report, or one of the DBA_HIST_* tables, may also help. If you have grid control you can very easily select a 24-hour period and generate a report that will display the top queries.
AWR 报告或 DBA_HIST_* 表之一也可能有所帮助。如果您有网格控制，您可以非常轻松地选择 24 小时时段并生成一个报告，该报告将显示最热门的查询。
Update:
更新：
I don't think there's any way to get statistics for each individual execution. The historical performance views work by sampling, they do not capture everything. In practice, this is enough information for performance tuning. If something doesn't happen often enough to get sampled it's not worth tuning.
我认为没有任何方法可以获得每个单独执行的统计信息。历史性能视图通过抽样工作，它们不能捕获所有内容。实际上，这对于性能调优来说已经足够了。如果某些事情发生的频率不足以进行采样，则不值得进行调整。
AWR can be used to find the approximate number of executions per day:
AWR 可用于计算每天的大致执行次数：
--Number of Executions today.
select
--Executions at last snap.
(
select executions_total
from dba_hist_sqlstat
where snap_id =
(
--Latest snapshot.
select max(snap_id) keep
(dense_rank last order by begin_interval_time) end_snap_id
from dba_hist_snapshot
)
and sql_id = '5ms6rbzdnq16t'
)
-
--Executions at beginning of day.
(
select executions_total
from dba_hist_sqlstat
where snap_id =
(
--Snapshot for beginning of the day.
--Assumes there are hourly snapshots that start on time.
select snap_id begin_snap_id
from dba_hist_snapshot
where trunc(begin_interval_time, 'hh') = trunc(sysdate)
)
and sql_id = '5ms6rbzdnq16t'
) executions_today
from dual;
