oracle 插入时花费大量时间的数据泵表

bqucvtff 于 2023-01-20 发布在 Oracle

关注(0)|答案(2)|浏览(147)

首先，对不起我的英语，我是西班牙人，我不太擅长 *

我一直有一些麻烦导出和导入与数据泵之间的2个克隆数据库的一些模式（使单一的更新数据）。
首先，我尝试使用这个parfile创建一个expdp：

[oracle@ES-NAW-ORACLEVM-PRO backup]$ cat /u01/app/oracle/EXPORTS/FEDBPRE/EXP_FEDBPRE_para_CLON.par
directory=EXPORT_TEMP
dumpfile=EXP_FEDBPRE_%U.dmp
logfile=EXP_FEDBPRE.log 
schemas=AQADM,ASPNETOP,ASSISTANT,AUTOPUB,AUTOPUBOP,AVANTTIC,AVAN_SPA,DBAWKE,JAUSER,JURIMETRIA,JURIMETRIA_OLD,JURI_OPW,MONDB,NAGIOS,NASPOP,NTTAM,PREOP,PREOP_TEST,PRESENTATION,PRESENTATION_TEMP,PRESENT_ACT,PUB,PUBOP,SCOTT,TRACE,TRACEOP,WKE
FILESIZE=10g
parallel=4

然后：

expdp \'/ as sysdba\' PARFILE=/u01/app/oracle/EXPORTS/FEDBPRE/EXP_FEDBPRE_para_CLON.par

导出所有模式需要15分钟。我将DMP文件移动到克隆的服务器上，使用CASCADE选项删除数据库上的用户，并使用此parfile让导入运行一整夜：
一个二个一个一个
第二天，我看了它，花了大约4小时30分钟完成导入。我认为15分钟的导出时间太长了，所以我重新运行导入，看看实时发生了什么。
当它运行时，我正在查看它是如何在数据库上进行的，使用以下查询搜索处理它的会话：

select s.sid, s.module, s.state, substr(s.event, 1, 21) as event, 
s.seconds_in_wait as secs, substr(sql.sql_text, 1, 30) as sql_text
from v$session s
join v$sql sql on sql.sql_id = s.sql_id
where s.module like 'Data Pump%'
order by s.module, s.sid;

一开始，看起来一切都运转良好：

Import: Release 12.1.0.2.0 - Production on Mon Jan 16 13:44:55 2023

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "ignore=TRUE" Location: Parameter File, Replaced with: "table_exists_action=append"
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02":  SYS/******** PARFILE=/backup/FEDBPRE_bkp/IMP_FEDBPRE_para_CLON.par 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "PUB"."PUBLICATIONS"                        1.582 GB 23242881 rows
. . imported "ASSISTANT"."ASSIST_NODES_RESOURCES"        1.319 GB 74670288 rows

使用查询，我看到一切正常：

