the SM64 camera was absolutely groundbreaking in its day, but it really doesn't stack up against modern games. remember that part in wet-dry world where you have to walk across a narrow plank, and the only reason it's hard is because you're fighting the camera to walk in a straight line? that's a bit telling (and both Zelda games for the 64 had the same problem).
the GDC presentation by Journey's camera designer [1] makes a few very good points on this topic. modern 3D Mario games use hybrid camera systems that operate as fixed cameras most of the time, and it's a big improvement.
https://www.youtube.com/watch?v=C7307qRmlMI