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

oracle認證輔導:oracle優化和管理sql1

2013-11-13 22:14:14  來源: Oracle 

  oracle認證輔導oracle優化和管理sql

  查看當前正在執行的等待情況

  SELECT TAsid

  TAseq#

  TBUsername

  TBTerminal

  TBProgram

  Decode(TBCommand ] NoCommand

   ] CreateTable

   ] Insert

   ] Select

   ] Update

   ] Delete

   ] CreateIndex

  ] AlterTable

  ] CreateView

  ] ValidateIndex

  ] AlterDatabase

  ] CreateTablespace

  ] DropTablespace

  ] AlterTablespace

  ] DropUser

  ] AnalyzeTable

  ] AnalyzeIndex

  TBCommand || ther) Command

  DECODE(TAeventdb file scattered read通表掃描

  db file sequential read索引掃描

  latch freelatch contention

  free buffer waits等待DBWR 清除弄髒塊

  log file syncLGWR寫COMMIT或ROLLBACK數據

  write complete waits等待DBWR寫

  buffer busy wait可能是FreeList競爭

  TAevent) Event

  TAptextTAp TApraw

  TAptextTAp TApraw

  TAptextTAp TApraw

  TAwait_time

  TAseconds_in_wait

  TAstate

  TBsql_address

  TBsql_hash_value

  FROM v$session_wait TA

  v$session TB

  WHERE TBterminal=FUTUREMGET AND

  TASID = TBSID AND

  TAevent NOT LIKE % timer AND

  TAevent NOT LIKE rdbms ipc message AND

  TAevent NOT LIKE SQL*Net %

  查看連接等待事件

  SELECT Sid || || Event || || Total_Waits || || Average_Wait

  FROM V$session_Event

  WHERE Sid = &上面的SID

  每個用戶命中率(命中率應該超過%)

  SELECT TASid 連接ID

  username 用戶名

  consistent_gets 讀一致性

  block_gets 緩沖區讀

  physical_reads 物理讀

  *(consistent_gets+block_getsphysical_reads)/(consistent_gets+block_gets) hiratio

  TAsql_address

  TAsql_hash_value

  FROM v$session TA

  v$sess_io TB

  WHERE TAsid=TBsid

  AND (consistent_gets+block_gets)》

  AND username IS NOT NULL

  ORDER BY ASC;

  查詢耗資源的SQL

  SELECT ADDRESSHASH_VALUE

  SUBSTR(SQL_TEXT ) TEXT

  BUFFER_GETS

  EXECUTIONS

  BUFFER_GETS / EXECUTIONS AVG

  FROM V$SQLAREA

  WHERE EXECUTIONS 》

  AND BUFFER_GETS 》

  ORDER BY ;

  查詢耗CPU資源的SQL

  SELECT SSSID

  Decode(SECommand ] NoCommand

   ] CreateTable

   ] Insert

   ] Select

   ] Update

   ] Delete

   ] CreateIndex

  ] AlterTable

  ] CreateView

  ] ValidateIndex

  ] AlterDatabase

  ] CreateTablespace

  ] DropTablespace

  ] AlterTablespace

  ] DropUser

  ] AnalyzeTable

  ] AnalyzeIndex

  SECommand || ther

  SSVALUE CPU

  SEUSERNAME

  SEPROGRAM

  SEsql_address

  SEsql_hash_value

  FROM V$SESSTAT SS V$SESSION SE

  WHERE SSSTATISTIC# IN

  (SELECT STATISTIC#

  FROM V$STATNAME

  WHERE NAME = CPU used by this session

  AND SESID = SSSID

  AND SSSID 》

  ORDER BY SSSID;


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