Is megatexturing a distinctly perceptible thing? I'm always surprised when it's specifically brought up. In my mind, it's like, you only notice it (or you think you notice it) when someone tells you it's there. And even then. *squints very hard at some RAGE screens*
Megatexture has several noticeable impacts:
1) textures will 'blur in' when they come into view. How much depends on the amount of memory the engine is using to cache textures on your machine and how the camera moves in the game. How long they stay blurry largely depends on the amount of reading from disk that is allowed per frame and how fast your disk speed is.
2) it tends to limit the maximum resolution the textures. This is more of a tradeoff of detail vs. hard drive size and load speed though. There is no technical reason that all the textures can't be ginormous, it would just be really bad in practice.
3) Gigantic game installs. Seriously. Wolf is 40gb.