本文提供了Oracle数据库管理的重要技巧，包括表空间监控、性能监控和其他关键领域的管理策略。介绍了如何查询表空间信息、监控表空间使用率、了解表空间自动扩展能力等。
1.关于数据库构架体系
①表空间的监控是一个重要的任务，我们必须时刻关心表空间的设置，是否满足现在应
用的需求，以下的语句可以查询到表空间的详细信息。
SQL code
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;
②对于某些数据文件没有设置为自动扩展的表空间来说，如果表空间满了，就将意味着
数据库可能会因为没有空间而停止下来。监控表空间，最主要的就是监控剩余空间的大
小或者是使用率。以下是监控表空间使用率与剩余空间大小的语句。
SQL code
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 --if have tempfile 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(+)
③除了监控表空间的剩余空间，有时候我们也有必要了解一下该表空间是否具有自动扩
展空间的能力，虽然我们建议在生产系统中预先分配空间。以下语句将完成这一功能。
SQL code
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
④我相信使用字典管理的表空间的也不少吧，因为字典管理的表空间中，每个表的下一
个区间的大小是不可以预料的，所以我们必须监控那些表在字典管理的表空间中的下一
个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。以下语句检
查那些表的扩展将引起表空间的扩展。
SQL code
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
⑤段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或
者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），如果段没有可以
再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工
作。
SQL code
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
⑥对象的空间分配与空间利用情况，除了从各个方面的分析，如分析表，查询rowid等
方法外，其实oracle提供了一个查询空间的包dbms_space，如果我们稍封装一下，将是
非常好用的一个东西。
SQL code
CREATE OR REPLACE PROCEDURE show_space(p_segname IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT
USER,
p_type IN VARCHAR2 DEFAULT
'TABLE',
p_partition IN VARCHAR2 DEFAULT
NULL)
-- This procedure uses AUTHID CURRENT USER so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user who wanted to use it.
AUTHID CURRENT_USER 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;
l_segment_space_mgmt varchar2(255);
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;
-- Inline procedure to print out numbers nicely formatted
-- with a simple label.
PROCEDURE p(p_label in varchar2, p_num in number) IS
BEGIN
dbms_output.put_line(rpad(p_label, 40, '.') ||
to_char(p_num, '999,999,999,999'));
END;
BEGIN
-- This query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- This query determines if the object is an ASSM object or not.
BEGIN
EXECUTE IMMEDIATE 'select ts.segment_space_management
FROM dba_segments seg, dba_tablespaces ts
WHERE seg.segment_name = :p_segname
AND (:p_partition is null or
seg.partition_name = :p_partition)
AND seg.owner = :p_owner
AND seg.tablespace_name = ts.tablespace_name'
INTO l_segment_space_mgmt
USING p_segname, p_partition, p_partition, p_owner;
EXCEPTION
WHEN too_many_rows THEN
dbms_output.put_line('This must be a partitioned table, use p_partition => ');
RETURN;
END;
-- If the object is in an ASSM tablespace, we must use this API
-- call to get space information; else we use the FREE_BLOCKS
-- API for the user managed segments.
IF l_segment_space_mgmt = 'AUTO' THEN
dbms_space.space_usage(p_owner,
p_segname,
p_type,
l_unformatted_blocks,
l_unformatted_bytes,
l_fs1_blocks,
l_fs1_bytes,
l_fs2_blocks,
l_fs2_bytes,
l_fs3_blocks,
l_fs3_bytes,
l_fs4_blocks,
l_fs4_bytes,
l_full_blocks,
l_full_bytes,
p_partition);
p('Unformatted Blocks ', l_unformatted_blocks);
p('FS1 Blocks (0-25) ', l_fs1_blocks);
p('FS2 Blocks (25-50) ', l_fs2_blocks);
p('FS3 Blocks (50-75) ', l_fs3_blocks);
p('FS4 Blocks (75-100)', l_fs4_blocks);
p('Full Blocks ', l_full_blocks);
ELSE
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;
-- And then the unused space API call to get the rest of the
-- information.
dbms_space.unused_space(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
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('Total Blocks', l_total_blocks);
p('Total Bytes', l_total_bytes);
p('Total MBytes', trunc(l_total_bytes / 1024 / 1024));
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;
执行结果将如下所示：
SQL code
SQL> exec show_space2('test_stevie');
Free Blocks............................. 3
Total Blocks............................ 32
Total Bytes............................. 262,144
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 27
Last Used Ext BlockId................... 41,617
Last Used Block......................... 8
PL/SQL procedure successfully completed
⑦数据库的常规参数我就不说了，除了V$parameter中的常规参数外，ORACLE还有大量
的隐含参数，下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述。
SQL code
SELECT NAME,
VALUE,
decode(isdefault, 'TRUE', 'Y', 'N') as "Default",
decode(ISEM, 'TRUE', 'Y', 'N') as SesMod,
decode(ISYM, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') as SysMod,
decode(IMOD, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') as Modified,
decode(IADJ, 'TRUE', 'Y', 'N') as Adjusted,
description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance,
x.indx + 1,
ksppinm as NAME,
ksppity,
ksppstvl as VALUE,
ksppstdf as isdefault,
decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') as ISEM,
decode(bitand(ksppiflg / 65536, 3),
1,
'IMMEDIATE',
2,
'DEFERRED',
'FALSE') as ISYM,
decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') as IMOD,
decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE') as IADJ,
ksppdesc as DESCRIPTION
FROM x$ksppi x, x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm, 1, 1) = '_'
AND x.inst_id = USERENV('Instance')) ORDER BY NAME
⑧数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片，所以，
在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎片，提高查询
速度。
SQL code
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出来的文件，就可以直接运行了。
⑨表的主键是必要的，没有主键的表可以说是不符合设计规范的，所以我们需要监控表
是否有主键。
SQL code
SELECT table_name
FROM all_tables
WHERE owner = USER
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = USER
AND constraint_type = 'P'
2.关于性能监控
①数据缓冲区的命中率已经不是性能调整中的主要问题了，但是，过低的命中率肯定是
不可以的，在任何情况下，我们必须保证有一个大的data buffer和一个高的命中率。
这个语句可以获得整体的数据缓冲命中率，越高越好。
SQL code
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'
②库缓冲说明了SQL语句的重载率，当然，一个SQL语句应当被执行的越多越好，如果重
载率比较高，就考虑增加共享池大小或者是提高Bind变量的使用。
以下语句查询了Sql语句的重载率，越低越好。
SQL code
SELECT SUM(pins) total_pins,
SUM(reloads) total_reloads,
SUM(reloads) / SUM(pins) * 100 libcache_reload_ratio
FROM v$librarycache
③用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必
须找到发生等待的锁，有可能的话，杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产
生了两个锁，一个是表锁，一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话。
SQL code
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
④锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
SQL code
SELECT /*+ rule */
lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
sysdate,
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。
⑤如果发生了事务或锁，想知道哪些回滚段正在被使用吗？其实通过事务表，我们可以
详细的查询到事务与回滚段之间的关系。同时，如果关联会话表，我们则可以知道是哪
个会话发动了这个事务。
SQL code
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
⑥想知道现在哪个用户正在利用临时段吗？这个语句将告诉你哪个用户正在利用临时
段。
SQL code
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;
⑦如果利用会话跟踪或者是想查看某个会话的跟踪文件，那么查询到OS上的进程或线程
号是非常重要的，因为文件的令名中，就包含这个信息，以下的语句可以查询到进程或
线程号，由此就可以找到对应的文件。
SQL code
SELECT p1.value || '/' || p2.value || '_ora_' || p.spid filename
FROM v$process p, v$session s, v$parameter p1, v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV('SESSIONID');
⑧在ORACLE 9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，
减少DML操作时的操作。
以下就是开始索引监控与停止索引监控的脚本。
SQL code
SQL>set heading off
SQL>set echo off
SQL>set feedback off
SQL>set pages 10000
SQL>spool start_index_monitor.sql
SQL>SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
SQL>FROM dba_indexes
SQL>WHERE owner = USER;
SQL>spool off
set heading on
SQL>set echo on
SQL>set feedback on
----------------------------
SQL>set heading off
SQL>set echo off
SQL>set feedback off
SQL>set pages 10000
SQL>spool stop_index_monitor.sql
SQL>SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
SQL>FROM dba_indexes
SQL>WHERE owner = USER;
SQL>spool off
SQL>set heading on
SQL>set echo on
SQL>set feedback on
如果需要监控更多的用户，可以将owner=User改写成别的
监控结果在视图v$object_usage中查询。
3.其他方面
①根据实例来查看进程id。
SQL code
select spid
from v$process
where addr in (select paddr from v$session where sid = $sid)
②根据进程id来查看实例。
SQL code
select sid
from v$session
where paddr in (select addr from v$process where spid = $pid)
③查看当前在session中的sql文。
SQL code
select SQL_TEXT
from V$SQLTEXT
where HASH_VALUE = (select SQL_HASH_VALUE from v$session where sid = &sid)
order by PIECE
④查看v$session_wait。
SQL code
select *
from v$session_wait
where event not like 'rdbms%'
and event not like 'SQL*N%'
and event not like '%timer';
⑤Dictionary缓存的命中率。
SQL code
/*It should be about 15%, otherwise add share_pool_size*/
SELECT sum(getmisses) / sum(gets) FROM v$rowcache;
⑥利用文件号和数据块来查看DB中的各个对象。
SQL code
select owner,segment_name,segment_type
from dba_extents
where file_id = [$fno and &dno between block_id and block_id + blocks - 1 ]
⑦寻找hot block。
SQL code
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;
⑧找出每个文件上的等待事件。
SQL code
select df.name, kf.count
from v$datafile df, x$kcbfwait kf
where (kf.indx + 1) = df.file#;
⑨找出引起等待事件的SQL语句。
SQL code
select sql_text, c.event
from v$sqlarea a, v$session b, v$session_wait c
where a.address = b.sql_address
and b.sid = c.sid;
⑩判断你是从pfile启动还是spfile启动。
SQL code
SQL> select decode(count(*), 1, 'spfile', 'pfile' ) as DECODE
2 from v$spparameter
3 where rownum=1
4 and isspecified='TRUE';
DECODE
------
spfile
SQL>
扫一扫
6910
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
