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

oracle性能檢測sql語句

2013-11-13 16:05:44  來源: 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;

  轉自ml


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