I believe that one of the higher-ups at Nintendo (possibly Miyamoto?) was quoted as hating load times, and was fairly influential in keeping the N64 as a cartridge-based system. This is also why they've worked to optimize them when possible.
Metroid Prime was actually an interesting example, because those games got sneaky and hid the load times by preloading the next room when possible. Doors would pause after being shot before opening if the next room wasn't quite loaded yet, so instead of going to a loading screen and freezing, the game just has a sticky door.
God of War did this too, with certain segments of areas that were basically long foot paths between chunks that needed to be loaded. If you replay GoW or GoW2 and encounter a long (10-15s, probably scenic) path without any obstacles to navigate or enemies to murder, that was almost certainly a load buffering section.