ORACLE常用SQL

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,
                     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 = '00000003DFC20D08'
        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