数据库监控脚本

一、数据库构架体系

1、表空间的监控

2、监控表空间使用率与剩余空间大小的语句

3、表空间是否具有自动扩展空间的能力

4、使用字典管理的表空间哪些表的扩展将引起表空间的扩展

5、段的占用空间与区间数

6、重建索引

7、监控表是否有主键

二、性能监控

1、数据缓冲区的命中率

2、库缓冲说明了SQL语句的重载率，越低越好

3、用户锁

4、锁与等待，查询谁锁了表，而谁在等待

5、发生了事务或锁，查找使用的回滚段

6、哪个用户正在利用临时段吗？

7、在ORACLE 9i中，可以监控索引的使用，开始索引监控与停止索引监控的脚本

8、通过sid找到os进程号（Check OS process id from Oracle sid ）

9、通过os进程找sid（Check Oracle sid from OS process id ）

10、通过sid找sql语句（Check current SQL in a session ）

11、找等待事件Checking v$session_wait

12、数据缓冲区GETMISS相对gets的比例Dictionary Cache Hits MISS RATIO

13、通过文件号及块号找对应数据库对象Check DB object name from file id and block#

14、寻找hot block

15、找出每个文件上的等待事件

16、找出引起等待事件的SQL语句.

17、监控共享池中哪个对象引起了大的内存分配



1、表空间的监控

SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,

MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,

CONTENTS,LOGGING,

EXTENT_MANAGEMENT, -- Columns not available in v8.0.x

ALLOCATION_TYPE, -- Remove these columns if running

PLUGGED_IN, -- against a v8.0.x database

SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later

FROM DBA_TABLESPACES

ORDER BY TABLESPACE_NAME;



2、监控表空间使用率与剩余空间大小的语句

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",

BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",

ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",

FREE_SPACE "FREE_SPACE(M)"

FROM

(SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES)/(1024*1024),2) SPACE,

SUM(BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE

FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,

USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",

NVL(FREE_SPACE,0) "FREE_SPACE(M)"

FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,

SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);



3、表空间是否具有自动扩展空间的能力

SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS

FROM DBA_TABLESPACES T,DBA_DATA_FILES D

WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME

ORDER BY TABLESPACE_NAME,FILE_NAME;



4、使用字典管理的表空间哪些表的扩展将引起表空间的扩展。

SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME

FROM ALL_TABLES A,

(SELECT TABLESPACE_NAME, MAX(BYTES) BIG_CHUNK

FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F

WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME

AND A.NEXT_EXTENT > F.BIG_CHUNK;



5、段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），

如果段没有可以再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工作

SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,

ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",

EXTENTS USED_EXTENTS,S.MAX_EXTENTS,

S.BLOCKS ALLOCATED_BLOCKS,

S.BLOCKS USED_BOLCKS,

S.PCT_INCREASE,

S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)"

FROM DBA_SEGMENTS S

WHERE S.OWNER NOT IN ('SYS','SYSTEM')

ORDER BY Used_Extents DESC;



6、重建索引

数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片，所以，在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎片，提高查询速度。

SQL> set heading off

SQL> set feedback off

SQL> spool d:index.sql

SQL>

SELECT 'alter index ' || index_name || ' rebuild '

||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'

FROM all_indexes

WHERE ( tablespace_name != 'INDEXES' OR next_extent != ( 256 * 1024 ) )

AND owner = USER

SQL>spool off



这个时候，我们打开spool出来的文件，就可以直接运行了。



7、监控表是否有主键

SELECT table_name

FROM all_tables

WHERE owner = USER

MINUS

SELECT table_name

FROM all_constraints

WHERE owner = USER

AND constraint_type = 'P';



二、性能监控



1、数据缓冲区的命中率

SELECT a.VALUE + b.VALUE logical_reads,

c.VALUE phys_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、库缓冲说明了SQL语句的重载率，越低越好

SELECT SUM(pins) total_pins,SUM(reloads) total_reloads,

SUM(reloads)/SUM(pins)*100 libcache_reload_ratio

FROM v$librarycache;



3、用户锁

任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。

可以通过alter system kill session ‘sid,serial#’来杀掉会话

SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',

NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type,

s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser

FROM v$session s,v$lock l,dba_objects o

WHERE l.sid = s.sid

AND l.id1 = o.object_id(+)

AND s.username is NOT NULL



4、锁与等待，查询谁锁了表，而谁在等待

SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,

o.owner,o.object_name,o.object_type,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

ORDER BY o.object_id,xidusn DESC;

以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN



5、发生了事务或锁，查找使用的回滚段

其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。

同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。

SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum",

t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used",

t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollName

FROM v$session s,v$transaction t,v$rollname r

WHERE s.SADDR=t.SES_ADDR

AND t.XIDUSN=r.usn;



6、哪个用户正在利用临时段吗？

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,

a.username, a.osuser, a.status,c.sql_text

FROM v$session a,v$sort_usage b, v$sql c

WHERE a.saddr = b.session_addr

AND a.sql_address = c.address(+)

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;



7、在ORACLE 9i中，可以监控索引的使用，开始索引监控与停止索引监控的脚本

set heading off

set echo off

set feedback off

set pages 10000

spool start_index_monitor.sql

SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'

FROM dba_indexes

WHERE owner = USER;

spool off

set heading on

set echo on

set feedback on ------------------------------------------------

set heading off

set echo off

set feedback off

set pages 10000

spool stop_index_monitor.sql

SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'

FROM dba_indexes WHERE owner = USER;

spool off

set heading on

set echo on

set feedback on



如果需要监控更多的用户，可以将owner=User改写成别的

监控结果在视图v$object_usage中查询



8、Check OS process id from Oracle sid

select spid from v$process

where addr in

( select paddr from v$session where sid=&sid) ;



9、Check Oracle sid from OS process id

select sid from v$session

where paddr in ( select addr from v$process where spid=&pid) ;



10、Check current SQL in a session

select SQL_TEXT

from V$SQLTEXT

where HASH_VALUE

= ( select SQL_HASH_VALUE from v$session

where sid = &sid)

order by PIECE;



11、Checking v$session_wait

select * from v$session_wait

where event not like 'rdbms%'

and event not like 'SQL*N%'

and event not like '%timer';



12、Dictionary Cache Hits

SELECT sum(getmisses)/sum(gets) FROM v$rowcache; /*It should be < 15%, otherwise Add share_pool_size*/



13、Check DB object name from file id and block#

select owner,segment_name,segment_type

from dba_extents

where file_id = &fno

and &dno between block_id

and block_id + blocks – 1 ;



14、寻找hot block

select /*+ ordered */

e.owner ||'.'|| e.segment_name segment_name,

e.extent_id extent#,

x.dbablk - e.block_id + 1 block#,

x.tch,

l.child#

from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e

where l.name = 'cache buffers chains'

and l.sleeps > &sleep_count

and x.hladdr = l.addr

and e.file_id = x.file#

and x.dbablk between e.block_id

and e.block_id + e.blocks - 1;



15、找出每个文件上的等待事件

select df.name,kf.count from v$datafile df,x$kcbfwait kf where (kf.indx+1)=df.file#;



16、找出引起等待事件的SQL语句.

select sql_text from v$sqlarea a,v$session b,v$session_wait c where a.address=b.sql_address and b.sid=c.sid and c.event=&ll;



