資料庫監控指令碼（二）

資料庫監控指令碼（二）

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-733947/，如需轉載，請註明出處，否則將追究法律責任。