A console game from this era consumes the exact same amount of RAM in exactly the same way every time they load. Unless a game is specifically written to use the extra RAM of the Arcade/Super cards then they never will. The card itself is such a minor upgrade to your PCE that you can't even tell its there unless you have compatible software. It changes the BIOS screen not one bit, still says 3.0. Even verifying the card is seated correctly can be tricky on a Duo since you get the same boot screen every time regardless. You can only tell it's not working right if, when you go to run an Arcade Card game, it gives you an error screen.
Arcade Card CDROM2 games (as defined by the logo on the spine) require the card and won't run without it.
Super CDROM2 games require the Super System Card to run but in some cases they will have slight improvements if you run with the AC. Examples of these games: 3x3 Eyes, Flash Hiders, Guliver Boy, Linda3. For the most part the AC enhancements with these games are mild, sometimes almost impossible to detect. Sometimes the difference is extremely obvious (the intro to 3x3 eyes). These game will all say Super on the spine but might have Arcade Card logos on the case or in the manual.
The Arcade Card is an AMAZING thing, but sadly it was very under utilized. Most of what it did at the time was make for extremely impressive Neo Geo ports compared to the other systems of the time. Compared to the SFC/MD/MCD versions of Fatal Fury Special the PCE one absolutely rules, no question. However, one can get an actual Fatal Fury Special MVS cart for $5 nowadays (at one point, $700) so the AC advantage has sorta expired in the SNK world. Also, they really only did this with the first Neo fighter boom. You can't get Viewpoint or Pulstar or Windjammers, just some old fighters that aren't even in the top ten best Neo Fighters list anymore...ok, maybe FFS is still top ten. World Heroes 2 certainly isn't though.
So the best AC software now would be the exclusives and that list is really short, with Sapphire at the top. That leaves us with six other games...Kabuki is decent, one of the better PCE fighters for sure, but not totally amazing. The rest are either mediocre wrestling, heavy in Japanese, or an uneven port of Strider.
The most technically impressive game on the system is Sapphire, and any PCE fan would want to see that. Sapphire however, while amazing, isn't very inspired and IMO isn't as good as several other PCE shooters like Gate of Thunder, Lords of Thunder, and the Solider games. If Sapphire were a 3DO game it would be totally worth getting, but the PCE is already overrun with amazing shooters.
So basically buy it to play Sapphire and compare how the intro of 3x3 Eyes runs with or without it. That's what most of us have done with our Arcade Cards.