After Bilam failed in his attempts to curse Bnei Yisroel, he hit upon another plan. "And he turned his face toward the desert" (Bamidbar 24, 1). Rashi quotes the Targum who explains that Bilam's intention was to mention Bnei Yisroel's transgressions and thus causing Hashem to "remember" them. This would provide fertile ground for a curse to be successfully placed on Bnei Yisroel. Therefore, "he turned his head toward the desert" i.e. he mentioned the desert: the location where Bnei Yisroel worshipped the golden calf.
Rav Wolbe (Shiurei Chumash) notes that we find a similar concept when the Torah discusses the laws of the sotah. The Torah refers to the sotah's korban as a "mazkeres avon" - a remembrance of her sin. If she is guilty, her sin is remembered, and the sotah water enters her body and wreaks havoc on it.
There is another aspect to the dangerous effects of remembering a sin. There are wise men who say that when one recites the vidui on Yom Kippur he should not spend too much time thinking about each individual sin. For should he begin reliving his transgression, he might G-d forbid think to himself - even subconsciously - that he really enjoyed what he did and it actually wasn't so bad after all. In such a situation, remembering the sin will cause him to desire it once again, and the vidui will turn into a cause for punishment.
The Mashgiach cited a source that there is a way to know when one has been forgiven for a specific transgression. When he completely forgets about that sin, and thoughts about it don't even enter his mind, he can rest assured that he has been forgiven. In a similar vein the Mishna in Pirkei Avos (2, 2) tells us that it is good for one to busy himself with both Torah and derech eretz, for together they cause "sin to be forgotten."
Although David Hamelech declared, "My sin is before me at all times" one must be on an extremely high spiritual level to be able to withstand the continuous remembrance of his sins. Moreover, even such a person probably should not be constantly thinking about his sins. Rather, the pasuk is teaching us that at all times one should be careful lest he come to the situation that brought him to sin in the first place.
Proper teshuvah for a transgression should not include a detailed analysis of the sin. One must regret the performance of the sin, and the more one thinks about it the more he might become susceptible to repeating his mistake once again. Instead, one should take precautionary measures to ensure that he does not place himself in the same situation that led him to sin in the first place.