DIRECT clause not working

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.

Thanks,

Abhi

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?

Thanks,

Abhi

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?

Thanks,

Abhi

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.