If you have an inefficient query, and you add an index to help out performance, should the query "instantly" start using the index?
Or do you need to clear out the Oracle "cache" (v$sql I believe) by running alter system flush shared_pool;?
As the DBA loves to answer, "it depends."
It depends on if Oracle thinks the index will help performance. If Oracle thinks the index isn't the best choice for the query, Oracle's not using it anyway.
It depends on whether you're using prepared statements. A prepared statement isn't reparsed during its lifetime, so if a running app uses a prepared statement you're trying to fix, you'lll need to restart the app.
Flushing the shared pool will force Oracle to reparse and reoptimize all statements (a hard parse), so if Oracle thinks the index will help performance, flushing the shared pool will do trick. However, it can also have far reaching consequences in a live production system -- causing a "parse storm", as every statement in use must be reparsed and reoptimized -- and should only be undertaken as a last resort.