英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论