Except for some odd corner cases, it is very hard for a good engineer to beat compilers these days (they are very good now). Not to mention, tuning depends on the platform, and often the exact model of chip.
I keep hearing this for the last 20 odd years, yet I see evidence to the contrary each and every day. It is hard and honest work though.
Assembly has unique advantages like guaranteed execution duration for cryptography or exact optimization of hot-loop computation.
99% of the even performance-sensive code doesn't need assembly and compilers beat good engineers in this category since API surface is big and complicated. For 1% very hot code or very sensitive code due to execution duration / predictability / throughput, humans usually code better than compilers. This is due to specialization and limited DoF problems that can be solved and optimized to hell.
The original SSE (SIMD) extensions were actually written with this use case in mind. When they first came out, the compilers were awful. They've since caught up.