如何优化直接路径插入(到子分区)?

huangapple go评论61阅读模式
英文:

How to optimize direct path insert (into subpartition)?

问题

We are using Direct-Path approach to fill one of our Data Marts. We are also pointing the exact subpartition to which insert should be done. Here is the sql:

INSERT /*+ APPEND_VALUES */ INTO DWH.DM_CORE_AC_SALDOS SUBPARTITION(SYS_SUBP7709) VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 )

All process is controlled by dbms_parallel_execute with parallel level of 40. That is there are 40 separate sessions doing their own insert to separate subpartitions. However, the process is taking too long to finish, in sessions windows we can see that sessions are not locking each other, but they have wait class 'User I/O' and wait state 'WAITING'. Here is the snapshot of how sessions windows looks during the process:

如何优化直接路径插入(到子分区)?

Is there anything we are doing wrong? Maybe we should have set some database parameters right or changed a table structure (initrans or pctfree)?

Edit: Almost all sessions have event 'db file sequential read'. I suppose the reason why it is running slow may be because of an index maintenance operation. I checked file# from dba_data_files and most of the show files allocated for index tablespace. I read that direct path insert does index maintenance in the end of insert so maybe this is a problem. In this case should I avoid using direct path insert?

英文:

We are using Direct-Path approach to fill one of our Data Marts. We are also pointing the exact subpartition to which insert should be done. Here is the sql:

INSERT /*+ APPEND_VALUES */ INTO DWH.DM_CORE_AC_SALDOS SUBPARTITION(SYS_SUBP7709) VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ) 

All process is controlled by dbms_parallel_execute with parallel level of 40. That is there are 40 separate sessions doing their own insert to separate subpartitions. However, the process is taking too long to finish, in sessions windows we can see that sessions are not locking each other, but they have wait class 'User I/O' and wait state 'WAITING' . Here is the snapshot of how sessions windows looks during the process:

如何优化直接路径插入(到子分区)?

Is there anything we are doing wrong? Maybe we should have set some database parameters right or changed a table structure (initrans or pctfree) ?

Edit : Almost all sessions have event 'db file sequential read'. I suppose reason why it is running slow may be because of an index maintenance operations. I checked file# from dba_data_files and most of the show files allocated for index tablespace. I read that direct path insert does index maintanance in the end of insert so may be this is a problem. In this case should I avoid using direct path insert?

答案1

得分: 2

使用直接路径写入会在相应表/分区上获取独占锁。一次只能有一个会话在对象上执行直接路径写入。

同时运行40个并行进程在这里不会有所帮助。你正在依次运行40个事务。如果你想使用直接路径写入,最好在同一个事务中运行;放弃 dbms_parallel_execute。

英文:

Using direct path writes takes exclusive locks on the corresponding table/partitions. Only one session at a time can do direct-path writes on an object.

Running 40 parallel processes won't help here. You're running 40 transactions one after the other. If you want to use direct path writes, you're better off running it all the same transaction; ditch dbms_parallel_execute.

huangapple
  • 本文由 发表于 2023年6月12日 18:41:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/76455845.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定