Hi All,
Suppose I run an SQL query, once it is executed it will get into Shared pool.
Second time when I will execute the same query, this query will be in shared pool. So second time query execution will not need optimization and generate explain plan again. It will use explain plan used in first execution of the query.
Now suppose the explain plan used is not optimal plan and before running the query second time I created some indexes on tables used in query and those indexes can enhance query performance.
Is it right or wrong?
Does optimizer generate plan again even in Soft Parsing or not?
OR
Soft Parsing and Hard Parsing are differentiated only in term of "Loading the code into Shared Pool", rest steps are same for both Soft Parse and Hard Parse, Optimzer will execute and find plan again even in Soft Parsing?
Two posts on Hard Parse Soft Parse creating confusion:
http://www.dba-oracle.com/t_hard_vs_soft_parse_parsing.htm
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082