ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 常用的一些监控脚本

常用的一些监控脚本

原创 Linux操作系统 作者:font2008 时间:2008-12-26 10:22:30 0 删除 编辑

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
  2  FROM v$sysstat cur, v$sysstat con, v$sysstat phy
  3  WHERE cur.name = 'db block gets'
  4  AND con.name = 'consistent gets'
  5  AND phy.name = 'physical reads'
  6  /

高成本(10g-R2)sql:
select OPTIMIZER_COST,EXECUTIONS,sql_text from v$sqlarea
where OPTIMIZER_COST>
(select max(OPTIMIZER_COST)/5 from v$sqlarea)
order by 1;

高IO SQL:
select round(DISK_READS/EXECUTIONS) ,DISK_READS,EXECUTIONS,sql_text
from v$sqlarea
where round(DISK_READS/EXECUTIONS)>
(select max(round((DISK_READS/EXECUTIONS)/5)) from v$sqlarea
where EXECUTIONS>0)
and EXECUTIONS>0 and DISK_READS>100
order by 1;

参看频繁调度最多的5条sql语句(内存里)
select sql_text,executions from
(select sql_text,executions,rank() over (order by executions desc) exec_rank from v$sql) where exec_rank <=5;
查看表空间使用情况
set col colsep '    '
select dbf.tablespace_name,
   dbf.totalspace as "总量(M)",
   dbf.totalblocks as 总块数,
   dfs.freespace as "剩余总量(M)",
   dfs.freeblocks as "剩余块数",
   round((dfs.freespace / dbf.totalspace) * 100,2)||'%' as "空闲比例%",
   round((1-(dfs.freespace / dbf.totalspace)) * 100,2)||'%' as "已使用空间比例%"
   from (select t.tablespace_name,
   round(sum(t.bytes) / 1024 / 1024,2) totalspace,
   sum(t.blocks) totalblocks
   from dba_data_files t
   group by t.tablespace_name) dbf,
   (select tt.tablespace_name,
   round(sum(tt.bytes) / 1024 / 1024,2) freespace,
   sum(tt.blocks) freeblocks
   from dba_free_space tt
   group by tt.tablespace_name) dfs
   where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
   ;

set colsep '   ';
select dbf.tablespace_name,
   dbf.totalspace as "总量(M)",
   dbf.totalblocks as 总块数,
   dfs.freespace as "剩余总量(M)",
   dfs.freeblocks as "剩余块数",
   concat(round((dfs.freespace / dbf.totalspace) * 100,2),'%') as "空闲比例%"
   from (select t.tablespace_name,
   round(sum(t.bytes) / 1024 / 1024,2) totalspace,
   sum(t.blocks) totalblocks
   from dba_data_files t
   group by t.tablespace_name) dbf,
   (select tt.tablespace_name,
   round(sum(tt.bytes) / 1024 / 1024,2) freespace,
   sum(tt.blocks) freeblocks
   from dba_free_space tt
   group by tt.tablespace_name) dfs
   where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
   ;

concat(字符串1,字符串2,....,字符串n):此函数是字符串连接函数,用户连接字符串,其作用与连接符(||)完全相同。

迅速发现oracle server的性能问题的成因,我们可以求助于v$session_wait这个视图,看系统的这些session在等什么,使用了多少的io。以下是参考脚本:
脚本说明:查看占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>6
and st.wait_time=0
and st.event not like '%sql%'
order by physical_reads desc;

 

确定最耗CPU用户、语句


