Usage of Library Cache during Parsing
Reading some articles online and in Oracle metalink I understood that
Library Cache Locks and Pins are needed while parsing(hard parse) phase of a sql execution in Oracle.Do we need the same during a soft parse too?
During the soft parse what Oracle does is to check syntatic and sematics of the statement and return result set using the existing plan,so I think access to objects cached in library cache is needed in Soft parse also to do the semantic and syntatic check.
Correct me if I am wrong here.
So I think the reason why the hard parse is much slower here is due to loading the object info into library cache(part of sharedpool) if the statement is parsed for the first time.
Please advice me if my assumtion is correct or am I misstating some things here.
Thanks,
Ravi.