`

如何找出Oracle中需要或值得重建的索引

阅读更多
This script determines whether an index is a good candidate for a rebuild or for
a bitmap index.  All indexes for a given schema or for a subset of schema’s are
analyzed (except indexes under SYS and SYSTEM)

Product Name, Product Version
Oracle Server, 7.3.x to 10g
Platform     Platform Independent
Date Created     15-Feb-2001

Instructions

Execution Environment:
<SQL, SQL*Plus, iSQL*Plus>

Access Privileges:
Requires DBA privileges in order to be executed.

Usage:
sqlplus <user>/<pw> @ind_an

Instructions:
Copy the script into the file ind_an.sql. Execute the script from SQL*Plus connected
with a user with DBA privileges.  The script requires to parameters:

1. Name of the output file where the report while be generated
2. Name of the SCHEMA to be analyzed.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.

Description

This script determines whether an index is a good candidate for a rebuild or for
a bitmap index.  All indexes for a given schema or for a subset of schema’s are
analyzed (except indexes under SYS and SYSTEM).

Sample Output
————-

Owner           Index Name                % Deleted Entries Blevel Distinctivenes
————— ————————- —————– —— ————-
SMS             PLATMAP_I1                           31.504      1        32.927
SMS             PLATMAP_I2                           27.682      1        29.399
SMS             PLATMAP_I3                           31.237      1        31.237
SMS             PRODMAP_I4                            8.765      5          99.9
SMS             SB_CR_BOM_ITEMS_I1                   34.496      2        97.356
SMS             SB_CR_OS_VERSIONS_I1                 51.942      1        68.063
SMS             SB_CR_RELEASES_I1                    34.584      1        18.426
SMS             TAR_HEAD_I2                          21.728      5        22.344

Rebuild the index when:

- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.

Possible candidate for bitmap index:

- when distinctiveness is more than 99%

References

SQL Reference Guide

Script

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
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

Disclaimer

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN “AS IS” AND “AS AVAILABLE” BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

Limitation of Liability

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.

摘自 metalink
http://www.youyus.com/?p=288

分享到:
评论

相关推荐

    浅谈oracle中重建索引

    ### 浅谈Oracle中重建索引 #### 一、索引的基本概念与作用 在数据库管理系统(DBMS)中,索引是一种特殊的数据结构,它能够加速数据检索的速度。Oracle数据库同样提供了强大的索引功能来优化查询性能。索引通过在...

    oracle索引的建立、修改、删除.doc

    提示:DBA 经常用 REBUILD 来重建索引可以减少硬盘碎片和提高应用系统的性能。Oracle8i 以后的新功能可以对索引的无用空间进行合并,它由下面命令完成:ALTER INDEX [user.]index COALESCE; 例如:ALTER INDEX ...

    Oracle数据库导入导出

    2. `full=y` 导入整个数据库,`ignore=y` 忽略导入过程中的错误,`indexes=n` 表示不重建索引。 3. `fromuser` 和 `touser` 参数用于指定源用户和目标用户。 4. `TABLES=(table1, table2)` 参数用于导入指定的表。 ...

    oracle坏块处理

    ### Oracle坏块处理详解 ...值得注意的是,对于发生在回滚段、系统表空间或在线重做日志的坏块,修复难度极大,此时可能需要Oracle技术支持的介入。维护好数据库的健康状态,及时应对各种潜在威胁,是每个DBA的责任。

    Oracle常见问题的解决方法

    2. **查询受影响的段**:使用`dba_extents`视图来找出坏块所在的段。执行如下查询: ```sql SELECT segment_name, segment_type, block_id, blocks FROM dba_extents WHERE (1234 BETWEEN block_id AND (block_...

    Oracle数据库整理表碎片

    3. **使用脚本检查特定模式下的表碎片**:还可以使用特定脚本来找出某个Schema中表碎片超过25%的表。例如,可以先查看表最后一次收集统计信息的时间: ```sql select table_name, last_analyzed from dba_tables ...

    Oracle10g对回滚操作时间的准确评估

    值得注意的是,V$SESSION_LONGOPS 视图不仅适用于回滚操作,还涵盖了其他长时间运行的数据库操作,如大表的DML操作或大段的索引重建。这为数据库管理员提供了更全面的监控和性能优化工具。 通过利用 Oracle 10g 中...

    Oracle 大数据量操作优化

    因为在更新过程中,索引能够提供快速查询的能力,索引重建导致的性能下降相比于索引提供的查询速度提升而言可能是微不足道的。 2. **批量更新与提交** - 实施批量更新并在每次更新一定数量的记录后立即提交。这种...

    oracle dba 面试题目及答案

    要找出执行时间最长的前10条SQL语句,可以使用以下SQL语句: ```sql SELECT * FROM ( SELECT b.username username, a.disk_reads / DECODE(a.executions, 0, 1, a.executions) rds_exec_ratio, a.sql_text ...

    ORACLE数据库重组技术作业文档.docx

    评估索引是否需要重组的一个关键指标是其质量,这在DB02中可以查看。如果索引质量低于50%,则建议进行重组。RSANAORA程序在SE38中可用,也可作为后台作业执行,用于索引重组。例如,在PRD系统的MSEG表中,对多个索引...

    oracle优化

    6. **SQL执行计划分析**:通过EXPLAIN PLAN或DBMS_XPLAN可以查看SQL执行计划,理解数据库如何执行查询,找出性能瓶颈。 7. **绑定变量**:使用绑定变量可以避免硬解析,提高SQL语句的重用性,从而提升性能。 8. **...

    oracle数据库整体优化方案全

    - 通过重建表或增加数据块大小的方式来解决链接行问题。 3. **优化索引存储参数**: - 调整索引的初始化参数,例如INITRANS和PCTFREE等,以改善索引性能。 #### 结论 通过对Oracle数据库进行全方位的优化,可以...

    oracle数据恢复工具。

    值得注意的是,虽然AUL 5.0提供了一种强大的逻辑恢复手段,但物理数据恢复(如磁盘损坏)可能还需要其他工具,如RMAN(Recovery Manager)或第三方专业工具。同时,最佳实践是在日常运营中定期进行数据库备份,以...

    Oracle内置包的使用方法

    例如,DBMS_INDEX.CREATE_INDEX允许我们在运行时动态创建索引,而DBMS_INDEX.REBUILD则可以用来重建索引,优化其性能。 对于数据操作,DBMS_SQL包是一个不可或缺的工具。它允许我们动态执行SQL语句,非常适合处理不...

    Oracle数据的导入和导出.pdf

    Oracle数据库的导入和导出是数据库管理中的关键操作,用于数据迁移、备份和恢复。本文主要探讨了Oracle数据库中Exp(导出)和Imp(导入)工具的使用方法及其注意事项。 Exp工具允许管理员将数据库中的数据和结构...

    三思笔记\ORACLE9I服务器的一次调优实施过程.pdf

    - **问题**: 通常认为只有相异值较高的列才值得创建索引,但实际上,在某些场景下即使是相异值较低的列创建索引也能带来性能上的改善。 - **解决方案**: 对于低相异值的列创建索引,并利用覆盖索引等技术减少数据...

    PeopleSoft Enterprise Performance on Oracle 10G

    1. **索引重构**:定期重建索引可以消除碎片,优化存储结构,提高查询效率。 2. **基于函数的索引**:基于函数的索引适用于复杂查询,可以减少函数调用次数,提高查询速度。 六、游标共享:减少资源消耗 1. **...

    Oracle降低高水位的方法

    每当向表中插入、更新或删除数据时,Oracle数据库会自动管理数据的存储位置。值得注意的是,删除操作不会立即释放空间,而是将其标记为“可重用”。这意味着即使某些数据行被删除,它们占用的空间也不会立即返回给...

    Oralce 12.1 DB performence

    ### Oracle 12.1 数据库性能调优...然而值得注意的是,每个组织的具体情况可能不同,因此在实施任何变更之前都需要充分评估其潜在影响。此外,随着Oracle后续版本的发布,新的特性和改进也将为性能调优带来更多可能性。

Global site tag (gtag.js) - Google Analytics