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

總結--常用的監控SQL語句集合

2013-11-15 14:42:08  來源: SQL Server 

  監控事例的等待:
  
  select eventsum(decode(wait_time)) prevsum(decode(wait_time)) curr
  
  count(*)from v$session_wait group by event order by ;
  
  回滾段的爭用情況:
  
  select namewaitsgetswaits/gets ratio fromv$rollstat av$rollname b where ausn=busn;
  
  監控表空間的I/O比例:
  
  selectdftablespace_namenamedffile_name filefphyrds pyrfphyblkrd pbrfphywrtspywfphyblkwrtpbw fromv$filestatfdba_data_files df
  
  where ffile#=dffile_id
  
  在某個用戶下找所有的索引:
  
  selectuser_indexestable_nameuser_indexesindex_nameuniqueness column_namefrom 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_sizesum(parsed_size)parsed_size
  
  sum(code_sizecode_sizesum(er
  
  ror_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 MISSESFROM 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=&ownergroup 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;
  
  監控log_buffer的使用情況:(值最好小於%否則增加log_buffer 的大小)
  
  seletnamerbarvaluerenamerevalue
  
  (rbarvalue*)/revalue||% radio
  
  from v$sysstat rbarv$sysstat re
  
  where rbarname=redo buffer allocation retries
  
  and rename=redo entries;
  
  查看運行過的SQL語句:
  
  SELECT SQL_TEXTFROM V$SQL
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22192.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.