#为什么35岁以上的人容易失业?# #数据库##Oracle##IT# #程序员##职场##学习##技能改变生活#

在Oracle数据库中，表碎片是常见的性能问题之一。频繁的插入、更新和删除操作会导致表空间中的数据变得分散，从而影响查询性能。本文将详细介绍如何使用导入导出（expdp 和 impdp）的方式来整理表碎片，以提高数据库性能。

一、 什么是表碎片？

表碎片是指表的数据块在物理存储上不连续分布的情况。当表中的数据被频繁地插入、更新或删除时，可能会导致数据块之间的空隙，这些空隙会浪费存储空间并降低I/O性能。

二、 为什么需要整理表碎片？

• 提高查询性能：减少I/O操作次数，加快查询速度。
• 节省存储空间：释放由于碎片导致的未使用空间。
• 优化维护操作：简化备份和恢复操作，提高维护效率。

三、 检测表碎片

在进行碎片整理之前，首先需要检测表是否存在碎片。

3.1 使用以下SQL查询来检查表的碎片情况：

Plain TextJavaScriptPythonJavaGoRubyTypeScriptHTMLCSSC/C++PHPC#ScalaShellDartRustKotlinSwiftPowerShellGroovy-ABAPActionScriptAdaAppleScriptArduinoASP.NET (C#)AtomAutoHotkeyAutoItBashBASICBatchClojureCoffeeScriptDDiffDockerEditorConfigElixirElmERBErlangF#FortranGraphQLHaskellHaxeJSONJuliaWeb App ManifestLaTeXTeX ConTeXtLessLispLiveScriptLuaMakefileMarkdownMarkup templatingMathematicaMathMLMATLABNASMnginxObjective-COCamlObject PascalPascalPerlPL/SQLPrologPuppetPureScriptQMLRRacketReact JSXReact TSXReasonreStructuredTextRSSSass (Sass)Sass (Scss)SmalltalkSplunk SPLSQLSSMLSVGTclTextileTypoScriptValaVB.NetVerilogVHDLvimVBAVisual BasicWiki markupYAMLXQueryXML
-- 查看表的HWM（高水位线）和实际使用的容量
SELECT TABLE_NAME,
 trunc(ROUND ((blocks * 8), 2)/1024,2) "High water levelM",
 trunc(ROUND ((num_rows * avg_row_len / 1024), 2)/1024,2) "Real used spaceM",
 trunc(ROUND ((blocks * 10 / 100) * 8, 2)/1024,2) "Reserve space(pctfree) M",
 trunc(ROUND ((blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100 ), 2 ) /1024,2) "Waste spaceM"
FROM dba_tables
where owner = 'APPS' order by 5 desc;

3.2 检查收缩之后表相关对象的体积

Plain TextJavaScriptPythonJavaGoRubyTypeScriptHTMLCSSC/C++PHPC#ScalaShellDartRustKotlinSwiftPowerShellGroovy-ABAPActionScriptAdaAppleScriptArduinoASP.NET (C#)AtomAutoHotkeyAutoItBashBASICBatchClojureCoffeeScriptDDiffDockerEditorConfigElixirElmERBErlangF#FortranGraphQLHaskellHaxeJSONJuliaWeb App ManifestLaTeXTeX ConTeXtLessLispLiveScriptLuaMakefileMarkdownMarkup templatingMathematicaMathMLMATLABNASMnginxObjective-COCamlObject PascalPascalPerlPL/SQLPrologPuppetPureScriptQMLRRacketReact JSXReact TSXReasonreStructuredTextRSSSass (Sass)Sass (Scss)SmalltalkSplunk SPLSQLSSMLSVGTclTextileTypoScriptValaVB.NetVerilogVHDLvimVBAVisual BasicWiki markupYAMLXQueryXML
--表
SQL> select sum(size_MB/1024) size_gb from (select segment_name,bytes/1024/1024 size_MB from dba_segments where segment_name='EMPLOYEES');



--索引
SQL> select sum(size_MB)/1024 size_gb from (
select segment_name,bytes/1024/1024 size_MB from dba_segments where OWNER='EAPPRD' and segment_name in (
SELECT a.index_name
FROM all_ind_columns a, all_indexes b
WHERE a.index_name = b.index_name
AND a.INDEX_OWNER=b.OWNER
AND a.table_name = upper('EMPLOYEES')
and a.TABLE_OWNER='SCOTT'
group by a.index_name, a.table_name, b.uniqueness));

四、使用导入导出方式整理表碎片

通过使用 expdp 和 impdp 工具，可以将表的数据导出到一个文件，然后重新导入到原表中，从而消除碎片。以下是以Oracle RAC环境为例的详细操作步骤：

4.1 创建目录对象

首先，你需要在数据库中创建一个目录对象，指向你希望存放导出文件的文件系统目录。

Plain TextJavaScriptPythonJavaGoRubyTypeScriptHTMLCSSC/C++PHPC#ScalaShellDartRustKotlinSwiftPowerShellGroovy-ABAPActionScriptAdaAppleScriptArduinoASP.NET (C#)AtomAutoHotkeyAutoItBashBASICBatchClojureCoffeeScriptDDiffDockerEditorConfigElixirElmERBErlangF#FortranGraphQLHaskellHaxeJSONJuliaWeb App ManifestLaTeXTeX ConTeXtLessLispLiveScriptLuaMakefileMarkdownMarkup templatingMathematicaMathMLMATLABNASMnginxObjective-COCamlObject PascalPascalPerlPL/SQLPrologPuppetPureScriptQMLRRacketReact JSXReact TSXReasonreStructuredTextRSSSass (Sass)Sass (Scss)SmalltalkSplunk SPLSQLSSMLSVGTclTextileTypoScriptValaVB.NetVerilogVHDLvimVBAVisual BasicWiki markupYAMLXQueryXML
-- root用户创建导出目录
mkdir -p /export/20241008
chown -R oracle:oinstall /export/20241008

