ORACLE常用sql

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,

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