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

分析數據庫性能的SQL

2013-11-13 15:51:14  來源: Oracle 

  用於查看哪些實例的哪些操作使用了大量的臨時段
  
  SELECT to_number(decode(SID NULL SID)) sid
  operation_type OPERATIONtrunc(EXPECTED_SIZE/) ESIZE
  trunc(ACTUAL_MEM_USED/) MEM trunc(MAX_MEM_USED/) MAX MEM
  NUMBER_PASSES PASS trunc(TEMPSEG_SIZE/) TSIZE
  FROM V$SQL_WORKAREA_ACTIVE
  ORDER BY ;
  
  查詢有熱塊查詢的SQL語句
  
  select hash_value
  from v$sqltext a
  (select distinct aownerasegment_nameasegment_type from
  dba_extents a
  (select dbarfildbablk
  from (select dbarfildbablk
  from x$bh order by tch desc) where rownum < ) b
  where aRELATIVE_FNO = bdbarfil
  and aBLOCK_ID <= bdbablk and ablock_id + ablocks > bdbablk) b
  where asql_text like %||bsegment_name||% and bsegment_type = TABLE
  order by ahash_valueaaddressapiece;
  
  全表掃描
  
  select opnametargetbnum_rowsbtablespace_namecount(target) from v$session_longops aall_all_tables b
  where aTARGET=bowner||||btable_name
  having count(target)> group by  opnametargetbnum_rowsbtablespace_name
  
  查看磁盤排序和緩存排序次數
  
  select to_char(snsnap_timeyyyymmdd hh) time_
  avg(newmenvalue oldmenvalue) sorts_memeory
  avg(newdskvalue olddskvalue) disk_sort
  from  stats$sysstat oldmen
  stats$sysstat newmen
  stats$sysstat newdsk
  stats$sysstat olddsk
  stats$snapshot sn
  where  newdsksnap_id=snsnap_id
  and   olddsksnap_id=snsnap_id
  and   newmensnap_id=snsnap_id
  and   newdsksnap_id=snsnap_id
  and   oldmenname=sorts (memory)
  and   newmenname=sorts (memory)
  and   olddskname=sorts (disk)
  and   newdskname=sorts (disk)
  group by to_char(snsnap_timeyyyymmdd hh)
  
  執行最慢的前個SQL???
  
  select * from (
  select
  to_char(snap_timedd Mon HH:mi:ss) mydate
  executions               exec
  loads                 loads
  parse_calls              parse
  disk_reads               reads
  buffer_gets              gets
  rows_processed             rows_proc
  sorts                 sorts
  sql_text
  hash_value
  from
  perfstatstats$sql_summary sql
  perfstatstats$snapshot   sn
  where
  sqlsnap_id >
  (select min(snap_id) min_snap
  from stats$snapshot where snap_time > sysdate$days_back)
  and
  sqlsnap_id = snsnap_id
  order by $sortskey desc) tt where rownum<;
  
  SQL緩存池的命中率查詢(pinhitratiogethitratio應該大於%以上)
  
  select namespacegethitratiopinhitratioreloadsinvalidations
  from v$librarycache
  where namespace in (SQL AREATABLE/PROCEDUREBODYTRIGGER)
  
  數據庫的常規參數我就不說了除了V$parameter中的常規參數外ORACLE還有大量的隱含參數下面的語句就可以查詢到數據庫的所有隱含參數以及其值與參數的描述
  
  SELECT NAME
  VALUE
  decode(isdefault TRUEYN) as Default
  decode(ISEMTRUEYN) as SesMod
  decode(ISYMIMMEDIATE I
  DEFERRED D
  FALSE N) as SysMod
  decode(IMODMODIFIEDU
  SYS_MODIFIEDSN) as Modified
  decode(IADJTRUEYN) as Adjusted
  description
  FROM ( GV$SYSTEM_PARAMETER
  SELECT xinst_id as instance
  xindx+
  ksppinm as NAME
  ksppity
  ksppstvl as VALUE
  ksppstdf as isdefault
  decode(bitand(ksppiflg/)TRUEFALSE) as ISEM
  decode(bitand(ksppiflg/)
  IMMEDIATEDEFERREDFALSE) as ISYM
  decode(bitand(ksppstvf)MODIFIEDFALSE) as IMOD
  decode(bitand(ksppstvf)TRUEFALSE) as IADJ
  ksppdesc as DESCRIPTION
  FROM x$ksppi x
  x$ksppsv y
  WHERE xindx = yindx
  AND substr(ksppinm) = _
  AND xinst_id = USERENV(Instance)
  )
  ORDER BY NAME
  
  想知道現在哪個用戶正在利用臨時段嗎?這個語句將告訴你哪個用戶正在利用臨時段
  
  SELECT btablespace bsegfile# bsegblk# bblocks asid aserial#
  ausername aosuser astatuscsql_text
  FROM v$session av$sort_usage b v$sql c
  WHERE asaddr = bsession_addr
  AND asql_address = caddress(+)
  ORDER BY btablespace bsegfile# bsegblk# bblocks;
  
  查看磁盤碎片
  
  select tablespace_namesqrt(max(blocks)/sum(blocks))*
  (/sqrt(sqrt(count(blocks)))) FSFI
  from dba_free_space
  group by tablespace_name order by
  
  查看表空間的名稱及大小
  
  select ttablespace_name round(sum(bytes/(*))) ts_size
  from dba_tablespaces t dba_data_files d
  where ttablespace_name = dtablespace_name
  group by ttablespace_name;
  
  查看表空間物理文件的名稱及大小
  
  select tablespace_name file_id file_name
  round(bytes/(*)) total_space
  from dba_data_files
  order by tablespace_name;
  
  查看回滾段名稱及大小
  
  select segment_name tablespace_name rstatus
  (initial_extent/) InitialExtent(next_extent/) NextExtent
  max_extents vcurext CurExtent
  From dba_rollback_segs r v$rollstat v
  Where rsegment_id = vusn(+)
  order by segment_name
  
  耗資源的進程(top session)
  
  select sschemaname schema_name  decode(sign( command)
  to_char(command) Action Code # || to_char(command) ) action  status
  session_status  sosuser os_user_name  ssid     pspid      sserial# serial_num
  nvl(susername [Oracle process]) user_name  sterminal terminal
  sprogram program  stvalue criteria_value from v$sesstat st  v$session s  v$process p
  where stsid = ssid and  ststatistic# = to_number() and  (ALL = ALL
  or sstatus = ALL) and paddr = spaddr order by stvalue desc pspid asc susername asc sosuser asc
  
  查看鎖(lock)情況
  
  select /*+ RULE */ lsosuser os_user_name  lsusername user_name
  decode(lstype RW Row wait enqueue lock TM DML enqueue lock TX
  Transaction enqueue lock UL User supplied lock) lock_type
  oobject_name object  decode(lslmode null Row Share
  Row Exclusive Share Share Row Exclusive Exclusive null)
  lock_mode  oowner  lssid  lsserial# serial_num  lsid  lsid
  from sysdba_objects o (  select sosuser  susername  ltype
  llmode  ssid  sserial#  lid  lid  from v$session s
  v$lock l  where ssid = lsid ) ls where oobject_id = lsid and  oowner
  <> SYS  order by oowner oobject_name
  
  查看低效率的SQL語句
  
  SELECT EXECUTIONS DISK_READS BUFFER_GETS
  ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS) Hit_radio
  ROUND(DISK_READS/EXECUTIONS) Reads_per_run
  SQL_TEXT
  FROM  V$SQLAREA
  WHERE EXECUTIONS>
  AND   BUFFER_GETS >
  AND (BUFFER_GETSDISK_READS)/BUFFER_GETS <
  ORDER BY DESC
From:http://tw.wingwit.com/Article/program/Oracle/201311/17408.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.