The thirty-eighth psalm is a song of confession of sin. In it, David is processing the raw pain of guilt and anguish over sin. Many Bible scholars see it as the companion of Psalm 51 and talk about it in terms of David's consequences after his adultery, murder and conspiracy are found out.
It very well could be that these circumstances were behind the psalm. There is however nothing to demand these specific circumstances exclusively as the occasion for its writing. In fact, I tend to think that this Psalm might be different from Psalm 51. Given David's focus on his enemies taunting him, and his struggle with not seeing God come to his aid, I think this might be referencing some other events in his life, perhaps not recorded for us. The title of the psalm indicates it was written "for the memorial offering". This was the firstfruits grain offering burned up before God on the altar recognizing God's ownership of all our blessings. It seems to be a strange rite to associate with mourning over sin... until you think about it more.
David knew that none of us "deserve" even common grace. This psalm reminds us that even the good blessings come to us "while we are yet sinners". It keeps perspective. It humbles us to know that God blesses us even when we confess our failures and ask for His mercies.
In confession we wait for God to move. That is what David does. He waits for The Lord to answer him, confident that in humble confession and repentance he is in the place where God will work. And that is something to remember as I confess my own sins.