重庆思庄Oracle、Redhat认证学习论坛

标题: 查询Oracle数据库一周内每天的SQL执行次数 [打印本页]

作者: mahan 时间: 2024-7-21 18:33
标题: 查询Oracle数据库一周内每天的SQL执行次数
一、问题：
今天引入的问题是：oracle数据库怎么查询一周内，每天的查询次数？

正好这周在学数据库的调优工作，我记得数据库的AWR报告会记录SQL的执行情况，可以从DBA_HIST_SNAPSHOT里面找出7天的快照ID，在DBA_HIST_SQLSTAT收集一下。
先来了解一下相关视图

二、相关视图：
dba_hist_sqlstat 和dba_hist_snapshot视图是 Oracle AWR（Automatic Workload Repository）的一部分。其中，

1、DBA_HIST_SQLSTAT
dba_hist_sqlstat是Oracle数据库中的历史SQL统计信息视图，用于提供有关SQL语句执行的历史性能信息。它记录了SQL语句的执行计划、执行时间、消耗的资源等统计数据。dba_hist_sqlstat可以用于监控和分析数据库中的SQL性能问题。
常用字段：
SQL_ID：SQL语句的唯一标识符。
SNAP_ID：快照ID，表示采样的时间点。
DBID：数据库ID。
INSTANCE_NUMBER：实例编号。
PLAN_HASH_VALUE：SQL执行计划的哈希值。

2、DBA_HIST_SNAPSHOT
dba_hist_snapshot是Oracle数据库中的动态视图，用于提供有关历史性能快照的信息。它记录了数据库在不同时间点的性能指标和统计数据。dba_hist_snapshot可以用于分析数据库的性能变化和趋势，帮助管理员进行性能监控和故障排查。
常用字段：
SNAP_ID：快照的唯一标识符。
BEGIN_INTERVAL_TIME：快照的开始时间。
END_INTERVAL_TIME：快照的结束时间。
DBID：数据库的唯一标识符。
INSTANCE_NUMBER：实例的编号。

3、v$SQLTEXT
用于提供有关共享SQL区域中SQL语句文本的信息。它记录了数据库中执行过的SQL语句的文本。vsqltext可以用于查看和分析数据库中执行过的SQL语句的具体文本内容。
常用字段：
SQL_ID：SQL语句的唯一标识符。
SQL_TEXT：SQL语句的文本。

4、v$SQL⭐
用于提供有关SQL语句执行的统计信息和执行计划
SQL_TEXT：SQL语句的文本，最多1000个字符。
SQL_FULLTEXT：SQL语句的完整文本，以CLOB（Character Large Object）形式存储。SQL_ID：SQL语句的唯一标识符，最多13个字符。
SHARABLE_MEM：共享内存的大小，以字节为单位。
PERSISTENT_MEM：持久内存的大小，以字节为单位。
RUNTIME_MEM：运行时内存的大小，以字节为单位。
SORTS：排序操作的次数。
EXECUTIONS：SQL语句的执行次数。
PARSE_CALLS：解析调用的次数。
DISK_READS：磁盘读取的次数。
DIRECT_WRITES：直接写入的次数。
DIRECT_READS：直接读取的次数。
BUFFER_GETS：缓冲区获取的次数。
APPLICATION_WAIT_TIME：应用程序等待的时间。
CONCURRENCY_WAIT_TIME：并发等待的时间。
CLUSTER_WAIT_TIME：集群等待的时间。
USER_IO_WAIT_TIME：用户I/O等待的时间。
PLSQL_EXEC_TIME：PL/SQL执行的时间。
JAVA_EXEC_TIME：Java执行的时间。
ROWS_PROCESSED：处理的行数。
COMMAND_TYPE：命令类型的编号。
OPTIMIZER_MODE：优化器模式。
OPTIMIZER_COST：优化器成本。
OPTIMIZER_ENV：优化器环境。

三、SQL语句
将这两个视图通过快照 id join一下得到SQL，记录了系统七天内Oracle数据库的SQL执行次数

