All of the gems have competitive usage in VGC in one form or another, due to the fast pace nature of the format. Although some are definitely more rare than others. Here's the most common uses.
Normal Gem: Fake Out, Explosion, Hyper Beam.
Explosion users, such as Metagross and Landorus, gain a huge power boost from a Normal Gem boosted Explosion. Fake Out gains a noticeable boost in power, especially with a Technician user like Ambipom (who also gains STAB). Download-boosted or Adaptability Porygon-Z's Hyper Beam gains an insane boost in damage output, probably OHKOing everything not Chansey, Blissey, or resistant.
Fire Gem: Fire Blast, Heat Wave, Overheat
Nothing special, just a one time boost to these moves. Fire Gem Overheat is one of the strongest attacks in the game.
Water Gem: Hydro Pump
Nothing special. Most likely to see it on a Rotom-W.
Grass Gem: Leaf Storm
Nothing special. Most likely to see it on a Rotom-C.
Electric Gem: Thunderbolt, Thunder
Commonly seen on Zapdos and Thundurus for the power boost to these powerful attacks.
Ice Gem: Blizzard
Although Ice attacks are common, you're not likely to see this gem unless it's being abused by a Hail team.
Flying Gem: HP Flying, Acrobatics, Hurricane
Acrobatics literally triples in base power with a Flying Gem, and remains doubled in base power after the Flying Gem is consumed. Hurricane is a niche move but useful in rain. HP Flying isn't seen much, but it is a secondary STAB move for Salamence, Zapdos, and Thundurus, and inflicts heavy damage to ever-present Fighting types.
Fighting Gem: Close Combat.
Common with Hitmontop, but also seen on other fighting types.
Poison Gem: Sludge Bomb, Sludge Wave (rare gem)
Almost never seen due to the rarity of Poison-type attacks not named Toxic. Most competitively viable Poison types have a secondary type (and secondary STAB) they can use instead.
Ground Gem: Earthquake, Earth Power
Nothing special about this gem
Psychic Gem: Psychic, Psyshock, Zen Headbutt (rare gem)
Nothing special about this gem, although Psychic attacks are quite useful in the VGC metagame due to the abundance of Fighting-type Pokemon.
Rock Gem: Rock Slide, Stone Edge
Nothing special about this gem.
Bug Gem: Bug Buzz, Bug Bite
Nothing special about this gem, although a Scizor's Technician STAB Bug Gem boosted Bug Bite will inflict extreme damage.
Dragon Gem: Dragon Pulse, Draco Meteor
VERY commonly seen on Latios, sometimes seen on Salamence or Garchomp. A Dragon Gem boosted Draco Meteor is going to inflict extreme damage to anything that doesn't resist it.
Ghost Gem: Shadow Ball (rare gem)
Few Pokemon can make use of a Ghost Gem boosted Shadow Ball, and all of them are ghost types to begin with. However Ghost Gem Shadow Ball is one of the deadliest attacks that can hit a Psychic type.
Dark Gem: Sucker Punch, Dark Pulse, Crunch
Hitmontop is one of the top abusers of a Dark Gem boosted Sucker Punch, but it's not the only Pokemon with Sucker Punch either.
Steel Gem: Meteor Mash, Bullet Punch, Iron Head
Metagross and Scizor can use Steel Gem Bullet Punch to inflict heavy damage in a pinch. Many Steel types also gain access to Meteor Mash or Iron Head, which can inflict substantial damage.
One of the major advantages of the gems is that you get the one time power boost of a Choice Band or Choice Specs, without the penalty of being locked into a single move. Often in the VGC environment, this is enough to net a KO that you would otherwise miss, and can easily decide the rest of the battle.