SET  line 800
SET pages 100
col sid format 9990
col SERIAL# format 999990
col top_io_machine format a8
col top_io_program format a10
col top_io_sql format a50
SET colsep |
SELECT /*+ rule */
 aa.sid      AS "sid",
 aa.serial#  AS "SERIAL#",
 aa.VALUE    AS "CPU",
 bb.VALUE    AS "MEN-PGA",
 cc.VALUE    AS "I/O-Phy-read",
 cc.machine  AS top_io_machine,
 cc.program  AS top_io_program,
 cc.sql_text AS top_io_sql
  FROM (SELECT d.sql_text,
               c.machine,
               c.program,
               c.sid,
               c.serial#,
               a.NAME,
               b.VALUE
          FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
         WHERE NAME = 'CPU used by this session'
           AND a.statistic# = b.statistic#
           AND c.sid = b.sid
           AND c.status = 'ACTIVE'
           AND d.address = c.sql_address) aa,
       (SELECT d.sql_text,
               c.machine,
               c.program,
               c.sid,
               c.serial#,
               a.NAME,
               b.VALUE
          FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
         WHERE NAME = 'session pga memory'
           AND a.statistic# = b.statistic#
           AND c.sid = b.sid
           AND c.status = 'ACTIVE'
           AND d.address = c.sql_address) bb,
       (SELECT d.sql_text,
               c.machine,
               c.program,
               c.sid,
               c.serial#,
               a.NAME,
               b.VALUE
          FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
         WHERE NAME = 'physical reads'
           AND a.statistic# = b.statistic#
           AND c.sid = b.sid
           AND c.status = 'ACTIVE'
           AND d.address = c.sql_address) cc
 WHERE aa.sid = bb.sid
   AND aa.sid = cc.sid
   AND aa.serial# = bb.serial#
   AND aa.serial# = cc.serial#
 ORDER BY 5 ASC, 4 ASC, 3 ASC;

下面给出一个综合的sql语句,它可以查询出浪费空间的表(浪费超过25%),而且还计算出其它信息(使用时根据具体情况修改where子句中的blocks,owner限制条件):

SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,
ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS,
BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,
CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,
DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE,
NEXT_EXTENT, MAX_FREE_SPACE,
O_TABLESPACE_NAME TABLESPACE_NAME
FROM (SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)
) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,
SYS.DBA_TABLES B,
SYS.TS$ C
WHERE A.OWNER =B.OWNER and
SEGMENT_NAME = TABLE_NAME and
SEGMENT_TYPE = 'TABLE' AND
B.TABLESPACE_NAME = C.NAME
UNION ALL
SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1,
ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)
) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,
SYS.DBA_TAB_PARTITIONS B,
SYS.TS$ C,
SYS.DBA_TABLES D
WHERE A.OWNER = B.TABLE_OWNER and
SEGMENT_NAME = B.TABLE_NAME and
SEGMENT_TYPE = 'TABLE PARTITION' AND
B.TABLESPACE_NAME = C.NAME AND
D.OWNER = B.TABLE_OWNER AND
D.TABLE_NAME = B.TABLE_NAME AND
A.PARTITION_NAME = B.PARTITION_NAME),
(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES)
MAX_FREE_SPACE
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME)
WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25
AND WNER = '??' AND BLOCKS > 128
ORDER BY 10 DESC, 1 ASC, 2 ASC;

查看所有表空间的碎片程度(值在30以下表示碎片很多)

select tablespace_name,sum(bytes),sum(free),round(sum(free)*100/sum(bytes),2) from (select
  b.file_id file_ID,
  b.tablespace_name tablespace_name,
  b.bytes Bytes,
  (b.bytes-sum(nvl(a.bytes,0))) used,
  sum(nvl(a.bytes,0)) free,
  sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
  from dba_free_space a,dba_data_files b
  where a.file_id=b.file_id
  group by b.tablespace_name,b.file_id,b.bytes
  order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);


对showsql.sql脚本的改进
很久没有更新博客了,主要是最近被博客上的垃圾评论整得心烦,眼不见心不烦,今天对showsql.sql脚本做了点更新,相信大家用起来更顺手,所以就发出来给大家做参考,本次改进减少了一个sql的调用,输出的结果就更准确了...

$ORACLE_HOME/bin/sqlplus -S /nolog <connect / as sysdba;

set serveroutput on size 1000000
set lines 200
set pages 1000
set feedback off
column username format a20
column sql_text format a98

declare
type tab_varchar2 is table of varchar2(128);
v_list tab_varchar2 := tab_varchar2();

