分区索引碎片整理Script

最新推荐文章于 2025-05-19 16:46:55 发布
最新推荐文章于 2025-05-19 16:46:55 发布
阅读量815 收藏
点赞数
分类专栏： SCRIPT
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/trsenzhang/article/details/35292255
版权
本文介绍了一种通过查询sys和system用户的视图来评估Oracle数据库中索引的效率，并提供了具体的SQL语句来创建和更新一张用于监测索引状态的表。通过执行这些步骤，可以有效地识别出需要进行优化的索引。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

第一步、在sys、system用户下执行查询语句并插入到自己创建的表里
create table monitor.test
SELECT /*ORACONF*/ /*+ ordered */
 u.name,
 o.name index_name,
 o.subname partition_name,
 i.blevel,
 to_char(100 * (1 - floor(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                                 isp.obj#,
                                 isp.leafcnt,
                                 ip.obj#,
                                 ip.leafcnt,
                                 i.leafcnt) -
                          decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                                 isp.obj#,
                                 isp.rowcnt,
                                 ip.obj#,
                                 ip.rowcnt,
                                 i.rowcnt) * (SUM(h.avgcln) + 10) /
                          ((p.value - 66 -
                           decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                                   isp.obj#,
                                   isp.initrans,
                                   ip.obj#,
                                   ip.initrans,
                                   i.initrans) * 24) *
                           (1 - decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                                       isp.obj#,
                                       isp.pctfree$,
                                       ip.obj#,
                                       ip.pctfree$,
                                       i.pctfree$) / 100))) /
         decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                           isp.obj#,
                           isp.leafcnt,
                           ip.obj#,
                           ip.leafcnt,
                           i.leafcnt)),
         '999.00') || '%' density,
 floor(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
              isp.obj#,
              isp.leafcnt,
              ip.obj#,
              ip.leafcnt,
              i.leafcnt) -
       decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
              isp.obj#,
              isp.rowcnt,
              ip.obj#,
              ip.rowcnt,
              i.rowcnt) * (SUM(h.avgcln) + 10) /
       ((p.value - 66 - decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                               isp.obj#,
                               isp.initrans,
                               ip.obj#,
                               ip.initrans,
                               i.initrans) * 24) *
        (1 - decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                    isp.obj#,
                    isp.pctfree$,
                    ip.obj#,
                    ip.pctfree$,
                    i.pctfree$) / 100))) extra_blocks,
 MAX(o.mtime) last_ddl_time,
 decode(MAX(nvl(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                       isp.obj#,
                       isp.analyzetime,
                       ip.obj#,
                       ip.analyzetime,
                       i.analyzetime),
                to_date('01.01.1900', 'dd.mm.yyyy'))),
        to_date('01.01.1900', 'dd.mm.yyyy'),
        'not analyzed',
        MAX(nvl(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                       isp.obj#,
                       isp.analyzetime,
                       ip.obj#,
                       ip.analyzetime,
                       i.analyzetime),
                to_date('01.01.1900', 'dd.mm.yyyy')))) last_analyzed,
 MAX(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
            isp.obj#,
            'N/A',
            ip.obj#,
            decode(bitand(ip.flags, 1024),
                   0,
                   'DISABLED',
                   1024,
                   'ENABLED',
                   'N/A'),
            decode(bitand(i.flags, 32), 0, 'DISABLED', 32, 'ENABLED', 'N/A'))) compression
  FROM sys.ind$ i,
       sys.icol$ ic,
       (SELECT obj#,
               part#,
               bo#,
               ts#,
               rowcnt,
               leafcnt,
               initrans,
               pctfree$,
               analyzetime,
               flags
          FROM sys.indpart$
        UNION ALL
        SELECT /*ORACONF*/
         obj#,
         part#,
         bo#,
         defts#,
         rowcnt,
         leafcnt,
         definitrans,
         defpctfree,
         analyzetime,
         flags
          FROM sys.indcompart$) ip,
       sys.indsubpart$ isp,
       (SELECT ts#, blocksize VALUE FROM sys.ts$) p,
       sys.hist_head$ h,
       sys.obj$ o,
       sys.user$ u,
       sys.obj$ op
 WHERE i.obj# = ip.bo#(+)
   AND ip.obj# = isp.pobj#(+)
   AND decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
              isp.obj#,
              isp.leafcnt,
              ip.obj#,
              ip.leafcnt,
              i.leafcnt) > 1
   AND i.type# IN (1)
   AND -- exclude special types
       i.pctthres$ IS NULL
   AND -- exclude IOT secondary indexes
       decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
              isp.obj#,
              isp.ts#,
              ip.obj#,
              ip.ts#,
              i.ts#) = p.ts#
   AND ic.obj# = i.obj#
   AND h.obj# = i.bo#
   AND h.intcol# = ic.intcol#
   AND o.obj# = nvl(isp.obj#, nvl(ip.obj#, i.obj#))
   AND o.owner# NOT IN (SELECT USER#
                          FROM sys.user$
                         WHERE NAME IN ('ANONYMOUS',
                                        'AURORA$',
                                        'AURORA',
                                        'CTXSYS',
                                        'DBSNMP',
                                        'DIP',
                                        'DMSYS',
                                        'DVF',
                                        'DVSYS',
                                        'EXFSYS',
                                        'HR',
                                        'LBACSYS',
                                        'MDDATA',
                                        'MDSYS',
                                        'MGMT_VIEW',
                                        'ODM',
                                        'ODM_MTR',
                                        'OE',
                                        'OLAPSYS',
                                        'ORACLE_OCM',
                                        'ORAWSM',
                                        'ORDPLUGINS',
                                        'ORDSYS',
                                        'OSE',
                                        'OUTLN',
                                        'PERFSTAT',
                                        'PM',
                                        'QS',
                                        'QS_ADM',
                                        'QS_CB',
                                        'QS_CBADM',
                                        'QS_CS',
                                        'QS_ES',
                                        'QS_OS',
                                        'QS_WS',
                                        'REPADMIN',
                                        'SCOTT',
                                        'SH',
                                        'SI_INFORMTN_SCHEMA',
                                        'SYS',
                                        'SYSMAN',
                                        'SYSTEM',
                                        'TRACESVR',
                                        'TSMSYS',
                                        'WKPROXY',
                                        'WKSYS',
                                        'WK_TEST',
                                        'WKUSER',
                                        'WMSYS',
                                        'XDB'))
   AND u.user# = o.owner#
   AND op.obj# = nvl(ip.obj#, i.obj#)
 GROUP BY u.name,
          o.name,
          o.subname,
          i.blevel,
          decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                 isp.obj#,
                 isp.rowcnt,
                 ip.obj#,
                 ip.rowcnt,
                 i.rowcnt),
          decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                 isp.obj#,
                 isp.leafcnt,
                 ip.obj#,
                 ip.leafcnt,
                 i.leafcnt),
          decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                 isp.obj#,
                 isp.initrans,
                 ip.obj#,
                 ip.initrans,
                 i.initrans),
          decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)),
                 isp.obj#,
                 isp.pctfree$,
                 ip.obj#,
                 ip.pctfree$,
                 i.pctfree$),
          p.value