17、监控共享池中哪个对象引起了大的内存分配

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;





第二章数据库监控脚本（二）
2007年12月29日 星期六 11:48--1、查找trace文件

--2、session下的重做数量

--3、估算自数据库启动以来每天的平均日志量

--4、估算日志数量

--5、查找隐含参数

--6、创建session的重做日志视图

--7、一致性读取的段及数据块信息

--8、等待事件分类及数量

--9、根据sid找到相应的sql语句

--10、系统自启动以来的累计等待时间前十名

--11、查找全表扫描(full scan)及快速全索引扫描(fast full index)

--12、通过具体的等待事件查找到有问题的sql语句(输入参数等待事件如：free buffer waits)

--13、查找数据库最繁忙的buffer

--14、查找热点buffer来自哪些对象

--15、关于latch信息

--16、具体热点块的latch及buffer信息及找到相应对象的sql语句

--17、创建临时表保存X$KSMSP的状态

--18、找出library cache pin等待的原因

--19、获得参数的描述信息

--20、oracle收集的buffer cache及shared pool 的建议信息

--21、是10g中，决定各参数组件大小的查询

--22、10g各动态组件调整时间及调整类型

--23、sql在工作区中工作方式所占比例

--24、pga动态性能视图信息

--25、获得存在问题的sql，根据pid

--26、fast_start_mttr_target

--27、实例恢复的时间计算

--28、show_space过程及使用

--29、分析表

--30、unix环境快速shutdown数据库之前先删除各个进程



--1、查找trace文件

SELECT d.VALUE

|| '/'

|| LOWER (RTRIM (i.INSTANCE, CHR (0)))

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

FROM (SELECT p.spid

FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p

WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

(SELECT t.INSTANCE

FROM SYS.v$thread t, SYS.v$parameter v

WHERE v.NAME = 'thread'

AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

(SELECT VALUE

FROM SYS.v$parameter

WHERE NAME = 'user_dump_dest') d

/



--2、session下的重做数量

col name for a30

select a.name,b.value

from v$statname a,v$mystat b

where a.STATISTIC# = b.STATISTIC# and a.name = 'redo size';



--3、估算自数据库启动以来每天的平均日志量

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';



select startup_time from v$instance;



select (select value/1024/1024/1024 from v$sysstat where name='redo size')/

(select round(sysdate - ( select startup_time from v$instance)) from dual) REDO_GB_PER_DAY

from dual;



--4、估算日志数量

--一段时间的

SELECT NAME, completion_time, blocks * block_size / 1024 / 1024 mb

FROM v$archived_log

WHERE ROWNUM < 11

AND completion_time BETWEEN TRUNC (SYSDATE) - 2 AND TRUNC (SYSDATE) - 1

/

--每日全天的

SELECT TRUNC (completion_time), SUM (mb) / 1024 day_gb

FROM (SELECT NAME, completion_time, blocks * block_size / 1024 / 1024 mb

FROM v$archived_log

WHERE completion_time BETWEEN TRUNC (SYSDATE) - 2 AND TRUNC (SYSDATE) - 1)

GROUP BY TRUNC (completion_time)

/

--最近日期的日志生成统计

SELECT TRUNC (completion_time), SUM (mb) / 1024 day_gb

FROM (SELECT NAME, completion_time, blocks * block_size / 1024 / 1024 mb

FROM v$archived_log)

GROUP BY TRUNC (completion_time)

order by 1

/



--5、查找隐含参数

set linesize 132

column name format a30

column value format a25

select

x.ksppinm name,

y.ksppstvl value,

y.ksppstdf isdefault,

decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,

decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj

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

order by

translate(x.ksppinm, ' _', ' ')

/



--6、创建session的重做日志视图

CREATE OR REPLACE VIEW redo_size

AS

SELECT VALUE

FROM v$mystat, v$statname

WHERE v$mystat.statistic# = v$statname.statistic#

AND v$statname.NAME = 'redo size'

/



--7、一致性读取的段及数据块信息

select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state

from x$bh a,dba_extents b

where b.RELATIVE_FNO = a.dbarfil

and b.BLOCK_ID <= a.dbablk and b.block_id + b.blocks > a.dbablk

and b.owner='SCOTT' and b.segment_name='EMP'

/



--8、等待事件分类及数量

SELECT wait_class#, wait_class_id, wait_class, COUNT (*) AS "count"

FROM v$event_name

GROUP BY wait_class#, wait_class_id, wait_class

ORDER BY wait_class#

/



--9、根据sid找到相应的sql语句

SELECT sql_text

FROM v$sqltext a

WHERE a.hash_value = (SELECT sql_hash_value

FROM v$session b

WHERE b.SID = '&sid')

ORDER BY piece ASC

/





--10、系统自启动以来的累计等待时间前十名

SELECT *

FROM (SELECT event, time_waited

FROM v$system_event

ORDER BY time_waited DESC)

WHERE ROWNUM < 10;



--11、查找全表扫描(full scan)及快速全索引扫描(fast full index)

SELECT sql_text

FROM v$sqltext t, v$sql_plan p

WHERE t.hash_value = p.hash_value

AND p.operation = 'TABLE ACCESS'

AND p.options = 'FULL'

ORDER BY p.hash_value, t.piece;



SELECT sql_text

FROM v$sqltext t, v$sql_plan p

WHERE t.hash_value = p.hash_value

AND p.operation = 'INDEX'

AND p.options = 'FULL SCAN'

ORDER BY p.hash_value, t.piece;



--12、通过具体的等待事件查找到有问题的sql语句(输入参数等待事件如：free buffer waits)

SET linesize 120

COL operation format a55

COL cost format 99999

COL kbytes format 999999

COL object format a25

SELECT hash_value, child_number,

LPAD (' ', 2 * DEPTH)

|| operation

|| ' '

|| options

|| DECODE (ID,

0, SUBSTR (optimizer, 1, 6) || ' Cost=' || TO_CHAR (COST)

) operation,

object_name OBJECT, COST, ROUND (BYTES / 1024) kbytes

FROM v$sql_plan

WHERE hash_value IN (

SELECT a.sql_hash_value

FROM v$session a, v$session_wait b

WHERE a.SID = b.SID

AND b.event = '&waitevent')

ORDER BY hash_value, child_number, ID;



--13、查找数据库最繁忙的buffer

SELECT *

FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch

FROM x$bh

ORDER BY tch DESC)

WHERE ROWNUM < 11;



--14、查找热点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.dbarfil

AND e.block_id <= b.dbablk

AND e.block_id + e.blocks > b.dbablk;



--15、关于latch信息

--主要latch free信息

select * from

(select * from v$latch order by misses desc)

where rownum<11;

--获得session的等待信息

select sid,seq#,event from v$session_wait

--获得具体的子latch信息

SELECT *

FROM (SELECT addr, child#, gets, misses, sleeps, immediate_gets igets,

immediate_misses imiss, spin_gets sgets

FROM v$latch_children

WHERE NAME = 'cache buffers chains'

ORDER BY sleeps DESC)

WHERE ROWNUM < 11;



--16、具体热点块的latch及buffer信息及找到相应对象的sql语句

SELECT b.addr, a.ts#, a.dbarfil, a.dbablk, a.tch, b.gets, b.misses, b.sleeps

FROM (SELECT *

FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch, hladdr

FROM x$bh

ORDER BY tch DESC)

WHERE ROWNUM < 11) a,

