---
title: "ORACLE常用"
author: "hotjava"
date: "2008-01-10T18:53:48Z"
tags: []
slug: "oracle"
draft: false
lastmod: "2009-01-14T10:59:19Z"
id: 154842
url: "https://hotjava.iteye.com/blog/154842"
---

## [1.1   查看session使用数？（或者称建立的连接数）]()

 

[select   count(\*)   from   v$session;]()

 

## []()[1.2   查看oracle锁]()

[SELECT substr(v$lock.sid,1,4) "SID",  
\
substr(username,1,12) "UserName",osuser,machine,  
\
substr(object\_name,1,25) "ObjectName",  
\
v$lock.type "LockType",  
\
decode(rtrim(substr(lmode,1,4)),  
\
'2','Row-S (SS)','3','Row-X (SX)',  
\
'4','Share',     '5','S/Row-X (SSX)',  
\
'6','Exclusive', 'Other' ) "LockMode",  
\
substr(v$session.program,1,25) "ProgramName"  
\
FROM V$LOCK,SYS.DBA\_OBJECTS,V$SESSION  
\
WHERE (OBJECT\_ID = v$lock.id1  
\
AND v$lock.sid = v$session.sid  
\
AND username IS NOT NULL  
\
AND username NOT IN ('SYS','SYSTEM')  
\
AND SERIAL# != 1);]()

 

## []()[1.3   数据库分析]()

[语句       execute DBMS\_UTILITY.ANALYZE\_DATABASE('COMPUTE',10000,NULL,'');]()          

## []()[1.4   查看表统计数据]()

       [select table\_name,num\_rows,blocks,empty\_blocks,avg\_space,chain\_cnt,avg\_row\_len,last\_analyzed from all\_tables where owner='RMS'          
\
分析单表       analyze table rms.user\_port compute statistics]()           

## []()[1.5   查看索引统计数据]()

       [select index\_name,blevel,leaf\_blocks,distinct\_keys,avg\_leaf\_blocks\_per\_key,avg\_data\_blocks\_per\_key,clustering\_factor,last\_analyzed  from all\_indexes where owner='RMS';]()        

## []()[1.6   生成单表分析语句]()

       [select 'analyze table rms.'||segment\_name||' compute statistics ;'segment\_name,sum(bytes)/1024/1024 MB from user\_segments,all\_tables  
\
where all\_tables.owner='RMS' and all\_tables.table\_name= segment\_name GROUP BY segment\_name]()         

 

## []()[1.7   发现表链接行]()

[语句       select table\_name,chain\_cnt from user\_tables order by chain\_cnt desc               
\
select table\_name,chain\_cnt from all\_tables  where owner='RMS' order by chain\_cnt desc]()           

## []()[1.8   计算高速缓存命中率]()

[语句       "select 1-(phy.value/(cur.value+con.value)) from v$sysstat cur,v$sysstat con,v$sysstat phy  
\
where cur.name='db block gets' and con.name='consistent gets'  
\
and phy.name='physical reads'"             
\
db block gets  4709479237            
\
consistent gets 22146942               
\
physical reads 8931785          
\
命中率    0.998112322          1-physical reads/(db block gets+consistent gets)  
\
建议       达到0.90或以上认为是好的]()            

                    

## []()[1.9   找出大量消耗Buffer Cache的对象]()

       [" column c0 heading 'Owner'                     format a15  
\
column c1 heading 'Object|Name'               format a30  
\
column c2 heading 'Number|of|Buffers'         format 999,999  
\
column c3 heading 'Percentage|ofData|Buffer' format 999,999,999  
\
select  
\
owner                        c0,  
\
object\_name                  c1,  
\
count(1)                     c2,  
\
(count(1)/(select count(\*) from v$bh)) \*100  c3  
\
from  
\
dba\_objects o,  
\
v$bh        bh  
\
where  
\
o.object\_id  = bh.objd  
\
and  
\
o.owner not in ('SYS','SYSTEM')  
\
group by  
\
owner,  
\
object\_name  
\
order by  
\
count(1) desc  
\
;"]()         

                    

## []()[1.10 计算库缓存命中率]()

[语句       SELECT SUM(pins-reloads)/SUM(pins) from v$librarycache            
\
建议       低于0.99需要试着改善它，可以增加共享池的规模来改善它，如果有很多可用空间那就得从改善我们的程序         
\
查看当前未使用的共享池           "select to\_number(v$parameter.value) value, v$sgastat.BYTES,  
\
(v$sgastat.bytes/v$parameter.value)\*100 ""percent free""  
\
from v$sgastat, v$parameter  
\
where v$sgastat.name= 'free memory'  
\
and v$parameter.name = 'shared\_pool\_size'  
\
and v$sgastat.pool='shared pool'"]()

                    

## []()[1.11 手工方式清空共享池]()

       [ALTER SYSTEM FLUSH SHARED\_POOL;]()            

                    

 

## []()[1.12 计算字典缓存命中率]()

[语句       select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache           
\
建议       达到0.85或以上认为是可以接受的]()

 

## []()[1.13 检查回滚段争用]()

[语句       select sum(gets),sum(waits),sum(waits)/sum(gets) from v$rollstat  
\
建议       大于1%需要处理，可以通过更多回滚段来降低争用  
\
查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。      
\
"  
\
select s.username, u.name from v$transaction t,v$rollstat r,  
\
v$rollname u,v$session s where s.taddr=t.addr and  
\
t.xidusn=r.usn and r.usn=u.usn order by s.username;  
\
"  
\
"SELECT a.name , b.xacts ""活动事务个数"", b.writes ""写入的字节数"", b.extents as ""区个数""  
\
FROM v$rollname a, v$rollstat b  
\
WHERE a.usn=b.usn;"]()

## []()[1.14 检查回滚段收缩]()

[语句       select name,shrinks from v$rollstat,v$rollname where V$rollstat.usn=v$rollname.usn]()

 

## []()[1.15 redo日志缓存器]()

       [select name,value from v$sysstat where name ='redo buffer allocation retries']()

  

      

 

## []()[1.16 生成删除约束脚本]()

       [select 'ALTER TABLE '||TABLE\_NAME||' DISABLE  CONSTRAINT '||CONSTRAINT\_NAME||';' from all\_constraints where owner='RMS' AND CONSTRAINT\_TYPE&lt;&gt;'P'  
\
发现表链接行        
\
select table\_name,chain\_cnt from user\_tables order by chain\_cnt desc;]()

 

## []()[1.17 查看表空间情况]()

       ["SELECT UPPER(F.TABLESPACE\_NAME) ""表空间名"",  
\
D.TOT\_GROOTTE\_MB ""表空间大小(M)"",  
\
D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES ""已使用空间(M)"",  
\
TO\_CHAR(ROUND((D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES) / D.TOT\_GROOTTE\_MB * 100,  
\
2),  
\
'990.99') ""使用比"",  
\
F.TOTAL\_BYTES ""空闲空间(M)"",  
\
F.MAX\_BYTES ""最大块(M)""  
\
FROM (SELECT TABLESPACE\_NAME,  
\
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL\_BYTES,  
\
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX\_BYTES  
\
FROM SYS.DBA\_FREE\_SPACE  
\
GROUP BY TABLESPACE\_NAME) F,  
\
(SELECT DD.TABLESPACE\_NAME,  
\
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT\_GROOTTE\_MB  
\
FROM SYS.DBA\_DATA\_FILES DD  
\
GROUP BY DD.TABLESPACE\_NAME) D  
\
WHERE D.TABLESPACE\_NAME = F.TABLESPACE\_NAME  
\
ORDER BY 4 DESC"]()

 

## []()[1.18 合并表空间]()

       [alter tablespace trmsdata coalesce;]()

 

## []()[1.19 查看表占用空间数]()

       ["select segment\_name,sum(bytes)/1024/1024 MB from user\_segments,all\_tables  
\
where all\_tables.owner='RMS' and all\_tables.table\_name= segment\_name GROUP BY segment\_name"]()

## []()[1.20 查看索引占用空间数]()

       ["select segment\_name,sum(bytes)/1024/1024 MB from user\_segments, all\_indexes  
\
where all\_indexes.owner='RMS' and all\_indexes.index\_name= segment\_name GROUP BY segment\_name  
\
"]()

 

## []()[1.21 查看系统内存使用]()

       [swapinfo -atm HP UNIX内核参数      bufpages 61992 缓冲页 dbc\_max\_pct 10 动态缓存占内存最大百分比 dbc\_min\_pct 10 动态缓存占内存最小百分比]()

 

## []()[1.22 查看session使用的内存]()

       [select   c.sid,c.OSUSER,c.MACHINE,name,value  from v$statname n,v$sesstat s,v$session c]()           

 

## []()[1.23 找最耗资源的session]()

       ["SELECT ses.sid  
\
, DECODE(ses.action,NULL,'online','batch')          ""User""  
\
, MAX(DECODE(sta.statistic#,9,sta.value,0))  
\
/greatest(3600\*24\*(sysdate-ses.logon\_time),1)     ""Log IO/s""  
\
, MAX(DECODE(sta.statistic#,40,sta.value,0))  
\
/greatest(3600\*24\*(sysdate-ses.logon\_time),1)     ""Phy IO/s""  
\
, 60\*24\*(sysdate-ses.logon\_time)                    ""Minutes""  
\
FROM V$SESSION ses  
\
, V$SESSTAT sta  
\
WHERE ses.status     = 'ACTIVE'  
\
AND sta.sid        = ses.sid  
\
AND sta.statistic# IN (9,40)  
\
GROUP BY ses.sid, ses.action, ses.logon\_time  
\
ORDER BY  
\
SUM( DECODE(sta.statistic#,40,100\*sta.value,sta.value) )  
\
/ greatest(3600\*24\*(sysdate-ses.logon\_time),1)  DESC;"]()            

                    

## []()[1.24 查找前十条性能差的sql]()

       ["SELECT * FROM (select PARSING\_USER\_ID,EXECUTIONS,SORTS,  
\
COMMAND\_TYPE,DISK\_READS,sql\_text.address FROM v$sqlarea  
\
order BY disk\_reads DESC )where ROWNUM&lt;10 ;"]()        

                    

## []()[1.25 查出SQL的查询计划]()

       ["select lpad(' ', 2\*(level-1))||operation ""Operation"",  
\
options ""Options"",              
\
decode(to\_char(id), '0', 'Cost='||nvl(to\_char(position), 'n/a'), object\_name) ""Object Name"",  
\
substr(optimizer, 1, 6) ""Optimizer""  
\
from v$sql\_plan a                     
\
start with address = 'C0000000FCCDEDA0'  
\
and id = 0                                        
\
connect by prior id = a.parent\_id                 
\
and prior a.address = a.address                   
\
and prior a.hash\_value = a.hash\_value;"]()           

 

## []()[1.26 查看oracle系统进程]()

       [select * from V$PROCESS         spid为系统进程ID]()

 

## []()[1.27 查看进程正在执行的SQL]()

       ["SELECT a.username,a.machine,a.program,a.sid,a.serial#,  
\
a.status,c.piece,c.sql\_text from v$session a,v$process b,  
\
v$sqltext c WHERE b.spid='ORCL' AND b.addr=a.paddr AND  
\
a.sql\_address=c.address(+)order BY c.piece"]()             

 

## []()[1.28 查看占io较大的正在运行的session]()

 

       ["SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,  
\
se.terminal,se.program,se.MODULE,se.sql\_address,st.event,st.  
\
p1text,si.physical\_reads,  
\
si.block\_changes FROM v$session se,v$session\_wait st,  
\
v$sess\_io si,v$process pr WHERE st.sid=se.sid AND st.  
\
sid=si.sid AND se.PADDR=pr.ADDR AND se.sid&gt;8 AND st.  
\
wait\_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical\_reads DESC"]()            

 

## []()[1.29 查看session正在执行的SQL]()

[select sql\_text,piece  
\
from v$sqltext   
\
where (address,hash\_value) in ( select decode(sql\_hash\_value,0,prev\_sql\_addr,sql\_address) ,  
\
decode(sql\_hash\_value,0,prev\_hash\_value,sql\_hash\_value)  
\
from v$session  
\
where sid =&sid)  
\
order by piece]()

## []()[1.30 脏缓冲回写到磁盘去]()

       [alter system set events = 'immediate trace name flush\_cache';     
\
导记录  
\
insert into tablename  
select * from username.tablename]()