HAVING 100 * (1 - floor(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt) - decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt) * (SUM(h.avgcln) + 10) / ((p.value - 66 - decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans) * 24) * (1 - decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$) / 100))) / decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt)) <= nvl('75', '75') AND floor(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt) - decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt) * (SUM(h.avgcln) + 10) / ((p.value - 66 - decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans) * 24) * (1 - decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$) / 100))) > 0
 ORDER BY 6 DESC, 5;

第二步、向monitor.test表添加相关标识字段并插入信息
alter table monitor.test add bytes NUMBER;
alter table monitor.test add segment_type varchar2(50);
alter table monitor.test add tablespace_name varchar2(50);

Merge into  monitor.test m
using dba_segments a
on (a.owner= m.name and a.segment_name = m.index_name and nvl(a.partition_name,'NULL_ORG')=nvl(m.partition_name,'NULL_ORG') )
when matched then
update set m.segment_type = a.segment_type,
m.tablespace_name = a.tablespace_name,
m.bytes = a.BYTES;

第三步、生成执行脚本，这里注意选择前一个月分区之前的信息
update SPARSE_INDEX_20140627 set density=replace(density, '%','')

select ' alter index '||name||'.'||index_name||' rebuild '
||case when a.segment_type = 'INDEX PARTITION' then ' PARTITION ' when a.segment_type= 'INDEX SUBPARTITION' then ' SUBPARTITION ' else '' end
||partition_name||' online tablespace '||a.tablespace_name||';', a.partition_name, a.density,a.bytes/1024/1024 "MB"
from test a
where a.density < 50
and a.partition_name <= 'PAR_2014_05'
order by a.partition_name, a.density