SID MODULE            STATE               EVENT                       SECS SQL_TEXT
----- ----------------- ------------------- --------------------- ---------- ------------------------------
  312 Data Pump Master  WAITING             wait for unread messa          1 BEGIN :1 := sys.kupc$que_int.r
   65 Data Pump Worker  WAITING             log file switch (chec         46  BEGIN    SYS.KUPW$WORKER.MAIN
   75 Data Pump Worker  WAITING             log file switch (chec         39  BEGIN    SYS.KUPW$WORKER.MAIN
  127 Data Pump Worker  WAITING             log file switch (chec         55  BEGIN    SYS.KUPW$WORKER.MAIN
  187 Data Pump Worker  WAITING             wait for unread messa          4 BEGIN :1 := sys.kupc$que_int.t
  187 Data Pump Worker  WAITING             wait for unread messa          4 BEGIN :1 := sys.kupc$que_int.t
  194 Data Pump Worker  WAITING             wait for unread messa          4 BEGIN :1 := sys.kupc$que_int.t
  194 Data Pump Worker  WAITING             wait for unread messa          4 BEGIN :1 := sys.kupc$que_int.t
  247 Data Pump Worker  WAITING             wait for unread messa          3 BEGIN :1 := sys.kupc$que_int.t
  247 Data Pump Worker  WAITING             wait for unread messa          3 BEGIN :1 := sys.kupc$que_int.t
  249 Data Pump Worker  WAITING             direct path sync               1 INSERT /*+ APPEND PARALLEL("TR
  301 Data Pump Worker  WAITING             log file switch (chec         55 INSERT /*+ APPEND PARALLEL("TR
  361 Data Pump Worker  WAITING             log file switch (chec         55 INSERT /*+ APPEND PARALLEL("AS
  371 Data Pump Worker  WAITING             direct path sync               2 INSERT /*+ APPEND PARALLEL("TR
  418 Data Pump Worker  WAITING             direct path sync               2 INSERT /*+ APPEND PARALLEL("TR
  428 Data Pump Worker  WAITING             PX Deq: Execute Reply          1 INSERT /*+ APPEND PARALLEL("TR

但是突然间，impdp在表ASSISTANT.ASSIST_NODES之后看起来像冻结了一样，我想知道发生了什么：

[...]
. . imported "ASSISTANT"."ASSIST_NODES_DA"               307.6 MB 4322248 rows
. . imported "ASSISTANT"."ASSIST_TYPES_CHANGED"          21.15 MB 1249254 rows
. . imported "ASSISTANT"."STR_RESOURCES"                 845.4 MB 10994245 rows
. . imported "ASSISTANT"."ASSIST_NODES"                  6.526 GB 74638678 rows

SID MODULE            STATE               EVENT                       SECS SQL_TEXT
----- ----------------- ------------------- --------------------- ---------- ------------------------------
  312 Data Pump Master  WAITING             wait for unread messa          1 BEGIN :1 := sys.kupc$que_int.r
   65 Data Pump Worker  WAITING             wait for unread messa          2 BEGIN :1 := sys.kupc$que_int.t
   65 Data Pump Worker  WAITING             wait for unread messa          2 BEGIN :1 := sys.kupc$que_int.t
   75 Data Pump Worker  WAITING             wait for unread messa          4 BEGIN :1 := sys.kupc$que_int.t
   75 Data Pump Worker  WAITING             wait for unread messa          4 BEGIN :1 := sys.kupc$que_int.t
  127 Data Pump Worker  WAITING             wait for unread messa          2 BEGIN :1 := sys.kupc$que_int.t
  127 Data Pump Worker  WAITING             wait for unread messa          2 BEGIN :1 := sys.kupc$que_int.t
  187 Data Pump Worker  WAITING             wait for unread messa          3 BEGIN :1 := sys.kupc$que_int.t
  187 Data Pump Worker  WAITING             wait for unread messa          3 BEGIN :1 := sys.kupc$que_int.t
  194 Data Pump Worker  WAITING             wait for unread messa          4 BEGIN :1 := sys.kupc$que_int.t
  194 Data Pump Worker  WAITING             wait for unread messa          4 BEGIN :1 := sys.kupc$que_int.t
  247 Data Pump Worker  WAITING             wait for unread messa          2 BEGIN :1 := sys.kupc$que_int.t
  247 Data Pump Worker  WAITING             wait for unread messa          2 BEGIN :1 := sys.kupc$que_int.t
  361 Data Pump Worker  WAITED KNOWN TIME   direct path sync               0 INSERT /*+ APPEND PARALLEL("AS
  428 Data Pump Worker  WAITING             wait for unread messa          2 BEGIN :1 := sys.kupc$que_int.t
  428 Data Pump Worker  WAITING             wait for unread messa          2 BEGIN :1 := sys.kupc$que_int.t

我搜索了SID = 361的会话，并正在执行以下SQL_ID = bh6qct41h9bth，文本为：

INSERT /*+ APPEND PARALLEL("ASSIST_NODES_METADATA",1)+*/ 
INTO RELATIONAL("ASSISTANT"."ASSIST_NODES_METADATA" NOT XMLTYPE) 
("NODE_ID", "AST_NODES_MT_TYPE", "XML_DATA")     SELECT "NODE_ID", 
"AST_NODES_MT_TYPE", SYS.XMLTYPE.CREATEXML("XML_DATA")      FROM 
"SYS"."ET$0169B1810001" KU$

看起来，数据插入是一个接一个地进行的，即使知道我在parfile上设置了PARALLEL = 8。我不知道这个表的XML_DATA列是否是导致它的原因。
为了寻找这种缓慢，我找到了以下Oracle文档： Doc ID 2014960.1其中，我可以看到从版本www.example.com到www.example.com的Oracle数据库企业版可能会受到错误19520061的影响。11.2.0.3 to 12.1.0.2 can be affected by Bug 19520061.
所以...他们提出了三个解决方案：

1. Upgrade the database to 12.2, when available, where issue is fixed.
- OR -
2. For earlier database releases please check Patch 19520061, if available 
for your platform and RDBMS version.
- OR -
3. Run the DataPump import job with an user other than SYS.

确认这个表是使impdp需要这么长的时间，我不得不告诉，我做了另一个导入不包括表，它花了20分钟左右。
我尝试了第三种方法，用户被授予DBA角色，但没有任何变化，所以......解决方案3被驳回。
我看过一些文章讨论增加表的并行度，但也没有效果。
我在考虑如何"强制" oracle插入带有特定parallel的行，但不在parfile中设置它，就像oracle这样进行插入，在table_name后面带有特定parallel（8）：

INSERT /*+ APPEND PARALLEL("ASSIST_NODES_METADATA",8)+*/ INTO 
RELATIONAL("ASSISTANT"."ASSIST_NODES_METADATA" NOT XMLTYPE)...

除了应用补丁程序或升级之外，还有什么解决方案可以减少此impdp时间？

oracle

来源：https://stackoverflow.com/questions/75135096/table-of-datapump-taking-so-much-time-while-inserting

2条答案

按热度按时间

piztneat1#

如果您想专注于优化表ASSISTANT.ASSIST_NODES_METADATA的XML列的缓慢导入，我相信如果将该列存储为SECUREFILE（如果还没有，但考虑到您遇到并要求解决的性能问题，我预计目前是BASICFILE），您将获得最大的好处。
主要有两种方法-您可以在源数据库上将XML列XML_DATA转换为SECUREFILE（在执行导出之前），或在目标数据库上执行。最好是在源数据库上执行，因为您应该执行一次，然后在每次必须执行模式的Exp/Imp时，不需要任何额外的步骤。2但这取决于你的申请和其他条件-你是否能够/被允许进行这样的更改。
如果转换为SECUREFILE，则应在目标数据库上完成-以下注意事项：

在某些版本的impdp实用程序中引入了新的转换参数TRANSFORM =LOB_STORAGE:SECUREFILE-检查，您的www.example.com版本中是否有此参数12.1.0.2
后来添加了其他类似的选项LOB_STORAGE=SECUREFILE-这两个参数都允许您在导入过程中（创建表段时）将LOB列（您的XML_DATA列）自动转换为SECUREFILE存储类型
另一种方法是单独导入所有模式和对象/它们的数据，并单独导入有问题的表ASSISTANT.ASSIST_NODES_METADATA（就像您已经做过的那样）。在导入表的过程中，首先只导入表的定义（使用METADATA_ONLY模式，或者只是从源数据库手动复制DDL），但是将CREATE TABLE语句的STORAGE AS部分修改为SECUREFILE
然后只导入该表的数据（DATA_ONLY模式）
您可以尝试使用参数文件中的QUERY参数并从parallel 8开始模拟并行导入（或任何其它优选的并行度）单独的X1 M19 N1 X进程，在QUERY参数中使用适当的筛选器值。对于QUERY值，应在NODE_ID或AST_NODES_MT_TYPE列上使用筛选器-分析这些列的内容，是否可以将表拆分为或多或少相同块或片段

和一些评论，只是关于导入过程：

在参数文件选项METRICS=YES和LOGTIME=ALL中进行设置-这样就无需坐下来观察导入过程如何运行-您将获得每个步骤（即每个已处理表）的时间戳和持续时间。您还将看到为每个表选择的数据泵“路径”-CONVENTIONAL或DIRECT_PATH插入路径
通过EXLUDE=TABLE_STATISTICS,INDEX_STATISTICS参数排除表和索引的优化程序统计信息也很重要（同时适用于导出和导入阶段）。此类排除的理由是-无论如何，最好的做法是在将所有对象导入新数据库后重新收集它们的优化程序统计信息。因此，导入统计信息是无用的，如果统计数据将被新的收集过程覆盖。但排除将在导入过程中确保可测量的时间量。
在您发布的其中一个输出中，显示了事件"log file switch (checkpoint incomplete)"- seems的长时间等待事件，在导入过程中，重做日志配置无法有效地赶上此类负载。请检查重做日志配置，并可能添加更多重做日志组和/或使用更大的重做日志成员。同时，无需尝试实现（有时）建议重做日志切换频率约为“每20分钟/每小时3次”-因为您的导入过程是罕见的事件。所以，需要一些平衡。
此外，作为一种有时使用的技巧方法-考虑在导入阶段将数据库切换到NOARCHIVELOG模式，并在导入完成后-切换回ARCHIVELOG模式并立即执行数据库备份
或者，如果您不想更改NOARCHIVELOG/ARCHIVELOG模式（因为这需要重新启动2次数据库），那么您可以将表空间（-es）（将存储导入的数据段）置于NOLOGGING模式，执行导入，将表空间切换回LOGGING模式，然后立即执行数据库备份，从而实现几乎相同的效果

几个URL关于，阅读和了解什么聪明的家伙建议关于数据泵导入优化：

P.S.我相信，您希望优化导入阶段，因为您计划定期执行它（例如，用源数据库中的最新数据刷新目标数据库）。这意味着，导出/导入练习计划是定期任务，而不是一次性任务？

赞(0）回复(0）举报 2023-01-20

wrrgggsh2#

感谢您的回答，非常清楚，信息量也很大。我试过在Source DB上将XML_DATA列更改为securefile（它是basicfile）：

SYS@FEDBPRE> alter table ASSISTANT.ASSIST_NODES_METADATA move lob(SYS_NC00004$) store as securefile(tablespace ASSISTANT_DAT) parallel 10;

Table altered.

Elapsed: 00:04:52.35

制作ALTER表花了5分钟，但它起作用了。
我找到的原始语句是这样的：

alter table ASSISTANT.ASSIST_NODES_METADATA move lob(SYS_NC00004$) store as securefile( tablespace ASSISTANT_DAT compress high deduplicate )  parallel 10;

但我不敢使用“compress high”和“deduplicate”选项，因为我在Oracle的一份说明中看到，我需要一个名为“Oracle Advanced Compression”的许可证：https://docs.oracle.com/database/121/ADLOB/adlob_smart.htm#ADLOB45944
无论如何，我尝试了这个不带这些选项的ALTER TABLE，并保持并行10，如果这可以增加INSERT的话。
我从de SourceDB只导出表，并将其导入DestinationDB与并行6和最后，我可以看到7从工作在INSERT（我想，因为我有7 DMP文件和表并行设置为10）：

SID MODULE             STATE               EVENT                       SECS SQL_TEXT
---- ------------------ ------------------- --------------------- ---------- ------------------------------
 187 Data Pump Master   WAITING             wait for unread messa          1 BEGIN :1 := sys.kupc$que_int.r
  75 Data Pump Worker   WAITED SHORT TIME   PX Deq Credit: send b          0 INSERT /*+ APPEND PARALLEL("AS
 191 Data Pump Worker   WAITING             PX Deq Credit: send b          0 INSERT /*+ APPEND PARALLEL("AS
 247 Data Pump Worker   WAITING             PX Deq Credit: send b          0 INSERT /*+ APPEND PARALLEL("AS
 314 Data Pump Worker   WAITING             PX Deq Credit: send b          0 INSERT /*+ APPEND PARALLEL("AS
 361 Data Pump Worker   WAITING             PX Deq Credit: send b          0 INSERT /*+ APPEND PARALLEL("AS
 423 Data Pump Worker   WAITED SHORT TIME   PX Deq: Execute Reply          0 INSERT /*+ APPEND PARALLEL("AS
 428 Data Pump Worker   WAITED KNOWN TIME   PX Deq Credit: send b          0 INSERT /*+ APPEND PARALLEL("AS

查找SQL_ID执行现在应用并行6：

INSERT /*+ APPEND PARALLEL("ASSIST_NODES_METADATA",6)+*/ INTO RELATIONAL("ASSISTANT"."ASSIST_NODES_METADATA" NOT XMLTYPE) 
("NODE_ID", "AST_NODES_MT_TYPE", "XML_DATA")     SELECT "NODE_ID", "AST_NODES_MT_TYPE", SYS.XMLTYPE.CREATEXML("XML_DATA")
      FROM "AVANTTIC"."ET$01A739BA0001" KU$

最终在1h39min结束，比上次导入减少了3h。
我将再次尝试使用filesize=4g（而不是10g）导出以生成更多DMP文件，并使用parallel=16导入以查看效果如何。
非常感谢Shane，您的帮助非常有用，感谢您花时间来做这件事：D

赞(0）回复(0）举报 2023-01-20

我来回答

oracle 插入时花费大量时间的数据泵表

2条答案

相关问题

热门标签

最新问答