Hugo Kornelis digs into one type of cursor:
I have used the GLOBAL scope option, so that it is possible to step through the code one statement at a time. This option does not affect the execution plans used. The FORWARD_ONLY and READ_ONLY options are probably the most commonly used options with cursors, especially with static cursors. We will briefly look at the effect of other options for read direction and concurrency at the end of this post.
Click through to learn more about how cursors show up in execution plans and how you can tell, based on the execution plan of a static cursor, why it’s either really fast (relative to other cursor types) or really slow.