第四步、将脚本放到shell里去执行

mysql 碎片率_MySQL表碎片整理
weixin_39973518的博客
12-22 197
# cat optimize_table.sh#!/bin/shsocket=/tmp/mysql3306.socktime=`date +"%Y-%m-%d"`SQL="select concat(d.TABLE_SCHEMA,'.',d.TABLE_NAME) from information_schema.TABLES d where d.TABLE_SCHEMA = 'employees'...
表空间碎片较多
夜澜偶作庄周梦 酒后聊为楚客狂
06-19 5276
Oralce Database Health Check (Performance)--------------------------------------------------------------------------------List Contents1. 数据库概要 2. 参数文件(是spfile还是pfile) 3. 非默认的参数 4. 控制文件及其状态 5. 表空间及数据文
oracle数据表碎片整理
01-10
oracle数据表碎片整理 alter table shrink space [ | compact | cascade ];
分区表碎片整理（move）
Du.的博客
04-04 952
1. 当前执行存储过程的用户下建立表MON_SEGMENT_USAGE CREATE TABLE MON_SEGMENT_USAGE(owner varchar2(20),segment_name varchar2(40),segment_type varchar2(40),total_bytes number(20),full_bytes number(20),pct_used number(10),created_date date); 2. 若表MON_SEGMENT_USAGE已经存在，在执行存
shrink回收分区表碎片
congjiu2607的博客
11-17 281
实验如下： CREATE TABLE "SCOTT"."T4" ( "A" NUMBER, "B" NUMBER ) PARTITION BY RANGE ("A") (P...
MySQL之表碎片化
AaronCao
06-23 3152
因为删除表数据发现表使用空间未被释放，继而发现有表碎片问题，查找一些资料去了解表碎片的产生以及表碎片的处理，最终让自己学习到了关于InnoDB表碎片相关的知识。
涨薪技术|0到1学会性能测试第70课-调校索引&索引维护
最新发布
m0_60889254的博客
05-19 738
引可以提高查询速度，但并不是说索引没有缺点，当表中的记录在不断的增多时，索引可能就会发生很多问题，随着不断的插入记录、删除记录和更新记录，索引的数据分布可能会不平衡，一些页会被完全充满，这样再插入记录时会导致页拆分的现象，这样其它页中的行可能会变得稀疏，而使用几行数据就会引发很多页被读取的现象，这样就必须对索引进行维护。一般情况下不需要监控那么多列的值，只需要常用的几列值即可：index_id、index_level、avg_fragmention_in_percent，代码如下：​​​​​​​。
2021最新整理JAVA常见面试题附答案
weixin_47345084的博客
01-03 7058
包含的模块： 本文分为十九个模块，分别是：Java 基础、容器、多线程、反射、对象拷贝、Java Web 、异常、网络、设计模式、Spring/Spring MVC、Spring Boot/Spring Cloud、Hibernate、MyBatis、RabbitMQ、Kafka、Zookeeper、MySQL、Redis、JVM需要的可以【点击这里领取完整版的面试题~暗号优快云】 如下图所示： 共包含 208 道面试题，本文的宗旨是为读者朋友们整理一份详实而又权威的面试清单，下面一起进入主题吧。 ==
索引优化指南：避免常见的MySQL索引错误
## 1.1 什么是索引？ 索引是数据库中用于提高查询速度和性能的一种特殊数据结构。在没有索引的情况下，数据库必须执行全表扫描来找到匹配的行。随着数据量的增加，全表扫描的效率会显著下降。通过索引，数据库可以...
PHP 面试知识点整理归纳
PHP学习日志——地雷
09-05 1万+
全文已整理补充完毕，以后还会继续更新文章里面的错误，以及补充尚不完善的问题。 该篇文章是针对Github上wudi/PHP-Interview-Best-Practices-in-China资源的答案个人整理 lz也是初学者，以下知识点均为自己整理且保持不断更新，也希望各路大神多多指点，若发现错误或有补充，可直接comment，lz时刻关注着。 由于内容比较多，没有直接目录，请自行对照 Gi...
mysql 整理碎片_mysql 表碎片整理
weixin_36452340的博客
01-18 479
查看数据库中表、索引和碎片大小的大小：select round(sum(data_length/1024/1024),2) as data_length_MB,round(sum(index_length/1024/1024),2) as index_length_MB ,round(sum(data_free/1024/1024),2) as data_free_MB ,table_name...
mysql碎片优化_mysql 表碎片优化
weixin_27219587的博客
02-28 125
mysql 怎样统计分组数mysql统计分组数的方法：1、统计总记录数【SELECT count(*) FROM 表名 WHERE 条件】；2、统计每组的记录条数【SELECT count(*) FROM 表名 WHERE 条件 GROUP BY id】。 mysql统计分组数的方法： SELECT count(*) FROM 表名 WHERE 条件 这样查出来的是总记录数mysql数据意外删了怎...
oracle 中sql的分类,Oracle数据库的段类型详解
weixin_31659095的博客
04-02 371
【IT168技术文档】Oracle数据库中有多少类型的段，除了常见的TABLE、INDEX之外还有哪些?下面通过Oracle9i的数据字典来探讨Oracle的段类型。SQL> select distinctsegment_type from dba_segments;SEGMENT_TYPE---------------------------------------------------...
Oracle表空间碎片查询以及整理
雨花石
09-21 9234
一、查看表空间碎片的方法 查看fsfi值 /* Formatted on 2017/9/21 13:42:29 (QP5 v5.240.12305.39476) */   SELECT a.tablespace_name,          TRUNC (               SQRT (MAX (blocks) / SUM (blocks))             *
ORACLE表空间和表碎片分析及整理方法
热门推荐
贤时间
01-27 3万+
表空间碎片率idle> select a.tablespace_name,sqrt(max(a.blocks)/sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks)))) FSFI from dba_free_space a,dba_tablespaces b where a.tablespace_name=b.tablespace_name and b.con
Oracle 表碎片检查及整理方案
JiekeXu的博客
03-15 4725
作者 | JiekeXu来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 表...
Oracle表的空间释放，碎片整理记录
ggwxk1990的博客
09-06 1万+
一、表的占用空间大小释放原理 背景： 生产环境中，经常会遇到表由于数据不断插入，导致空间越来越大，由于前期配置问题，没有做分区或者其他优化，而且生产数据实时向表插入。要删除历史数据来释放空间。 分析： （1）没做分区表： 由于没有分区表，所以无法分块对表进行清理。 （2）表实时插入数据，数据量大，要保留部分数据： 无法采用将有效数据插入另一个表，drop原表，修改名字来删除原表，或者
oracle 表的碎片化整理
分支的博客
11-18 8076
数据库在日常的使用过程中，不断的insert，delete，update，导致表和索引出现碎片。这会导致HWM之前有很多的空闲空间，而oracle在做全表扫描的时候会读取HWM一下的所有块，这样会产生更多的IO，影响性能。 oracle提供了shrink space碎片整理的功能，对于索引要采取rebuild online的方式进行碎片整理。 实验： 1.创建一张表，数据量50w，并创建索引
深入浅出Oracle数据库调优技巧
- 定期执行维护任务：如数据库碎片整理、索引重建和数据压缩等。 6. Oracle 11g新增优化特性： - 自动内存管理（AMM）：取代手动SGA和PGA调整，简化内存管理。 - 自动工作负载存储库（AWR）报告：通过自动收集...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值