DIRECT clause not working

abhi112

Hello,

 

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.

 

Thanks!

Arvind_Kumar

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

abhi112

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

Arvind_Kumar

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

abhi112

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

Sharon_Cutter

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.

 

 

abhi112

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

Arvind_Kumar

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.