Most optimisers cache the execution plan based on a hash of the query, hence reuse when using prepared statements vs not.

Oracle has an option to detect literals (CURSOR_SHARING) and essentially replace them with binds internally, in order to increase performance and stop the query pool getting filled up with the same statement(s).