ORACLE常用

数据库  /  管理员 发布于 3年前   196

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<>'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,