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

ORACLE 應用經驗(2)

2022-06-13   來源: Oracle 

  誰正在訪問數據庫?
  Select csid cserial#cusernameaobject_idbobject_name
  cprogramcstatusdnamecosuser
  from v$Locked_object a
  All_objects b
  v$session c
  audit_actions d
  where aobject_id=bobject_id
  and asession_id =csid(+)
  and mand=daction;
  
  alter system kill session &&;
  
  Select asidaserial#ausernameastatusaprogrambnameaosuser
  from v$session aaudit_actions b
  where mand=baction
  And username=&;
  誰被鎖住?
  Select asidaserial#ausernameALOCKWAITastatusaprogrambname
  from v$session aaudit_actions b
  where mand=baction
  AND LOCKWAIT IS NOT NULL;
  誰在鎖表?
  Select asidaserial#ausernameALOCKWAITastatusaprogrambname
  from v$session aaudit_actions b
  where mand=baction
  AND STATUS=ACTIVE;
  
  Select sid serial# object_name row_wait_block#
  row_wait_row# row_wait_file#
  from all_objects v$session
  where row_wait_obj#=object_id and type=USER
  and lockwait is not null ;
  
  Select slusername slsid slserial#
  from v_$lock l v$session s
  where exists (select * from v_$lock l v$session s
  where lsid=ssid and lid=l
  and slockwait=lkaddr
  and request=
  and lsid=ssid) ;
  
  select count(*) from v$session;
  select count(*) from sysv_$process;
  select count(*) from sysv_$transaction;
  
  ZYP_
  
  查看哪些包要固定
  COLUMN OWNER FORMAT A
  Select owner name type
  source_size+code_size+parsed_size+error_size BYPES
  from dba_object_size
  where type=PACKAGE BODY ORDER BY DESC ;
  
  查看一個用戶擁有哪些表空間的實體信息:
  Select tablespace_name owner segment_namesegment_type
  from dba_segments
  where ownerSyS
  and segment_type_ROLLBACK
  order by tablespace_name owner segment_name ;
  
  break on owner on segment_name
  COLUMN segment_name FORMAT A
  cOLUMN tablespace_name FORMAT A
  COLUMN file_name FORMAT A
  SELECT Aowner asegment_name btablespace_name bfile_name
  sum(abytes) bytes
  from dba_extents a dba_data_files b
  where afile_idbfile_id group by aowner asegment_name
  btablespace_name bfile_name ;
  
  看內存緩沖區使用效率的指數是命中率HITS:
  Hits=Logical_reads/(logical_reads+physical_reads)
  其中logical_reads=db_block_gets+consistent_reads
  
  select curvalue db convalue con phyvalue phy
  (curvalue+convalue)/curvalue+convalue+phyvalue)* HITS
  from v$sysstat cur v$sysstat con v$sysstat phy
  where CURNAME=db block gets AND
  CONNAME=consistent gets AND
  PHYNAME=physical reads ;
  
  如何檢測ROLLBACK SEGMENT競爭?
  select class count from v$waitstat
  where class in
  (system undo header system undo block
  undo header undo block) ;
  
  select sum(value) from v$sysstat where name in
  (db block gets consistents gets) ;
  
  若count/sum(value)大於%則應考慮增加ROLLBACK SEGMENT
  
  查看有事務在哪幾個回退段中
  COLUMN u FORMAT A
  COLUMN s FORMAT A
  COLUMN s FORMAT A
  select osuser o username u segment_name s sasql_text
  from v$session s v$transaction t dba_rollback_segs r v$sqlarea sa
  where staddr=taddr and tsidusn=rsegmant_id(+)
  and ssql_address=saaddress(+) ;
  
  
  
  
  

From:http://tw.wingwit.com/Article/program/Oracle/201311/18905.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.