杭州社保系统分析方案
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) / GREATEST(NVL(HWM, 1), 1)), 2), 0) WASTE_PER,
ROUND(BYTES / 1024, 2) TABLE_KB, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS, CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,
DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0, 'N', 'Y') CAN_EXTEND_SPACE, NEXT_EXTENT, MAX_FREE_SPACE,
O_TABLESPACE_NAME TABLESPACE_NAME FROM (SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES, B.NUM_ROWS, A.BLOCKS BLOCKS,
B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM, DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS *
(1 + (PCT_FREE / 100))) / C.BLOCKSIZE, 0), 0, 1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS *
6
(1 + (PCT_FREE / 100))) / C.BLOCKSIZE, 0)) + 2 AVG_USED_BLOCKS, ROUND(100 *
(NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS / A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C WHERE A.OWNER = B.OWNER
and SEGMENT_NAME = TABLE_NAME and SEGMENT_TYPE = 'TABLE' AND B.TABLESPACE_NAME = C.NAME UNION ALL
SELECT A.OWNER OWNER,
SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES, B.NUM_ROWS, A.BLOCKS BLOCKS,
B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE(ROUND((B.AVG_ROW_LEN * B.NUM_ROWS *
(1 + (B.PCT_FREE / 100))) / C.BLOCKSIZE, 0), 0, 1,
ROUND((B.AVG_ROW_LEN * B.NUM_ROWS *
(1 + (B.PCT_FREE / 100))) / C.BLOCKSIZE, 0)) + 2 AVG_USED_BLOCKS,
7
杭州社保系统分析方案
ROUND(100 *
(NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS / A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME FROM SYS.DBA_SEGMENTS A, SYS.DBA_TAB_PARTITIONS B, SYS.TS$ C, SYS.DBA_TABLES D WHERE A.OWNER = B.TABLE_OWNER and SEGMENT_NAME = B.TABLE_NAME and SEGMENT_TYPE = 'TABLE PARTITION' AND B.TABLESPACE_NAME = C.NAME AND D.OWNER = B.TABLE_OWNER AND D.TABLE_NAME = B.TABLE_NAME
AND A.PARTITION_NAME = B.PARTITION_NAME),
(SELECT TABLESPACE_NAME F_TABLESPACE_NAME, MAX(BYTES) MAX_FREE_SPACE FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME)
WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME
AND GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) / GREATEST(NVL(HWM, 1), 1)), 2), 0) > 25 AND OWNER = 'HZSIMIS' AND BLOCKS > 128
ORDER BY 10 DESC, 1 ASC, 2 ASC; 然后统计出以上表的真实数据量
8
3.6Top SQL 监控
PROMPT Top 10 most expensive SQL (Buffer Gets by Executions)... PROMPT
select rownum as rank, a.* from (select buffer_gets, executions,
buffer_gets / decode(executions, 0, 1, executions) gets_per_exec, hash_value, sql_text from v$sqlarea
where buffer_gets > 50000 order by buffer_gets desc) a where rownum < 11/
PROMPT Top 10 most expensive SQL (Physical Reads by Executions)... PROMPT
select rownum as rank, a.* from (select disk_reads, executions,
disk_reads / decode(executions, 0, 1, executions) reads_per_exec, hash_value, sql_text from v$sqlarea
where disk_reads > 10000 order by disk_reads desc) a where rownum < 11
3.7临时表空间监控
select se.username, se.sid, su.extents,
9
杭州社保系统分析方案
su.blocks * to_number(rtrim(p.value)) as Space, tablespace, segtype, sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s where p.name = 'db_block_size' and su.session_addr = se.saddr and s.hash_value = su.sqlhash and s.address = su.sqladdr order by se.username, se.sid;
3.8数据库运行日志分析
获得数据库的alert日志后10万行及对应的trace文件
3.9Oracle Session会话监控
select saddr, sid, audsid, user#,
serial# AS serial, username, lockwait, schemaname, osuser, machine, terminal, program, type, module, action, client_info, last_call_et, taddr, logon_time from v$session t
where t.status = 'INACTIVE' AND username IN
10