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

oracle數據庫性能監控的SQL的幾種情況

2013-11-13 22:17:49  來源: Oracle 

   監控事例的等待
  select eventsum(decode(wait_Time)) Prev
  sum(decode(wait_Time)) Currcount(*) Tot
  from v$session_Wait
  group by event order by ;
  
   回滾段的爭用情況
  select name waits gets waits/gets Ratio
  from v$rollstat a v$rollname b
  where ausn = busn;
  
   監控表空間的 I/O 比例
  select dftablespace_name namedffile_name filefphyrds pyr
  fphyblkrd pbrfphywrts pyw fphyblkwrt pbw
  from v$filestat f dba_data_files df
  where ffile# = dffile_id
  order by dftablespace_name;
  
   監控文件系統的 I/O 比例
  select substr(afile#) # substr(aname) Name
  astatus abytes bphyrds bphywrts
  from v$datafile a v$filestat b
  where afile# = bfile#;
  
  在某個用戶下找所有的索引
  select user_indexestable_name user_indexesindex_nameuniqueness column_name
  from user_ind_columns user_indexes
  where user_ind_columnsindex_name = user_indexesindex_name
  and user_ind_columnstable_name = user_indexestable_name
  order by user_indexestable_type user_indexestable_name
  user_indexesindex_name column_position;
  
   監控 SGA 的命中率
  select avalue + bvalue logical_reads cvalue phys_reads
  round( * ((avalue+bvalue)cvalue) / (avalue+bvalue)) BUFFER HIT RATIO
  from v$sysstat a v$sysstat b v$sysstat c
  where astatistic# = and bstatistic# =
  and cstatistic# = ;
  
   監控 SGA 中字典緩沖區的命中率
  select parameter getsGetmisses getmisses/(gets+getmisses)* miss ratio
  ((sum(getmisses)/ (sum(gets)+sum(getmisses))))* Hit ratio
  from v$rowcache
  where gets+getmisses <>
  group by parameter gets getmisses;
  
   監控 SGA 中共享緩存區的命中率應該小於%
  select sum(pins) Total Pins sum(reloads) Total Reloads
  sum(reloads)/sum(pins) * libcache
  from v$librarycache;
  
  select sum(pinhitsreloads)/sum(pins) hit radiosum(reloads)/sum(pins) reload percent
  from v$librarycache;
  
   顯示所有數據庫對象的類別和大小
  select count(name) num_instances type sum(source_size) source_size
  sum(parsed_size) parsed_size sum(code_size) code_size sum(error_size) error_size
  sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
  from dba_object_size
  group by type order by ;
  
   監控 SGA 中重做日志緩存區的命中率應該小於%
  SELECT name gets misses immediate_gets immediate_misses
  Decode(getsmisses/gets*) ratio
  Decode(immediate_gets+immediate_misses
  immediate_misses/(immediate_gets+immediate_misses)*) ratio
  FROM v$latch WHERE name IN (redo allocation redo copy);
  
   監控內存和硬盤的排序比率最好使它小於 增加 sort_area_size
  SELECT name value FROM v$sysstat WHERE name IN (sorts (memory) sorts (disk));
  
   監控當前數據庫誰在運行什麼SQL語句
  SELECT osuser username sql_text from v$session a v$sqltext b
  where asql_address =baddress order by address piece;
  
   監控字典緩沖區
  SELECT (SUM(PINS RELOADS)) / SUM(PINS) LIB CACHE FROM V$LIBRARYCACHE;
  SELECT (SUM(GETS GETMISSES USAGE FIXED)) / SUM(GETS) ROW CACHE FROM V$ROWCACHE;
  SELECT SUM(PINS) EXECUTIONS SUM(RELOADS) CACHE MISSES WHILE EXECUTING FROM V$LIBRARYCACHE;
  
  後者除以前者此比率小於%接近%為好
  
  SELECT SUM(GETS) DICTIONARY GETSSUM(GETMISSES) DICTIONARY CACHE GET MISSES
  FROM V$ROWCACHE
  
   找ORACLE字符集
  select * from sysprops$ where name=NLS_CHARACTERSET;
  
   監控 MTS
  select busy/(busy+idle) shared servers busy from v$dispatcher;
  
  此值大於參數需加大
  
  select sum(wait)/sum(totalq) dispatcher waits from v$queue where type=dispatcher;
  select count(*) from v$dispatcher;
  select servers_highwater from v$mts;
  
  servers_highwater接近mts_max_servers時參數需加大
  
   碎片程度
  select tablespace_namecount(tablespace_name) from dba_free_space group by tablespace_name
  having count(tablespace_name)>;
  
  alter tablespace name coalesce;
  alter table name deallocate unused;
  
  create or replace view ts_blocks_v as
  select tablespace_nameblock_idbytesblocksfree space segment_name from dba_free_space
  union all
  select tablespace_nameblock_idbytesblockssegment_name from dba_extents;
  
  select * from ts_blocks_v;
  
  select tablespace_namesum(bytes)max(bytes)count(block_id) from dba_free_space
  group by tablespace_name;
  
  查看碎片程度高的表
  
  SELECT segment_name table_name COUNT(*) extents
  FROM dba_segments WHERE owner NOT IN (SYS SYSTEM) GROUP BY segment_name
  HAVING COUNT(*) = (SELECT MAX( COUNT(*) FROM dba_segments GROUP BY segment_name);
  
  索引的存儲情況檢查
  select segment_namesum(bytes)count(*) ext_quan from dba_extents where
  tablespace_name=&tablespace_name and segment_type=TABLE group by tablespace_namesegment_name;
  
  select segment_namecount(*) from dba_extents where segment_type=INDEX and owner=&owner
  group by segment_name;
  
  找使用CPU多的用戶session
  是cpu used by this session
  
  select asidspidstatussubstr(aprogram) progaterminalosuservalue// value
  from v$session av$process bv$sesstat c
  where cstatistic#= and csid=asid and apaddr=baddr order by value desc;
  

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