ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 总结

总结

原创 Linux操作系统 作者:bulls_523 时间:2013-07-01 16:25:43 0 删除 编辑

HK job:
jobsdb.com.hk, monster.com

唯品會:LMFL12119975352300

 

 

yu-xin.yang@foxconn.com
NyStYo7d

 

1、查看buffer_cache命中率:
select  a.value+b.value logical_reads,c.value physical_reads,round(100*(1-c.value/(a.value+b.value)),4) hit_ratio
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name='db block gets'
and b.name='consistent gets'
and c.name='physical reads';


2、查看性能最差的TOP10 SQL語句:
select * from (select parsing_user_id,executions,sorts,command_type,disk_reads,sql_text
from v$sqlarea
order by disk_reads desc)
where rownum<11;


3、鎖住的對象:
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;

select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id=o.object_id and l.session_id=s.sid;

 

4、session與對應process的情況:
SELECT s.SID, p.pid, p.spid signaled, s.osuser, s.program FROM v$process p, v$session s WHERE p.addr = s.paddr;


5、根據pid查看session當前SQL語句:
select  /*+ordered*/ sql_text from v$sqltext a
where (a.hash_value,a.address)in(select decode (sql_hash_value,0,prev_hash_value,sql_hash_value),decode

(sql_hash_value,0,prev_sql_addr,sql_address)
from v$session b where b.paddr=(select addr from v$process c where c.spid='&pid'))
order by piece;


6、根據sid查看session當前SQL語句:
select  /*+ordered*/ sql_text from v$sqltext a
where (a.hash_value,a.address)in(select decode (sql_hash_value,0,prev_hash_value,sql_hash_value),decode

(sql_hash_value,0,prev_sql_addr,sql_address)
from v$session b where b.sid='&sid') order by piece;


