Some games seem to benefit more from the 3D effect than without. Super Mario 3D Land, I played through the entire thing with the slider on. Same with Mario & Luigi: Dream Team. The first one, because some of the areas were designed for 3D, the second one was because the game's art style was designed for it, giving it a rather pretty pop-up look. (Say what you will about Paper Mario: Sticker Star's gameplay, I haven't gotten to playing it yet, but I still am looking forward to it for the art style.)
Ocarina of Time 3D was nice with the 3D effect on, except that some parts of the game were not designed with it in mind. For example, when you fight Phantom Ganon, you can tell that he's not flying into the picture, but just shrinking in size while subtly moving towards the ceiling. I actually had to turn off the 3D effect for that fight just so that particular effect wasn't distracting. I'm playing Kingdom Hearts: Dream Drop Distance right now, and while I always make sure to turn on the 3D effect for the Dive segments, I tend to only use it for a brief bit in each world to see how the world looks, and then once I get to the point where I'm returning to rooms I've already been in, I'll turn it back off. The added depth perception isn't necessary, I get a more nostalgic feel since having the 3D effect turned off makes it look more like the PS2 games, and I've gotten used to the 3D effect to where I can notice a very faint ghosting effect during slower scenes even on my N3DS.
I do still like the looks I get from other people who've never played a 3DS before. Just the other day, I was playing KH in the car, and one of my younger sister's friends who was spending the night with us mentioned that the 3D effect was really cool, but she couldn't understand how I could play it when it was hurting her eyes to see it. Well, I gave it to her when I got to a moderately safe area, and let her try it with the eye-tracking camera actually aimed at her face rather than mine, and her jaw dropped at the full effect.
Come to think of it, eye-strain and headaches are a common complaint of the 3D effect. Has anybody still had that issue when playing a N3DS? So far, as long as the tracking camera has you figured out and focused on you correctly, I've never had a problem, nor has anybody in my family. (of course, after they damaged the hinge on my DS Lite, I don't let them play the 3DS for anything other than showing off a new game with something interesting, or letting them watch anime online. They only get to mess with it for maybe five minutes at most, just to be on the safe side. I've learned not to lend them anything for any period of time, so they aren't the best control group for this.)