hard parse takes long time randomly
From: Lei Zeng <lzeng_at_yahoo-inc.com>
Date: Fri, 23 Jul 2010 12:53:29 -0500
Message-ID: <62E2719E3944214188DE69DBB22560502310F378_at_SP2-EX07VS06.ds.corp.yahoo.com>
Hi, list:
Date: Fri, 23 Jul 2010 12:53:29 -0500
Message-ID: <62E2719E3944214188DE69DBB22560502310F378_at_SP2-EX07VS06.ds.corp.yahoo.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 23 2010 - 12:53:29 CDT