逐步讲解 Oracle数据库碎片如何整理

发布网友发布时间：2022-04-29 21:46

共2个回答

懂视网时间：2022-04-30 02:07

x1 as (select userid,startdate,coalesce(min(case when type=-1 then add_months(startdate,-1) else startdate end) over(partition by userid order by vid rows between 1 following and unbouded following),startdate+1) as minsdate, enddate as orgenddate, case when enddate is null and (lead(type) over(partition by userid order by vid))=-1 then add_months((lead(startdate) over(partition by userid order by vid)),-1) else enddate end as enddate,type,vid, max(vid) over(partition by userid) as max_id from test, x2 as (select userid,startdate,minsdate,enddate,type,vid,max_id, case when (lag(enddate) over(partition by userid order by vid))<add_months(startdate,-1) then 1 when (lag(type) over(partition by userid order by vid))=1 then null else 1 end as so from x1), x3 as (select userid,vid,max_id,type,sum(so) over(partition by userid order by vid) as so, startdate,minsdate, case when minsdate<enddate and minsdate>=startdate then minsdate else enddate end as enddate from x2 where type=1 and startdate<=minsdate), x4 as (select userid,max_id,max(vid) as max_id2,sum(type) as type, min(startdate) keep(dense_rank first order by vid) as startdate, max(enddate) keep(dense_rank last order by vid) as enddate from x3) select userid,to_char(startdate,‘yyyymm‘)||‘--‘||coalesce(to_char(enddate,‘yyyymm‘),‘NULL‘) as rangeSpace from x4 where (max_id=max_id2 or startdate<=enddate) and type>-1;

Oracle数据整理

标签：

热心网友时间：2022-04-29 23:15

对于系统管理员来讲，如何保证网络稳定运行，如何提高数据库性能，使其更加安全高效，就显得尤为重要。作为影响数据库性能的一大因素 -- 数据库碎片，应当引起 DBA 的足够重视，及时发现并整理碎片乃是 DBA 一项基本维护内容。 1、碎片是如何产生的当生成一个数据库时，它会分成称为表空间（ Tablespace ）的多个逻辑段（ Segment ），如系统（System）表空间 , 临时（Temporary）表空间等。一个表空间可以包含多个数据范围（Extent）和一个或多个自由范围块，即自由空间（Free Space）。表空间、段、范围、自由空间的逻辑关系如下：当表空间中生成一个段时，将从表空间有效自由空间中为这个段的初始范围分配空间。在这些初始范围充满数据时，段会请求增加另一个范围。这样的扩展过程会一直继续下去，直到达到最大的范围值，或者在表空间中已经没有自由空间用于下一个范围。最理想的状态就是一个段的数据可被存在单一的一个范围中。这样，所有的数据存储时靠近段内其它数据，并且寻找数据可少用一些指针。但是一个段包含多个范围的情况是大量存在的，没有任何措施可以保证这些范围是相邻存储的，当要满足一个空间要求时，数据库不再合并相邻的自由范围（除非别无选择），而是寻找表空间中最大的自由范围来使用。这样将逐渐形成越来越多的离散的、分隔的、较小的自由空间，即碎片。例如： 2、碎片对系统的影响随着时间推移，基于数据库的应用系统的广泛使用，产生的碎片会越来越多，将对数据库有以下两点主要影响： 1）导致系统性能减弱。如上所述，当要满足一个空间要求时，数据库将首先查找当前最大的自由范围，而 “最大”自由范围逐渐变小，要找到一个足够大的自由范围已变得越来越困难，从而导致表空间中的速度障碍，使数据库的空间分配愈发远离理想状态； 2）浪费大量的表空间。尽管有一部分自由范围（如表空间的 pctincrease 为非 0 ）将会被 SMON （系统监控）后台进程周期性地合并，但始终有一部分自由范围无法得以自动合并，浪费了大量的表空间。 3、自由范围的碎片计算由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可用 FSFI--Free Space Fragmentation Index （自由空间碎片索引）值来直观体现： FSFI=100*SQRT(max(extent)/sum(extents))*1/SQRT(SQRT(count(extents))) 可以看出， FSFI 的最大可能值为 100 （一个理想的单文件表空间）。随着范围的增加， FSFI 值缓慢下降，而随着最大范围尺寸的减少， FSFI 值会迅速下降。下面的脚本可以用来计算 FSFI 值： rem FSFI Value Compute rem fsfi.sql column FSFI format 999,99 select tablespace_name,sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))) FSFI from dba_free_space group by tablespace_name order by 1; spool fsfi.rep; / spool off;比如，在某数据库运行脚本 fsfi.sql, 得到以下 FSFI 值： TABLESPACE_NAME FSFI ------------------------------------- RBS 74.06 SYSTEM 100.00 TEMP 22.82 TOOLS 75.79 USERS 100.00 USER_TOOLS 100.00 YDCX_DATA 47.34 YDCX_IDX 57.19 YDJF_DATA 33.80 YDJF_IDX 75.55统计出了数据库的 FSFI 值，就可以把它作为一个可比参数。在一个有着足够有效自由空间，且FSFI 值超过 30 的表空间中，很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时，就需要做碎片整理了。 4、自由范围的碎片整理1）表空间的 pctincrease 值为非 0。可以将表空间的缺省存储参数 pctincrease 改为非 0 。一般将其设为 1 ，如： alter tablespace temp default storage(pctincrease 1);这样SMON 便会将自由范围自动合并。也可以手工合并自由范围： alter tablespace temp coalesce。 5、段的碎片整理我们知道，段由范围组成。在有些情况下，有必要对段的碎片进行整理。要查看段的有关信息，可查看数据字典 dba_segments ，范围的信息可查看数据字典 dba_extents 。如果段的碎片过多，将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建，然后将旧表中的数据插入到新表，同时删除旧表。这个过程可以用 Import/Export （输入 / 输出）工具来完成。 Export （）命令有一个（压缩）标志，这个标志在读表时会引发 Export 确定该表所分配的物理空间量，它会向输出转储文件写入一个新的初始化存储参数 -- 等于全部所分配空间。若这个表关闭，则使用 Import （）工具重新生成。这样，它的数据会放入一个新的、较大的初始段中。例如： exp user/password file=exp.dmp compress=Y grants=Y indexes=Y tables=(table1,table2);若输出成功，则从库中删除已输出的表，然后从输出转储文件中输入表： imp user/password file=exp.dmp commit=Y buffer=64000 full=Y 这种方法可用于整个数据库。以上简单分析了 Oracle 数据库碎片的产生、计算方法及整理，仅供参考。数据库的性能优化是一项技术含量高，同时又需要有足够耐心、认真细致的工作。对数据库碎片的一点探讨，下面是一种如何自动处理表空间碎片的代码，希望对上大家看上文有用 Coalesce Tablespace Automatically This technique comes from Sandeep Naik, a database administrator for GSXXI, Inc. in New York City, New York Here is a handy script which can be scheled to automatically run and coalesces the tablespaces. This script is designed to run in NT but can be run in any operating system by slight modifications in the path where the file spools from the SQLPLUS environment. It assumes that the user who runs the script has priviledges to view the data dictionary. Start of code -------------------------------------- sqlplus / prompt this script will coalesce the tablespace automatically set verify off; set termout off; set head off; spool c: empcoalesce.log select alter tablespace ||TABLESPACE_NAME|| coalesce ; from DBA_FREE_SPACE_COALESCED where PERCENT_EXTENTS_COALESCED