oracle 索引重建提示指令碼
點選(此處)摺疊或開啟
-
REM =============================================================
-
REM
-
REM rebuild_indx.sql
-
REM
-
REM Copyright (c) Oracle Software, 1998 - 2000
-
REM
-
REM Author : Jurgen Schelfhout
-
REM
-
REM The sample program in this article is provided for educational
-
REM purposes only and is NOT supported by Oracle Support Services.
-
REM It has been tested internally, however, and works as documented.
-
REM We do not guarantee that it will work for you, so be sure to test
-
REM it in your environment before relying on it.
-
REM
-
REM This script will analyze all the indexes for a given schema
-
REM or for a subset of schema\'s. After this the dynamic view
-
REM index_stats is consulted to see if an index is a good
-
REM candidate for a rebuild or for a bitmap index.
-
REM
-
REM Database Version : 7.3.X and above.
-
REM
-
REM NOTE: If running this on 10g, you must exclude the
-
REM objects in the Recycle Bin
-
REM cursor c_indx is
-
REM select owner, table_name, index_name
-
REM from dba_indexes
-
REM where owner like upper(\'&schema\')
-
REM and table_name not like \'BIN$%\'
-
REM and owner not in (\'SYS\',\'SYSTEM\');
-
REM
-
REM Additional References for Recycle Bin functionality:
-
REM Note.265254.1 Flashback Table feature in Oracle Database 10g
-
REM Note.265253.1 10g Recyclebin Features And How To Disable it(_recyclebin)
-
REM
-
REM =============================================================
-
-
prompt
-
ACCEPT spoolfile CHAR prompt \'Output-file : \';
-
ACCEPT schema CHAR prompt \'Schema name (% allowed) : \';
-
prompt
-
prompt
-
prompt Rebuild the index when :
-
prompt - deleted entries represent 20% or more of the current entries
-
prompt - the index depth is more then 4 levels.
-
prompt Possible candidate for bitmap index :
-
prompt - when distinctiveness is more than 99%
-
prompt
-
-
spool &spoolfile;
-
set serveroutput on;
-
set verify off;
-
set linesize 140;
-
declare
-
c_name INTEGER;
-
ignore INTEGER;
-
height index_stats.height%TYPE := 0;
-
lf_rows index_stats.lf_rows%TYPE := 0;
-
del_lf_rows index_stats.del_lf_rows%TYPE := 0;
-
distinct_keys index_stats.distinct_keys%TYPE := 0;
-
cursor c_indx is
-
select owner, table_name, index_name
-
from dba_indexes
-
where owner like upper(\'&schema\')
-
and owner not in (\'SYS\', \'SYSTEM\');
-
begin
-
dbms_output.enable(1000000);
-
dbms_output.put_line(\'Owner Index Name % Deleted Entries Blevel Distinctiveness\');
-
dbms_output.put_line(\'-------------- --------------------------------- ------------ ----- -----\');
-
-
c_name := DBMS_SQL.OPEN_CURSOR;
-
for r_indx in c_indx loop
-
DBMS_SQL.PARSE(c_name,
-
\'analyze index \' || r_indx.owner || \'.\' ||
-
r_indx.index_name || \' validate structure\',
-
DBMS_SQL.NATIVE);
-
ignore := DBMS_SQL.EXECUTE(c_name);
-
-
select HEIGHT,
-
decode(LF_ROWS, 0, 1, LF_ROWS),
-
DEL_LF_ROWS,
-
decode(DISTINCT_KEYS, 0, 1, DISTINCT_KEYS)
-
into height, lf_rows, del_lf_rows, distinct_keys
-
from index_stats;
-
/*
-
- Index is considered as candidate for rebuild when :
-
- - when deleted entries represent 20% or more of the current entries
-
- - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
-
- Index is (possible) candidate for a bitmap index when :
-
- - distinctiveness is more than 99%
-
*/
-
if (height > 5) OR ((del_lf_rows / lf_rows) > 0.2) then
-
dbms_output.put_line(rpad(r_indx.owner, 16, \' \') ||
-
rpad(r_indx.index_name, 40, \' \') ||
-
lpad(round((del_lf_rows / lf_rows) * 100, 3),
-
17,
-
\' \') || lpad(height - 1, 7, \' \') ||
-
lpad(round((lf_rows - distinct_keys) * 100 /
-
lf_rows,
-
3),
-
16,
-
\' \'));
-
end if;
-
-
end loop;
-
DBMS_SQL.CLOSE_CURSOR(c_name);
-
end;
-
/
-
spool off;
-
set verify on;
-
-
[oracle@svn zxw]$ cat rebuild_indx.sql
-
REM =============================================================
-
REM
-
REM rebuild_indx.sql
-
REM
-
REM Copyright (c) Oracle Software, 1998 - 2000
-
REM
-
REM Author : Jurgen Schelfhout
-
REM
-
REM The sample program in this article is provided for educational
-
REM purposes only and is NOT supported by Oracle Support Services.
-
REM It has been tested internally, however, and works as documented.
-
REM We do not guarantee that it will work for you, so be sure to test
-
REM it in your environment before relying on it.
-
REM
-
REM This script will analyze all the indexes for a given schema
-
REM or for a subset of schema\'s. After this the dynamic view
-
REM index_stats is consulted to see if an index is a good
-
REM candidate for a rebuild or for a bitmap index.
-
REM
-
REM Database Version : 7.3.X and above.
-
REM
-
REM NOTE: If running this on 10g, you must exclude the
-
REM objects in the Recycle Bin
-
REM cursor c_indx is
-
REM select owner, table_name, index_name
-
REM from dba_indexes
-
REM where owner like upper(\'&schema\')
-
REM and table_name not like \'BIN$%\'
-
REM and owner not in (\'SYS\',\'SYSTEM\');
-
REM
-
REM Additional References for Recycle Bin functionality:
-
REM Note.265254.1 Flashback Table feature in Oracle Database 10g
-
REM Note.265253.1 10g Recyclebin Features And How To Disable it(_recyclebin)
-
REM
-
REM =============================================================
-
-
prompt
-
ACCEPT spoolfile CHAR prompt \'Output-file : \';
-
ACCEPT schema CHAR prompt \'Schema name (% allowed) : \';
-
prompt
-
prompt
-
prompt Rebuild the index when :
-
prompt - deleted entries represent 20% or more of the current entries
-
prompt - the index depth is more then 4 levels.
-
prompt Possible candidate for bitmap index :
-
prompt - when distinctiveness is more than 99%
-
prompt
-
-
spool &spoolfile;
-
set serveroutput on;
-
set verify off;
-
set linesize 140;
-
declare
-
c_name INTEGER;
-
ignore INTEGER;
-
height index_stats.height%TYPE := 0;
-
lf_rows index_stats.lf_rows%TYPE := 0;
-
del_lf_rows index_stats.del_lf_rows%TYPE := 0;
-
distinct_keys index_stats.distinct_keys%TYPE := 0;
-
cursor c_indx is
-
select owner, table_name, index_name
-
from dba_indexes
-
where owner like upper(\'&schema\')
-
and owner not in (\'SYS\', \'SYSTEM\');
-
begin
-
dbms_output.enable(1000000);
-
dbms_output.put_line(\'Owner Index Name % Deleted Entries Blevel Distinctiveness\');
-
dbms_output.put_line(\'-------------- --------------------------------- ------------ ----- -----\');
-
-
c_name := DBMS_SQL.OPEN_CURSOR;
-
for r_indx in c_indx loop
-
DBMS_SQL.PARSE(c_name,
-
\'analyze index \' || r_indx.owner || \'.\' ||
-
r_indx.index_name || \' validate structure\',
-
DBMS_SQL.NATIVE);
-
ignore := DBMS_SQL.EXECUTE(c_name);
-
-
select HEIGHT,
-
decode(LF_ROWS, 0, 1, LF_ROWS),
-
DEL_LF_ROWS,
-
decode(DISTINCT_KEYS, 0, 1, DISTINCT_KEYS)
-
into height, lf_rows, del_lf_rows, distinct_keys
-
from index_stats;
-
/*
-
- Index is considered as candidate for rebuild when :
-
- - when deleted entries represent 20% or more of the current entries
-
- - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
-
- Index is (possible) candidate for a bitmap index when :
-
- - distinctiveness is more than 99%
-
*/
-
if (height > 5) OR ((del_lf_rows / lf_rows) > 0.2) then
-
dbms_output.put_line(rpad(r_indx.owner, 16, \' \') ||
-
rpad(r_indx.index_name, 40, \' \') ||
-
lpad(round((del_lf_rows / lf_rows) * 100, 3),
-
17,
-
\' \') || lpad(height - 1, 7, \' \') ||
-
lpad(round((lf_rows - distinct_keys) * 100 /
-
lf_rows,
-
3),
-
16,
-
\' \'));
-
end if;
-
-
end loop;
-
DBMS_SQL.CLOSE_CURSOR(c_name);
-
end;
-
/
-
spool off;
- set verify on
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27036311/viewspace-1068278/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle重建索引Oracle索引
- oracle重建controlfile指令碼Oracle指令碼
- oracle 索引分析及索引重建Oracle索引
- oracle重建索引(一)Oracle索引
- oracle重建索引(三)Oracle索引
- oracle重建索引(二)Oracle索引
- oracle批量重建索引方法Oracle索引
- Oracle表與索引的分析及索引重建Oracle索引
- 淺談oracle中重建索引 (ZT)Oracle索引
- Oracle表與索引的分析及索引重建(轉)Oracle索引
- ORACLE分析表和索引的指令碼Oracle索引指令碼
- oracle 索引什麼時候重建和重建方法討論Oracle索引
- 重建重定位表指令碼指令碼
- Oracle 表的移動和索引的重建Oracle索引
- 索引的重建命令索引
- sqlserver 全部索引重建SQLServer索引
- MSSQL Rebuild(重建)索引SQLRebuild索引
- 【TUNE_ORACLE】索引定期重建的利與弊Oracle索引
- 關於 Oracle 分割槽索引的失效和重建Oracle索引
- 淺談索引系列之索引重建索引
- Oracle指令碼(Oracle Scripts) – 檢視索引訪問次數及索引訪問型別Oracle指令碼索引型別
- 自動重建失效index的shell指令碼Index指令碼
- 關於oracle的索引重建問題及原因分析Oracle索引
- oracle重建索引的一些參考性依據Oracle索引
- Oracle 指令碼 線上哪些索引從來沒有被使用過Oracle指令碼索引
- Oracle 11g重建控制檔案——如何獲取建立控制檔案指令碼Oracle指令碼
- oracle10g_備份控制檔案_得到重建控制檔案的指令碼Oracle指令碼
- 批量重建不可用索引索引
- 指令碼::Vim進階索引[3]指令碼索引
- 找出冗餘索引的指令碼索引指令碼
- 教你如何在 elasticsearch 中重建索引Elasticsearch索引
- 索引重建的資料來源索引
- 批量匯出建立索引的指令碼索引指令碼
- 表及索引 move tablespace 常用指令碼索引指令碼
- 通過impdp匯出索引指令碼索引指令碼
- Oracle表table與索引index的分析及索引重建及統計資訊匯入匯出Oracle索引Index
- AM右鍵新易髮選單重建bat指令碼BAT指令碼
- oracle重建ocrOracle