查詢執行慢的SQL語句

xypincle發表於2017-04-12
SQL

  1. select s.sql_text,
  2.        s.sql_fulltext,
  3.        s.sql_id,
  4.        round(elapsed_time / 1000000 / (case
  5.                when (executions = 0 or nvl(executions, 1) = 1) then 1 else executions end),2) "執行時間'S'",
  6.        s.executions "執行次數",
  7.        s.optimizer_cost "COST",
  8.        s.sorts,
  9.        s.module, --連線模式（jdbc thin client：程式）
  10.        -- s.locked_total,
  11.        s.physical_read_bytes "物理讀",
  12.        -- s.physical_read_requests "物理讀請求",
  13.        s.physical_write_requests "物理寫",
  14.        -- s.physical_write_bytes "物理寫請求",
  15.        s.rows_processed "返回行數",
  16.        s.disk_reads "磁碟讀",
  17.        s.direct_writes "直接路徑寫",
  18.        s.parsing_schema_name,
  19.        s.last_active_time
  20.   from gv$sqlarea s
  21.  where round(elapsed_time / 1000000 / (case when (executions = 0 or nvl(executions, 1) = 1) then 1 else executions end),2) > 5 --100 0000微秒=1s
  22.    --and s.parsing_schema_name = user
  23.    and to_char(s.last_load_time, 'YYYY-MM-DD') = to_char(sysdate, 'YYYY-MM-DD')
  24.    and s.command_type in (2, 3, 5, 6, 189)
  25.  order by "執行時間'S'" desc;

  1. /*  SQL中 COMMAND_TYPE意義:
  2. 2：INSERT
  3. 3：SELECT
  4. 6：UPDATE
  5. 7：DELETE
  6. 189：MERGE
  7. 詳情可透過查詢V$SQLCOMMAND檢視  */


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28878983/viewspace-2137117/，如需轉載，請註明出處，否則將追究法律責任。

相關文章