魔镜魔镜告诉我，最耗时的sql是哪个？

给你一个库，来，很快啊，告诉客户，哪个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