procedure p (p_str in varchar2)
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line(substr(l_str, 1, 250));
l_str := substr(l_str, 251);
end loop;
end;
begin
for x in (select a.username||'('||a.sid||','||a.serial#||') spid='||b.spid||
' hash_value='||to_char(a.sql_hash_value)||' execs='||to_char(s.executions)||
' els_time='||to_char(trunc(elapsed_time/1000000/decode(executions,0,null,executions),2)) username,
' program='||a.program program,a.sid,a.serial#,
' disk_reads='||to_char(trunc(disk_reads/decode(executions,0,null,executions),2)) disk_reads,
' buffer_gets='||to_char(trunc(buffer_gets/decode(executions,0,null,executions),2)) buffer_gets,sql_address,sql_hash_value
from v$session a,v$process b,v$sqlarea s
where a.status = 'ACTIVE' and s.hash_value=a.sql_hash_value
and a.paddr = b.addr and rawtohex(sql_address) <> '00' and a.username is not null
and sid <> (select sid from v$mystat where rownum = 1) order by last_call_et)
loop
dbms_output.put_line( '--------------------------------------------------------------------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.program || ' ' ||x.disk_reads || ' '|| x.buffer_gets);
v_list.extend;
v_list(v_list.count) := 'alter system kill session '''||to_char(x.sid)||','||to_char(x.serial#)||''';';
for y in ( select sql_text
from v$sqltext_with_newlines
where address = x.sql_address
order by piece )
loop
p(y.sql_text);
end loop;

--output sql execution plan
dbms_output.put_line( '--------------------------------------------------------------------------------' );
for i in (select rpad('|'||substr(lpad(' ',1 * (depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----',
substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null,
object_name)||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',decode(sign(cardinality-1000),
-1, cardinality||' ',decode(sign(cardinality-1000000), -1,
trunc(cardinality/1000)||'K',decode(sign(cardinality-1000000000), -1,
trunc(cardinality/1000000)||'M',trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',decode(sign(bytes-1073741824),
-1, trunc(bytes/1048576)||'M',trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as Explain_plan
from v$sql_plan
where hash_value = x.sql_hash_value
and child_number = (select max(child_number) from v$sql_plan where hash_value = x.sql_hash_value))
loop
dbms_output.put_line(i.explain_plan);
end loop;
end loop;

--output kill session script
dbms_output.put_line( '----------------------------alter system kill session---------------------------' );
dbms_output.put_line( '--------------------------------------------------------------------------------' );
for i in 1..v_list.count loop
dbms_output.put_line(v_list(i));
end loop;
end;
/
exit
EOF

显示碎片show_space

Declare
 l_total_blocks number;
 l_total_bytes  number;
 l_unused_blocks number;
 l_unused_bytes number;
 l_lastusedextfileid number;
 l_lastusedextblockid number;
 l_last_used_block number;
begin
  dbms_space.unused_space(segment_owner =>'&segment_owner',
                          segment_name =>'&segment_name',
                          segment_type =>'TABLE',
                          total_blocks =>l_total_blocks,
                          total_bytes =>l_total_bytes,
                          unused_blocks =>l_unused_blocks,
                          unused_bytes =>l_unused_bytes,
                          last_used_extent_file_id =>l_lastusedextfileid,
                          last_used_extent_block_id =>l_lastusedextblockid,
                          last_used_block =>l_last_used_block);
   dbms_output.put_line(rpad('Total Blocks',40,'.')||l_total_blocks);
   dbms_output.put_line(rpad('Total Bytes',40,'.')||l_total_bytes);
   dbms_output.put_line(rpad('Unused Blocks',40,'.')||l_unused_blocks);
   dbms_output.put_line(rpad('Unused Bytes',40,'.')||l_unused_bytes);
   dbms_output.put_line(rpad('Last Used Ext FileId',40,'.')||l_lastusedextfileid);
   dbms_output.put_line(rpad('Last Used Ext BlockId',40,'.')||l_lastusedextblockid);
   dbms_output.put_line(rpad('Last Used Block',40,'.')||l_last_used_block);
end;
/


023课程中的一个实验,我的owner, segment_name, segment_type 都是大写,仍然出现下列错误:

 DECLARE
 v_owner VARCHAR2(30) := 'TT';
 v_segment_name VARCHAR2(30) := 'TEST1';
 v_segment_type VARCHAR2(30) := 'TABLE';
 v_total_blocks NUMBER;
 v_total_bytes NUMBER;
 v_unused_blocks NUMBER;
 v_unused_bytes NUMBER;
 v_last_used_extent_file_id NUMBER;
 v_last_used_extent_block_id NUMBER;
 v_last_used_block NUMBER;
 BEGIN
 dbms_space.unused_space(v_owner,
 v_segment_name,
 v_segment_type,
 v_total_blocks,
 v_total_bytes,
 v_unused_blocks,
 v_unused_bytes,
 v_last_used_extent_file_id,
 v_last_used_extent_block_id,
 v_last_used_block
 );
 dbms_output.put_line(INITCAP(v_segment_type)||':'||v_owner||'.'||v_segment_name);
 dbms_output.put_line('Total Blocks :'||TO_CHAR(v_total_blocks));
 dbms_output.put_line('Blocks above HWM :'||TO_CHAR(v_unused_blocks));
 END;
 /


-- logical_reads, physical_reads, physical_writes by segment (tablespace, table, object_type).
select n.owner , n.tablespace_name,
       case when n.object_type = 'TABLE' then n.object_name
            when n.object_type = 'INDEX' then (SELECT table_name from dba_indexes WHERE index_name = n.object_name AND wner = n.owner)
            when n.object_type = 'LOB' then (SELECT table_name from dba_lobs WHERE segment_name = n.object_name AND wner = n.owner)
                                       else 'N/A' end as Table_name,
       n.object_name, n.object_type, r.logical_reads,
       round(r.logical_reads_ratio * 100, 2) logical_reads_ratio,
       r.physical_reads,
       round(r.physical_reads_ratio * 100, 2) physical_reads_ratio,
       r.physical_writes,
       round(r.physical_writes_ratio * 100, 2) physical_writes_ratio
from dba_hist_seg_stat_obj n,
     (select *
      from (select e.dataobj#, e.obj#, e.dbid,
                   sum(e.logical_reads_delta) logical_reads,
                   ratio_to_report(sum(e.logical_reads_delta)) over () logical_reads_ratio,
                   sum(e.physical_reads_delta) physical_reads,
                   ratio_to_report(sum(e.physical_reads_delta)) over () physical_reads_ratio,
                   sum(e.physical_writes_delta) physical_writes,
                   ratio_to_report(sum(e.physical_writes_delta)) over () physical_writes_ratio
            from dba_hist_seg_stat e
            where e.snap_id > 209 and
                  e.snap_id <= 210
            group by e.dataobj#,
       e.obj#,
       e.dbid
     having sum(e.logical_reads_delta)  > 0 or
                   sum(e.physical_reads_delta)  > 0 or
                   sum(e.physical_writes_delta) > 0
            order by logical_reads desc) d
   ) r
where n.dataobj# = r.dataobj# and
      n.obj# = r.obj# and
      n.dbid     = r.dbid and
      n.owner = 'TT'
order by logical_reads desc;

 

select n.owner , n.tablespace_name,
       case when n.object_type = 'TABLE' then n.object_name
            when n.object_type = 'INDEX' then (SELECT table_name from dba_indexes WHERE index_name = n.object_name AND wner = n.owner)
            when n.object_type = 'LOB' then (SELECT table_name from dba_lobs WHERE segment_name = n.object_name AND wner = n.owner)
                                       else 'N/A' end as Table_name,
       n.object_name, n.object_type, r.logical_reads,
       round(r.logical_reads_ratio * 100, 2) logical_reads_ratio,
       r.physical_reads,
       round(r.physical_reads_ratio * 100, 2) physical_reads_ratio,
       r.physical_writes,
       round(r.physical_writes_ratio * 100, 2) physical_writes_ratio
from dba_hist_seg_stat_obj n,
     (select *
      from (select e.dataobj#, e.obj#, e.dbid,
                   sum(e.logical_reads_delta) logical_reads,
                   ratio_to_report(sum(e.logical_reads_delta)) over () logical_reads_ratio,
                   sum(e.physical_reads_delta) physical_reads,
                   ratio_to_report(sum(e.physical_reads_delta)) over () physical_reads_ratio,
                   sum(e.physical_writes_delta) physical_writes,
                   ratio_to_report(sum(e.physical_writes_delta)) over () physical_writes_ratio
            from dba_hist_seg_stat e
            where e.snap_id > 209 and
                  e.snap_id <= 210
            group by e.dataobj#,
       e.obj#,
       e.dbid
     having sum(e.logical_reads_delta)  > 0 or
                   sum(e.physical_reads_delta)  > 0 or
                   sum(e.physical_writes_delta) > 0
            order by logical_reads desc) d
   ) r
where n.dataobj# = r.dataobj# and
      n.obj# = r.obj# and
      n.dbid     = r.dbid and
      n.owner = 'MKTG'
order by logical_reads desc

创建一个表语句
create table test13
 (id int,fname varchar2(18))
 storage(initial 10k next 20k
pctincrease 0
minextents 1 maxextents 3)
pctfree 10 pctused 40 tablespace test;

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

下一篇: oracle 表空间学习
请登录后发表评论 登录
全部评论

注册时间:2008-07-07

  • 博文量
    6
  • 访问量
    13147