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:

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-l
Received on Fri Jul 23 2010 - 12:53:29 CDT

Original text of this message