Oracle檢視正在執行的SQL以及執行計劃分析

壹頁書發表於2014-05-04
OracleSQL
前臺反應響應時間很長,
首先，看看Oracle正在做些什麼。
  1. SELECT a.username, b.sql_id, b.SQL_TEXT, b.SQL_FULLTEXT
  2. FROM v$session a, v$sqlarea b
  3. WHERE a.sql_address = b.address
  4.     AND a.SQL_HASH_VALUE = b.HASH_VALUE
找到問題SQL的sql_id，然後檢視執行計劃
  1. select * from table(dbms_xplan.display_awr('2k55nnc3y1xw4'));

分析這個SQL
執行順序應該是 8,7,6,5,4,9,3,10,2,1,0
  1. SELECT opus_type, a.opus_id, opus_name, a.userid, nickname
  2.     , performer, visit_num,
  3.     to_char(a.createtime, 'yyyy-mm-dd') AS createtime,
  4.     file_domain, upload_domain
  5.     , file_url, round(file_size / 1024000, 2) AS file_size
  6. FROM (
  7.     SELECT rid
  8.     FROM (
  9.         SELECT rownum AS rn, rid
  10.         FROM (
  11.             SELECT rowid AS rid
  12.             FROM music_video a
  13.             WHERE 1 = 1
  14.                 AND opus_stat = 1
  15.                 AND auditing = 1
  16.                 AND convert_mark = 1
  17.             ORDER BY a.createtime DESC
  18.         )
  19.         WHERE rownum <= :1
  20.     )
  21.     WHERE rn >= :2
  22. ) t, music_video a, space_user b
  23. WHERE t.rid = a.ROWID
  24.     AND a.userid = b.userid
  25. ORDER BY a.createtime DESC
關於執行計劃順序的糾正:




參考:
http://www.askmaclean.com/archives/read-sql-execution-plan.html

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

相關文章