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

DBA常用腳本2-性能監控

2013-11-13 22:19:40  來源: Oracle 

  數據緩沖區的命中率已經不是性能調整中的主要問題了但是過低的命中率肯定是不可以的在任何情況下我們必須保證有一個大的data buffer和一個高的命中率
  

  這個語句可以獲得整體的數據緩沖命中率越高越好
  
  Code: [Copy to clipboard]
  
  SELECT aVALUE + bVALUE logical_reads
  cVALUE phys_reads
  round(*(cvalue/(avalue+bvalue))) hit_ratio
  FROM v$sysstat av$sysstat bv$sysstat c
  WHERE aNAME=db block gets
  AND bNAME=consistent gets
  AND cNAME=physical reads
  
  庫緩沖說明了SQL語句的重載率當然一個SQL語句應當被執行的越多越好如果重載率比較高就考慮增加共享池大小或者是提高Bind變量的使用以下語句查詢了Sql語句的重載率越低越好
  

  Code: [Copy to clipboard]
  SELECT SUM(pins) total_pinsSUM(reloads) total_reloads
  SUM(reloads)/SUM(pins)* libcache_reload_ratio
  FROM v$librarycache
  
  用戶鎖數據庫的鎖有的時候是比較耗費資源的特別是發生鎖等待的時候我們必須找到發生等待的鎖有可能的話殺掉該進程
  

  這個語句將查找到數據庫中所有的DML語句產生的鎖還可以發現任何DML語句其實產生了兩個鎖一個是表鎖一個是行鎖
  
  可以通過alter system kill session sidserial#來殺掉會話
  
  Code: [Copy to clipboard]
  
  SELECT /*+ rule */ susername
  decode(ltypeTMTABLE LOCK
  TXROW LOCK
  NULL) LOCK_LEVEL
  oowneroobject_nameoobject_type
  ssidsserial#sterminalsmachinesprogramsosuser
  FROM v$session sv$lock ldba_objects o
  WHERE lsid = ssid
  AND lid = oobject_id(+)
  AND susername is NOT NULL
  
  鎖與等待如果發生了鎖等待我們可能更想知道是誰鎖了表而引起誰的等待以下的語句可以查詢到誰鎖了表而誰在等待
  

  Code: [Copy to clipboard]
  
  SELECT /*+ rule */ lpad( decode(lxidusn ))||loracle_username User_name
  oowneroobject_nameoobject_typessidsserial#
  FROM v$locked_object ldba_objects ov$session s
  WHERE lobject_id=oobject_id
  AND lsession_id=ssid
  ORDER BY oobject_idxidusn DESC
  
  以上查詢結果是一個樹狀結構如果有子節點則表示有等待發生如果想知道鎖用了哪個回滾段還可以關聯到V$rollname其中xidusn就是回滾段的USN
  
  如果發生了事務或鎖想知道哪些回滾段正在被使用嗎?其實通過事務表我們可以詳細的查詢到事務與回滾段之間的關系同時如果關聯會話表我們則可以知道是哪個會話發動了這個事務
  

  Code: [Copy to clipboard]
  
  SELECT sUSERNAMEsSIDsSERIAL#tUBAFIL UBA filenum
  tUBABLK UBA Block numbertUSED_UBLK Number os undo Blocks Used
  tSTART_TIMEtSTATUStSTART_SCNBtXIDUSN RollIDrNAME RollName
  FROM v$session sv$transaction tv$rollname r
  WHERE sSADDR=tSES_ADDR
  AND tXIDUSN=rusn
  
  如果利用會話跟蹤或者是想查看某個會話的跟蹤文件那麼查詢到OS上的進程或線程號是非常重要的因為文件的令名中就包含這個信息以下的語句可以查詢到進程或線程號由此就可以找到對應的文件
  

  Code: [Copy to clipboard]
  
  SELECT pvalue||\||pvalue||_ora_||pspid filename
  FROM
  v$process p
  v$session s
  v$parameter p
  v$parameter p
  WHERE pname = user_dump_dest
  AND pname = db_name
  AND paddr = spaddr
  AND saudsid = USERENV (SESSIONID);
  
  在ORACLE i中可以監控索引的使用如果沒有使用到的索引完全可以刪除掉減少DML操作時的操作
  

  以下就是開始索引監控與停止索引監控的腳本
  
  Code: [Copy to clipboard]
  
  set heading off
  set echo off
  set feedback off
  set pages
  spool start_index_monitorsql
  
  SELECT alter index ||owner||||index_name|| monitoring usage;
  FROM dba_indexes
  WHERE owner = USER;
  
  spool off
  set heading on
  set echo on
  set feedback on
  
  set heading off
  set echo off
  set feedback off
  set pages
  spool stop_index_monitorsql
  
  SELECT alter index ||owner||||index_name|| nomonitoring usage;
  FROM dba_indexes
  WHERE owner = USER;
  
  spool off
  set heading on
  set echo on
  set feedback on
  
  如果需要監控更多的用戶可以將owner=User改寫成別的監控結果在視圖v$object_usage中查詢
  
  Code: [Copy to clipboard]
  CREATE OR REPLACE PROCEDURE show_space
  ( p_segname IN VARCHAR
  p_owner  IN VARCHAR DEFAULT USER
  p_type  IN VARCHAR DEFAULT TABLE
  p_partition IN VARCHAR DEFAULT NULL )
   This procedure uses AUTHID CURRENT USER so it can query DBA_*
   views using privileges from a ROLE and so it can be installed
   once per database instead of once per user who wanted to use it
  AUTHID CURRENT_USER
  as
  l_free_blks         number;
  l_total_blocks       number;
  l_total_bytes        number;
  l_unused_blocks       number;
  l_unused_bytes       number;
  l_LastUsedExtFileId     number;
  l_LastUsedExtBlockId    number;
  l_LAST_USED_BLOCK      number;
  l_segment_space_mgmt    varchar();
  l_unformatted_blocks number;
  l_unformatted_bytes number;
  l_fs_blocks number; l_fs_bytes number;
  l_fs_blocks number; l_fs_bytes number;
  l_fs_blocks number; l_fs_bytes number;
  l_fs_blocks number; l_fs_bytes number;
  l_full_blocks number; l_full_bytes number;
  
   Inline procedure to print out numbers nicely formatted
   with a simple label
  PROCEDURE p( p_label in varchar p_num in number )
  IS
  BEGIN
  dbms_outputput_line( rpad(p_label) ||
  to_char(p_num) );
  END;
  BEGIN
   This query is executed dynamically in order to allow this procedure
   to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
   via a role as is customary
   NOTE: at runtime the invoker MUST have access to these two
   views!
   This query determines if the object is an ASSM object or not
  BEGIN
  EXECUTE IMMEDIATE
  select tssegment_space_management
  FROM dba_segments seg dba_tablespaces ts
  WHERE segsegment_name   = :p_segname
  AND (:p_partition is null or
  segpartition_name = :p_partition)
  AND segowner = :p_owner
  AND segtablespace_name = tstablespace_name
  INTO l_segment_space_mgmt
  USING p_segname p_partition p_partition p_owner;
  EXCEPTION
  WHEN too_many_rows THEN
  dbms_outputput_line
  ( This must be a partitioned table use p_partition => );
  RETURN;
  END;
  
   If the object is in an ASSM tablespace we must use this API
   call to get space information; else we use the FREE_BLOCKS
   API for the user managed segments
  IF l_segment_space_mgmt = AUTO
  THEN
  dbms_spacespace_usage
  ( p_owner p_segname p_type l_unformatted_blocks
  l_unformatted_bytes l_fs_blocks l_fs_bytes
  l_fs_blocks l_fs_bytes l_fs_blocks l_fs_bytes
  l_fs_blocks l_fs_bytes l_full_blocks l_full_bytes p_partition);
  
  p( Unformatted Blocks l_unformatted_blocks );
  p( FS Blocks ()  l_fs_blocks );
  p( FS Blocks () l_fs_blocks );
  p( FS Blocks () l_fs_blocks );
  p( FS Blocks () l_fs_blocks );
  p( Full Blocks     l_full_blocks );
  ELSE
  dbms_spacefree_blocks(
  segment_owner   => p_owner
  segment_name   => p_segname
  segment_type   => p_type
  freelist_group_id =>
  free_blks     => l_free_blks);
  
  p( Free Blocks l_free_blks );
  END IF;
  
   And then the unused space API call to get the rest of the
   information
  dbms_spaceunused_space
  ( segment_owner   => p_owner
  segment_name   => p_segname
  segment_type   => p_type
  partition_name  => p_partition
  total_blocks   => l_total_blocks
  total_bytes    => l_total_bytes
  unused_blocks   => l_unused_blocks
  unused_bytes   => l_unused_bytes
  LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId
  LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId
  LAST_USED_BLOCK => l_LAST_USED_BLOCK );
  
  p( Total Blocks l_total_blocks );
  p( Total Bytes l_total_bytes );
  p( Total MBytes trunc(l_total_bytes//) );
  p( Unused Blocks l_unused_blocks );
  p( Unused Bytes l_unused_bytes );
  p( Last Used Ext FileId l_LastUsedExtFileId );
  p( Last Used Ext BlockId l_LastUsedExtBlockId );
  p( Last Used Block l_LAST_USED_BLOCK );
  END;
From:http://tw.wingwit.com/Article/program/Oracle/201311/18788.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.