(SELECT addr, gets, misses, sleeps

FROM v$latch_children

WHERE NAME = 'cache buffers chains') b

WHERE a.hladdr = b.addr

/



SELECT distinct 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.dbarfil

AND e.block_id <= b.dbablk

AND e.block_id + e.blocks > b.dbablk;

--找引起竞争的相应sql语句

break on hash_value skip 1

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

/



--17、创建临时表保存X$KSMSP的状态

CREATE GLOBAL TEMPORARY TABLE e$ksmsp ON COMMIT PRESERVE ROWS AS

SELECT a.ksmchcom,

SUM (a.CHUNK) CHUNK,

SUM (a.recr) recr,

SUM (a.freeabl) freeabl,

SUM (a.SUM) SUM

FROM (SELECT ksmchcom, COUNT (ksmchcom) CHUNK,

DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,

DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,

SUM (ksmchsiz) SUM

FROM x$ksmsp GROUP BY ksmchcom, ksmchcls) a

where 1 = 0

GROUP BY a.ksmchcom;



--使用创建的临时表保存当前的shared pool的状态

INSERT INTO E$KSMSP

SELECT a.ksmchcom,

SUM (a.CHUNK) CHUNK,

SUM (a.recr) recr,

SUM (a.freeabl) freeabl,

SUM (a.SUM) SUM

FROM (SELECT ksmchcom, COUNT (ksmchcom) CHUNK,

DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,

DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,

SUM (ksmchsiz) SUM

FROM x$ksmsp

GROUP BY ksmchcom, ksmchcls) a

GROUP BY a.ksmchcom

/



--比较前后shared pool内存分配的变化

select a.ksmchcom,a.chunk,a.sum,b.chunk,b.sum,(a.chunk - b.chunk) c_diff,(a.sum -b.sum) s_diff

from

(SELECT a.ksmchcom,

SUM (a.CHUNK) CHUNK,

SUM (a.recr) recr,

SUM (a.freeabl) freeabl,

SUM (a.SUM) SUM

FROM (SELECT ksmchcom, COUNT (ksmchcom) CHUNK,

DECODE (ksmchcls, 'recr', SUM (ksmchsiz), NULL) recr,

DECODE (ksmchcls, 'freeabl', SUM (ksmchsiz), NULL) freeabl,

SUM (ksmchsiz) SUM

FROM x$ksmsp

GROUP BY ksmchcom, ksmchcls) a

GROUP BY a.ksmchcom) a,e$ksmsp b

where a.ksmchcom = b.ksmchcom and (a.chunk - b.chunk) <>0

/



--18、找出library cache pin等待的原因

--获得library cache pin等待的对象

select p1raw from v$session_wait where event like 'library%'; --获得等待handle的地址



--通过上面获得的地址得到等待的对象

col KGLNAOWN for a10

col KGLNAOBJ for a20

select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ

from X$KGLOB

where KGLHDADR ='&adr'

/



--获得持有等待对象的session信息，其中&hdl=(上面获得的handle的地址)

select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,

b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ

from v$session a,x$kglpn b

where a.saddr=b.kglpnuse and b.kglpnhdl = '&hdl' and b.KGLPNMOD<>0

/



--上面的语句可以综合到下面的语句具体实现

SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj

FROM x$kglob

WHERE kglhdadr IN (SELECT p1raw

FROM v$session_wait

WHERE event LIKE 'library%')

/



SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,

b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq

FROM v$session a, x$kglpn b

WHERE a.saddr = b.kglpnuse

AND b.kglpnmod <> 0

AND b.kglpnhdl IN (SELECT p1raw

FROM v$session_wait

WHERE event LIKE 'library%')

/



--根据sid信息取得sql信息

SELECT sql_text

FROM v$sqlarea

WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (

SELECT sql_address, sql_hash_value

FROM v$session

WHERE SID IN (

SELECT SID

FROM v$session a, x$kglpn b

WHERE a.saddr = b.kglpnuse

AND b.kglpnmod <> 0

AND b.kglpnhdl IN (SELECT p1raw

FROM v$session_wait

WHERE event LIKE 'library%')))

/



--19、获得参数的描述信息

set linesize 120

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

/



--20、oracle收集的buffer cache及shared pool 的建议信息

select id,name,block_size,size_for_estimate sfe,size_factor sf,

estd_physical_read_factor eprf,estd_physical_reads epr

from v$db_cache_advice;



select SHARED_POOL_SIZE_FOR_ESTIMATE SPSFE,SHARED_POOL_SIZE_FACTOR SPSF,

ESTD_LC_SIZE,ESTD_LC_MEMORY_OBJECTS ELMO,ESTD_LC_TIME_SAVED ELTS,

ESTD_LC_TIME_SAVED_FACTOR ELTSF,ESTD_LC_MEMORY_OBJECT_HITS ELMOH

from v$shared_pool_advice;



--21、是10g中，决定各参数组件大小的查询

col name for a30

col value for a30

col describ for a50



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 '%pool_size%'

/



--22、10g各动态组件调整时间及调整类型

col component for a30

select COMPONENT,CURRENT_SIZE,MIN_SIZE,LAST_OPER_TYPE,LAST_OPER_MODE,to_char(LAST_OPER_TIME,'yyyy-mm-dd hh24:mi:ss') LOT

from v$sga_dynamic_components;



--23、sql在工作区中工作方式所占比例

col value for 999999999999

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

/



--24、pga动态性能视图信息

SELECT pga_target_factor factor, low_optimal_size / 1024 low,

ROUND (high_optimal_size / 1024) high,

estd_optimal_executions estd_opt, estd_onepass_executions estd_op,

estd_multipasses_executions estd_mp, estd_total_executions estd_exec

FROM v$pga_target_advice_histogram

WHERE pga_target_factor = 0.25 AND estd_total_executions > 0

/



--25、获得存在问题的sql，根据pid

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

/



--26、fast_start_mttr_target

select MTTR_TARGET_FOR_ESTIMATE MttrEst,

ADVICE_STATUS AD,

DIRTY_LIMIT DL,

ESTD_CACHE_WRITES ESTCW,

ESTD_CACHE_WRITE_FACTOR EstCWF,ESTD_TOTAL_WRITES ESTW,

ESTD_TOTAL_WRITE_FACTOR ETWF,ESTD_TOTAL_IOS ETIO

from v$mttr_target_advice;

/



--27、实例恢复的时间计算

select RECOVERY_ESTIMATED_IOS REIO,

ACTUAL_REDO_BLKS ARB,

TARGET_REDO_BLKS TRB,

LOG_FILE_SIZE_REDO_BLKS LFSRB,

LOG_CHKPT_TIMEOUT_REDO_BLKS LCTRB,

LOG_CHKPT_INTERVAL_REDO_BLKS LCIRB,

FAST_START_IO_TARGET_REDO_BLKS FSIOTRB,

TARGET_MTTR TMTTR,

ESTIMATED_MTTR EMTTR,

CKPT_BLOCK_WRITES CBW

from v$instance_recovery;



--而在10g中则为，其中writes_autotrne字段指由于自动调整检查点执行的写出次数，而ckpt_block_writes指检查点写出的block数量

SELECT recovery_estimated_ios reios, target_mttr tmttr, estimated_mttr emttr,

writes_mttr wmttr, writes_other_settings woset,

