If you're hunting for Super Famicom and Famicom games, Super Potato in Akihabara offers perhaps the largest selection in Japan. More obscure titles and titles scarce due to high demand can usually be found here.
The ubiquitous BOOKOFF stores you will see all over Japan also carry a much smaller selection of Super Famicom games in every store. You're less likely to find a specific title at a BOOKOFF, but they are worth stopping in and will offer a greater value than a dedicated retro videogame shop.