From: Dilip Kumar <dilipbalaut@gmail.com> > I think some other cause of contention on relation extension locks are > 1. CTAS is using a buffer strategy and due to that, it might need to > evict out the buffer frequently for getting the new block in. Maybe > we can identify by turning off the buffer strategy for CTAS and > increasing the shared buffer so that data fits in memory.
Yes, both Bhrath-san (on a rich-man's machine) and I (on a poor-man's VM) saw that it's effective. I think we should remove this shackle from CTAS.
The question is why CTAS chose to use BULKWRITE strategy in the past. We need to know that to make a better decision.
Basically you are creating a new table and loading data to it and that means you will be less likely to access those data soon so for such thing spoiling buffer cache may not be a good idea. I was just suggesting only for experiments for identifying the root cause.