ckpt_block_writes ckptbw, writes_autotune wauto,

writes_full_thread_ckpt wftckpt

FROM v$instance_recovery;



--28、show_space过程及使用

--普通版本（不适应assm）

create or replace procedure show_space ( p_segname in varchar2,

p_owner in varchar2 default user, p_type in varchar2 default 'TABLE' )

as

l_free_blks number;

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;

procedure p( p_label in varchar2, p_num in number ) is

begin

dbms_output.put_line( rpad(p_label,40,'.') || p_num );

end;

begin

dbms_space.free_blocks ( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

freelist_group_id => 0,

free_blks => l_free_blks );

dbms_space.unused_space ( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

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

p( 'Free Blocks', l_free_blks );

p( 'Total Blocks', l_total_blocks );

p( 'Total Bytes', l_total_bytes );

p( 'Unused Blocks', l_unused_blocks );

p( 'Unused Bytes', l_unused_bytes );

p( 'Last Used Ext FileId', l_LastUsedExtFileId );

p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );

p( 'Last Used Block', l_LAST_USED_BLOCK );

end;



--完整版本

create or replace procedure show_space

( p_segname_1 in varchar2,

p_space in varchar2 default 'MANUAL',

p_type_1 in varchar2 default 'TABLE' ,

p_analyzed in varchar2 default 'N',

p_owner_1 in varchar2 default user)

as

p_segname varchar2(100);

p_type varchar2(10);

p_owner varchar2(30);



l_unformatted_blocks number;

l_unformatted_bytes number;

l_fs1_blocks number;

l_fs1_bytes number;

l_fs2_blocks number;

l_fs2_bytes number;

l_fs3_blocks number;

l_fs3_bytes number;

l_fs4_blocks number;

l_fs4_bytes number;

l_full_blocks number;

l_full_bytes number;



l_free_blks number;

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;



procedure p( p_label in varchar2, p_num in number )

is

begin

dbms_output.put_line( rpad(p_label,40,'.') ||

p_num );

end;

begin

p_segname := upper(p_segname_1); -- rainy changed

p_owner := upper(p_owner_1);

p_type := p_type_1;



if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed

p_type := 'INDEX';

end if;



if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed

p_type := 'TABLE';

end if;



if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed

p_type := 'CLUSTER';

end if;





dbms_space.unused_space

( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

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



if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then

dbms_space.free_blocks

( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

freelist_group_id => 0,

free_blks => l_free_blks );



p( 'Free Blocks', l_free_blks );

end if;



p( 'Total Blocks', l_total_blocks );

p( 'Total Bytes', l_total_bytes );

p( 'Unused Blocks', l_unused_blocks );

p( 'Unused Bytes', l_unused_bytes );

p( 'Last Used Ext FileId', l_LastUsedExtFileId );

p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );

p( 'Last Used Block', l_LAST_USED_BLOCK );





/*IF the segment is analyzed */

if p_analyzed = 'Y' then

dbms_space.space_usage(segment_owner => p_owner ,

segment_name => p_segname ,

segment_type => p_type ,

unformatted_blocks => l_unformatted_blocks ,

unformatted_bytes => l_unformatted_bytes,

fs1_blocks => l_fs1_blocks,

fs1_bytes => l_fs1_bytes ,

fs2_blocks => l_fs2_blocks,

fs2_bytes => l_fs2_bytes,

fs3_blocks => l_fs3_blocks ,

fs3_bytes => l_fs3_bytes,

fs4_blocks => l_fs4_blocks,

fs4_bytes => l_fs4_bytes,

full_blocks => l_full_blocks,

full_bytes => l_full_bytes);

dbms_output.put_line(rpad(' ',50,'*'));

dbms_output.put_line('The segment is analyzed');

p( '0% -- 25% free space blocks', l_fs1_blocks);

p( '0% -- 25% free space bytes', l_fs1_bytes);

p( '25% -- 50% free space blocks', l_fs2_blocks);

p( '25% -- 50% free space bytes', l_fs2_bytes);

p( '50% -- 75% free space blocks', l_fs3_blocks);

p( '50% -- 75% free space bytes', l_fs3_bytes);

p( '75% -- 100% free space blocks', l_fs4_blocks);

p( '75% -- 100% free space bytes', l_fs4_bytes);

p( 'Unused Blocks', l_unformatted_blocks );

p( 'Unused Bytes', l_unformatted_bytes );

p( 'Total Blocks', l_full_blocks);

p( 'Total bytes', l_full_bytes);



end if;



end;





ASSM 类型的表



SQL> exec show_space('t','auto');

Total Blocks............................512

Total Bytes.............................4194304

Unused Blocks...........................78

Unused Bytes............................638976

Last Used Ext FileId....................9

Last Used Ext BlockId...................25608

Last Used Block.........................50



PL/SQL procedure successfully completed.





ASSM 类型的索引





SQL> exec show_space('t_index','auto','i');

Total Blocks............................80

Total Bytes.............................655360

Unused Blocks...........................5

Unused Bytes............................40960

Last Used Ext FileId....................9

Last Used Ext BlockId...................25312

Last Used Block.........................3



PL/SQL procedure successfully completed.





对analyze 过的segment 可以这样



SQL> exec show_space('t','auto','T','Y');

Total Blocks............................512

Total Bytes.............................4194304

Unused Blocks...........................78

Unused Bytes............................638976

Last Used Ext FileId....................9

Last Used Ext BlockId...................25608

Last Used Block.........................50

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........0

75% -- 100% free space bytes............0

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................418

Total bytes.............................3424256



PL/SQL procedure successfully completed.





摘自：关于SHOW_SPACE()工具的用法



--29、分析表

analyze table table_name compute statistics for table for all indexes for all indexed columns;



Analyze table tablename compute statistics;

Analyze index|cluster indexname estimate statistics;

ANALYZE TABLE tablename COMPUTE STATISTICS

FOR TABLE

FOR ALL [LOCAL] INDEXES

FOR ALL [INDEXED] COLUMNS;

ANALYZE TABLE tablename DELETE STATISTICS

ANALYZE TABLE tablename VALIDATE REF UPDATE

ANALYZE TABLE tablename VALIDATE STRUCTURE

[CASCADE]|[INTO TableName]

ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]

等等。

如果想分析整个用户或数据库，还可以采用工具包，可以并行分析

Dbms_utility(8i以前的工具包)

Dbms_stats(8i以后提供的工具包)

如

dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);

dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);

这是对命令与工具包的一些总结

1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。

a) 可以并行进行，对多个用户，多个Table

b) 可以得到整个分区表的数据和单个分区的数据。

c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区

d) 可以倒出统计信息

e) 可以用户自动收集统计信息

2、DBMS_STATS的缺点

a) 不能Validate Structure

b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。

c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True

3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息



--30、unix环境快速shutdown数据库之前先删除各个进程

$ ps -ef|grep $ORACLE_SID|grep -v ora_|grep LOCAL=NO|awk '{print $2}'|xargs kill

然后你再shutdown immediate就很快的了。







第三章监控Oracle数据库的常用shell脚本
2007年11月15日 星期四 16:08