1、七天内Oracle数据库的SQL执行次数
SELECT
SUM(ss.executions_delta) AS total_executions
FROM
DBA_HIST_SQLSTAT ss
JOIN
DBA_HIST_SNAPSHOT sn ON ss.snap_id = sn.snap_id
WHERE
sn.begin_interval_time BETWEEN SYSDATE - 7 AND SYSDATE;

--结果
TOTAL_EXECUTIONS
----------------
   122282
注意：
1、AWR收集：该查询依赖于AWR数据，因此AWR必须启用，需要确保系统快照的保留策略为7天以上。详情请看扩展1，平时如何管理AWR报告的收集时间，默认是1小时收集一次，保留策略怎么设置。
2、 executions_delta参数指的是该查询计算的过去7天内所有 SQL 语句的总执行次数变化。⭐
Oracle给出的定义是：自将此对象引入库缓存以来，对该对象执行的增量执行次数
参考文档：https://docs.oracle.com/en/datab ... A_HIST_SQLSTAT.html

2、优化：按天分组按次数降序排列
现在想要了解在特定时间范围内 SQL 语句的执行频率。所以更改一下SQL查询每天的SQL执行次数，通过TRUN 截断日期时间值，按日期分组并按照每天总执行次数降序排序。

SELECT TRUNC(sn.begin_interval_time) AS query_date,
   SUM(ss.executions_delta) AS total_executions
  FROM DBA_HIST_SQLSTAT ss
  JOIN DBA_HIST_SNAPSHOT sn
ON ss.snap_id = sn.snap_id
WHERE sn.begin_interval_time BETWEEN SYSDATE - 7 AND SYSDATE
GROUP BY TRUNC(sn.begin_interval_time)
ORDER BY total_executions desc;

QUERY_DATE    TOTAL_EXECUTIONS
------------------ ----------------
16-JUL-24       59989
18-JUL-24       21884
17-JUL-24       21523
12-JUL-24       12903
15-JUL-24       10264
在项目上大神开发的可视化界面首页应该就是用的这条SQL，可以很明显看出哪一天的SQL执行量，如果有异常，再去分析问题。
如果某一条SQL执行异常，需要做分析，怎么找出这条SQL文本。

3、根据sql_id查询SQL语句
SELECT SQL_TEXT FROM v$sqltext WHERE SQL_ID = 'cmhz88h821m04';
有时SQL文本过长vsqltext中SQL_TEXT字段最多1000个字符，可能放不下，我们可以看vsql中的SQL_FULLTEXT字段保留了SQL语句的完整文本，以CLOB形式存储。

4、根据sql_id查询完整的SQL
SELECT SQL_FULLTEXT FROM v$sql WHERE SQL_ID = 'cmhz88h821m04';
拿到SQL之后可以看看sql的执行计划分析问题

5、根据sql_id查看执行计划
select * from table(dbms_xplan.display_cursor('cmhz88h821m04',0,'ALLSTATS LAST'));
扩展2，查看执行计划的方法
可以先从vsql中查出sql_id，再通过dbms_xplan_display_cursor( sql_id,child_number,format)查指定的sql执行计划 **sql_id：**指定位于库缓存执行计划中 SQL 语句的父游标。默认值为 null，表示最后一条语句的执行计划，可以换成想要查看SQL的sql_id。通过查询VSQL 或V$SQLAREA的SQL_ID列来获得SQL语句的SQL_ID。
**child_number：**指定父游标下子游标的序号，默认值为 0，不返回子游标的执行计划，null则全部返回。
format：控制 SQL 语句执行计划的输出部分。常用的有：BASIC: 显示最少的信息、TYPICAL: 默认值。SERIAL、ALL: 显示最多的信息、IOSTATS、MEMSTATS、ALLSTATS等。

