What could be the reason of low "Parse CPU to Parse Elapsd %"?
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.97 Redo NoWait %: 100.00
Buffer Hit %: 99.90 In-memory Sort %: 100.00
Library Hit %: 99.48 Soft Parse %: 98.56
Execute to Parse %: 91.34 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 16.97 % Non-Parse CPU: 99.65
Cursor_sharing is EXACT, the application already uses bind variables, and the Library Cache Hit ratio is 99.48%, percentage of soft parses is 98.56%!
Since,
Parse CPU = CPU time spent parsing
Parse Elapsed = Total time spent parsing
Parse Elapsed = CPU time + waiting time.
there is some waiting during parsing. About 83% of the parse time is spent for waiting!
Where could this waiting be coming from?
Maybe from dynamic sampling?
Oracle could be trying to read table or index blocks in order to dynamicly gather approximate statistics for those who has no or stale statistics?
