Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1424065
  • 博文数量: 314
  • 博客积分: 1860
  • 博客等级: 上尉
  • 技术积分: 3473
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文(314)

文章存档

2021年(46)

2020年(115)

2019年(11)

2018年(9)

2017年(9)

2016年(17)

2015年(7)

2014年(4)

2013年(1)

2012年(11)

2011年(27)

2010年(35)

2009年(11)

2008年(11)

分类: Oracle

2020-12-22 22:06:51


给你一个库,来,很快啊,告诉客户,哪个sql最慢?

点击(此处)折叠或打开

  1. SELECT *
  2.   FROM (SELECT status,
  3.                --username,
  4.                sql_id,
  5.                sql_exec_id,
  6.                TO_CHAR(sql_exec_start, 'yyyy-mm-dd hh24:mi:ss') AS sql_exec_start,
  7.                ROUND(elapsed_time / 1000000) AS "Elapsed (s)",
  8.                ROUND(cpu_time / 1000000) AS "CPU (s)",
  9.                buffer_gets,
  10.                ROUND(physical_read_bytes / (1024 * 1024)) AS "Phys reads (MB)",
  11.                ROUND(physical_write_bytes / (1024 * 1024)) AS "Phys writes (MB)"
  12.           FROM v$sql_monitor
  13.          ORDER BY elapsed_time DESC)
  14.  WHERE rownum <= 20;



接着挖黑历史:

点击(此处)折叠或打开

  1. SELECT ROUND(elapsed_time /1000000) AS "Elapsed (s)",
  2.      ROUND(cpu_time /1000000,3) AS "CPU (s)",
  3.      ROUND(queuing_time /1000000,3) AS "Queuing (s)",
  4.      ROUND(application_wait_time/1000000,3) AS "Appli wait (s)",
  5.      ROUND(concurrency_wait_time/1000000,3) AS "Concurrency wait (s)",
  6.      ROUND(cluster_wait_time /1000000,3) AS "Cluster wait (s)",
  7.      ROUND(user_io_wait_time /1000000,3) AS "User io wait (s)",
  8.      ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
  9.      ROUND(physical_write_bytes /(1024*1024)) AS "Phys writes (MB)",
  10.      buffer_gets AS "Buffer gets",
  11.      ROUND(plsql_exec_time/1000000,3) AS "Plsql exec (s)",
  12.      ROUND(java_exec_time /1000000,3) AS "Java exec (s)"
  13.      FROM v$sql_monitor
  14.      WHERE sql_id = '&sql_id'
  15.      AND sql_exec_id = &exec_id
  16.      AND sql_exec_start=TO_DATE('&exec_date','yyyy-mm-dd hh24:mi:ss');



引自:https://blog.yannickjaquier.com/oracle/real-time-sql-monitoring.html
阅读(72) | 评论(0) | 转发(0) |
0

上一篇:隐藏 temp 1.23

下一篇:nbu客户端安装点滴

给主人留下些什么吧!~~