-- 以具有DBA权限的用户登录
CONNECT / AS SYSDBA;

-- 创建目录对象
CREATE OR REPLACE DIRECTORY expdir AS '/export/20241008';

-- 授予读写权限
GRANT READ, WRITE ON DIRECTORY expdir TO public;

4.2 导出表数据使用 expdp 工具导出表的数据。

假设你要导出 SCOTT 模式下的 EMPLOYEES 表，其中EMPLOYEES表为分区表。

Plain TextJavaScriptPythonJavaGoRubyTypeScriptHTMLCSSC/C++PHPC#ScalaShellDartRustKotlinSwiftPowerShellGroovy-ABAPActionScriptAdaAppleScriptArduinoASP.NET (C#)AtomAutoHotkeyAutoItBashBASICBatchClojureCoffeeScriptDDiffDockerEditorConfigElixirElmERBErlangF#FortranGraphQLHaskellHaxeJSONJuliaWeb App ManifestLaTeXTeX ConTeXtLessLispLiveScriptLuaMakefileMarkdownMarkup templatingMathematicaMathMLMATLABNASMnginxObjective-COCamlObject PascalPascalPerlPL/SQLPrologPuppetPureScriptQMLRRacketReact JSXReact TSXReasonreStructuredTextRSSSass (Sass)Sass (Scss)SmalltalkSplunk SPLSQLSSMLSVGTclTextileTypoScriptValaVB.NetVerilogVHDLvimVBAVisual BasicWiki markupYAMLXQueryXML
nohup expdp \'/as sysdba\' directory=expdir dumpfile=EMPLOYEES_%U logfile=expdp_EMPLOYEES_202410.log tables=SCOTT.EMPLOYEES compression=ALL parallel=8 cluster=n 2>&1 &

4.3 导入表数据

使用 impdp 工具将导出的数据重新导入到新的表中。这样可以确保数据在新的表中是连续存储的，从而消除碎片。

Plain TextJavaScriptPythonJavaGoRubyTypeScriptHTMLCSSC/C++PHPC#ScalaShellDartRustKotlinSwiftPowerShellGroovy-ABAPActionScriptAdaAppleScriptArduinoASP.NET (C#)AtomAutoHotkeyAutoItBashBASICBatchClojureCoffeeScriptDDiffDockerEditorConfigElixirElmERBErlangF#FortranGraphQLHaskellHaxeJSONJuliaWeb App ManifestLaTeXTeX ConTeXtLessLispLiveScriptLuaMakefileMarkdownMarkup templatingMathematicaMathMLMATLABNASMnginxObjective-COCamlObject PascalPascalPerlPL/SQLPrologPuppetPureScriptQMLRRacketReact JSXReact TSXReasonreStructuredTextRSSSass (Sass)Sass (Scss)SmalltalkSplunk SPLSQLSSMLSVGTclTextileTypoScriptValaVB.NetVerilogVHDLvimVBAVisual BasicWiki markupYAMLXQueryXML
nohup impdp \'/as sysdba\' directory=expdir dumpfile=EMPLOYEES_%U logfile=impdp_EMPLOYEES_202410.log tables=SCOTT.EMPLOYEES TABLE_EXISTS_ACTION=replace parallel=8 cluster=n 2>&1 &

4.4 收集统计信息

针对表碎片处理的表，收集统计信息

Plain TextJavaScriptPythonJavaGoRubyTypeScriptHTMLCSSC/C++PHPC#ScalaShellDartRustKotlinSwiftPowerShellGroovy-ABAPActionScriptAdaAppleScriptArduinoASP.NET (C#)AtomAutoHotkeyAutoItBashBASICBatchClojureCoffeeScriptDDiffDockerEditorConfigElixirElmERBErlangF#FortranGraphQLHaskellHaxeJSONJuliaWeb App ManifestLaTeXTeX ConTeXtLessLispLiveScriptLuaMakefileMarkdownMarkup templatingMathematicaMathMLMATLABNASMnginxObjective-COCamlObject PascalPascalPerlPL/SQLPrologPuppetPureScriptQMLRRacketReact JSXReact TSXReasonreStructuredTextRSSSass (Sass)Sass (Scss)SmalltalkSplunk SPLSQLSSMLSVGTclTextileTypoScriptValaVB.NetVerilogVHDLvimVBAVisual BasicWiki markupYAMLXQueryXML
begin
 dbms_stats.gather_table_stats(ownname => 'SCOTT' ,
 tabname =>'EMPLOYEES',
 method_opt => 'for all columns size repeat',
 estimate_percent => 30,
 no_invalidate => false,
 degree => 8,
cascade => true,
 granularity => 'ALL');
end;
/

五、最佳实践

• 定期检查：定期运行碎片检测脚本，监控表的碎片情况。
• 选择合适的时间：在低峰时段进行碎片整理，以减少对生产环境的影响。
• 备份数据：在进行任何表重组操作之前，确保有完整的数据库备份。
• 测试：在测试环境中先进行碎片整理操作，验证其效果后再应用到生产环境。
• 监控性能：整理后监控表的性能，确保碎片整理确实提高了查询性能。

写在最后

通过使用 expdp 和 impdp 工具，可以有效地整理表碎片，提高数据库性能。

Tags：oracle insert