这篇文章介绍了DBA每天在监控Oracle数据库方面的职责，讲述了如何通过shell脚本来完成这些重复的监控工作。本文首先回顾了一些DBA常用的Unix命令，以及解释了如何通过Unix Cron来定时执行DBA脚本。同时文章还介绍了8个重要的脚本来监控Oracle数据库：



    检查实例的可用性

   

    检查监听器的可用性



    检查alert日志文件中的错误信息

   

    在存放log文件的地方满以前清空旧的log文件



    分析table和index以获得更好的性能



    检查表空间的使用情况



    找出无效的对象



    监控用户和事务



    DBA需要的Unix基本知识



    基本的UNIX命令



    以下是一些常用的Unix命令：



    ps--显示进程

    grep--搜索文件中的某种文本模式

    mailx--读取或者发送mail

    cat--连接文件或者显示它们

    cut--选择显示的列

    awk--模式匹配语言

    df--显示剩余的磁盘空间



    以下是DBA如何使用这些命令的一些例子：



    显示服务器上的可用实例：



    $ ps -ef | grep smon

    oracle 21832 1 0 Feb 24 ? 19:05 ora_smon_oradb1

    oracle 898 1 0 Feb 15 ? 0:00 ora_smon_oradb2

    dliu 25199 19038 0 10:48:57 pts/6 0:00 grep smon

    oracle 27798 1 0 05:43:54 ? 0:00 ora_smon_oradb3

    oracle 28781 1 0 Mar 03 ? 0:01 ora_smon_oradb4、



    显示服务器上的可用监听器：



    $ ps -ef | grep listener | grep -v grep

    （译者注：grep命令应该加上-i参数，即grep -i listener，该参数的作用是忽略大小写，因为有些时候listener是大写的，这时就会看不到结果）

    oracle 23879 1 0 Feb 24 ? 33:36 /8.1.7/bin/tnslsnr listener_db1 -inherit

    oracle 27939 1 0 05:44:02 ? 0:00 /8.1.7/bin/tnslsnr listener_db2 -inherit

    oracle 23536 1 0 Feb 12 ? 4:19 /8.1.7/bin/tnslsnr listener_db3 -inherit

    oracle 28891 1 0 Mar 03 ? 0:01 /8.1.7/bin/tnslsnr listener_db4 -inherit



    查看Oracle存档目录的文件系统使用情况



    $ df -k | grep oraarch

    /dev/vx/dsk/proddg/oraarch 71123968 4754872 65850768 7% /u09/oraarch



    统计alter.log文件中的行数：



    $ cat alert.log | wc -l

    2984   



    列出alert.log文件中的全部Oracle错误信息：



    $ grep ORA- alert.log

    ORA-00600: internal error code, arguments: [kcrrrfswda.1], [], [], [], [], []

    ORA-00600: internal error code, arguments: [1881], [25860496], [25857716], []



    CRONTAB基本



    一个crontab文件中包含有六个字段：



    分钟 0-59



    小时 0-23



    月中的第几天 1-31



    月份 1 - 12



    星期几 0 - 6, with 0 = Sunday



    Unix命令或者Shell脚本



    要编辑一个crontab文件，输入：



    Crontab -e



    要查看一个crontab文件，输入：



    Crontab -l

    0 4 * * 5 /dba/admin/analyze_table.ksh

    30 3 * * 3,6 /dba/admin/hotbackup.ksh /dev/null 2>&1





    在上面的例子中，第一行显示了一个分析表的脚本在每个星期5的4：00am运行。第二行显示了一个执行热备份的脚本在每个周三和周六的3：00a.m.运行





监控数据库的常用Shell脚本



    以下提供的8个shell脚本覆盖了DBA每日监控工作的90%，你可能还需要修改UNIX的环境变量。



    检查Oracle实例的可用性



    oratab文件中列出了服务器上的所有数据库



    $ cat /var/opt/oracle/oratab

    ###################################################################

    ## /var/opt/oracle/oratab ##

    ###################################################################

    oradb1:/u01/app/oracle/product/8.1.7:Y

    oradb2:/u01/app/oracle/product/8.1.7:Y

    oradb3:/u01/app/oracle/product/8.1.7:N

    oradb4:/u01/app/oracle/product/8.1.7:Y



    以下的脚本检查oratab文件中列出的所有数据库，并且找出该数据库的状态（启动还是关闭）



