熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

查詢Oracle正在執行和執行過的SQL語句

2013-11-13 22:25:21  來源: Oracle 

  正在執行的

  select ausername asidbSQL_TEXT bSQL_FULLTEXT

  from v$session a v$sqlarea b

  where asql_address = baddress

  執行過的

  select bSQL_TEXTbFIRST_LOAD_TIMEbSQL_FULLTEXT

  from v$sqlarea b

  where bFIRST_LOAD_TIME between /:: and

  /:: order by bFIRST_LOAD_TIME

  (此方法好處可以查看某一時間段執行過的sql並且 SQL_FULLTEXT 包含了完整的 sql 語句)

  其他

  select OSUSERPROGRAMUSERNAMESCHEMANAMEBCpu_TimeSTATUSBSQL_TEXT

  from V$SESSION A LEFT JOIN V$SQL B ON ASQL_ADDRESS=BADDRESS AND ASQL_HASH_VALUE=BHASH_VALUE order by bcpu_time desc

  select address sql_text piece

  from v$session v$sqltext

  where address = sql_address

   and machine = < you machine name >

  order by address piece

  查找前十條性能差的sql

  SELECT * FROM (select PARSING_USER_IDEXECUTIONSSORTS

  COMMAND_TYPEDISK_READSsql_text FROM v$sqlarea

  order BY disk_reads DESC )where ROWNUM< ;

  查看占io較大的正在運行的session

  SELECT sesidseserial#prSPIDseusernamesestatus

  seterminalseprogramseMODULEsesql_addresssteventst

  ptextsiphysical_reads

  siblock_changes FROM v$session sev$session_wait st

  v$sess_io siv$process pr WHERE stsid=sesid AND st

  sid=sisid AND sePADDR=prADDR AND sesid> AND st

  wait_time= AND stevent NOT LIKE %SQL% ORDER BY physical_reads DESC


From:http://tw.wingwit.com/Article/program/Oracle/201311/19070.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.