获取数据库30天内各个指标的TOP语句
来源:互联网 发布:合昏尚知时的下半句 编辑:程序博客网 时间:2024/05/21 08:41
要查询短时间内的可以从v$sql 或者是v$sqlarea 如果要查询一周或者一个月内 那么有可能在V$SQLAREA里找不到!下面是通过历史DBA_HIST_SQLSTAT里获得, 这个是通过快照方式保留下来的.
--执行时间最长的WITH BASTABLE AS ( SELECT DBMS_LOB.SUBSTR(SQL_TEXT,4000, 1 ) AS SQL_FULL_TEXT, DHST.SQL_ID, ROUND (X.ELAPSED_TIME / 1000000 / X.EXECUTIONS_DELTA, 3) AVG_ELAPSED_TIME_SEC, ROUND (X.CPU_TIME / 1000000 / X.EXECUTIONS_DELTA, 3) AVG_CPU_TIME_SEC, ROUND (X.BUFFER_GETS_DELTA / X.EXECUTIONS_DELTA, 3) AVG_BUFFER_GETS, ROUND (X.PARSE_CALLS_DELTA/X.EXECUTIONS_DELTA*100, 3) EXEC_PARSE_RATE, ROUND (X.PHYSICAL_READ_BYTES_DELTA/1024/X.EXECUTIONS_DELTA, 3) AVG_PHYSICAL_READ_KB, ROUND (X.DISK_READS_DELTA / X.EXECUTIONS_DELTA, 3) AVG_DISK_READS, EXECUTIONS_DELTA AS EXEC_TOTAL_NUM,DHST.COMMAND_TYPE,N.COMMAND_NAME FROM DBA_HIST_SQLTEXT DHST, DBA_HIST_SQLCOMMAND_NAME N, ( SELECT DHSS.SQL_ID SQL_ID, SUM (DHSS.CPU_TIME_DELTA) CPU_TIME, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME, CASE SUM (DHSS.EXECUTIONS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.EXECUTIONS_DELTA) END AS EXECUTIONS_DELTA, CASE SUM (DHSS.SORTS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.SORTS_DELTA) END AS SORTS_DELTA, CASE SUM (DHSS.FETCHES_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.FETCHES_DELTA) END AS FETCHES_DELTA, CASE SUM (DHSS.PARSE_CALLS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.PARSE_CALLS_DELTA) END AS PARSE_CALLS_DELTA, CASE SUM (DHSS.DISK_READS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.DISK_READS_DELTA) END AS DISK_READS_DELTA, CASE SUM (DHSS.BUFFER_GETS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.BUFFER_GETS_DELTA) END AS BUFFER_GETS_DELTA, CASE SUM (DHSS.IOWAIT_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.IOWAIT_DELTA) END AS IOWAIT_DELTA, CASE SUM (DHSS.PHYSICAL_READ_BYTES_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.PHYSICAL_READ_BYTES_DELTA) END AS PHYSICAL_READ_BYTES_DELTA FROM DBA_HIST_SQLSTAT DHSS WHERE DHSS.SNAP_ID IN (SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME >= TRUNC(SYSDATE)-30 AND END_INTERVAL_TIME <TRUNC(SYSDATE)-0 ) AND DHSS.PARSING_SCHEMA_NAME =UPPER('SHARK') GROUP BY DHSS.SQL_ID ) X WHERE X.SQL_ID = DHST.SQL_ID AND DHST.COMMAND_TYPE = N.COMMAND_TYPE )SELECT * FROM (SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_DISK_READS AS VALUE_S, 'AVG_DISK_READS' AS VALUES_TYPE FROM BASTABLE WHERE COMMAND_TYPE<>47 AND SQL_FULL_TEXT NOT LIKE '/* SQL A%' ORDER BY AVG_DISK_READS DESC ) WHERE ROWNUM <=5UNION ALL SELECT * FROM (SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_ELAPSED_TIME_SEC AS VALUE_S, 'AVG_ELAPSED_TIME_SEC' AS VALUES_TYPE FROM BASTABLE WHERE COMMAND_TYPE<>47 AND SQL_FULL_TEXT NOT LIKE '/* SQL A%' ORDER BY AVG_ELAPSED_TIME_SEC DESC ) WHERE ROWNUM <=5UNION ALL SELECT * FROM (SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_CPU_TIME_SEC AS VALUE_S, 'AVG_CPU_TIME_SEC' AS VALUES_TYPE FROM BASTABLE WHERE COMMAND_TYPE<>47 AND SQL_FULL_TEXT NOT LIKE '/* SQL A%' ORDER BY AVG_CPU_TIME_SEC DESC ) WHERE ROWNUM <=5UNION ALL SELECT * FROM (SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_BUFFER_GETS AS VALUE_S, 'AVG_BUFFER_GETS' AS VALUES_TYPE FROM BASTABLE WHERE COMMAND_TYPE<>47 AND SQL_FULL_TEXT NOT LIKE '/* SQL A%' ORDER BY AVG_BUFFER_GETS DESC ) WHERE ROWNUM <=5UNION ALL SELECT * FROM (SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, EXEC_PARSE_RATE AS VALUE_S, 'EXEC_PARSE_RATE' AS VALUES_TYPE FROM BASTABLE WHERE COMMAND_TYPE<>47 AND SQL_FULL_TEXT NOT LIKE '/* SQL A%' ORDER BY EXEC_PARSE_RATE DESC ) WHERE ROWNUM <=5UNION ALL SELECT * FROM (SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_PHYSICAL_READ_KB AS VALUE_S, 'AVG_PHYSICAL_READ_KB' AS VALUES_TYPE FROM BASTABLE WHERE COMMAND_TYPE<>47 ORDER BY AVG_PHYSICAL_READ_KB DESC ) WHERE ROWNUM <=5UNION ALL SELECT * FROM (SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, EXEC_TOTAL_NUM AS VALUE_S, 'EXEC_TOTAL_NUM' AS VALUES_TYPE FROM BASTABLE WHERE COMMAND_TYPE<>47 AND SQL_FULL_TEXT NOT LIKE '/* SQL A%' ORDER BY EXEC_TOTAL_NUM DESC ) WHERE ROWNUM <=5UNION ALL SELECT * FROM (SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_ELAPSED_TIME_SEC AS VALUE_S, 'PROCEDURES_EXEC_TIME' AS VALUES_TYPE FROM BASTABLE WHERE COMMAND_TYPE=47 AND SQL_FULL_TEXT NOT LIKE '/* SQL A%' ORDER BY AVG_ELAPSED_TIME_SEC DESC ) WHERE ROWNUM <=5;
2 0
- 获取数据库30天内各个指标的TOP语句
- 各个数据库获取随机列的语法
- 图像分割中各个指标的计算方法
- 获取数据库的sql语句
- sql语句的内连接三个数据库.
- 迟到的Top语句
- 获取局域网内所有的数据库实例
- 性能测试指标的获取
- yslow各个指标含义
- yslow 各个指标含义
- 获取xml里设置的statelistdrawable内的各个状态对应的drawable
- access数据库 top语句失效解决方法
- 获取数据库中对象的DDL语句
- 通过sql语句获取数据库的基本信息
- 获取数据库中语句执行的时间
- top-N推荐中以recall为指标的实验设计
- MySQL中TOP语句的取代语句
- 数据库查询语句的左右连接和内连接
- 网页设计中7大令人抓狂的错误
- Mysql简单的查询多表
- 反向代理服务器软件nginx和nat123等配置使用方法
- 【转】CSS3圆角详解
- XP系统走了 盖茨来了
- 获取数据库30天内各个指标的TOP语句
- springMVC教程(二)@RequestMapping的基本设置
- Android的5个进程等级
- 工业以太网和CAN现场总线比较
- 黑马程序员-OC笔记(五)内存管理
- 块设备
- js prototype
- hadoop备战:hive命令集
- winform上传文件至服务器