DIRECT clause not working



We have an insert query which is inserting aroung 3 Millions of data.

WOS Spill was happening so we added the DIRECT clause as a hint in the query.


Even after that , WOS spill is hapenning as checked from the logs (query_events table).


Please advise.




  • Arvind_KumarArvind_Kumar Community Edition User

    Why you dont use "Copy with direct ..on any node" ?

  • Hello Arvind,


    Thanks for the response.

    Here we are loading a table via insert command joining a few tables.

    I dont think copy command will work here.

    Let me know your thoughts on this.




  • Arvind_KumarArvind_Kumar Community Edition User

    Yes in your case you need to use Insert with direct.

    Are you using /*+ DIRECT */ directive.

    You can test this


    stop tuple mover

    then insert data for table and use table 

    storage_containers to check where your data is going?


    then check for table for which you are inserting data should go to ROS if you are using direct ditective for verification use table storage_containers

  • Hi Arvind,


    Yes I am using /*+ DIRECT */ directive.

    Query_events table shows me that the WOS Spill is happening.


    The problem is this is a huge insert (3+ million) and takes 3-4 hours in prod.

    I can not perform the test as it might affect other jobs also.


    Any other approach?




  • It sounds like the real concern is the 3-4 hours for the 3+ million row result set from the joins.   While it's worth understanding why the /*+direct*/ hint isn't working, that's not a big factor in the performance.  In fact loading to WOS is faster than loading directly to ROS, since there's no sort.


    Review the profiling data for the COPY to see where the time is being spent - this is probably more of a join performance issue.  And verify in the RESOURCE_POOL_STATUS table that the query budget for the resource pool is reasonable - at least a few GB if you are joining and loading.



  • Hi Sharon,


    Thanks for the response.

    You are right.

    Most of the time has consumed in the join steps.

    Query is taking memory of approx 41 GB (borrowing it from general pool.)


    I wanted to understand if the WOS spill is happening (as shown in the logs), won't it affect the overall performance of a query?




  • Arvind_KumarArvind_Kumar Community Edition User

    Question is here why WOS spill is happening while you are using DIRECT directive because when you use DIRECT directive data should go to ROS not WOS , so WOS spill should not happen.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file