数据库监控脚本（二）

 

 

--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就很快的了。