Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in Oracle through which I can see what are the alternative plans the optimizer has ever considered (and pruned because they are more expensive than the plan that the optimizer chooses to execute)?
Yes! This is called a 10053 trace in Oracle parlance. It's more formally known as "Tracing of Optimizer Computations".
Check out MOS doc 225598.1, or Jonathon Lewis' blog post here: https://jonathanlewis.wordpress.com/2014/05/23/10053-trace/
There's loads of hits for "10053 trace" so if you can't find what you need, it's probably out there in search-land.