We actually locked a table stats (TABLE1) with intention to prevent the nightly auto gather stats from picking up from stats gathering.
Understand that the nightly gather stats job kick start at 10pm daily (during wkday).
The below manual stats gathering will run about 4 hours.
If we trigger the below at 8.30pm, will the nightly gather stats (10pm) pick it up for stats gathering?
As the first sql will unlock the stats and lock it only after completion which is about 12.30am.
exec dbms_stats.unlock_table_stats('AC', 'TABLE1');
exec dbms_stats.gather_table_stats(ownname=>'ac',tabname=>'TABLE1',estimate_percent => 50,cascade=>true, method_opt => 'for all columns size 254');
exec dbms_stats.lock_table_stats('AC', TABLE1);
It depends, it may or it may not.
As the default GATHER_AUTO mode is basically automatic pilot, oracle may or may not end up gathering stats for your table while it is unlocked, since the two statistics jobs overlap with each other for about two hours as you said.
The most sensible thing to do would be to change time of your customized stats job, in my opinion. The force option mentioned above would work as well.
By the way, you do realize that you're forcing histogram creation for every single column in your table? Is this what you're trying to achieve? Histograms may end up hurting your performance more than helping if you don't know exactly where to build them.