If you've ever texted from an area with spotty cell reception, you'll notice that the iPhone doesn't tell you if an SMS failed -- it just expects you to check back in a few minutes to confirm the message went through. That's annoying. With SMSConfirmation, you can get notifications that your message actually has been sent.
I would imagine it'd get equally annoying to get delivery notifications for every single text you ever send, which is what's going to happen, because SMSConfirmation doesn't have an on-off switch. To disable it you have to completely uninstall the Cydia tweak. Still, if you're often losing texts and having to go back and re-send them, it's very useful.