################################################



    ## ckinstance.ksh ##############################



    ORATAB=/var/opt/oracle/oratab



    echo "`date` "



    echo "Oracle Database(s) Status `hostname` :/n"



    db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "/#" | grep -v "/*"`



    pslist="`ps -ef | grep pmon`"



    for i in $db ; do



    echo "$pslist" | grep "ora_pmon_$i" > /dev/null 2>$1



    if (( $? )); then



    echo "Oracle Instance - $i: Down"



    else



    echo "Oracle Instance - $i: Up"



    fi



done





使用以下的命令来确认该脚本是可以执行的：



    $ chmod 744 ckinstance.ksh

    $ ls -l ckinstance.ksh

    -rwxr--r-- 1 oracle dba 657 Mar 5 22:59 ckinstance.ksh*



    以下是实例可用性的报表：



    $ ckinstance.ksh

    Mon Mar 4 10:44:12 PST 2002

    Oracle Database(s) Status for DBHOST server:

    Oracle Instance - oradb1: Up

    Oracle Instance - oradb2: Up

    Oracle Instance - oradb3: Down

    Oracle Instance - oradb4: Up



    检查Oracle监听器的可用性



    以下有一个类似的脚本检查Oracle监听器。如果监听器停了，该脚本将会重新启动监听器：



#########################################



    ## cklsnr.sh ##



    #########################################



    #!/bin/ksh



    DBALIST="[e-mail],[e-mail]";export DBALIST



    cd /var/opt/oracle



    rm -f lsnr.exist



    ps -ef | grep mylsnr | grep -v grep > lsnr.exist



    if [ -s lsnr.exist ]



    then



    echo



    else



    echo "Alert" | mailx -s "Listener 'mylsnr' on `hostname` is down" $DBALIST



    TNS_ADMIN=/var/opt/oracle; export TNS_ADMIN



    ORACLE_SID=db1; export ORACLE_SID



    ORAENV_ASK=NO; export ORAENV_ASK



    PATH=$PATH:/bin:/usr/local/bin; export PATH



    . oraenv



    LD_LIBRARY_PATH=${ORACLE_HOME}/lib;export LD_LIBRARY_PATH



    lsnrctl start mylsnr



fi





检查Alert日志（ORA-XXXXX）



    每个脚本所使用的一些环境变量可以放到一个profile中：



############################################



    ## oracle.profile ##



    ###############################################



    EDITOR=vi;export EDITOR ORACLE_BASE=/u01/app/oracle; export



    ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/8.1.7; export



    ORACLE_HOME LD_LIBRARY_PATH=$ORACLE_HOME/lib; export



    LD_LIBRARY_PATH TNS_ADMIN=/var/opt/oracle;export



    TNS_ADMIN NLS_LANG=american; export



    NLS_LANG NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export



    NLS_DATE_FORMAT ORATAB=/var/opt/oracle/oratab;export



    ORATAB PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:



    /usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/



    sbin:/usr/openwin/bin:/opt/bin:.; export



    PATH DBALIST="[e-mail],



   [e-mail]";export



DBALIST





以下的脚本首先调用oracle.profile来设置全部的环境变量。如果发现任何的Oracle错误，该脚本还会给DBA发送一个警告的email。





###################################################



    ## ckalertlog.sh ##



    ####################################################



    #!/bin/ksh



    .. /etc/oracle.profile



    for SID in `cat $ORACLE_HOME/sidlist`



    do



    cd $ORACLE_BASE/admin/$SID/bdump



    if [ -f alert_${SID}.log ]



    then



    mv alert_${SID}.log alert_work.log



    touch alert_${SID}.log



    cat alert_work.log >> alert_${SID}.hist



    grep ORA- alert_work.log > alert.err



    fi



    if [ `cat alert.err|wc -l` -gt 0 ]



    then



    mailx -s "${SID} ORACLE ALERT ERRORS" $DBALIST < alert.err



    fi



    rm -f alert.err



    rm -f alert_work.log



done





清除旧的归档文件



    以下的脚本将会在log文件达到90%容量的时候清空旧的归档文件：



    $ df -k | grep arch

    Filesystem kbytes used avail capacity Mounted on

    /dev/vx/dsk/proddg/archive 71123968 30210248 40594232 43% /u08/archive



#######################################################



    ## clean_arch.ksh ##



    ########################################################



    #!/bin/ksh



    df -k | grep arch > dfk.result



    archive_filesystem=`awk -F" " '{ print $6 }' dfk.result`



    archive_capacity=`awk -F" " '{ print $5 }' dfk.result`





    if [[ $archive_capacity > 90% ]]



    then



    echo "Filesystem ${archive_filesystem} is ${archive_capacity} filled"



    # try one of the following option depend on your need



    find $archive_filesystem -type f -mtime +2 -exec rm -r {} /;



    tar



    rman



fi





分析表和索引（以得到更好的性能）



    以下我将展示如果传送参数到一个脚本中：



##########################################################



    ## analyze_table.sh ##



    ########################################################



    #!/bin/ksh



    # input parameter: 1: password # 2: SID



    if (($#<1)) then echo "Please enter 'oracle' user password as the first parameter !" exit 0



    fi



    if (($#<2)) then echo "Please enter instance name as the second parameter!" exit 0



fi





要传入参数以执行该脚本，输入：



    $ analyze_table.sh manager oradb1



    脚本的第一部分产生了一个analyze.sql文件，里面包含了分析表用的语句。脚本的第二部分分析全部的表：



###########################################################



    ## analyze_table.sh ##



    #########################################################



    sqlplus -s < oracle/$1@$2



    set heading off



    set feed off



    set pagesize 200



    set linesize 100



    spool analyze_table.sql



    select 'ANALYZE TABLE ' || owner || '.' || segment_name ||



    ' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'



    from dba_segments



    where segment_type = 'TABLE'



    and owner not in ('SYS', 'SYSTEM');



    spool off



    exit



    !



    sqlplus -s < oracle/$1@$2



    @./analyze_table.sql



    exit



!





以下是analyze.sql的一个例子：



    $ cat analyze.sql

    ANALYZE TABLE HIRWIN.JANUSAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT;

    ANALYZE TABLE HIRWIN.JANUSER_PROFILE ESTIMATE STATISTICS SAMPLE 10 PERCENT;

    ANALYZE TABLE APPSSYS.HIST_SYSTEM_ACTIVITY ESTIMATE STATISTICS SAMPLE 10 PERCENT;

    ANALYZE TABLE HTOMEH.QUEST_IM_VERSION ESTIMATE STATISTICS SAMPLE 10 PERCENT;

    ANALYZE TABLE JSTENZEL.HIST_SYS_ACT_0615 ESTIMATE STATISTICS SAMPLE 10 PERCENT;

    ANALYZE TABLE JSTENZEL.HISTORY_SYSTEM_0614 ESTIMATE STATISTICS SAMPLE 10 PERCENT;

    ANALYZE TABLE JSTENZEL.CALC_SUMMARY3 ESTIMATE STATISTICS SAMPLE 10 PERCENT;

    ANALYZE TABLE IMON.QUEST_IM_LOCK_TREE ESTIMATE STATISTICS SAMPLE 10 PERCENT;

    ANALYZE TABLE APPSSYS.HIST_USAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT;

    ANALYZE TABLE PATROL.P$LOCKCONFLICTTX ESTIMATE STATISTICS SAMPLE 10 PERCENT;





    检查表空间的使用



    以下的脚本检测表空间的使用。如果表空间只剩下10%，它将会发送一个警告email。



####################################################



    ## ck_tbsp.sh ##



    ##############################################



    #!/bin/ksh



    sqlplus -s < oracle/$1@$2



    set feed off



    set linesize 100



    set pagesize 200



    spool tablespace.alert



    SELECT F.TABLESPACE_NAME,



    TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",



    TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",



    TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",



    TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')



   ||' %' PER_FREE



    FROM (



    SELECT TABLESPACE_NAME,



    ROUND (SUM (BLOCKS*(SELECT VALUE/1024



    FROM V/$PARAMETER



    WHERE NAME = 'db_block_size')/1024)



    ) FREE_SPACE



    FROM DBA_FREE_SPACE



    GROUP BY TABLESPACE_NAME



    ) F,



    (



    SELECT TABLESPACE_NAME,



    ROUND (SUM (BYTES/1048576)) TOTAL_SPACE



    FROM DBA_DATA_FILES



    GROUP BY TABLESPACE_NAME



    ) T



    WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME



    AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;



    spool off



    exit



    !



    if [ `cat tablespace.alert|wc -l` -gt 0 ]



    then



    cat tablespace.alert -l tablespace.alert > tablespace.tmp



    mailx -s "TABLESPACE ALERT for ${2}" $DBALIST < tablespace.tmp



fi





警告email输出的例子如下：



    TABLESPACE_NAME USED (MB) FREE (MB) TOTAL (MB) PER_FREE

    ------------------- --------- ----------- ------------------- ------------------

    SYSTEM 2,047 203 2,250 9 %

    STBS01 302 25 327 8 %

    STBS02 241 11 252 4 %

    STBS03 233 19 252 8 %



    查找出无效的数据库对象



    以下查找出无效的数据库对象：



#################################################



    ## ck_tbsp.sh ##



    #################################################



    #!/bin/ksh



    sqlplus -s < oracle/$1@$2



    set feed off



    set linesize 100



    set pagesize 200



    spool tablespace.alert



    SELECT F.TABLESPACE_NAME,



    TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",



    TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",



    TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",



    TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE



    FROM (



    SELECT TABLESPACE_NAME,



    ROUND (SUM (BLOCKS*(SELECT VALUE/1024



    FROM V/$PARAMETER



    WHERE NAME = 'db_block_size')/1024)



    ) FREE_SPACE



    FROM DBA_FREE_SPACE



    GROUP BY TABLESPACE_NAME



    ) F,



    (



    SELECT TABLESPACE_NAME,



    ROUND (SUM (BYTES/1048576)) TOTAL_SPACE



    FROM DBA_DATA_FILES



    GROUP BY TABLESPACE_NAME



    ) T



    WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME



    AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;



    spool off



    exit



    !



    if [ `cat tablespace.alert|wc -l` -gt 0 ]



    then



    cat tablespace.alert -l tablespace.alert > tablespace.tmp



    mailx -s "TABLESPACE ALERT for ${2}" $DBALIST < tablespace.tmp



fi





监视用户和事务（死锁等）



    以下的脚本在死锁发生的时候发送一个警告e-mail：



########################################################



    ## deadlock_alert.sh ##



    ######################################################



    #!/bin/ksh



    .. /etc/oracle.profile



    sqlplus -s < oracle/$1@$2



    set feed off



    set heading off



    spool deadlock.alert



    SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,



    DECODE(REQUEST, 0, 'NO','YES' ) WAITER



    FROM V$LOCK



    WHERE REQUEST > 0 OR BLOCK > 0



    ORDER BY block DESC;



    spool off



    exit



    !



    if [ `cat deadlock.alert|wc -l` -gt 0 ]



    then



    mailx -s "DEADLOCK ALERT for ${2}" $DBALIST < deadlock.alert



fi





结论



    0,20,40 7-17 * * 1-5 /dba/scripts/ckinstance.sh > /dev/null 2>&1

    0,20,40 7-17 * * 1-5 /dba/scripts/cklsnr.sh > /dev/null 2>&1

    0,20,40 7-17 * * 1-5 /dba/scripts/ckalertlog.sh > /dev/null 2>&1

    30 * * * 0-6 /dba/scripts/clean_arch.sh > /dev/null 2>&1

    * 5 * * 1,3 /dba/scripts/analyze_table.sh > /dev/null 2>&1

    * 5 * * 0-6 /dba/scripts/ck_tbsp.sh > /dev/null 2>&1

    * 5 * * 0-6 /dba/scripts/invalid_object_alert.sh > /dev/null 2>&1

    0,20,40 7-17 * * 1-5 /dba/scripts/deadlock_alert.sh > /dev/null 2>&1



通过以上的脚本，可大大减轻你的工作。你可以使用这些是来做更重要的工作，例如性能调整。



http://hi.baidu.com/mymv/blog/item/2976b4455e8aa427cffca3b3.html



解决SQL语句过度消耗CPU问题
问题描述：

     现场工程师报告OSS应用系统运行缓慢，具体操作是通过OSS系统查询话单，很长时间才能返回结果，严重影响了客户的正常使用。

问题处理：

1.登陆数据库主机，用sar命令看到idle的值持续为0，CPU的资源已经耗尽：

bz_db1# sar 2 4

SunOS kest 5.8 Generic_108528-19 sun4u     10/26/04

10:56:46     %usr     %sys     %wio    %idle

10:56:48        1        4       95        0

10:56:50        1        5       94        0

10:56:52        0        6       93        0

10:56:54        1        6       93        0

Average         1        5       94       0



2.使用TOP命令看到有两个明显占用CPU利用率过高的进程,以下是top命令的结果:

bz_db1# top

last pid:   1664;load averages:   3.26,   3.24,   3.69                       

159 processes: 152 sleeping, 2 running, 2 zombie, 1 stopped, 2 on cpu

CPU states:   1.5% idle, 72.5% user, 17.9% kernel,   8.0% iowait,   0.0% swap

Memory: 2.0G real, 233M free, 2.0G swap in use, 3.4G swap free

    PID USERNAME THR PR NCE   SIZE    RES STATE    TIME FLTS     CPU COMMAND

27420 oracle      1 10    0   1.3G   1.2G cpu01   22.9H     2 31.94% oracle

27418 oracle      1 10    0   1.3G   1.2G run     23.0H     6 26.86% oracle

   5943 oracle      1 59    0   1.3G   1.2G sleep   25:26    37   4.92% oracle

   6295 oracle      1 55    0   1.3G   1.2G run     25:14    74   4.90% oracle

   7778 oracle      1 43    0   1.3G   1.2G sleep   11:43   110   4.86% oracle

13270 oracle      1 59    0   1.3G   1.2G sleep 210.6H     0   0.96% oracle

13056 oracle      1 48    0   1.3G   1.2G sleep 303:30     0   0.37% oracle

10653 root        1 58    0 2560K 1624K cpu00    0:00     0   0.32% top

18827 oracle      1 58    0   1.3G   1.2G sleep   18.4H     0   0.31% oracle

12748 oracle    258 58    0   1.3G   1.2G sleep 555:14     0   0.21% oracle

10634 oracle      1 59    0   1.3G   1.2G sleep    0:01     0   0.21% oracle

28458 oracle      1 58    0   1.3G   1.2G sleep 535:02     0   0.18% oracle

13075 oracle      1 59    0   1.3G   1.2G sleep 326:33     0   0.15% oracle

13173 oracle      1 58    0   1.3G   1.2G sleep 593:07     0   0.13% oracle

   4927 oracle      1 59    0   1.3G   1.2G sleep   33.4H     0   0.11% oracle

可以看到这两个进程号分别是27420和27418.

3.捕获占用CPU利用率过高的SQL语句：

以下用到了我总结的SQL语句：

SQL>set line 240

SQL>set verify off

SQL>column sid format 999

SQL>column pid format 999

SQL>column S_# format 999

SQL>column username format A9 heading "ORA User"

SQL>column program   format a29

SQL>column SQL format a60

SQL>COLUMN OSname format a9 Heading "OS User"

SQL>SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,

S.osuser osname,P.serial# S_#,P.terminal,P.program   program,

P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80))   SQL

FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr

AND S.sql_address = a.address (+)   AND P.spid LIKE '%&1%';

Enter value for 1: 27420（注意这里应输入占用CPU最高的进程对应的PID）

得到以下SQL语句：

Select nvl(sum(LOCALCHARGE),0),nvl(sum(usage),0) from LOCALUSAGE where to_char(ENDTIME,'YYYYMMDD')=20041016

and LOCALCHARGE>0 and caller like '0543886%';

27418进程对应的SQL语句如下：

select nvl(sum(LOCALCHARGE),0) from LOCALUSAGE where to_char(ENDTIME,'YYYYMMDD')=20041016 and caller like '0543888%';



4.使用相关用户连接到数据库，检查其执行计划:

SQL>connect wacos/oss

Connected.

SQL>@?/rdbms/admin/utlxplan.sql

Table created.

SQL>set autotrace on

SQL>set timing on

SQL>Select nvl(sum(LOCALCHARGE),0),nvl(sum(usage),0) from LOCALUSAGE where to_char(ENDTIME,'YYYYMMDD')=20041016

and LOCALCHARGE>0 and caller like '0543886%';

NVL(SUM(LOCALCHARGE),0) NVL(SUM(USAGE),0)

----------------------- -----------------

                       0                  0

Elapsed: 00:02:56.37

Execution Plan

----------------------------------------------------------

    0       SELECT STATEMENT Optimizer=CHOOSE (Cost=13435 Card=1 Bytes=5

           3)

    1     0    SORT (AGGREGATE)

    2     1      PARTITION RANGE (ALL)

    3     2        TABLE ACCESS (FULL) OF 'LOCALUSAGE' (Cost=13435 Card=1

           81 Bytes=9593)

Statistics

----------------------------------------------------------

         258   recursive calls

           0   db block gets

       88739   consistent gets

       15705   physical reads

           0   redo size

         580   bytes sent via SQL*Net to client

         651   bytes received via SQL*Net from client

           2   SQL*Net roundtrips to/from client

           8   sorts (memory)

           0   sorts (disk)

           1   rows processed

发现对localusage表做了全表扫描，什么记录也没有返回居然用了2分多钟。

SQL> select nvl(sum(LOCALCHARGE),0) from LOCALUSAGE where to_char(ENDTIME,'YYYYMMDD')=20040816 and caller like '0543888%';

NVL(SUM(LOCALCHARGE),0)

-----------------------

                    27.6

Elapsed: 00:03:56.46

Execution Plan

----------------------------------------------------------

    0       SELECT STATEMENT Optimizer=CHOOSE (Cost=13435 Card=1 Bytes=4

           0)

    1     0    SORT (AGGREGATE)

    2     1      PARTITION RANGE (ALL)

    3     2        TABLE ACCESS (FULL) OF 'LOCALUSAGE' (Cost=13435 Card=3

           615 Bytes=144600)

Statistics

----------------------------------------------------------

           0   recursive calls

           0   db block gets

       88588   consistent gets

       15615   physical reads

           0   redo size

         507   bytes sent via SQL*Net to client

         651   bytes received via SQL*Net from client

           2   SQL*Net roundtrips to/from client

           0   sorts (memory)

           0   sorts (disk)

           1   rows processed

这个SQL语句有结果返回，发现也是对localusage表做了全表扫描，但速度也很慢，用了3分多钟。

SQL> select count(*) from localusage;

   COUNT(*)

----------

    5793776

该表有579万多条记录，数据量很大，全表扫描已经不再适合。

5.检查该表的类型：

SQL> SELECT INDEX_NAME, TABLE_NAME, STATUS, PARTITIONED FROM USER_INDEXES WHERE TABLE_NAME='LOCALUSAGE';

INDEX_NAME                      TABLE_NAME                      STATUS    PAR

------------------------------ ------------------------------ -------- ---

I_LOCALUSAGE_SID                LOCALUSAGE                      N/A       YES

UI_LOCALUSAGE_ST_SEQ            LOCALUSAGE                      N/A       YES

SQL> SELECT index_name,table_name,locality FROM user_part_indexes where table_name='LOCALUSAGE';

INDEX_NAME                      TABLE_NAME                      LOCALI

------------------------------ ------------------------------ ------

I_LOCALUSAGE_SID                LOCALUSAGE                      LOCAL

UI_LOCALUSAGE_ST_SEQ            LOCALUSAGE                      LOCAL

发现该表是分区表，并在SERVICEID,STARTIME和CDRSEQUENCE列上建立了分区索引，索引类型是local索引。

6.查看分区索引的索引键值:

SQL> select INDEX_NAME,COLUMN_NAME,INDEX_OWNER from dba_ind_columns where TABLE_NAME='LOCALUSAGE';

INDEX_NAME            COLUMN_NAME           INDEX_OWNER

-------------------- -------------------- ------------------------------

I_LOCALUSAGE_SID      SERVICEID             WACOS

UI_LOCALUSAGE_ST_SEQ STARTTIME             WACOS

UI_LOCALUSAGE_ST_SEQ CDRSEQUENCE           WACOS

发现在endtime和caller列上都没有建立索引，这也是导致SQL语句做全表扫描的最终原因。

7.决定创建新的分区索引以消除全表扫描：

(1).首先查看localusage表分区情况：

SQL> select PARTITION_NAME,tablespace_name from user_tab_partitions where table_name='LOCALUSAGE';

PARTITION_NAME                  TABLESPACE_NAME

------------------------------ ------------------------------

LOCALUSAGE_200312               WACOS

LOCALUSAGE_200401               WACOS

LOCALUSAGE_200402               WACOS

LOCALUSAGE_200404               WACOS

LOCALUSAGE_200405               WACOS

LOCALUSAGE_200406               WACOS

LOCALUSAGE_200407               WACOS

LOCALUSAGE_200409               WACOS

LOCALUSAGE_200410               WACOS

LOCALUSAGE_200411               WACOS

LOCALUSAGE_200403               WACOS

LOCALUSAGE_200408               WACOS

LOCALUSAGE_200412               WACOS

13 rows selected.

(2).在caller列上创建local分区索引：

SQL>set timing on

SQL>create index I_LOCALUSAGE_CALLER on localusage(caller)

LOCAL

(      

         PARTITION LOCALUSAGE_200312,

PARTITION LOCALUSAGE_200401,

PARTITION LOCALUSAGE_200402,

PARTITION LOCALUSAGE_200404,

PARTITION LOCALUSAGE_200405,

PARTITION LOCALUSAGE_200406,

PARTITION LOCALUSAGE_200407,

PARTITION LOCALUSAGE_200409,

PARTITION LOCALUSAGE_200410,

PARTITION LOCALUSAGE_200411,

PARTITION LOCALUSAGE_200403,

PARTITION LOCALUSAGE_200408,

PARTITION LOCALUSAGE_200412

)

TABLESPACE wacos

STORAGE(

INITIAL 6553600

NEXT 6553600

MAXEXTENTS unlimited

PCTINCREASE 0)

PCTFREE 5

NOLOGGING;

Index created.

Elapsed: 00:06:27.90   (由于数据量比较大，耗时6分钟）

8.再次查看执行计划：

SQL>Select nvl(sum(LOCALCHARGE),0),nvl(sum(usage),0) from LOCALUSAGE where to_char(ENDTIME,'YYYYMMDD')=20041016

and LOCALCHARGE>0   and caller like '0543886%';

NVL(SUM(LOCALCHARGE),0) NVL(SUM(USAGE),0)

----------------------- -----------------

                       0                  0

Elapsed: 00:00:03.00

Execution Plan

----------------------------------------------------------

    0       SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=53)

    1     0    SORT (AGGREGATE)

    2     1      PARTITION RANGE (ALL)

    3     2        TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOCALUSAGE' (Cost=22 Card=181 Bytes=9593)

    4     3          INDEX (RANGE SCAN) OF 'I_LOCALUSAGE_CALLER' (NON-UNIQUE) (Cost=14 Card=65063)

Statistics

----------------------------------------------------------

           0   recursive calls

           0   db block gets

       16813   consistent gets

         569   physical reads

           0   redo size

         580   bytes sent via SQL*Net to client

         651   bytes received via SQL*Net from client

           2   SQL*Net roundtrips to/from client

           0   sorts (memory)

           0   sorts (disk)

           1   rows processed

这次走了索引后速度明显快多了，用了3秒钟就返回了结果。

SQL>select nvl(sum(LOCALCHARGE),0) from LOCALUSAGE where to_char(ENDTIME,'YYYYMMDD')=20040816 and caller like '0543888%';

NVL(SUM(LOCALCHARGE),0)

-----------------------

                    27.6

Elapsed: 00:00:24.73

Execution Plan

----------------------------------------------------------

    0       SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=40)

    1     0    SORT (AGGREGATE)

    2     1      PARTITION RANGE (ALL)

    3     2        TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOCALUSAGE' (Cost=22 Card=3615 Bytes=144600)

    4     3          INDEX (RANGE SCAN) OF 'I_LOCALUSAGE_CALLER' (NON-UNIQUE) (Cost=14 Card=65063)

Statistics

----------------------------------------------------------

           0   recursive calls

           0   db block gets

      129336   consistent gets

        7241   physical reads

           0   redo size

         507   bytes sent via SQL*Net to client

         651   bytes received via SQL*Net from client

           2   SQL*Net roundtrips to/from client

           0   sorts (memory)

           0   sorts (disk)

           1   rows processed

这个SQL语句走了索引，用了24秒钟返回结果，性能明显提高了很多.