个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2020-12-22 22:06:51
给你一个库，来，很快啊，告诉客户，哪个sql最慢？
点击(此处)折叠或打开
-
select *
-
from (select status,
-
--username,
-
sql_id,
-
sql_exec_id,
-
to_char(sql_exec_start, 'yyyy-mm-dd hh24:mi:ss') as sql_exec_start,
-
round(elapsed_time / 1000000) as "elapsed (s)",
-
round(cpu_time / 1000000) as "cpu (s)",
-
buffer_gets,
-
round(physical_read_bytes / (1024 * 1024)) as "phys reads (mb)",
-
round(physical_write_bytes / (1024 * 1024)) as "phys writes (mb)"
-
from v$sql_monitor
-
order by elapsed_time desc)
- where rownum <= 20;
接着挖黑历史：
点击(此处)折叠或打开
-
select round(elapsed_time /1000000) as "elapsed (s)",
-
round(cpu_time /1000000,3) as "cpu (s)",
-
round(queuing_time /1000000,3) as "queuing (s)",
-
round(application_wait_time/1000000,3) as "appli wait (s)",
-
round(concurrency_wait_time/1000000,3) as "concurrency wait (s)",
-
round(cluster_wait_time /1000000,3) as "cluster wait (s)",
-
round(user_io_wait_time /1000000,3) as "user io wait (s)",
-
round(physical_read_bytes /(1024*1024)) as "phys reads (mb)",
-
round(physical_write_bytes /(1024*1024)) as "phys writes (mb)",
-
buffer_gets as "buffer gets",
-
round(plsql_exec_time/1000000,3) as "plsql exec (s)",
-
round(java_exec_time /1000000,3) as "java exec (s)"
-
from v$sql_monitor
-
where sql_id = '&sql_id'
-
and sql_exec_id = &exec_id
- and sql_exec_start=to_date('&exec_date','yyyy-mm-dd hh24:mi:ss');
引自：https://blog.yannickjaquier.com/oracle/real-time-sql-monitoring.html
给主人留下些什么吧！~~