Refresh Projection locking escalation

ckotsidimos

Hi,

I have a table with 3.5Billion rows. I have created a projection and I need to manually refresh. Will this lead to a table lock? Is there a way to manage the resources of the refresh?

 

 

Adrian_Oprea_1

 

 During the refresh of a new projection an (X) Exclusive lock will be aquired on the projection, but no exclusive lock will be put on the anchor table, you can still query the table. 

To get a better view of the lock process use the v_monitor.locks and v_monitor.lock_usage tables to monitor them. 

 

 

 Also you can monitor the active refresh operations

select node_name,
       projection_name,
       refresh_status,
       refresh_start,
       refresh_duration_sec,
       runtime_priority
  from projection_refreshes
 where is_executing = 't';

See refresh operations done on a anchor table

select node_name, projection_name, refresh_status, refresh_start
  from projection_refreshes
 where anchor_table_name = 'table_name';

ckotsidimos

Is there a way to limit the resources of the projection refresh??? Put it in a pool or so?

 

I assume that I can also insert on the table

 

Thank you so much

Adrian_Oprea_1

  You can alter the refresh pool  MAXMEMORYSIZE(how much memory can the pool borrow from the GENERAL POLL - default value is unlimited).

  You can give it  fixed MEMORYSIZE value if you want some prealocated memory for this pool(not recomanded as it will be unused resources while the refresh is not in use).

 

  But normally when you do refreshes on initial loads they must be scheduled on off hours when only mainatainance sessions are up. 

 

 

 

 

ckotsidimos

The thing is that at the moment I do not have the option to have a down time. I will limit the resources on the pool and the change them back to the original