The "V-SYNC when used with G-SYNC" issue has been the bane of my existence for years now in regards to trying to explain it to novices. I'm aware Nvidia can't change the "V-SYNC" label for in-game options, but you think they could conditionally change the label to something more accurate in the NVCP when G-SYNC is enabled.
But then the issue is, G-SYNC effectively IS V-SYNC when the framerate is above the refresh rate, whereas the V-SYNC option is only part of G-SYNC operation (and not traditional V-SYNC) when the framerate is within the refresh rate, so it's an extremely difficult thing to clarify, even if Nvidia attempted it.
Bottom line is, with G-SYNC on + V-SYNC on, V-SYNC = G-SYNC within the refresh rate, and G-SYNC = V-SYNC outside the refresh rate.
Depends on your max native refresh rate; the higher the refresh rate and the higher the framerate is above that refresh rate, the less noticeable tearing becomes. Once we ultimately reach 1000Hz displays, G-SYNC (or any form of sync) effectively won't be needed.
My suggestion is LLM "On" not "Ultra" with G-SYNC (as the only known difference between the two is the former doesn't have an auto FPS cap, and the latter does), and yes, both only really help for GPU bound scenarios. It's just a blanket recommendation in the cases where your framerate can't reach your cap (which usually means your GPU is maxed).