HB_YYM_929
- 浏览: 26379 次
- 性别:
- 来自: 深圳
-
文章分类
- 全部博客 (37)
- mysql (10)
- mysqld (0)
- jdbc资源池 (2)
- java (1)
- tungsten (0)
- linux 限制文件大小 (1)
- part001 (0)
- part001.1 (0)
- mysql间 数据迁移方案 (0)
- fm70chb1 (1)
- usmas_eclipsev2.0.1.jar (1)
- apache数据库连接池包 (1)
- 用apache的dbcp建立数据库连接池 (1)
- xtrabackup (2)
- xtrabackup-1.4.tar.gz (1)
- 多路径节点故障 (1)
- zangdimima (0)
- MySQL参数优化辅助工具 (1)
- mysql多实例 (1)
- 非技术 (2)
- 非技术1 (0)
- ORACLE (1)
最新评论
一、数据库构架体系
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=≪
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="primary.dba@company.com,another.dba@company.com";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="primary.dba@company.com,
another.dba@company.com";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秒钟返回结果，性能明显提高了很多.
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=≪
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="primary.dba@company.com,another.dba@company.com";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="primary.dba@company.com,
another.dba@company.com";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秒钟返回结果，性能明显提高了很多.
分享到：
发表评论
文章已被作者锁定，不允许评论。
-
mysql
2012-05-30 18:12 0查询 创建表次数 修改表次数 删除表次数 创建表索引次数 修 ... -
mysql5.6
2012-05-04 00:44 642mysql5.6 -
mysql5.6
2012-05-02 21:05 641mysql5.6 -
mysql slave IO
2011-11-18 01:21 570mysql slave IO -
InnoDB 表的限制
2011-09-22 01:07 485InnoDB 表的限制 -
mysql 性能监控脚本
2011-09-21 00:25 917mysql 性能监控脚本 -
MySQL, mystat, 监控, 脚本
2011-09-21 00:17 543#!/usr/bin/perl -w ############ ... -
MYSQL中限制资源的使用
2011-09-21 00:12 516自己查看MYSQL.USER 表就会发现里面最后几个字段： m ... -
MYSQL中限制资源的使用
2011-09-21 00:11 709自己查看MYSQL.USER 表就会发现里面最后几个字段： m ... -
mysql 监控脚本
2011-09-21 00:10 741mysql的空间使用脚本： #!/bin/sh #crea ...
相关推荐
数据库日常监控脚本.pdf
数据库静态监控脚本 根据生产统计数据增长情况 备份 IO 等等
关于最耗资源、表空间使用情况、查看锁等监控脚本整理。
oracle ,sybase,mysql,db2各种数据库监控脚本，及相关SQL语句。
Lepus是一套开源的数据库监控平台，目前已经支持MySQL、Oracle、SQLServer、MongoDB、Redis等数据库的基本监控和告警（MySQL已经支持复制监控、慢查询分析和定向推送等高级功能）。Lepus无需在每台数据库服务器部署...
该系统的设计和实现需要考虑多方面的因素，包括Linux操作系统、Oracle RAC集群数据库、数据库监控、shell脚本自动收集信息、Web网页技术、系统架构设计、数据访问、高可用性、自动化监控和实时监控等方面。
2 数据库监控 4 2.1 数据库监控工作内容 4 2.2 数据库监控工作步骤 4 2.2.1 查看数据库日志 4 2.2.2 检查是否有失效的数据库对象 5 2.2.3 查看数据库剩余空间 5 2.2.4 重点表检查 5 2.2.5 查看数据库是否正常 6 ...
Oracle数据库监控检测的相关脚本
Oracle数据库监控体检脚本，提供专业、全面的检测报告
"监控Oracle数据库的常用shell脚本" 监控Oracle数据库的常用shell脚本是DBA日常工作中不可或缺的一部分。本文将分享8个常用的shell脚本，涵盖了数据库实例的可用性、监听器的可用性、表空间的使用情况、无效对象的...
oracle数据库表空间监控实用脚本/a.查看某个表空间内所占空间大于查看所有表空间的碎片程度（值在30以下表示碎片很多）某个值的段/b查看某个表空间内最大连续的自由空间大小/c
linux下检测mysql进程状态脚本，做Mysql主备模式，可以监测mysql数据库允许状态。
DBA相关监控脚本， 用于监控Oracle数据库状态的常用语句
DBA常用的Oracle性能监控的脚本，非常详细，数据库巡检时非常有用
主要介绍了shell脚本实现服务器进程监控的方法，非常不错，具有参考借鉴价值，需要的朋友参考下吧
官方支持脚本，用于最新版Cacti 1.1.38监控mysql数据库 5.7
sql server数据库监控检查脚本，自定义任务，可以实现每天自动化监控。经过生产环境多次验证。