Ok, the documentation states that it lets the Activity manage the cursor's lifecycle. But I don't really see the point of it since when the activity is destroyed, any references to the newly created cursor should be also erased and then the cursor itself is left to perish in the next garbage collecting cycle. So why bother?
You should not rely on cursors being destroyed by the garbage collector... a cursor represents a significant amount of resources: all of the data held by the cursor, plus the connection to the content provider that owns the cursor which also means requiring that its process be kept in memory.
In more recent versions of Android, log messages are printed if a cursor's finalizer runs without being explicitly closed, because it is important for apps to close cursors when done with them.
Managed cursors take care of closing the cursor when the activity is destroyed, but they do more than that as well: they will be deactivated and requeried as the activities is stopped and restarted.
That said, at this point you should consider managed cursors to be deprecated. The new Loader API is a lot better, and has many improvements to the user experience of your app -- it ensures that all cursor operations are done off the main thread (so there are not glitches in your UI interactions and animations), and can propagate existing cursor data across activity instances when an activity is restarted due to a configuration change instead of having to reload the data.
If you need to run on older versions of Android than 3.0, you can use the v4 support library's implementation of Loader for those applications.