Why Execution Plan changes

Execution plans can differ due to the following:


1. Schema change(like new index... etc) 

2. Data volume and statistic change.

3. Bind variable types and values change.

4. Initialization parameters change, following are examples:

shared_pool_size                —The memory region allocated for the library cache and internal control structures
db_block_size                   —The size of each data block
db_cache_size                   —The number of data buffers to allocate for the instance
sort_area_size                  —The amount of RAM reserved for each user to perform sorting operations
optimizer_mode                  —The default mode in which all SQL statements will be optimized for an execution plan(all_row, first_rows, first_rows_n)
db_file_multiblock_read_count   —The parameter that controls the number of blocks that are read asynchronously in full-table scan operations