7、查看表空間利用率:
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",
round((f.free/a.total)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,(select tablespace_name,

round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u,(select tablespace_name, round(sum(bytes/

(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name and a.tablespace_name = u.tablespace_name
order by 5;


8、清理表的碎片:
alter table shrink space [ | compact | cascade ];

alter table shrink space compcat;   www.2cto.com 

收縮表,相當于把塊中數据打結實了,但會保持 high water mark;

alter table shrink space;

收縮表,降低 high water mark;

alter table shrink space cascade;

收縮表,降低 high water mark,并且相關索引也要收縮一下下。

alter index idxname shrink space;

--重組數據
alter table table_name enable row movement;

--降低高水位
alter table table_name shrink space;

 

9、查看SQL語句的執行計劃:
SELECT id,
           parent_id,
              LPAD (' ', 4 * (LEVEL - 1))
           || operation
           || ' '
           || options
           || ' '
           || object_name
              "Execution plan",
           cost,
           CARDINALITY,
           bytes
      FROM (SELECT p.*
              FROM v$sql_plan p, v$sql s
             WHERE     p.address = s.ADDRESS
                   AND p.hash_value = s.HASH_VALUE
                   AND p.hash_value = '&hash_value')
CONNECT BY PRIOR id = parent_id
START WITH id = 0;   


10、查看哪些表空間中包含XMLTypes(用于遷移表空間):
select distinct p.tablespace_name
from dba_tablespaces p,dba_xml_tables x,dba_users u,all_all_tables t
where t.table_name=x.table_name and t.tablespace_name=x.table_name and x.owner=u.username;


11、用DBMS_TTS.TRANSPORT_SET_CHECK過程查看表空間自包含:
exec dbms_tts.transport_set_check('D_ATTENDANCE_DATA',true,true);
select * from transport_set_violations;


12、查看數據庫的隱含參數:
set linesize 300
col name for a30
col value for a20
col describ for a60
select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
from sys.x$ksppi x,sys.x$ksppcv y
where x.inst_id=userenv('instance')
and y.inst_id=userenv('instance')
and x.indx=y.indx
and x.ksppinm like '%_&par%';


13、查看數據庫前20條基於parse排序的SQL語句:
select *
from
  (select sql_text,
     parse_calls,executions,hash_value,address
     from v$sqlarea
     where parse_calls>0
     order by parse_calls desc
  )
where rownum<21;


14、查看死鎖:
select sid,username,lockwait,status,machine,program
from v$session where sid in (select session_id from v$locked_object);


15、查看磁盤排序會話及SQL語句:
SELECT a.sid,a.value, b.name, d.sql_text
from V$SESSTAT a, V$STATNAME b, V$SESSION c, V$SQLAREA d
WHERE a.statistic#=b.statistic# AND b.name = 'sorts (disk)'
and a.sid = c.sid
and c.SQL_ADDRESS = d.ADDRESS(+)
and c.SQL_HASH_VALUE = d.HASH_VALUE(+)
and value > 0 ORDER BY 2 desc,1;


16、查看數據庫中Full Table Scan的SQL語句:
select distinct sql_text from v$sqlarea t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL';


17、查看數據段Physical Reads的統計信息:
select * from v$segment_statistics
where statistic_name='physical reads'
order by value desc;


18、查看數據段和對應索引的信息(blocks、clustering_factor):
select s.owner,s.segment_name,s.segment_type,i.index_name,
s.bytes,s.blocks,i.clustering_factor
from dba_segments s,dba_indexes i
where s.segment_name=i.table_name
and s.owner='PRD_ELMS_8610_DEV'
order by i.clustering_factor desc;


19、查看熱點塊信息:
SELECT e.owner, e.segment_name, e.segment_type,b.tch
  FROM dba_extents e,
    (SELECT *
       FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
         FROM x$bh
         ORDER BY tch DESC)
       WHERE ROWNUM < 11) b
  WHERE e.relative_fno = b.dbarfil
  AND e.block_id <= b.dbablk
  AND e.block_id + e.blocks > b.dbablk;


20、查看熱點塊對應的SQL語句:
SELECT /*+ rule */ hash_value,sql_text
  FROM v$sqltext
  WHERE (hash_value, address) IN (
    SELECT a.hash_value, a.address
      FROM v$sqltext a,
        (SELECT DISTINCT a.owner, a.segment_name, a.segment_type
           FROM dba_extents a,
             (SELECT dbarfil, dbablk
                FROM (SELECT dbarfil, dbablk
                        FROM x$bh
                        ORDER BY tch DESC)
                WHERE ROWNUM < 11) b
           WHERE a.relative_fno = b.dbarfil
           AND a.block_id <= b.dbablk
           AND a.block_id + a.blocks > b.dbablk) b
      WHERE a.sql_text LIKE '%' || b.segment_name || '%'
      AND b.segment_type = 'TABLE')
      ORDER BY hash_value, address, piece;
21、檢查備份運行時基於每個文件的文件的緩衝區大小:
select set_count,device_type,type,filename,buffer_size,buffer_count,open_time,close_time from v$backup_io order by

set_count,type,open_time_close_time;

22、查看备份或者还原进度的百分比
select sid,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where opname like 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
and totalwork!=0
and sofar<>totalwork;

23、监控备份运行时的给每个文件的缓冲区大小
select set_count,device_type,type,filename,buffer_size,buffer_count,open_time,close_time
from v$backup_async_io
order by set_count,type,open_time,close_time;

24、查看pga的优化情况:
select name,value,100*(value/decode((select sum(value)from v$sysstat where name like 'workarea executions%'),0,null,(select sum

(value)from v$sysstat where name like 'workarea executions%'))) pct from v$sysstat where name like 'workarea executions%';

25、查看pga的优化可能性:
SELECT PGA_TARGET_FOR_ESTIMATE BTFE,PGA_TARGET_FACTOR PTF,ADVICE_STATUS AS,ESTD_EXTRA_BYTES_RW

EXBR,ESTD_PGA_CACHE_HIT_PERCENTAGEEPCHP,ESTD_OVERALLOC_COUNT EOC FROM V$PGA_TARGET_ADVICE;

26、根据top查询中高cpu占用率的进程号获取占用cpu过高的sql语句:
select /*+ORDERED*/ sql_text from v$sqltext a where (a.hash_value,a.address) in (select DECODE

(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr,sql_address) FROM v$session b WHERE

b.paddr=(select addr from v$process c where c.spid='&pid')) order by piece ASC;

27、查看自动SGA管理时的各个SGA组建的值:
select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ from x$ksppi x,x$ksppcv y where
x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance') and x.indx=y.indx and
x.ksppinm like '%pool_size%';

28、获取当前数据库最繁忙(热点)的buffer:
select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum < 11;

29、结合28中的查询,查询热点buffer都来自那些对象:
select e.owner,e.segment_name,e.segment_type from dba_extents e,(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$BH

order by tch DESC) where rownum < 11) b where e.relative_fno=b.dbafil and e.block_id <= b.dbablk ande.block_id + e.blocks>b.dbablk;

30、查看追踪文件路径:
select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from (select p.spid from v$mystat

m,v$session s,v$process p where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,(select t.instance instance from v$thread

t,v$parameter v where v.name='thread' and (v.value=0 or t.thread#=to_number(v.value))) i,(select value,name from v$parameter where

name='user_dump_dest') d;

31、查看回滚段的使用情况,哪个用户正在使用回滚段的资源;

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27201538/viewspace-765212/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-06-18

  • 博文量
    10
  • 访问量
    37917