This is a pretty old sci-fi standard - a femme fatale is seducing men left, right and centre, and has to be stopped. In this case, said femme is Hathor, ancient Egyptian fertility godess and, of course, goa'uld. However, the writers do manage to switch things up from the standard by making this quite a female-empowering episode. Sam and Janet [Fraisier], along with some other women on the base, manage to get their own way and eventually overcome Hathor's power. They do enlist the help of the de-brainwashed O'Neill, but he's the star of the show so it's forgivable, and besides, Sam and Janet still do most of the cool stuff.
While it's quite funny to see some of the men on base so infatuated with Hathor (in particular, general Hammond), there are some more serious moments such as her trying to turn Jack into a jaffa, and taking advantage of the drugged Daniel to 'acquire' some human DNA (surely she could have just done a cheek-swab?) to make some human-compatible symbiotes - she is, as it turns out, a goa'uld queen, and the first one seen or spoken of. Also in this episode we see a goa'uld healing sarcophagus, like the one that brought Daniel and Sha'uri back from the dead in the movie. Obviously, this is a hugely powerful device and as such it had to be destroyed before the end of the episode, which is a shame, but sarcophagi do crop up every now and again in future episodes (presumably because the set piece cost a lot to build!). Hathor, too, will return - at the end of the episode she's seen escaping through the stargate to Chulak, though it's not really clear how friendly she ever was with Apophis. They both disliked Ra, though, and they now both hate Earth, so maybe they'll bond over that.