6、查询指定SQL语句的历史执行计划
SELECT SQL_ID, PLAN_HASH_VALUE, OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, COST, CARDINALITY, BYTES, PARTITION_START, PARTITION_STOP FROM dba_hist_sql_plan WHERE SQL_ID = 'cmhz88h821m04';
这里可以再做一个扩展3，快照的管理，手工创建快照。再找出SQL之后，我们还可能会用到AWR和ADDM等工具帮助我们去分析问题，AWR报告默认是一个小时收集一次快照，我们可以在想要分析的SQL语句执行前后手工创建或者建一个基线，方面分析报告。

工作中往往会遇到紧急案例CPU、IO、内存飙升、甚至宕机，下一篇我可能会写关于紧急案例-CPU飙升如何快速定位到SQL语句，数据库hang住，杀会话等问题的处理。
下面扩展2应该是重点部分，之前在天津实习的时候在那用不到，只学到了备份恢复那一块，来河北刚进组就听什么慢SQL处理，性能优化，压力很大啊。后面可能会开一个优化专题，关注博主，哈哈！

扩展1：AWR性能报告收集的管理
查看AWR的收集时间和保留时间
可以通过dba_hist_wr_control视图进行AWR的设置控制，包括快照间隔、保留时间和TOPNSQL设置。

select * from dba_hist_wr_control;

   DBID SNAP_INTERVAL
---------- ---------------------------------------------------------------------------
RETENTION       TOPNSQL
--------------------------------------------------------------------------- ----------
1696225019 +00000 01:00:00.0
+00008 00:00:00.0       DEFAULT

**DBID：**数据库实例的唯一标识
**SNAP_INTERVAL：**快照间隔，每一小时收集一次快照。格式天，时分秒，
**RETENTION：**快照保留时间，这里为8天，所以可以完全可以收集七天内的信息。格式，天，时分秒
**TOPNSQL：**在每个AWR快照期间收集的SQL语句数量

修改AWR收集时间和保留策略
awr 默认通过 mmon 及 mmnl 进程来每小自动收集一次，为了节省空间，采集的数据在在保留一定时间后自动清除。上面看到的是11g Oracle默认保留8天，10g为7天。
可以用PL/SQL中的dbms_workload_repository.modify_snapshot_settings设置
案例：设置AWR的收集时间为60min，保留时间为30day。

SYS@orcl>
begin
  dbms_workload_repository.modify_snapshot_settings(interval => 60,
                                                retention => 30*24*60);
  end;
/

PL/SQL procedure successfully completed.

SYS@orcl>
SYS@orcl> select * from dba_hist_wr_control;

   DBID SNAP_INTERVAL
---------- ---------------------------------------------------------------------------
RETENTION       TOPNSQL
--------------------------------------------------------------------------- ----------
1696225019 +00000 01:00:00.0
+00030 00:00:00.0
扩展2：查看执行计划的方法
SQLPLUS AUTOTRACE --计划执行不真实
**Explain Plan For SQL **–计划执行不真实
使用 DBMS_XPLAN 包 --计划执行真实⭐
**statistics_level=all; **–计划执行真实
**sql_trace 与 10046 ** --计划执行真实⭐

扩展3：快照的管理
1、场景：
Ⅰ、我不想要一个小时的，只想要看目标语句、性能测试、压力测试那一段的报告。
Ⅱ、数据库出现过异常，无法生成AWR，检查能否创建快照。

2、手工创建快照：
用 create_snapshot 存储过程手动创建快照：

begin
  dbms_workload_repository.create_snapshot();
end;
/
3、查看快照信息
select * from dba_hist_snapshot;

4、手工删除快照
指定要删除的快照id范围

begin
  dbms_workload_repository.drop_snapshot_range(low_snap_id => 30,high_snap_id => 31);
  end;
/
如果有删不掉的快照，可能是创建了基线，需要先把极限删除，再删除快照。

5、查看基线的视图
select * from dba_hist_baseline;

6、删除基线及其快照
也可以在删除基线的时候连快照一起删掉：

begin
  dbms_workload_repository.drop_baseline(baseline_name => 'xxx',
                                    cascade => true);
  end;
/
cascade 默认false不删除快照，true删除快照。

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)