SQL actually executed as planned in ORACLE? I wonder if SQL is definitely executed as execution plan.

I mean.. is the actual SQL execution process always consistent with execution plan, i.e. does the same SQL always give the same plan?
The short answer is that no, the same SQL doesn't automatically lead to the same execution plan.

For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups.

The "No" answer is the reason that Oracle introduced Outlines to implement Plan Stability. Many posts on the Oracle forums concern poor DBAs complaining about the fact that their system worked fine on Tuesday but that on Wednesday it had slowed right down. Further investigation showed that this was due to a critical change in the Execution Plan for a particular query.

What Plan Stability is basically doing is while accepting that some plans may be sub-optimal, the DBAs/Devs would prefer a stable system over one which might change catastrophically in an unpredictable fashion.

Some good information about this can be found here and here. The oracle-base site is one of the best sources of Oracle information and Tanel Poder is a very big hitter in the Oracle world.