hard parse takes long time randomly
- From: Lei Zeng <lzeng@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Jul 2010 12:53:29 -0500
Hi, list:
We have a database session which was running fine before; all of sudden spent
abnormal amount of time on hard parse. If we didn't do anything, it came back
fine again after certain time. Same code, same sql. However from the trace
file, seems that it spent quite different time doing 'db file sequential read'
on the same one datafile. Any idea what could be issue (shared pool, io
bottleneck, etc.)?
ASH report for good time:
Top Phases of Execution
Phase of Execution % Activity Avg Active Sessions
SQL Execution 99.20 0.99
Parse 14.08 0.14
Hard Parse 13.88 0.14
Top User Events
Event Event Class % Event Avg Active Sessions
CPU + Wait for CPU CPU 70.91 0.71
library cache lock Concurrency 12.68 0.13
db file sequential read User I/O 11.13 0.11
ASH report for bad time:
Phase of Execution % Activity Avg Active Sessions
SQL Execution 100.00 1.00
Parse 90.37 0.90
Hard Parse 90.33 0.90
Event Event Class % Event Avg Active Sessions db file
sequential read User I/O 87.44 0.87
CPU + Wait for CPU CPU 7.55 0.08
row cache lock Concurrency 3.32 0.03
Thanks very much,
Lei
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » hard parse takes long time randomly - Lei Zeng
