如何查找执行查询的次数? [英] how to find number of times query executed?

问题描述

我在应用程序中的Oracle 11g数据库中运行的查询很少，这些查询是可重复的.我想查找基于sql_id或sql_text的查询在一天中的执行次数以及每次执行所花费的时间吗?有办法找到这个吗?

I have few queries running in Oracle 11g database from application those are repeatable queries. I want to find number of times the query executed for the day and time it took for each execution based on sql_id or sql_text? Is there a way to find this?

推荐答案

执行次数在AWR报告中.这意味着它可能也可以从DBA_HIST_表派生，但我不知道是哪一个.根据您之前的问题，我假设您已获得AWR许可.

The number of executions is in the AWR reports. Which means it can probably also be derived from a DBA_HIST_ table but I don't know which one. Based on your previous question I assume you have AWR licensed.

--Find the SQL_ID.  If not in shared_pool it should be in a historical table.
select * from v$sql where sql_fulltext like ...;
select * from dba_hist_sqltext where sql_text like ...;

--Find the begin and end snapshot periods.
--Be careful here, I've seen some weird timezone issues, it can be annoyingly
--difficult to get the exact period of time you want.
select * from dba_hist_snapshot order by begin_interval_time desc;

--Get AWR report.
select dbms_workload_repository.awr_sql_report_text(l_dbid => 3037785498
  , l_inst_num => 1, l_bid => 53007, l_eid => 53020, l_sqlid => '57pfs5p8xc07w')
from dual;


WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

...

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   1329243004                    376           552         53008          53018

...

这篇关于如何查找执行查询的次数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！